sql query execution using exists and IN

Shreyas Tg

11y1.2k

SELECT * FROM FAMILY_DETAILS WHERE EXISTS(SELECT EMPLOYEE_ID FROM EMPLOYEE_MASTER WHERE EMPLOYEE_ID='EMP001')

SELECT * FROM FAMILY_DETAILS WHERE EMPLOYEE_ID IN(SELECT EMPLOYEE_ID FROM EMPLOYEE_MASTER WHERE EMPLOYEE_ID='EMP001')

I have these two queries in sql server 2008 R2. Both will give same results. Actually from these above queries output is only two rows, But in case if i have large number of records in that scenario Which one is better to use? and why?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyaranjan K S11y ago
Accepted answer
Hi ,
The guideline of the MS SQL query performance says that if we needs "IN" clause, instead of using "IN" clause we must use the "EXISTS" clause because "EXISTS" clause improve the performance of the query.
IN Clause

Returns true if a specified value matches any value in a sub query or a list.
EXISTS Clause
Returns true if a sub query contains any rows.
Hence Exists is proven to be faster because if at least one single matching row is found by the query it returns true . But IN clause will wait for entire sub query to complete execution and then match against the result set
Ref:
http://sqlknowledgebank.blogspot.com/2012/11/in-exists-clause-and-their-performance.html
http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx
Thanks,
Priyan

If you found this useful Please Accept this as Answer.
Jignesh Trivedi11y ago
Hi,
EXISTS will be faster because once the SQL engine has found a record, it will return true.
With IN it will collect all the results from the sub-query before further processing.
Please refer
http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx
hope this will help you
Ravi Patel11y ago
Hi Shreyas,

EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true.
With IN it will collect all the results from the sub-query before further processing.