Labels

Wednesday, 23 December 2015

SQL Server - Difference between IN and EXISTS

The below are the key differences between IN and EXISTS

S.No

IN

EXISTS

1


Returns true if specified value matches any
value in the sub query or a list.

Return true if sub query contain any rows.


2


The sub query will run first and then only outer query.

The Outer query will ran first and then only sub query.

3



IN is slower than EXISTS. The IN is used in the widely For Static variables
for eg: select name from table where ID in (Select ID from table2).
Exists is faster than IN.The Outer query will run first and then only inner query.
So it will reduce the over head. The Exists
is useful mostly in IF conditional statements.


Example:


SELECT id, [Name] FROM dbo.table a
WHERE id IN (SELECT id FROM dbo.table b) 

Example:

SELECT id, [Name] FROM dbo.table a
WHERE EXISTS (SELECT id FROM
dbo.table b WHERE b.id = a.id)

No comments:

Post a Comment