共计 7285 个字符，预计需要花费 19 分钟才能阅读完成。
这篇文章主要为大家展示了“sql 中 not in 与 not exists 的区别有哪些”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让丸趣 TV 小编带领大家一起研究并学习一下“sql 中 not in 与 not exists 的区别有哪些”这篇文章吧。
我先建两个示范表，便于说明：
create table ljn_test1 (col number);
create table ljn_test2 (col number);
然后插入一些数据：
insert into ljn_test1
select level from dual connect by level =30000;
insert into ljn_test2
select level+1 from dual connect by level =30000;
commit;
然后来分别看一下使用 not exists 和 not in 的性能差异：
select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);
COL
———-
1
Elapsed: 00:00:00.06
select * from ljn_test1 where col not in (select col from ljn_test2);
COL
———-
1
Elapsed: 00:00:21.28
可以看到，使用 not exists 需要 0.06 秒，而使用 not in 需要 21 秒，差了 3 个数量级！为什么呢？其实答案很简答，以上两个 SQL 其实并不是等价的。
我把以上两个表的数据清除掉，重新插入数据：
truncate table ljn_test1;
truncate table ljn_test2;
insert into ljn_test1 values(1);
insert into ljn_test1 values(2);
insert into ljn_test1 values(3);
insert into ljn_test2 values(2);
insert into ljn_test2 values(null);
commit;
然后再次执行两个 SQL：
select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);
COL
———-
3
1
select * from ljn_test1 where col not in (select col from ljn_test2);
no rows selected
这回 not in 的原形暴露了，竟然得到的是空集。来仔细分解一下原因：
A. select * from ljn_test1 where col not in (select col from ljn_test2);
A 在这个例子中可以转化为下面的 B：
B. select * from ljn_test1 where col not in (2,null);
B 可以进一步转化为下面的 C：
C. select * from ljn_test1 where col 2 and col null;
因为 col null 是一个永假式，所以最终查出的结果肯定也就是空了。
由此可以得出结论：只要 not in 的子查询中包含空值，那么最终的结果就为空！
not exists 语句不会出现这种情况，因为 not exists 子句中写的是 ljn_test1 与 ljn_test2 的关联，null 是不参与等值关联的，所以 ljn_test2 的 col 存在空值对最终的查询结果没有任何影响。
我在这里暂且把 ljn_test1 叫做外表，ljn_test2 叫做内表。
只要稍做归纳，就可以得到更详细的结论：
1、对于 not exists 查询，内表存在空值对查询结果没有影响；对于 not in 查询，内表存在空值将导致最终的查询结果为空。
2、对于 not exists 查询，外表存在空值，存在空值的那条记录最终会输出；对于 not in 查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。
讲到这里，我就可以开始解释为什么上面的 not in 语句比 not exists 语句效率差这么多了。
not exists 语句很显然就是一个简单的两表关联，内表与外表中存在空值本身就不参与关联，在 CBO(基于成本的优化器) 中常用的执行计划是 hash join，所以它的效率完全没有问题，看一下它的执行计划：
set autot on;
select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);
COL
———-
3
1
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
Plan hash value: 385135874
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 3 | 78 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 3 | 78 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| LJN_TEST1 | 3 | 39 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LJN_TEST2 | 2 | 26 | 3 (0)| 00:00:01 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – access(LJN_TEST1 . COL = LJN_TEST2 . COL)
这个执行计划很清晰，没有什么需要解释的，再看一下 not in:
select * from ljn_test1 where col not in (select col from ljn_test2);
no rows selected
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
Plan hash value: 3267714838
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| LJN_TEST1 | 3 | 39 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| LJN_TEST2 | 2 | 26 | 2 (0)| 00:00:01 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(NOT EXISTS (SELECT 0 FROM LJN_TEST2 LJN_TEST2
WHERE LNNVL(COL :B1)))
3 – filter(LNNVL( COL :B1))
可以看到关联谓词是 filter，它类似于两表关联中的 nested loop，也就是跑两层循环，可见它的效率有多差。为什么 not in 不能使用 hash join 作为执行计划呢？正如上面解释的，因为内表或外表中存在空值对最终结果产生的影响是 hash join 无法实现的，因为 hash join 不支持把空值放到 hash 桶中，所以它没办法处理外表和内表中存在的空值，效率与正确性放在一起时，肯定是要选择正确性，所以 oracle 必须放弃效率，保证正确性，采用 filter 谓词。
这个执行计划中我们还有感兴趣的东西，那就是：LNNVL(COL :B1)，关于 LNNVL 的解释可以参见官方文档：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions078.htm
它在这里的作用很巧妙，oracle 知道使用 filter 性能很差，所以它在扫描内表 ljn_test2 时，会使用 LNNVL 来检查 ljn_test2.col 是否存在 null 值，只要扫描到 null 值，就可以断定最终的结果为空值，也就没有了继续执行的意义，所以 oracle 可以马上终止执行，在某种意义上它弥补了 filter 较差的性能。
我用例子来证明这一点，首先先造一些数据：
truncate table ljn_test1;
truncate table ljn_test2;
insert into ljn_test1
select level from dual connect by level =30000;
insert into ljn_test2
select level+1 from dual connect by level =30000;
commit;
然后我为了让 oracle 尽快扫描到 ljn_test2.col 为 null 的那条记录，我要先找到物理地址最小的那条记录，因为通常情况全表扫描会先扫描物理地址最小的那条记录：
select col from ljn_test2 where rowid=(select min(rowid) from ljn_test2);
COL
———-
1982
然后我把这条记录更新为空：
update ljn_test2 set col = null where col=1982;
commit;
然后再来看一下 not in 的查询效率：
select * from ljn_test1 where col not in (select col from ljn_test2);
no rows selected
Elapsed: 00:00:00.17
看到这个结果后我很爽，它和之前查询需要用时 21 秒有很大的差别！
当然，我们不能总是指望 oracle 扫描表时总是最先找到 null 值，看下面的例子：
update ljn_test2 set col = 1982 where col is null;
select col from ljn_test2 where rowid=(select max(rowid) from ljn_test2);
COL
———-
30001
update ljn_test2 set col = null where col=30001;
commit;
再看一下 not in 的查询效率：
select * from ljn_test1 where col not in (select col from ljn_test2);
COL
———-
1
Elapsed: 00:00:21.11
这一下 not in 再一次原形毕露了！
机会主义不行，更杯具的是如果内表中没有空值，那 LNNVL 优化就永远起不到作用，相反它还会增大开销！
其实只要找到原因，问题很好解决，不就是空值在作怪嘛！在正常的逻辑下用户本来就是想得到和 not exists 等价的查询结果，所以只要让 oracle 知道我们不需要空值参与进来就可以了。
第一种解决方案：
将内表与外表的关联字段设定为非空的：
alter table ljn_test1 modify col not null;
alter table ljn_test2 modify col not null;
好了，再看一下执行计划：
set autot on;
select * from ljn_test1 where col not in (select col from ljn_test2);
COL
———-
1
Elapsed: 00:00:00.07
Execution Plan
———————————————————-
Plan hash value: 385135874
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 26 | 28 (8)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 26 | 28 (8)| 00:00:01 |
| 2 | TABLE ACCESS FULL| LJN_TEST1 | 30000 | 380K| 13 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| LJN_TEST2 | 30000 | 380K| 13 (0)| 00:00:01 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – access(COL = COL)
很好！这回 oracle 已经知道使用 hash join 了！不过有时候表中需要存储空值，这时候就不能在表结构上指定非空了，那也同样简单：
第二种解决方案：
查询时在内表与外表中过滤空值。
先把表结构恢复为允许空值的：
alter table ljn_test1 modify col null;
alter table ljn_test2 modify col null;
然后改造查询：
select * from ljn_test1 where col is not null and col not in (select col from ljn_test2 where col is not null);
COL
———-
1
Elapsed: 00:00:00.07
Execution Plan
———————————————————-
Plan hash value: 385135874
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 26 | 28 (8)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 26 | 28 (8)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| LJN_TEST1 | 30000 | 380K| 13 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| LJN_TEST2 | 30000 | 380K| 13 (0)| 00:00:01 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – access(COL = COL)
2 – filter(COL IS NOT NULL)
3 – filter(COL IS NOT NULL)
OK! hash join 出来了！我想我关于 not exists 与 not in 之间的比较也该结束了。
以上是“sql 中 not in 与 not exists 的区别有哪些”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！
