This blog post is part 2 of the earlier written article SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best by Paulo R. Pereira.

Paulo has left excellent comment to earlier article once again proving the point that SQL Server Engine is smart enough to figure out the best plan itself and uses the same for the query. Let us go over his comment as he has posted.

“I think IN or EXISTS is the best choice, because there is a little difference between ‘Merge Join’ of query with JOIN (Inner Join) and the others options (Left Semi Join), and JOIN can give more results than IN or EXISTS if the relationship is 1:0..N and not 1:0..1.

And if I try use NOT IN and NOT EXISTS the query plan is different from LEFT JOIN too (Left Anti Semi Join vs. Left Outer Join + Filter).

So, I found a case where EXISTS has a different query plan than IN or ANY/SOME:”

USE AdventureWorks
GO
-- use of SOME
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = SOME (
SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
UNION ALL
SELECT EA.EmployeeID
FROM HumanResources.EmployeeDepartmentHistory EA
)
-- use of IN
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID IN (
SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
UNION ALL
SELECT EA.EmployeeID
FROM HumanResources.EmployeeDepartmentHistory EA
)
-- use of EXISTS
SELECT *
FROM HumanResources.Employee E
WHERE EXISTS (
SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
UNION ALL
SELECT EA.EmployeeID
FROM HumanResources.EmployeeDepartmentHistory EA
)


When looked into execution plan of the queries listed above indeed we do get different plans for queries and SQL Server Engines creates the best (least cost) plan for each query. Click on image to see larger images.

Thanks Paulo for your wonderful contribution.

Reference : Pinal Dave (http://blog.SQLAuthority.com)