During one of my interviews, I faced this question “what is the difference Between IN and EXISTS? Answer: Consider this example I have two tables TableA and TableB, where TableA contains Name of an Employees and TableB Contains the address code of the Employees,both can be joined by ID and ID2.
- IN
- Returns true if specified value matches any value in the sub query or a list.
- The sub query will run first and then only outer query.
Example:
SELECT id,
[Name]
FROM dbo.tablea
WHERE id IN (SELECT id
FROM dbo.tableb)
- EXISTS
- Return true if sub query contain any rows.
- The Outer query will ran first and then only sub query.
Example:
SELECT id,
[Name]
FROM dbo.tablea AS a
WHERE EXISTS (SELECT id2
FROM dbo.tableb
WHERE id2 = a.id)
Hope everyone like this this article.Please give views and suggestions
sivanandan
14 years agoIN & Exists which one is faster? When we have to use IN & Exists
Varun R
14 years agoHi Sivanandan,
Nice Question Sivanandan..
Exist 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. The IN is used in the widely For Static variables for eg: select name from table where ID in (Select ID from table2).
SUNIL
14 years agoGood one.Thanks