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)”
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 (https://blog.sqlauthority.com)
6 Comments. Leave new
Hi Pinal,
Sorry, how I said at “SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best”, I forgot the WHERE clause of EXISTS, and it’s make a wrong query plan… then “I DIDN’T found a case where EXISTS has a different query plan than IN or ANY/SOME”…
The sql server engine does things internally, there is a book which i am reading internals of sql server 2008 by kalen delaney and co… It is really interesting, points out things as to how engine behaves in different situtations.
Novox is the best
Allis depend on the logic