WHERE clause usage on Left outer join

Database
Enthusiast

WHERE clause usage on Left outer join

Hi,

I have a question about the usuage of filter condition in WHERE clause vs ON clause of LOJ. My query is -

SEL * FROM

(SEL T1.COL1,T2.COL2,T2.COL4

FROM TABLE1 T1

LEFT OUTER JOIN

TABLE2 T2

ON

T1.COL1=T2.COL1

WHERE T1.COL3=1 AND T2.COL3=1) A

WHERE T2.COL4 IS NULL

is returning expected results, but if I change the WHERE to the ON clause of the LOJ part (like below) , it is returning incorrect records  ie., records with T1.COL3<>1 as well.

SEL * FROM

(SEL T1.COL1,T2.COL2,T2.COL4

FROM TABLE1 T1

LEFT OUTER JOIN

TABLE2 T2

ON

T1.COL1=T2.COL1

AND T1.COL3=1 AND T2.COL3=1) A

WHERE T2.COL4 IS NULL

Can someone please explain the difference and let me know which query is correct.

Thanks!

2 REPLIES
Enthusiast

Re: WHERE clause usage on Left outer join

Hi,

The where clause will be applied only after records are fetched. If you apply ON then the join will take into consideration only those records that have those condition satisfied.

Check the below link,it will help.

http://pauldhip.blogspot.dk/2014/05/sql-join-condition-on-where-clause-or.html

Re: WHERE clause usage on Left outer join

Hi Raj,

The Significance of using Where and ON Clause makes difference only with left outer joins.

Outer Join (Assume Left) would join with all value from left table and then filter values. Depending on where the condition is mentioned, the filtration would happen differently.

In Join: Before joining.Thus there are possibilities of having null values from the right table.(2nd Query)

In where: After joining. Thus, the whole records (after join) would be filtered. (1st Query)

Hope you are clear now.