A posting by Denis Gobo called Teaser: Left Join..the SQL ego crusher prompted me to write a query that illustrates what happens when a condition is placed in a LEFT OUTER JOIN’s ON clause versus when a condition is placed in a WHERE clause (SQL 2008 syntax):
DECLARE @strings TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX)); INSERT @strings(val) VALUES ('When using a left outer join,'), ('you'), ('put the condition'), ('in the outer join clause and not'), ('in the where clause. In effect'), ('all rows are returned and no'), ('filtering out '), ('is done.'), ('You can see that all rows are '), ('returned, both primes and'), ('non-primes'); DECLARE @primes TABLE (id INT) INSERT @primes VALUES(2), (3), (5), (7), (11); -- a query with a proper left join SELECT p.id, s.val FROM @strings s LEFT OUTER JOIN @primes p ON s.id = p.id ORDER BY s.id; -- a query with the condition in the where clause SELECT p.id, s.val FROM @strings s LEFT OUTER JOIN @primes p ON 1 =1 WHERE s.id = p.id ORDER BY s.id; |
Kinda reminds me of those Mad Magazine fold-ins.
that's a cool example. I wanted to try it out… too bad you're a 2008 elitist!
Comment by Cressa — August 10, 2009 @ 12:36 pm
… yeah, how about filling us in who don't have 2008.
(or any version for that matter)
Comment by d. swart — August 10, 2009 @ 5:23 pm
Here's the results, don't know how the formatting will hold up:
id val
———– ——————————–
NULL When using a left outer join,
2 you
3 put the condition
NULL in the outer join clause and not
5 in the where clause. In effect
NULL all rows are returned and no
7 filtering out
NULL is done.
NULL You can see that all rows are
NULL returned, both primes and
11 non-primes
id val
———– ——————————–
2 you
3 put the condition
5 in the where clause. In effect
7 filtering out
11 non-primes
Comment by Michael J. Swart — August 11, 2009 @ 5:18 am
So nerdy… 🙂
Comment by Christin — August 11, 2009 @ 9:08 am
Christin, I aim to please…
Comment by Michael J. Swart — August 11, 2009 @ 9:21 am
[…] Of course if you’re dealing with LEFT OUTER JOINS, then it matters where the filter is placed. […]
Pingback by Ten Things I Hate to See in T-SQL | Michael J. Swart — August 26, 2010 @ 11:36 am
— Works on SQL Server 2005
DECLARE @strings TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX));
INSERT @strings(val) VALUES (‘When using a left outer join,’)
INSERT @strings(val) VALUES (‘you’)
INSERT @strings(val) VALUES (‘put the condition’)
INSERT @strings(val) VALUES (‘in the outer join clause and not’)
INSERT @strings(val) VALUES (‘in the where clause. In effect’)
INSERT @strings(val) VALUES (‘all rows are returned and no’)
INSERT @strings(val) VALUES (‘filtering out ‘)
INSERT @strings(val) VALUES (‘is done.’)
INSERT @strings(val) VALUES (‘You can see that all rows are ‘)
INSERT @strings(val) VALUES (‘returned, both primes and’)
INSERT @strings(val) VALUES (‘non-primes’)
DECLARE @primes TABLE (id INT)
INSERT @primes VALUES (2)
INSERT @primes VALUES (3)
INSERT @primes VALUES (5)
INSERT @primes VALUES (7)
INSERT @primes VALUES (11)
— a query with a proper left join
SELECT p.id, s.val
FROM @strings s
LEFT OUTER JOIN @primes p
ON s.id = p.id
ORDER BY s.id;
— a query with the condition in the where clause
SELECT p.id, s.val
FROM @strings s
LEFT OUTER JOIN @primes p
ON 1 =1
WHERE s.id = p.id
ORDER BY s.id;
Comment by Mark A — September 7, 2010 @ 2:18 pm
Hi Michael,
I have read your above article I am not getting why we are comparing above two queries…
First query having left outer join on the column “s.id = p.id ”
and second query have left outer join on the basis of 1=1 means all possible combination or We can say always true and after that in where close we are adding one more condition “where s.id = p.id” which will eliminate all the possible combination and it will keep only records which having “s.id=p.id”..so we can not expect same things from both the above query’s
I read your very nice article “Ten Things I Hate to See in T-SQL” and by following link I came on this article…
I thought you were trying to explain something like
— a query with the condition in the where clause
SELECT p.id, s.val
FROM @strings s
LEFT OUTER JOIN @primes p
ON s.id = p.id
AND s.id 5
ORDER BY s.id;
SELECT p.id, s.val
FROM @strings s
LEFT OUTER JOIN @primes p
ON s.id = p.id
WHERE s.id 5
ORDER BY s.id;
that this kind of queries may return different data as expected(both the queries will return same result set) .
If you have any example like above query which i have written may give different result set then please add in this article .
Comment by Rahul Bhargava — October 11, 2010 @ 8:31 am
You’re right Rahul,
You got the point of my article. We cannot expect the same things from both queries. In fact, in this article I try to demonstrate (or contrast) exactly how the query results are different when we qualify our results in either the WHERE clause or the LEFT JOIN clause.
By noticing the difference between the query results, my hope is that others who are not familiar with these distinctions will understand that difference.
In fact, I don’t know if you missed it, but if you run my queries, the results themselves provide further explanation.
Comment by Michael J. Swart — October 11, 2010 @ 12:14 pm
thanks….
By The way I enjoyed your article and Yesterday was my first day on ur blog.I hope ,I will gain much from this blog.
Comment by Rahul Bhargava — October 12, 2010 @ 3:07 am
I know this is a bit old but I just came across it while I was looking for a solution to a problem I was having. I’m curious about something – if your desired result is to only see the rows from strings table that have a value in the prime table – why aren’t you selecting from the primes table first and then joining to the strings table?
For example – this query
Can be re-written as this
And give you the same results as this
I’m running this in SQL 2005 and ran it like this
I looked at the execution plans. The query costs (relative to the batch):
– Insert queries for the strings table – 4%
– Insert queries for the primes table – 4%
– a query with a proper left join – 8%
– a query with the condition in the where clause – 15%
– a query with a reversed proper left join – 8%
Comment by scott h — August 7, 2012 @ 8:46 am
Hi Scott
Good point! If my goal was to only see the rows from strings table that have a value in the prime table then I could actually go further and use primes first and not bother with left joins at all:
Of course your reversed query is logically different than this one. For example if the primes table had an extra value in it, say 13, then your reversed query would give a different result set.
If my goal was to have this query perform well, I might have considered the variations you mentioned.
But actually, my only goal was to demonstrate the difference between putting a condition in the WHERE clause versus putting a condition in the join’s ON clause. In the article, without explicitly saying so, I’m actually warning against queries like the one I wrote. The idea is that if you’re going to use a left join, put the condition in the where clause, otherwise what you end up with is an inner join.
Thanks for visiting Scott!
Comment by Michael J. Swart — August 7, 2012 @ 9:09 am
Ha – I got caught up with the article saying “Left Join”. If I hadn’t read your article first and was looking to get your result set I would have went for an inner join. Clearly my brain isn’t warm yet. Thanks for the prompt reply.
Comment by scott h — August 7, 2012 @ 9:18 am