Re: MINUS works, but is there a faster way?

Hi

If the tables have a primary key it is often a better idea to rewrite the SELECT as follows:

SELECT * FROM schema1.tablename s1
WHERE NOT EXISTS
( SELECT 1 FROM schema2.tablename s2
  WHERE s1.pkey = s2.pkey
);

Yours sincerely
Torben Jeppesen

John Mason Jr wrote in message <75sl3g$avi$1_at_winter.news.rcn.net>...
>Hi,
>I have two identical tables, same data types and columns. Currently I use
>the following query to select the records that are in the first table but
>not the second.
>
>INSERT INTO schema2.tablename
>SELECT * FROM schema1.tablename
>MINUS
>SELECT * FROM schema2.tablename
>
>There are 10 columns in each table and there is a composite index on 4 of
>the columns. The other six fields contain data that is similar, so I don't
>think adding another index would help.
>
>I tried
>
>select * form schema1.tablename,schema2.tablename
>where
>schema1.tablename.column1<>schema2.tablename.column1
>and
>schema1.tablename.column2<>schema2.tablename.column2
>and
>schema1.tablename.column3<>schema2.tablename.column3
>and
>schema1.tablename.column4<>schema2.tablename.column4;
>
>This didn't work!
>
>
>Schema1 table is imported form a VAX daily, and only has data for two days,
>schema2 table on NT is for longer term storage of data
>
>What is the proper way to find the all of the records in one table that are
>not in the second?
>
>
Received on Thu Dec 24 1998 - 07:04:00 CST