102-not in和not exists到底选哪个？这个问题有点复杂，但是结论很简单，不要被专家们带偏了。

有学员问：

最近在某平台学习一个关于oracle SQL优化培训课程中，听讲师在讲到not in的知识点时说：“not in的子查询是不等于的关系，不能用索引。跟in使用nested loops可以走索引的执行计划不一样”。 这个说法跟参加老师您的培训时学到的内容不太一样，到底以哪个为准呢？

看到学员的这个问题，我顺便问了一下讲师的名字，如雷贯耳，应该说业界非常知名，其技术水平我也是相当佩服的。但是就这个问题而言，我马上回复学员说这种说法不正确，同时很快给他做了一个test case，用反例证明了not in是可以走索引的：

说明：

如果说not in（变量列表）无法走索引，这是事实。 但是not in（子查询)不一样，如果满足条件，确实是可以走索引的，如上图所示。

既然大师都容易搞混的问题，我就想多花点时间再补充几句，于是就有了这篇文章。

正文：

网上关于not in和not exists的分析文章很多， 很多人都知道了下面两个知识点：

1.not in的写法在子查询存在null记录的时候，整个查询结果为空（老虎刘注：这个情况不是数据库的bug，而是符合not in逻辑的特性，可能导致的是应用出现bug）；

2.推荐使用not exists的写法，不但避免了not in有null出现不想要的结果集外，性能比not in要好。

我本人是非常赞同上面的说法，但是在对上面这些理论的解读上，网上还有其他一些专家说法， 我认为不是太恰当，为了避免这些文章误导广大读者，下面列举几个网上我认为不太准确的观点：

1.not in 和 not exists 的性能是一样的，不管字段上是否是null。网上的说法是“谣言”：这是从一个培训材料里面的截图，上图是培训师认为框框中的说法是错误的，培训师要“辟谣”，同时也给出了下面的“结论”。

老虎刘简单点评：

红框里面的说法只说对了一小部分；“辟谣”的结论是片面的。下面有对应的反例来证明。

2.使用了not in， 内外表都只能全表扫描，不能走索引；

老虎刘简单点评：

这篇文章的说法也是对错参半，本篇文章的开头就已经证明过了，内外表都可以走索引，只是not in不像in那样主查询和子查询都可以做Nested Loops的驱动表，not in如果是Nested Loops的执行计划，只能是主查询做驱动表。下面也有对应的test cast来举反例证明。

3.not in 是不等值查询，不能用索引；

老虎刘简单点评：

这个问题跟第2个问题类似。

4.not in会优先扫描子查询里面的表， 如果发现有null值就马上返回空结果集，不用再扫描主查询了；如果子查询没有null记录，会使用跟not exists一样的执行计划：

老虎刘简单点评：

通过两个反例来证明oracle的优化器实际并不是这么工作的，作者的推断有点主观臆断了。

下面集中对上面4个说法加以论证：

先补充一个知识点：

如果主查询和子查询的关联字段上都有not null约束，那么 not in和not exists可以认为是等同的，包括逻辑和性能。

如果关联字段上没有not null约束，而是sql中分别在主查询和子查询中增加is not null条件, 也等同于加了not null约束；

这种情况，如果适合走索引，not in和not exists都没问题。

如果不是上面说的情况，not in和not exists从逻辑上就不是等价的。有时候虽然事实上没有null记录，not in和not exists两种写法得到的结果集也相同，但是优化器不敢冒险，为了保证结果集的准确，不得不为两种写法生成不同的执行计划。

证明（反证法）正式开始：

先创建演示使用的表和索引(oracle 11g+的环境)：

创建两张表T1和T2：

表上没有not null约束，用来做关联的object_id字段也不存在空值记录：

create table t1 as select * from dba_objects where object_id is not null;

create table t2 as select * from dba_objects where object_id is not null;

收集统计信息：

exec dbms_stats.gather_table_stats(user,'t1');

exec dbms_stats.gather_table_stats(user,'t2');

创建演示需要的两个索引：

create index idx_t1_object_id on t1(object_id);

create index idx_t2_object_name on t2(object_name);

说法1的反例： “not in 和 not exists的性能没差别，无论列是否为空”

虽然下面两个查询返回的结果集相同，但是二者在逻辑上本来不等价（一旦主查询或子查询的关联字段上有null值，结果集就不同了），因为原培训材料就是在不考虑列是否为空的情况下做的比较，我就顺着这个思路给出了下面not exists比not in效率高的反例：

not in写法，使用Hash join的执行计划，有索引也用不上（即使用hint强制使用索引也是不可以的，因为不符合not in的逻辑要求）：

not exists写法，可以使用Nested Loops的执行计划，效率比not in高很多， 这也是not exists被推荐使用的原因之一。

说法2和说法3的反例：

我在文章开头给的not in可以走索引的例子实际上是在关联列上都有not null约束，其实只要主查询有not null约束（或条件）， 在子查询关联字段的索引上做点小文章，not in走索引也不是不可能：

对于not exists， 这两个前提都不需要。这也是为什么not exists被推荐使用的原因之二。

说法4的反例：

drop index idx_t1_object_id0; （避免索引快速全扫描的干扰）

update t1 set object_id = null where rownum=1; （让子查询里面有null记录）

commit;

exec dbms_stats.gather_table_stats(user,'t1');

exec dbms_stats.gather_table_stats(user,'t2');

由上面的执行计划可以看出， 优化器并没有先扫描主查询包含null的T1表，还是先扫描了结果集较小的T2表（符合hash join的特点），这个执行计划说明了说法4的第一个描述是不准确的；

再来看看说法4的第二个描述，如果发现子查询T1表没有null值， 会选择not exists的执行路径吗？

not in，子查询加了not null约束，走了hash join，明显不是想要的not exists走nested loops的执行计划：

not exists，不需要任何not null约束，就能使用Nested Loops：

上面not in如果想得到not exists同样的执行计划， 还必须要把主查询is not null的条件加上去才行：

总结：

因为not null的约束或是条件经常被开发人员忽略，所以推荐使用not exists，不要使用not in（除非你的业务就需要not in实现的特殊要求）

补充：

经本人测试，mysql和postgesql对于not in的处理，在某些方面要比oracle进步一些，因为oracle的索引不保存全是null的记录，而mysql和PG的索引都保存null记录。在mysql与PG的not in写法性能PK中，mysql比PG的表现还要好那么一点点。

尽管如此，这两种库的not in在某些场景的性能也还是不如not exists，所以mysql和PG也是推荐使用not exists。

建议：

对待网上和书上的文章和结论，要抱有一种怀疑的态度，专家也有看走眼的时候。我的文章如果有什么不妥的地方，还请广大读者给予批评指正，本人一定虚心接受并表示感谢！

（完）