Fact About joins

When I want to filter records in a query, I usually put the condition in the WHERE clause. When I make an inner join, I can put the condition in the ON clause instead, giving the same result. But with left joins this is not the case. Here is a quote from the SQL Server documentation:

Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.

I still can’t fully grasp the difference. Perhaps you could explain this better? I am sure that many of your readers could benefit from this too.
Thank you.

Yoel,

You have asked very good question. Let us go through following example first. Download the script for this example here and execute in SSMS. I will explain the behavior as we go through the example. If any user does not want to read complete explanation, just go last paragraph of article which is in bold fonts.

USE AdventureWorks
GO-- Create Table1CREATE TABLE Table1(ID INT, Value VARCHAR(10), Flag INT)GO-- Populate Table1INSERT INTO Table1 (ID, Value, Flag)SELECT 1, 'First', 1UNION ALLSELECT 2, 'Second', 1UNION ALLSELECT 3, 'Third', 2UNION ALLSELECT 4, 'Fourth', 1UNION ALLSELECT 5, 'Fifth', 2UNION ALLSELECT 6, 'Sixth', 1UNION ALLSELECT 7, 'Seventh', 2
GO-- Create Table2CREATE TABLE Table2(ID INT, Value VARCHAR(10), Flag INT)GO-- Populate Table2INSERT INTO Table2 (ID, Value, Flag)SELECT 1, 'First', 1UNION ALLSELECT 2, 'Second', 1UNION ALLSELECT 3, 'Third', 2UNION ALLSELECT 8, 'Eightth', 1UNION ALLSELECT 9, 'Nineth', 2
GO-- Check the data in Table1 and Table2SELECT *FROM Table1SELECT *FROM Table2
GO-- INNER JOIN with WHERE ConditionSELECT *FROM Table1 t1INNER JOIN Table2 t2 ON t1.ID = t2.IDWHERE t2.Flag = 1
GO-- INNER JOIN with Additional Condition on ON clauseSELECT *FROM Table1 t1INNER JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1
GO-- LEFT JOIN with WHERE ConditionSELECT *FROM Table1 t1LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1
GO-- LEFT JOIN with Additional Condition on ON clauseSELECT *FROM Table1 t1LEFT JOIN Table2 t2 ON t1.ID = t2.IDWHERE t2.Flag = 1
GO-- Clean up tablesDROP TABLE Table1DROP TABLE Table2
GO

Let us see the example in parts. Let us see the result of INNER JOIN clause. First of all we will run the query without WHERE clause and ON clause. Our result of join without any clause has to be same as they are essentially same query.

Now we will run the same query with WHERE clause and ON clause and compare our result with earlier result and later resultset.

As mentioned earlier it really does not matter for INNER JOIN if WHERE condition is moved to ON clause. In case of JOIN if ON clause (evaluated first) of WHERE clause (evaluated later) the result is same.

Now let us see the example of LEFT JOIN. First we will run both the queries without WHERE clause and ON clause. Our result of join without any clause has to be same as they are essentially same query.

Now we will run the same query with WHERE clause and ON clause and compare our result with earlier result and later resultset.

Now let us understand ON clause it is apply before JOIN that is why it retrieves all the result of Table2 where there are Flag = 1 but it does not affect Table1 so it retrieves all the rows of table1. When WHERE clause is applied it applies to complete result so it removes all the rows from Table1 and Table2 where Flag is not equal to 1, essentially keeping flag = 1 rows from Table1 and Table2.