Re: Help :- testing if values are not in a table

John Strange (jstrange_at_imtn.dsccc.com) wrote:

: select id from main
: minus
: select id from temp
: /

The MINUS operator will definitely be the fastest. But this one is upside down.... (swap temp and main)

: Mike Kofal (mkofal_at_csn.net) wrote:
: :> Andy MArr <andy_at_pindar.com> wrote:
 

: :> >I've got a text file of ID type values which need to be compared agains
: :> >the primary key of a main table with the same type of ID values.
 

: :> >What I want to know is, whats the best(fast) way to check which values
: :> >in the temporay table are NOT in the main table. Basically I want to
: :> >list the values from the import file which are not in the database.
 

: :> >I can only think of an individual select for each value or a WHERE NOT
: :> >EXISTS type clause. Which would be the fastest ? , are there any other
: :> >clever ways ? .
 

: :> I have been using this trick for the past few years. I can't explain
: :> WHY it is faster, but it works!
 

: :> SELECT Temp.column
: :> FROM Temp, Main
: :> WHERE Temp.column = Main.column(+)
: :> AND Main.column IS NULL

: --
: This posting represents the personal opinions of the author. It is not the
: official opinion or policy of the author's employer. Warranty expired when you
: opened this article and I will not be responsible for its contents or use.

--
    ..uu.                                     ----------------------
  .?$" '?i     .                              I  Randy DeWoolfson  I
 .T^M  ._at_"    d9    .     f   ,.un.  b,    i  I--------------------I
 "  Z :#"    M `8   U    <  .dP"``"# `M   _at_"  I  randyd_at_cais.com   I
    &H?`    Xl _R   $5.  $  ?*    _at_   'P,#"   I--------------------I
  ,d#^*L   :RP'~$b  f`$L:M  Xf  .f'    dH`    I        ,\//.       I
    &  'M ,P    `E  M   "$  Mux~      n!`     I        |o o|       I
   dk   `h"       ' j     " y"       *~       I====oOO==(_)==Ooo===I