Re: optimal sql

From: Dag Arne Matre <dag-arne_at_matreweb.com.antispam>
Date: Wed, 11 Dec 2002 10:59:50 +0100
Message-ID: <D_DJ9.7512$hV6.127948@news2.e.nsc.no>

Christoph Seidel wrote:
> how can i find all records by pk in table 1 which are not in table 2
>
> is this efficient:?
>
> select * from t1 where pk not in (select pk from t2)

I often use outer joins here:

select t1.*
from t1 left outer join t2 on t1.pk = t2.pk where t2.pk is null

This is Oracle9 syntax, for Oracle8 it would be:

select t1.*
from t1, t2
where t1.pk = t2.pk(+) and t2.pk is null

D A Received on Wed Dec 11 2002 - 03:59:50 CST