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
Hi Pinal,
As always you gave very simple and strait forward defination and example. Generally people would love to read strait forward defination which directly hit the mind. this is why I love to visit your blog very frequently.
Thanks for your precious support to the community.
Ritesh Shah
Beginner here, but I’m confused with the left join and the where clause vs ON clause. Are those reversed in the example?
I blogged about this myself about a 1½ ago, when I noticed the behavior, mostly because at that time I had recently been told by a “database expert” that the location of conditions in “on” clause didn’t matter. And then when my own experiences differed, I decided to look into why.
It can quickly cause problems for people writing SQL if they aren’t aware of the order that SQL gets “executed” in, because it isn’t an obvious situation when it doesn’t fail the execution. You just get a different result and all seems to be “perfect”.
There’s an excellent explanation for this in the book T-SQL Querying from Microsoft Press as well.
Checking for NULL will bring those missing rows back when it’s on the WHERE clause.
Unfortunately, I deal with dynamic SQL for a legacy reporting system, and it’s easier to swap out WHERE clauses for certain scenarios than it is to deal with conditions contained within an ON clause.
Just curious if you would generally recommend checking for NULL?
“the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.”
Does it means that JOIN statement is preferred to use in SQL Server also because of speed? (result set is smaller when other conditions will apply)
we can consider
– LEFT JOIN with WHERE Condition
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1
GO
as
– LEFT JOIN with WHERE Condition
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON (t1.ID = t2.ID AND t2.Flag = 1)
GO
so all the rows from t1 will be get.
Nice article
Hi Pinal, something I’ve previously come across that I thought was of relevance…
The differences described are apparent when considering the Logical Query Processing Phases. This is the order in which the conditions of a query are evaluated. These phases generate a series of virtual tables with each virtual table feeding into the next phase (virtual tables not viewable). These phases and their order are:
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
Because the outer join is applied after the on clause, any rows eliminated by the on clause will still be included by the outer join.
Hi all
I looked at actual execution plans that have been generated.
For
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag = 1
Table T1 is scaned
Table T2 is scaned with predicate t2.Flag = 1
then nested loop is applied with t1 as outer table and t2 as inner table, and logical operation applied with this nested loop is left outer join as expected
but for
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.Flag = 1
Table T1 is scaned
Table T2 is scaned with predicate t2.Flag = 1
and hash match operator is used as join with significant difference logical operation applied with this has match is inner join!!
So as far as i have concluded in both cases table2 will be scanned with predicate Flag = 1 but during joining logical operation in second case by optimizer will be switched to inner join instead left outer join due to WHERE condition. This way query becomes same as if we have written
SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON (t1.ID = t2.ID AND t2.Flag = 1)
Hi sir,
I read your post and enjoying it very much.
I have some questions, not able to find answer so finally I am asking with you.
I have one doubt regarding inline UDF(user defined function).
I am trying to see execution plan(Actual/Estimated) of inline UDF but it didnt display, why?
Also, could you please describe the performence impact for sequence of Inner Join and Left Outer join in a Select Query in inline UDF.
Regards,
Satyanarayan Agrawal
Software Developer
Hi Pinal ,
Thank you for this information . In your example you have mentioned something like this.
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
Both of these will give you same result set, but is there any performance difference between the two?
Regards,
Joxin
For the inner join case, the results and the actual query plans are identical, so there should be no difference in performance.
That being said, my stylistic preference is to put filters in the WHERE clause. Then when I see filters in a JOIN clause, I expect to see an outer join and a reason for the filter being there instead of its normal location.
Nice Blog….
keep it up.
Hi Pinal,
I saw ur blog its very helpful indeed, but i have some questions:
I just altered ur left join and checking for is NUll in the same join.
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID AND t2.Flag is null
it results in some weired output. it displays everything as NULL from table2 . Any reason ??
dear pinal,
i want to know ,when we are joining more than three or more tables .In this scenario i want to use the left join on particular two table.In such condition what is the rule for putting join.
dear sir,
thanks a lot….
its realy a valuable information
sagar
Hello,
I am very must confucius in join can you help me it will be grate oner for me.
Regards
Rakesh shrivastava
Very nice and interesting too!!
Hi Pinal,
This was realy nice Obeservation Article i found and Clear Explanation.
Thanks for this article :)
Sadanand
hi sir,
I saw your blog it’s really very nice.
nd this article is very nice and it contain clear explanation.
If your explaination is correct , why folling query comes back with all rows from the table1
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
AND (t1.flag = 1 and t2.Flag = 1)
As per your explaination , it should bring only
1,2,4,6
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.