Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 27 Jul 2007 @ 21:58:47 GMT


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


Subj:   Finding rows in one table not in another
 
From:   Duell, Bob

Hi,

This isn't a particularly Teradata question, but I'm going to ask anyway. It's probably just basic SQL.

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.

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


Thanks,

Bob



     
  <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