April 23, 2003 at 7:11 am
Hello...
I've noticed something in my queries for which I cannot find much if any documentation. Hopefully one of the local gurus can explain.
If I have this basic Master/Detail query:
/*
This one uses a standard Left Join and Where clause.
*/
SELECT
MAS.id_Master,
MAS.Master,
DET.Detail
FROM
tblMaster MAS
LEFT JOIN tblDetail DET
ON MAS.id_Master = DET.id_Master
WHERE
Master.SomeOtherProperty = 100
Assuming one of the Master rows(Master5) contains no detail items in the Detail table - the query will NOT return a row containing Master5 and NULL for the detail.
However, if I change the exact same query to this - I in fact DO get the Master row and NULL details.
/*
This one uses a modified Left join and no where clause at all. The "where" portion becomes part of the Left join
*/
SELECT
MAS.id_Master,
MAS.Master,
DET.Detail
FROM
tblMaster MAS
LEFT JOIN tblDetail DET
ON MAS.id_Master = DET.id_Master
AND Master.ID_SomeOtherProperty = 100
Can anyone explian why they behave differently ??? Or better yet - show me somewhere in the SQL Server documentation details on this behavior.
Thanks in advance - B
April 23, 2003 at 9:14 am
This isn't a SQL Server thing, it's a SQL thing. The join clause is used first to pick the rows, at which point the rows with Master(5) and null details will be in the result set. AFTER this, the WHERE clause is applied and the items with a Masterother property = 100 will the the ONLY items returned. IF one of these rows happens to have a null details, it should get returned. Otherwise it's dropped.
By moving to the ON clause, you keep that qualification in the join and not outside of the join.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply