left join example | Michael J. Swart

August 10, 2009

Another LEFT JOIN example

Filed under: Technical Articles,Tongue In Cheek — Tags: left join example — Michael J. Swart @ 11:22 am

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.

-- Comments (13)