Re: Query brings system dowh, needs help

In <l.carl.pedersen-0803951638220001_at_kip-2-sn-470.dartmouth.edu> l.carl.pedersen_at_dartmouth.edu (L Carl Pedersen) writes:

>
>In article <3jf9ct$fqh_at_usenet.rpi.edu>, hoffma_at_rpi.edu (Adam Hoffman)
 wrote:
>
>>In article <3j1osf$95b_at_access1.digex.net>, gmisrod_at_access.digex.net
 (gmisassoc) says:
>>>
>>>
>>>I have two tables and I am running the following query:
>>>
>>>SELECT id FROM table1 WHERE id NOT IN
>>>(SELECT id FROM table2)
>>>
>>>Both tables are about 20,000 records each. This query is
>>>bringing a Sequent to its knees.
>>>
>>>What I want are all the ids that are in table1 but are not in table2.
>>>
>>>Any help is appreciated.
>>>
>>>Please respond to
>>>
>>
>>
>>Try using a NOT EXISTS clause such as:
>>
>>select id from table1
>>where not exists (select 'x'
>> from table2
>> where table1.id = table2.id)
>>
>>This will be especially efficient with indexes on the is columns.
>
>NOT EXISTS is usually faster than NOT IN - depending on relative table
>sizes etc., but i've had good results with a more obvious approach:
>
> select id from table1 minus select id from table2;
>
>as often as not this is even faster than the NOT EXISTS method.
>
>if you need to make an updateable view or something out of this, you
 can
>even do:
>
> select id from table1 where id in
> (select id from table1 minus select id from table2);
>

This is correct the minus will (in MOST cases) be a lot faster!! Craig Received on Thu Mar 09 1995 - 13:07:37 CET