EXISTS、IN、NOT EXISTS、NOT IN的區別(ZT)

EXISTS、IN、NOT EXISTS、NOT IN的區別：


in適合內外表都很大的情況，exists適合外表結果集很小的情況。
exists 和 in 使用一例
===========================================================
今天市場報告有個sql及慢，執行需要20多分鐘，如下：
update p_container_decl cd
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
where exists(
select 1
from (
select tc.decl_no,tc.goods_no
from p_transfer_cont tc,P_AFFIRM_DO ad
where tc.GOODS_DECL_NO = ad.DECL_NO
and ad.DECL_NO = 'sssssssssssssssss'
) a
where a.decl_no = cd.decl_no
and a.goods_no = cd.goods_no
)
上面涉及的3個表的記錄數都不小，均在百萬左右。根據這種情況，我想到了前不久看的tom的一篇文章，說的是exists和in的區別，
in 是把外表和那表作hash join，而exists是對外表作loop，每次loop再對那表進行查詢。
這樣的話，in適合內外表都很大的情況，exists適合外表結果集很小的情況。

而我目前的情況適合用in來作查詢，於是我改寫了sql，如下：
update p_container_decl cd
set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
where (decl_no,goods_no) in
(
select tc.decl_no,tc.goods_no
from p_transfer_cont tc,P_AFFIRM_DO ad
where tc.GOODS_DECL_NO = ad.DECL_NO
and ad.DECL_NO = ‘ssssssssssss’
)

讓市場人員測試，結果執行時間在1分鐘內。問題解決了，看來exists和in確實是要根據表的資料量來決定使用。

請注意not in 邏輯上不完全等同於not exists，如果你誤用了not in，小心你的程式存在致命的BUG：


請看下面的例子：
create table t1 (c1 number,c2 number);
create table t2 (c1 number,c2 number);

insert into t1 values (1,2);
insert into t1 values (1,3);
insert into t2 values (1,2);
insert into t2 values (1,null);

select * from t1 where c2 not in (select c2 from t2);
no rows found
select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
c1 c2
1 3

正如所看到的，not in 出現了不期望的結果集，存在邏輯錯誤。如果看一下上述兩個select語句的執行計劃，也會不同。後者使用了hash_aj。
因此，請儘量不要使用not in(它會呼叫子查詢)，而儘量使用not exists(它會呼叫關聯子查詢)。如果子查詢中返回的任意一條記錄含有空值，則查詢將不返回任何記錄，正如上面例子所示。
除非子查詢欄位有非空限制，這時可以使用not in ,並且也可以通過提示讓它使用hasg_aj或merge_aj連線。
=======================

首先，版主(lfree)就是版主，果然有見地。

對於In, exists 和 not in, not exists, 在9i 和 10g中，如果關聯欄位在子查詢中不為null, oracle 的處理方式是基本一樣的，
In, exists, oracle server會盡量轉換成semi join
not in, not exists oracle server會盡量轉換成anti join
但是對於 not exists, 9i處理的時候有個bug, 那就是9i好像不會主動轉換成anti join，但是可以將not in 主動轉換成anti join

我想這就是樓主的遇到的問題的原因吧。

對於Null引起的not in 和 not exists的區別，主要是由於null 運算引起的
select * from t1 where c2 not in (select c2 from t2);
oracle server需要運算 t1.c2 <> t2.c2, 如果t2.c2存在null, 那麼t1.c2 <> t2.c2 ==> null，即導致條件不滿足,
所以no row selected

-==========

頂 特別是關於not in中子查詢有null的，以前一直沒注意！提醒我了~

==============