Archives of the TeradataForum

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

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

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>>