EXISTS、IN、NOT EXISTS、NOT IN的區別是什么

今天給大家介紹一下EXISTS、IN、NOT EXISTS、NOT IN的區別是什么。文章的內容小編覺得不錯，現在給大家分享一下，覺得有需要的朋友可以了解一下，希望對大家有所幫助，下面跟著小編的思路一起來閱讀吧。

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的，以前一直沒注意！提醒我了~

以上就是EXISTS、IN、NOT EXISTS、NOT IN的區別是什么的全部內容了，更多與EXISTS、IN、NOT EXISTS、NOT IN的區別是什么相關的內容可以搜索億速云之前的文章或者瀏覽下面的文章進行學習哈！相信小編會給大家增添更多知識,希望大家能夠支持一下億速云！