Archives of the TeradataForum
Message Posted: Mon, 17 Dec 2001 @ 17:40:54 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|