EXISTS、IN、NOT EXISTS、NOT IN的区别是什么

176次阅读

共计 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！

正文完