Re: Query brings system dowh, needs help

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); Received on Wed Mar 08 1995 - 22:38:22 CET