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.

Solarwinds

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.

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

Thanks Paulo for your wonderful contribution.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best
Next Post
SQL SERVER – Merge Operations – Insert, Update, Delete in Single Execution

Related Posts

6 Comments. Leave new

  • Paulo R. Pereira
    June 7, 2010 7:42 am

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

    Reply
    • 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

      Reply
  • Hi Paulo R. Pereira,

    Do not worry. The point is that SQL Server Engines knows what to do the best and I think that is demonstrated here.

    Kind Regards,
    Pinal

    Reply
  • 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.

    Reply
  • Novox is the best

    Reply
  • Allis depend on the logic

    Reply

Leave a Reply

Menu