Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Mon, 30 Jul 2007 @ 09:21:25 GMT


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


Subj:   Re: Finding rows in one table not in another
 
From:   Anomy Anom

<-- Anonymously Posted: Monday, July 30, 2007 00:37 -->

Bob Duell wrote:

  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)

Over time I've found that if I code it using your Method 2 (NOT EXISTS sub-select) then with a disturbing frequency the next request is of the type:

     WHERE
       the key isn't in the other table
       OR
       if it is in the other table then column b.x is in (purple, red).

And so I find myself having to rewrite the query back into Method 1 form anyway. This is especially common if the NOT EXISTS wasn't the only part of the WHERE clause.

Consequently these days I always write the code in Method 1 and only change it to Method 2 when it has become production code and I'm looking to make efficiency improvements.



     
  <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: 28 Jun 2020