This is followup post of my earlier article SQL SERVER – Convert IN to EXISTS – Performance Talk, after reading all the comments I have received I felt that I could write more on the same subject to clear few things out.
First let us run following four queries, all of them are giving exactly same resultset.
USE AdventureWorks
GO
-- use of =
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
-- use of in
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID IN ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
-- use of exists
SELECT *
FROM HumanResources.Employee E
WHERE EXISTS ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
-- Use of Join
SELECT *
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress EA ON E.EmployeeID = EA.EmployeeID
GO
Let us compare the execution plan of the queries listed above. Click on image to see larger image.
It is quite clear from the execution plan that in case of IN, EXISTS and JOIN SQL Server Engines is smart enough to figure out what is the best optimal plan of Merge Join for the same query and execute the same. However, in the case of use of Equal (=) Operator, SQL Server is forced to use Nested Loop and test each result of the inner query and compare to outer query, leading to cut the performance. Please note that here I no mean suggesting that Nested Loop is bad or Merge Join is better. This can very well vary on your machine and amount of resources available on your computer.
When I see Equal (=) operator used in query like above, I usually recommend to see if user can use IN or EXISTS or JOIN. As I said, this can very much vary on different system. What is your take in above query? I believe SQL Server Engines is usually pretty smart to figure out what is ideal execution plan and use it.
Reference: Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
For a 1-1 relationship, these queries are equivalent, but they aren’t generally all equivalent:
The = case will fail if multiple rows are returned in the subquery and in the join case, you will get all valid combinations of rows.
Hi Pinal! Great article!!!
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 than 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
)
Sorry… I forget the WHERE of EXISTS… :)
ANY, SOME, IN and EXISTS have the same query plan!!! :)
Nice article, i have rarely used the = in a subquery context, always gone with IN or EXISTS.
hey and how about subquerys that use the ‘=’ operator after the inner query has been completed such as this:
select table2.id,table2.title, table2.cid, table3.title, table3.iid
from
table3,
(
select moduleid, title, cursoid from table1
)as table2
where table2.id = table3.id
i think this might be a more competitive approach of subqueries against the join, exist and in methods
what do you think?
oh and i made a mistake! the column names in table1 should match the names of columns in table2 XD
sorry guys!!
nice statement on joins and sub…. i have an interactive sql server 2008 tutorial from LYNDA.COM every thing is explain as above.
The Joins ae not equivalent as well. These are different in some cases LEFT JOIN as well. How do we know it is INNER or LEFT JOIN