Why EXISTS a better choice in general than IN?

 Posted by Rajnilari2015 on 2/4/2016 | Category: Sql Server Interview questions | Views: 454 | Points: 40
Answer:

EXISTS informs if a query returned any results as soon as a match is found.E.g.

       SELECT ColumnName(s)

FROM TABLENAME t1
WHERE
-- the statemet turn out true as soon as a match is found
EXISTS ( SELECT COLUMNNAME(S)
FROM TABLENAME t2
WHERE t1.ID = t2.ID
)


In comparison, IN compares one value to several values e.g.

        SELECT ColumnName(s)

FROM TABLENAME t1
WHERE t1.ID IN(SELECT ID FROM FROM TABLENAME t2)


Also EXISTS clause uses INDEX at the time of fetching records and is there by faster than IN which on the other hand does not.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response