SQL SERVER – DIFFERENCE BETWEEN IN AND EXISTS

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
  1. Returns true if specified value matches any value in the sub query or a list.
  2. 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
  1. Return true if sub query contain any rows.
  2. 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