Re: optimal sql

From: damorgan <damorgan_at_exesolutions.com>
Date: Mon, 09 Dec 2002 18:03:24 GMT
Message-ID: <3DF4DAE5.C10182C3@exesolutions.com>

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)

No version ... why?

I have found that in 8.1.7 NOT EXISTS seems to be the best solution.

But in 9.2 MINUS is often faster.

That is what EXPLAIN PLAN is for. So make sure your statistics are current by analyzing tables and indexes and test it out. There are at least 7 different SQL statements, all with different explain plans that will accompish the goal. Try them all.

Dan Morgan Received on Mon Dec 09 2002 - 12:03:24 CST