《Difference between IN & Exists & NOT IN & NOT Exists 》

2012-10-11

摘要:《Difference between IN & Exists & NOT IN & NOT Exists 》

一直想搞清楚 in & exists 的關係，趁這機會弄懂跟大家分享我的結論。

以最後一個參考網址的範例說明:


CREATE TABLE EMP_MASTER

(

EMP_NBR NUMBER(10) NOT NULL PRIMARY KEY,

EMP_NAME VARCHAR2(20 CHAR),

MGR_NBR NUMBER(10) NULL

);

INSERT INTO EMP_MASTER VALUES (1, 'DON', 5);

INSERT INTO EMP_MASTER VALUES (2, 'HARI', 5);

INSERT INTO EMP_MASTER VALUES (3, 'RAMESH', 5);

INSERT INTO EMP_MASTER VALUES (4, 'JOE', 5);

INSERT INTO EMP_MASTER VALUES (5, 'DENNIS', NULL);

INSERT INTO EMP_MASTER VALUES (6, 'NIMISH', 5);

INSERT INTO EMP_MASTER VALUES (7, 'JESSIE', 5);

INSERT INTO EMP_MASTER VALUES (8, 'KEN', 5);

INSERT INTO EMP_MASTER VALUES (9, 'AMBER', 5);

INSERT INTO EMP_MASTER VALUES (10, 'JIM', 5);

COMMIT;

select count(*) from emp_master a where A.EMP_NBR not in (select mgr_nbr from emp_master);

COUNT(*)

----------

0

上面的 SQL 可視為


select count(*) from emp_master a where a.EMP_NBR not in (select distinct mgr_nbr from emp_master);

select count(*) from emp_master a where not exists (select null from emp_master b where a.emp_nbr=b.mgr_nbr);

COUNT(*)

----------

9

上面的 SQL 可視為


for x in ( select * from emp_master a )

loop

if ( exists ( select null from emp_master b where a.emp_nbr=b.mgr_nbr )

then

OUTPUT THE RECORD

end if

end loop

最後得到的結論如下:

not in & not exists 在關聯欄位裡若有 null 則結果不會相同

in & exists 則視情況使用，當關聯表資料較少時建議使用 exists；

當被關聯表資料較少時建議使用 distinct;

當兩個表資料均很龐大，則建議使用 exists

reference:

http://asktom.oracle.com/pls/asktom/f?p=100:11:3339938689247353::::P11_QUESTION_ID:953229842074

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:442029737684

http://decipherinfosys.wordpress.com/2007/01/21/32/

回首頁