Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 17 Dec 2001 @ 17:40:54 GMT


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


Subj:   Re: Exists/Not Exists versus IN/Not IN
 
From:   Geoffrey Rommel

  Which is usually faster, using exists/not exists or in/not, and in general why.  



The responses you have already received are good, but let me just throw in my two centimes' worth.

For IN, you have three options:

(1a)
select stuff
 from A, B
 where A.acct_no = B.acct_no;

(1b)
select stuff
 from A
 where A.acct_no in  /* or = ANY */
  (select acct_no from B);

(1c)
select stuff
 from A
 where exists
  (select 1 from B
   where B.acct_no = A.acct_no);

In my experience, you cannot tell ahead of time which of these will be the best. Generally 1c is the best, as previously explained (it avoids the hassle of NULL processing), but to make sure you must run EXPLAIN.

For NOT IN, you have two options. There is no "2a" because you cannot accomplish this with a plain inner join.

(2b)
select stuff
 from A
 where A.acct_no not in  /* or <> ALL */
  (select acct_no from B);

(2c)
select stuff
 from A
 where not exists
  (select 1 from B
   where B.acct_no = A.acct_no);

The same remark applies as above.


--wgr



     
  <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