CONFUSION about NULL behavior in Left outer join

[aamkumar]

I have a confusin about using NULL option like the one shown below.

Select * from Table A TA,

Inner Join TabB TB on TB.Col1=TA.Col1.

Left outer join TabC TC.Col1=TA.Col1 and TC.Col2 isnull.

Where

TB.Col3=33

othercase is ...

Select * from Table A TA,

Inner Join TabB TB on TB.Col1=TA.Col1.

Left outer join TabC TC.Col1=TA.Col1

Where

TB.Col3=33

and TC.Col2 isnull


Why is the behavior different in both the cases....How is the Query one working or flowing by taking into consideration isnull value.


Please Advice..


thanks

 

[r937]

the ON clause determines which rows are joined, and if a match is not found, then in a LEFT OUTER JOIN the columns from that table are set to NULL and the row is returned anyway

so if you are looking for

LEFT OUTER JOIN TabC as TC
ON TC.Col1 = TA.Col1
AND TC.Col2 is null

then there must actually be a row in TabC that meets those conditions

if there isn't, then the TC columns are all set to NULL and the TA row is returned anyway because it's a LEFT OUTER JOIN

note that in that case TC.Col1 (the column being joined) will be NULL too

then after the join has beed decided, only then does the WHERE clause come into play

here's an article that illustrates the difference --
LEFT OUTER JOIN with ON condition or WHERE condition?

r937.com | rudy.ca