Question about left outer join...

[ZemogSC]

This is a pretty basic question, but I'm having trouble understanding it (Please be kind ). Given the following information:

tbl1 has 2,500 rows
tbl2 has 55,000,000 rows

the UPI for both tables is col1

Will these two SQL statements yield the same results?

sel a.c1,a.c2,a.c3
from tbl1 a left outer join tbl2 b
on a.col1 = b.col1 and b.col1 is null;

sel a.c1,a.c2,a.c3
from tbl1 a left outer join tbl2 b
on a.col1 = b.col1
where b.col1 is null;

If not could you explain why not.

Doing an explain on both queries (stats have been collected), query 1 will take 7 + hours, and query 2 will take just over 2 minutes. By those results, I have a feeling that I may not get the same results I'm looking for.

 

[rrrkrishnan]

Your first stmt is equivalent to this.
sel a.*,b.col1
from tbl1 a left outer join (select * from tbl2 b where b.col1 is null) b
on a.col1 = b.col1;

The condition on tbl2 is applied before the join takes place. This gives you all records in tbl1 with null values for columns in tbl2 (since the derived table b has zero records) even if there are records in tbl2 that has a match for records in tbl1.

In the second stmt the condition 'b.col1 is null' is applied after the join and you get only records in tbl1 where there is no match for it in tbl2.

Hope this helps.

 

[ZemogSC]

Thanks, that explained it very well. I was very confused yesterday