SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best

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

About these ads

11 thoughts on “SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best

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

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

  3. Pingback: SQL SERVER – Subquery or Join – Various Options – SQL Server Engine Knows the Best – Part 2 Journey to SQL Authority with Pinal Dave

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

  5. 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?

  6. nice statement on joins and sub…. i have an interactive sql server 2008 tutorial from LYNDA.COM every thing is explain as above.

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