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

198次閱讀

共計 2200 個字符，預計需要花費 6 分鐘才能閱讀完成。

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

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 的區別是什么相關的內容可以搜索丸趣 TV 之前的文章或者瀏覽下面的文章進行學習哈！相信丸趣 TV 小編會給大家增添更多知識, 希望大家能夠支持一下丸趣 TV！

正文完