sql query execution using exists and IN

Question

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?

Priyaranjan K S · 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 Trivedi · Answer

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 Patel · Answer

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.