Monday, June 16, 2008

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

2 comments:

Simeon Pilgrim said...

The AND binds to the LEFT JOIN, while the WHERE binds to the SELECT.

Therefore the the first is saying matching any J row where the J.TheID is the same as the D.TheID and where J.TheID is NULL, which is not going to happen (well it wouldn't if J.TheID was a identity key).

Where-as the second statement says, join the J table, then filter the results to only show those that failed to find matching J rows.

Anonymous said...

ok I give up.

why is this?

Post a Comment