SQL: Joins and Where clause, a mix to be aware (Logical Query Processing Phases)

Hi,

Recently I got engaged in a discussion, Which I feel the ones that really was an eye opener for me and some thing i thought trivial played an important role in my day today work.

The conversation went as

What is the effect of WHERE clause in a (INNER/LEFT/RIGHT) join, if moved to ON clause (with AND operator)

And this got me thinking …

And as it goes, I went back and tried couple of examples and thought to share the result with you all:

  1. INNER Join

    Query:

    USE AdventureWorks2014
    GO	
    
    SELECT * FROM Sales.SalesOrderHeader -- Count: 31465 Rows
    SELECT * FROM Sales.SalesPerson -- Count: 17 Rows
    
    
    SELECT * FROM Sales.SalesOrderHeader SOH
    INNER JOIN Sales.SalesPerson SP ON SOH.SalesPersonID = SP.BusinessEntityID 
    -- Count: 3806 Rows
    
    SELECT * FROM Sales.SalesOrderHeader SOH
    INNER JOIN Sales.SalesPerson SP ON SOH.SalesPersonID = SP.BusinessEntityID AND SP.BusinessEntityID = 276
    -- Count: 418 Rows
    
    SELECT * FROM Sales.SalesOrderHeader SOH
    INNER JOIN Sales.SalesPerson SP ON SOH.SalesPersonID = SP.BusinessEntityID 
    WHERE SP.BusinessEntityID = 276
    -- Count: 418 Rows
    
    

    And the execution plan for the above query, comes out as:

    CropperCapture[10]

    Result: No difference, so when the filter condition in INNER JOIN is either placed in the WHERE clause, or in the ON clause, it makes no difference to the result set.
    And as can be seen, in the execution plans of the both.

  2. LEFT/RIGHT Join
    Query:

    USE AdventureWorks2014
    GO	
    
    SELECT * FROM Sales.SalesOrderHeader -- Count: 31465 Rows
    SELECT * FROM Sales.SalesPerson -- Count: 17 Rows
    
    
    SELECT * FROM Sales.SalesOrderHeader SOH
    LEFT JOIN Sales.SalesPerson SP ON SOH.SalesPersonID = SP.BusinessEntityID 
    -- Count: 31465 Rows
    
    SELECT * FROM Sales.SalesOrderHeader SOH
    LEFT JOIN Sales.SalesPerson SP ON SOH.SalesPersonID = SP.BusinessEntityID AND SP.BusinessEntityID = 276
    -- Count: 31465 Rows
    
    SELECT * FROM Sales.SalesOrderHeader SOH
    LEFT JOIN Sales.SalesPerson SP ON SOH.SalesPersonID = SP.BusinessEntityID 
    WHERE SP.BusinessEntityID = 276
    -- Count: 418 Rows
    
    

    And the execution plan for the above query, comes out as:

    CropperCapture[12]

    Result: Difference !!!, What happened in here…

    The first query returned the expected result, returning me the whole of the “Sales.SalesOrderHeader” table rows joined with the “Sales.SalesPerson” of ID 276 only, rest coming out as NULL’s

    But, what happened in the second instance is, the joined happens on both the tables with no filter in place.

    And then on the joined table the filter kicks in, by the WHERE clause, resulting in a shortened result set of 418 rows only.


Summary: Now what happened in the second instance, from the above two examples is that the Logical Query Processing Phases have kicked in, defning the operation sequence and hence returning the result sets.

Just to summarize the Logical Query Processing Phases order in this post:

  1. FROM
  2. ON
  3. OUTER
  4. WHERE
  5. GROUP BY
  6. CUBE | ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

Thus be mindful of the above, and hope it helps in you query designs !!!

Leave a Reply

Your email address will not be published. Required fields are marked *

*