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

  • Ok … I got the answer for the question I asked .. try this query and you will see the difference

    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
    AND (t1.flag = 2 and t2.Flag = 1)

    In an outer join, the join filters (expressions) that you specify in the ON clause determine which rows of the subordinate table join to the dominant (or outer) table. The dominant table, by definition, returns all its rows in the joined table. That is, a join filter in the ON clause has no effect on the dominant table.

    If the ON clause specifies a join filter on the dominant table, the database server joins only those dominant table rows that meet the criterion of the join filter to rows in the subordinate table. The joined result contains all rows from the dominant table.

    Reply
  • Bandhu Banerjee
    May 20, 2010 12:53 am

    I find your site too good. This particular post realy helped me clear my confusions related to ON clause and WHERE clause in LEFT JOINS.

    Reply
  • This really good article with easy explaination. I got lot of confusion on joins populating wiered result, when I join more then 2 tables and put condition on (ON clause) and where clause.

    Reply
  • Hi Pinal,
    it would be great if you answer separately.

    point 1 )

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

    you saying that in above select statement , rows from table2 will be filtered where flag = 1 (i.e. 1 , 2 . 8 will be there ) before joining. Now left join of table1 and table2( rows: 1,2,8) will be there and will get the required result.

    Right ?

    point 2 )
    what if

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

    i could not get correct result based on previous assumption.
    please explain

    Reply
    • “point 2 )
      what if

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

      Even i have this question.. Did you get any solution for this?

      Reply
      • Notice above comments by Amit by Mar 27, 2010.
        “In an outer join, the join filters (expressions) that you specify in the ON clause determine which rows of the subordinate table join to the dominant (or outer) table. The dominant table, by definition, returns all its rows in the joined table. That is, a join filter in the ON clause has no effect on the dominant table.

        If the ON clause specifies a join filter on the dominant table, the database server joins only those dominant table rows that meet the criterion of the join filter to rows in the subordinate table. The joined result contains all rows from the dominant table.”

        You will get all rows of left table and rows from right table will base on ‘On’ filter. Here only t1.ID = t2.ID. Note “t1.Flag =1” apply to right table actually do nothing.

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

        vs.

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

        return different rows

      • Correction: Note “on t1.Flag =1″ apply to right table, decide which rows from the right table will return.

      • also run following queries

        –LEFT JOIN with WHERE Condition
        SELECT *
        FROM Table1 t1
        LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t1.Flag = 2
        GO

        ID Value Flag ID Value Flag
        —————————————————-
        1 First 1 NULL NULL NULL
        2 Second 1 NULL NULL NULL
        3 Third 2 3 Third 2
        4 Fourth 1 NULL NULL NULL
        5 Fifth 2 NULL NULL NULL
        6 Sixth 1 NULL NULL NULL
        7 Seventh 2 NULL NULL NULL
        —————————————————————

        –LEFT JOIN with Additional Condition on ON clause
        SEL–LEFT JOIN with Additional Condition on ON clause
        SELECT *
        FROM Table1 t1
        LEFT JOIN Table2 t2 ON t1.ID = t2.ID
        WHERE t1.Flag = 2
        GO

        ID Value Flag ID Value Flag
        —————————————————-
        3 Third 2 3 Third 2
        5 Fifth 2 NULL NULL NULL
        7 Seventh 2 NULL NULL NULL
        ————————————————————-

      • First query return

        ID Value Flag ID Value Flag
        —————————————————-
        1 First 1 1 First 1
        2 Second 1 2 Second 1
        3 Third 2 3 NULL NULL NULL
        4 Fourth 1 NULL NULL NULL
        5 Fifth 2 NULL NULL NULL
        6 Sixth 1 NULL NULL NULL
        7 Seventh 2 NULL NULL NULL
        —————————————————

        Second query return

        ID Value Flag ID Value Flag
        —————————————————-
        1 First 1 1 First 1
        2 Second 1 2 Second 1
        4 Fourth 1 NULL NULL NULL
        6 Sixth 1 NULL NULL NULL
        ————————————————

  • Priyadarshi Alok
    July 22, 2010 11:04 am

    good job

    Reply
  • hi

    i have one query
    i want to update the data in table 1 based on certain condition which must be satisfy by table 2 how should i do this please give small example
    waiting for u r reply

    thanks

    Reply
    • Generic approach

      update t1
      set t1.col=t2.col
      from table1 as t1 inner join table2 as t2 on t1.keycol=t2.keycol
      where t2.somecol=

      Reply
  • Thanks Pinal..

    Your article saved me from committing a big mistake while writing a query.

    Thanks for the good work

    Reply
  • A very small grammatical error but sadly as it is in the title, I thought I would bring it to your attention.

    I realise that English is not your main language and totally admire your ability to master it and write such fantastic articles using it. I hope that you are not offended to be informed of this slight error in what is a very misunderstood area of the language.

    “SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN”

    Should be

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

    The difference being the word Affects (Effects).
    Affect is a verb where one thing affects another.
    Effect is a noun where we see the effect that one thing had on the other.
    (Effect can also be an adjective depending on how it is used however affect is always a verb os it is doing something to something else).

    I think you have done a really good job of explaining the issue. I have often had to explain this weird behaviour to DB developers but in future I think I will simply point them at your example.

    Regards

    Dave

    Reply
  • David Bridge, you are right. Pinal Dave is a hero!

    But we do all make mistakes with grammar and punctuation at times, especially when there is no spelling or grammar checker. For example, you failed to punctuate the word ‘however’ correctly in your comment and misplaced the period as well. Your sentende should have read:

    (Effect can also be an adjective depending on how it is used; however, affect is always a verb os it is doing something to something else.)

    So it goes. I think the meaning of your comment as well as the meaning of Pinal Dave’s title are completely clear. So I would call your comment and mine both nitpicks.

    :>)

    Reply
  • SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag is null

    ———-

    How To Hanle Left Join …
    If i want want to eliminate Resultant Record when t2.Id is Not Available in Table t2 from the above Query Result

    Reply
  • hai Pinal…

    Please Give me Solution for the below Problem!!!!!!!!

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

    ———-

    How To Hanle the Query…

    If i want want to eliminate Resultant Record when t2.Id is Not Available in Table t2 and t1.Id is Not Null from the above Query Result

    Reply
  • hai Pinal…

    Please Give me Solution for the below Problem!!!!!!!!

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

    ———-

    How To Hanle the Query…

    If i want want to eliminate Resultant Record when t2.Id is Not Available in Table t2 and t1.Id is Not Null from the above Query Result

    Note : t1.Id is Nullable Column in table t1
    t2.Id is Not Nullable and Identity Column in table t2

    Reply
    • Did you try this?

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

      Reply
      • Yes i tried.. it returns everything….

        My Issue is , i want to eliminate records if ID is not available in table t2

        Note : t1.Id is Nullable Column in table t1
        t2.Id is Not Nullable and Identity Column in table t2

      • How to handle the above logic with optimized query.

        Thank you

        Regards
        janani.S

      • What does this return?

        SELECT *
        FROM Table1 t1
        INNER JOIN Table2 t2 ON t1.ID = t2.ID

  • Create table #temp1(EmpNo int Identity Not Null, BadgeID int Null )
    Insert into #temp1 values (1)
    Insert into #temp1 values (NULL)
    Insert into #temp1 values (3)

    Create table #temp2(BadgeID int Identity Not NUll, Description varchar(20) Not NUll)
    Insert into #temp2 values ( ‘Mixing’)
    Insert into #temp2 values ( ‘Simplex’)
    Insert into #temp2 values ( ‘Combing’)
    Insert into #temp2 values ( ‘Adidas’)

    Delete from #temp2 where Description = ‘Combing’

    ——————————————–

    Select * from #temp1

    Result:
    EmpNo BadgeID
    1 1
    2 NULL
    3 3
    ———————————————

    Select * from #temp2

    Result :
    BadgeID Description
    1 Mixing
    2 Simplex
    4 Adidas
    ——————————————–

    Select a.EmpNo , b.Description from #temp1 a
    left outer join
    #temp2 b on a.BadgeID = b.BadgeID

    Result:
    EmpNo Description
    1 Mixing
    2 NULL
    3 NULL

    From the above Result, i want to eliminate the last record EmpNo = 3, since its Corresponding BadgeID 3 is not in table #temp2.

    I want the result as :

    EmpNo Description
    1 Mixing
    2 NULL

    Reply
    • How do you get ‘NULL’ in the Description column if there isn’t such record in that column. May be you want the result as:

      EmpNo Description
      ————————–
      1 Mixing
      2 Simplex

      If it is so, then you can try out the query this way:

      SELECT a.EmpNo , b.Description FROM #temp1 a
      INNER JOIN #temp2 b ON a.EmpNo = b.BadgeID

      Regards,
      Raghav

      Reply
      • This is not i Expect….
        By the by,… EmpNo and BadgeID can’t be equal all the time..

        Any way, .. thanks…. for your try..

        I need to get TextDescription from Master table for the corresponding ID in transaction table.
        Thats all the concept.. if the corresponding ID is not available in Master table.,, we have to omit that record While fetching from Trasaction table.

        Thats it………..

    • Hi janani,
      Please try this….

      Select a.EmpNo , b.Description
      from #temp1 a INNER JOIN #temp2 b
      on (a.BadgeID = b.BadgeID OR A.BadgeID IS NULL)

      Reply
  • Hi Pinal,
    First of all anything, I really appreciate your efforts and authority on Sql server; I was doing oracle and recently I started playing hands on MS Sql; one of my friend referred your blog and believe me this is the first place I look if I have any question.

    Today, I had question using conditions within ON clause and I landed up here; This article is exactly my question and your explanation was superb but….. the part which I did not understood and challenge I am facing is ..

    What if I apply conditions on first table
    relating to your above example –
    SELECT *
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t1.Flag != 1 — or using t1.Flag = 2

    I expected the result to be:
    T1 T2
    Value Flag Value Flag
    Third 2 Third 2
    Fifth 2 Null Null
    Seventh 2 Null Null

    but its also returning all values of table-1 with Flag =1 and joining with table – 2.

    I did not run your tables but I have a similar example. Please guide me if I am missing anything; I thought using condition within ON clause improves performance since it reduces the number of rows to be joined based on the conditions. Please suggest and clarify.

    Thanks,
    Sam.

    Reply
  • Thanks Pinal ! Your blog is *always* helpful to me. I am learning T SQL after years of Oracle SQL. Thanks!
    Doreen

    Reply
  • A way to understand this is to think of an inner join as a filter only showing rows where the on condition match.

    A outer join keeps all records of the outer table and fill in data on the outer table based on the on condition. if there is not a match in the on condition data will not be joined on the outer table.

    This is how I understand it and mabe it can help others

    Reply
  • CREATE TABLE #DEL1(ID INT IDENTITY(1,1),VALUE VARCHAR(MAX),FLAG CHAR(1))

    CREATE TABLE #DEL2(ID INT IDENTITY(1,1),VALUE VARCHAR(MAX),FLAG CHAR(1))

    INSERT INTO #DEL1 VALUES(‘1ST’,1),(‘2ND’,1),(‘3RD’,2),(‘4TH’,1),(‘5TH’,2),(‘6TH’,1)

    INSERT INTO #DEL2 VALUES(‘1ST’,1),(‘2ND’,1),(‘3RD’,2),(‘4TH’,2),(‘5TH’,2)

    SELECT * FROM #DEL1
    SELECT * FROM #DEL2

    —————————————————————
    — LEFT JOIN with Additional Condition on ON clause
    SELECT *
    FROM #DEL1 A
    LEFT JOIN #DEL2 B ON A.ID=B.ID AND B.FLAG=1
    —————————————————————
    /* EQUILENT TO THIS QRY */
    SELECT *
    FROM #DEL1 A
    LEFT JOIN (SELECT * FROM #DEL2 WHERE FLAG=1) B ON A.ID=B.ID
    /* SO I OBSERVED THAT HERE ORDER OF EXECUTION IS FROM ,()PARANTHESIS,ON */
    —————————————————————

    /*
    NOTE : LEFT OUTER JOIN MEANS THAT MATCHING THE VALUES OF RIGHT SIDE TABLE AND UNMATCHING VALUES OF LEFT SIDE TABLE
    .SO NOW THE QRY WILL BE LIKE THIS

    */
    —————————————————————
    SELECT *
    FROM
    (
    SELECT * FROM #DEL2 WHERE FLAG=1
    ) A
    LEFT JOIN
    (
    SELECT * FROM #DEL1
    ) B ON A.ID=B.ID AND A.FLAG=1
    —————————————————————
    /* EQUILENT TO THIS QRY */
    SELECT *
    FROM #DEL1 A
    LEFT JOIN #DEL2 B ON A.ID=B.ID
    WHERE B.FLAG=1
    —————————————————————
    –CLEAN UP THE TABLES
    DROP TABLE #DEL1
    DROP TABLE #DEL2

    Reply
    • SELECT *
      FROM
      (
      SELECT * FROM #DEL2 WHERE FLAG=1
      ) A
      LEFT JOIN
      (
      SELECT * FROM #DEL1
      ) B ON A.ID=B.ID

      Reply
  • excellent !!!!! Blog , Thanks.

    Reply
  • Really Good Understanding…

    Reply
  • its simple…very nice example

    Reply

Leave a Reply

Menu