- If you only come here for the pretty pictures or a laugh, this is not the post you are looking for.
- If you are a uber-geek feel free to leave a comment explaining in small words why I am a mental midget for not knowing this. (quoting appropriate reference works if desired)
- If you are a pragmatist join with me in the enjoyment of discovering a work-around for a problem.
it does not.
A LEFT JOIN with an AND clause looking for NULL is not the same as
a LEFT JOIN with a WHERE clause looking for NULL.
-- SQL Code follows
CREATE TABLE #Dude
( TheID INTEGER
, TheName VARCHAR(20)
)
CREATE TABLE #Jobs
( TheID INTEGER
, TheJob VARCHAR(20)
)
-- Dummy Data
INSERT INTO #Dude VALUES (1, 'Fred')
INSERT INTO #Dude VALUES (2, 'Freddy')
INSERT INTO #Dude VALUES (3, 'Fredrick')
INSERT INTO #Jobs VALUES (1, 'Boss')
INSERT INTO #Jobs VALUES (4, 'Slave')
-- 3 records returned - 1 with a match
SELECT D.TheID, D.TheName, J.TheID, J.TheJob
FROM #Dude D
LEFT JOIN #Jobs J On J.TheID = D.TheID
-- 3 records returned - even thought 1 has a match is is shown as having none - oops
SELECT D.TheID, D.TheName, J.TheID, J.TheJob
FROM #Dude D
LEFT JOIN #Jobs J On J.TheID = D.TheID AND J.TheID IS NULL
-- 2 records returned - only rows that do not match
SELECT D.TheID, D.TheName, J.TheID, J.TheJob
FROM #Dude D
LEFT JOIN #Jobs J On J.TheID = D.TheID
WHERE J.TheID IS NULL
-- Tidy Up
DROP TABLE #Dude
DROP TABLE #Jobs