Wednesday, June 13, 2012

Outer join vs Not in

Using outer join to find records in table1 which are not in table2

In this case, I have records in table, playdb..pbt_cusip_pool ( with cusips not in mast..sec_mst) and I need to find them. This outer join works better than not in and not exists clauses.

When you do the outer join, it will show everything from the first table, although not matched with the second table, value from the second tbl will be null, and that is what we are tapping into by using where s.cusip = null :) try it out.. this is fun.

select p.cusip, s.cusip from playdb..pbt_cusip_pool p
left outer join mast..sec_mst s
on (p.cusip = s.cusip)
where s.cusip is null