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

In article <368249cf.0_at_news.euroconnect.dk>,   "Torben Jeppesen" <torbenkj_at_post2.tele.dk> wrote:
> 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?
> >
> >
>
>

Sorry for posting the same answer as you. I haven't read your message prior to posting mine. Merry Christmas and a happy New Year!

--
Mladen Gogala

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Dec 24 1998 - 19:22:20 CST