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.

Solarwinds

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.

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

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

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

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.

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

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

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

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)

Solarwinds
,
Previous Post
SQLAuthority News – Lots of SQL Server News – Tip of the Article
Next Post
SQL SERVER – UDF – Pad Ride Side of Number with 0 – Fixed Width Number Display

Related Posts

77 Comments. Leave new

  • if i want the result grouped by t2.Flag instead of passing it as default value then how to do?

    Reply
  • if i want the result grouped by t2.Flag instead of passing it as default value then how to do?
    for example
    i have
    tableA tableB
    id student id data year
    1 s1 1 d1 2010
    2 s2 2 d2 2010
    1 d3 2012

    now if i join i want the result like
    id student data year
    1 s1 d1 2010
    1 s1 d3 2010
    2 s2 d2 2012
    2 s2 NULL 2012

    Reply
  • thanx pinal, i was also facing some problem like the above example ,thanx for your explanation it helped me.

    Reply
  • As we see the result of inner join and left join with where clause are identical do we really need to use a left join here? Are they both not same?

    Reply
  • Sir.. good article. .clear explanation with very simple queries.

    Reply
  • Nice article Pinal & thank you for your clear & straight explanation !

    Reply
  • Rafael Lima - Brazil
    August 31, 2016 9:09 pm

    excellent explanation, now I understand this concept

    Thanx a lot

    Reply
  • nice article

    Reply
  • Great post Pinal. Quick question: what would be difference between:

    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1

    — LEFT JOIN with WHERE clause and OR IS NULL

    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
    WHERE t2.Flag = 1 OR t2.Flag is null

    It actually produced different results for me (I’m using AWS RedShift though)

    Thank you,
    Bruno

    Reply
  • Hi Pinal,
    quick question. If On clause evaluate first it means that is going to be faster? since the join will use less data?
    thanks

    Reply
  • Grate Help guys…appreciate a lot

    Reply
  • Hi Pinal,
    Firstly, thanks for a great explanation. I have a question related to this topic. Let’s say that the Flag = 1 condition is instead applicable on the left table T1 thus T1.Flag = 1 is added in the ON clause. My understanding was that since this condition is for the left table, only the records matching this condition would be selected from the left table (T1) for the JOIN however that’s not the case. Can you please provide some explanation on this?

    Reply

Leave a Reply

Menu