SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause affects Resultset in LEFT JOIN

Today I received email from Yoel from Israel. He is one smart man always bringing up interesting questions. Let us see his latest email first.

Hi Pinal,

I am subscribed to your blog and enjoy reading it. I have a question which has been bothering me for some time now.

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 Table1
CREATE TABLE Table1
(ID INT, Value VARCHAR(10), Flag INT)
GO
-- Populate Table1
INSERT INTO Table1 (ID, Value, Flag)
SELECT 1, 'First', 1
UNION ALL
SELECT 2, 'Second', 1
UNION ALL
SELECT 3, 'Third', 2
UNION ALL
SELECT 4, 'Fourth', 1
UNION ALL
SELECT 5, 'Fifth', 2
UNION ALL
SELECT 6, 'Sixth', 1
UNION ALL
SELECT 7, 'Seventh', 2
GO
-- Create Table2
CREATE TABLE Table2
(ID INT, Value VARCHAR(10), Flag INT)
GO
-- Populate Table2
INSERT INTO Table2 (ID, Value, Flag)
SELECT 1, 'First', 1
UNION ALL
SELECT 2, 'Second', 1
UNION ALL
SELECT 3, 'Third', 2
UNION ALL
SELECT 8, 'Eightth', 1
UNION ALL
SELECT 9, 'Nineth', 2
GO
-- Check the data in Table1 and Table2
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
-- INNER JOIN with WHERE Condition
SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Flag = 1
GO
-- INNER JOIN with Additional Condition on ON clause
SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1
GO
-- LEFT JOIN with WHERE Condition
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1
GO
-- LEFT JOIN with Additional Condition on ON clause
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Flag = 1
GO
-- Clean up tables
DROP TABLE Table1
DROP 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.

I hope now this explanation is clear to Yoel. I am really waiting for feedback from my readers about this article. If you think this is interesting article, please share it on your social network and leave your comment here.

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