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:

   

   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:

   

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