SQL SERVER – Subquery or Join – Various Options – SQL Server Engine Knows the Best – Part 2

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 (http://blog.SQLAuthority.com)

7 thoughts on “SQL SERVER – Subquery or Join – Various Options – SQL Server Engine Knows the Best – Part 2

  1. 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”…

    Like

    • Hi Paulo,

      In this example where we are working on complete table, SQL Server picks up the plan which is optimal so I think the point is fine.

      If we change the query the way you are suggesting it may once again give the plan like we have in our part one.

      Good observation.

      Kind Regards,
      Pinal

      Like

  2. 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.

    Like

  3. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s