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)
77 Comments. Leave new
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.
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.
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
“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?
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
————————————————
good job
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
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=
Thanks Pinal..
Your article saved me from committing a big mistake while writing a query.
Thanks for the good work
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
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.
:>)
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
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
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
Did you try this?
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t2.Flag is null
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
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
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)
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.
Thanks Pinal ! Your blog is *always* helpful to me. I am learning T SQL after years of Oracle SQL. Thanks!
Doreen
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
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
SELECT *
FROM
(
SELECT * FROM #DEL2 WHERE FLAG=1
) A
LEFT JOIN
(
SELECT * FROM #DEL1
) B ON A.ID=B.ID
excellent !!!!! Blog , Thanks.
Really Good Understanding…
its simple…very nice example
if i want the result grouped by t2.Flag instead of passing it as default value then how to do?