Re: optimal sql

Christoph Seidel wrote:

>> SELECT
>> t1.key1,
>> t1.key2
>> FROM table1 t1
>> MINUS
>> SELECT
>> t2.key1,
>> t2.key2
>> FROM table1 t2

>
> is this more efficient than not in or not exists?

A long time ago in an Oracle version far far away.. it was more efficient for what I was doing. I never used an IN EXISTS again. :-)

Many of the problem queries that I'm often asked to look at by developers, have IN EXISTS or NOT IN EXISTS in them - so yes, like any other of these "mass operators" there are problems with it, if not used correctly.

The minus and intersection method usually works very well for me when dealing with large volumes of data in a warehouse-type environment (i.e. I deal with all the data in the table and not just subsets).

> and: this assumes that t1 and t2 have the same fields to select?

Yes. But that is also true for an EXIST check - the data types need to correspond, i.e. both needs to be char/varchar or date or numeric.

--
Billy