Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 30 Jul 2007 @ 13:49:47 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: Finding rows in one table not in another
 
From:   Dieter Noeth

Bob Duell wrote:

  I want to select all the rows from one table that do not exist in another table, based on comparing column values. In my case, the columns are a unique primary index to both tables (both varchar(255)).  


  Both of the following methods appear to be functionally equivalent. Is one "better" than the other?  


          > Method 1:
          >
          > select a.*
          > from   ONE_BIGTABLE a
          > left join ANOTHER_BIGTABLE b
          >    on a.key_field=b.key_field
          > where b.key_field is null

          > Method 2:
          >
          > select a.*
          > from   ONE_BIGTABLE a
          > where not exists (
          >     select 1 from ANOTHER_BIGTABLE b
          >     where a.key_field=b.key_field)

  The second method "looks" better. Also, the first "explain" says two spools are created while the second says only one spool is created. But I'm wondering if this is a generally better technique rather than somehow better because the indexes are so similar.  


It not only looks better, it *is* better.

Usually there will be no real difference between IN/EXISTS/JOIN, but if there is then EXISTS is the most efficient.


  No big deal; it's just a little Friday musing before I head off > to see the Simpson's movie!  


It's better to ask before, after the movie you'll be Homerized and don't care any more :-)


Dieter



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016