a JOIN without a NULL by any other name.. is different

• 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.

In the following example I was expecting query 2 & 3 to return the same result.
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