Joins with criteria

[thorny00]

Using SQL2000. I can not for the life of me figure out why the first query won't work properly, but the second does. Can someone please explain?? Thanks in advance!!

This query that drops 10 recs when you add the left outer join:
SELECT dbo.tblProgram.CliProdID, dbo.tblProgram.PIN, dbo.tblSupplemental.SupportID, dbo.tblSupplemental.FldValue
FROM dbo.tblProgram left outer JOIN
dbo.tblSupplemental ON dbo.tblProgram.PIN = dbo.tblSupplemental.PIN
WHERE (dbo.tblProgram.CliProdID IN (1166, 848)) AND (dbo.tblSupplemental.SupportID = 146)

This query includes the criteria in the join rather than the where clause :

SELECT dbo.tblProgram.CliProdID, dbo.tblProgram.PIN, dbo.tblSupplemental.SupportID, dbo.tblSupplemental.FldValue
FROM dbo.tblProgram left outer JOIN
dbo.tblSupplemental ON dbo.tblProgram.PIN = dbo.tblSupplemental.PIN and dbo.tblSupplemental.SupportID = 146
WHERE (dbo.tblProgram.CliProdID IN (1166, 848))

 

[vongrunt]

Because WHERE applies after JOIN.

That is irrelevant for inner joins - but not for outer ones.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[cmmrfrds]

Because of the LEFT OUTER join, this
"AND (dbo.tblSupplemental.SupportID = 146)"
effectively eliminates the cases where the SupportID is NULL.

This would include the null cases.
WHERE (dbo.tblProgram.CliProdID IN (1166, 848)) AND (dbo.tblSupplemental.SupportID is NULL or dbo.tblSupplemental.SupportID = 146)

In the 2nd cases, this
"WHERE (dbo.tblProgram.CliProdID IN (1166, 848))"
is only looking at the left side so will include the cases where the record is null on the right side.