SQL Not in vs Join example

Obviously SQL join is more efficient than the not in clause. Here is an example of both.


Most inefficient Not in query :

select count(*)
from
wire_instr w
where
w.short_code
not in (select short_code
from nostro_accts)


Slightly better Not Exists query :

select count(*) from
wire_instr w
where not exists(
select 1
from nostro_accts n where
n.short_code=w.short_code)


Finally the Join query :

Join infact creates a new table which

select w.short_code as wshort,n.short_code as nshort from wire_instr w
left outer join
nostro_accts n
on n.short_code=w.short_code
where n.short_code=null

Enjoy
-Sunny