“ EXISTS比IN效率高？它真的正确吗？EXISTS和IN他们到底有什么区别？本文将通过实验让你明白两者的区别以及执行效率，以及该如何选择”

当我们在网络上搜索EXISTS和IN时，总能搜索到推荐使用EXISTS而不建议使用IN的说法，说的头头是道，让人觉得就不该使用IN。但事实真的如此吗？我们不妨做个实验来验证两者的区别和效率。

IN和EXISTS的区别验证

首先我们需要先了解IN和EXITS有什么区别。IN和NOT IN 它们是成员条件，它会验证在值列表或子查询列表中是否存在该成员。EXISTS条件则是用于验证子查询中是否存在对应的行。如果子查询返回一行，则结果为TRUE，NOT EXISTS则刚好相反。所以从条件类型上来讲两者是不一致的，这也是他们的区别之一。

接下来我们将验证IN,EXISTS,NOT IN,NOT EXISTS对于NULL值和一般数据是否有区别。因此我们需要准备一些数据，需要一张名为DEMO_WYDXBG的表，表中数据如下图所示：

验证NULL值和正常的数值对IN和NOT IN的影响

SELECT F1,F6 FROM DEMO_WYDXBG A WHERE F1 IN(1,NULL);

执行结果显示，列表中存在NULL时，IN可以正常的查询出结果

SELECT F1,F6 FROM DEMO_WYDXBG A WHERE F1 NOT IN(1,NULL);

执行结果显示，列表中存在NULL时，NOT IN无法查询出任何结果

验证NULL值和正常的数值对EXISTS和NOT EXISTS的影响

SELECT F1,F6 FROM DEMO_WYDXBG A WHERE EXISTS(SELECT 1 FROM DEMO_WYDXBG B WHERE A.F1 = B.F1 AND (A.F1 = 1 OR A.F1 IS NULL))

执行结果显示，子查询数据存在NULL时，EXISTS可以正常的查询出结果，且和IN查询出的结果一致。

SELECT F1,F6 FROM DEMO_WYDXBG A WHERE NOT EXISTS(SELECT 1 FROM DEMO_WYDXBG B WHERE A.F1 = B.F1 AND (A.F1 = 1 OR A.F1 IS NULL))

执行结果显示，子查询数据存在NULL时，NOT EXISTS可以正常的查询出结果

所以通过以上例子我们可以得出以下结论:

1. IN，NOT IN 和EXISTS，NOT EXISTS条件的含义是不一样的。
2. IN和EXISTS两者在数据处理上是没有区别的
3. NOT IN和NOT EXISTS在处理NULL值时结果不同。

那么为什么存在NULL时NOT IN无法查询出结果？而NOT EXISTS却可以？

在NOT IN的例子中

F1 NOT IN(1,NULL) 相当于F1 ！=1 AND F1 != NULL因为NULL和任何表达式计算的结果是未知，所以条件包含NULL值时，则该条件必然是不成立的。所以虽然F1 ！=1条件成立，但是由于F1 != NULL条件不成立，所以导致无法查询出任何结果。

那么为什么NOT EXISTS又可以呢？我们要从EXISTS的含义来说明，EXISTS是判断有无数据，有则为TRUE，没有则为FALSE。NOT EXISTS则刚好相反。所以当子查询中的NULL和DEMO_WYDXBG表上的任何数据进行匹配时结果都是不成立的。因此都无法匹配上。也就意味着都没有数据，没有数据则意味着TRUE，所以就可以查询出这些数据。

IN和EXISTS的效率验证

我们已经验证了IN和EXISTS，以及NOT IN和NOT EXISTS的区别，接下来我们要验证的两者效率如何。为此我们需要新建两张表，一张名为BIG_TABLE的表，一张名为SMALL_TABLE的表。

BIG_TABLE表结构如下：

BIG_TABLE一共2000万数据，数据采用循环随机插入数据，唯一值大概有100万左右。生成数据之后需要收集统计信息

call dbms_stats.gather_table_stats('WYDXBG','BIG_TABLE');

SMALL_TABLE表结构如下：

SMALL_TABLE一共20万数据，数据采用循环插入数据，唯一值为10万。生成数据之后需要收集统计信息

call dbms_stats.gather_table_stats('WYDXBG','SMALL_TABLE');

首先我们以BIG_TABLE作为主表，SMALL_TABLE作为子查询中的表，采用IN的写法，SQL语句如下：

SELECT * FROM BIG_TABLE T WHERE T.F2 IN(SELECT T1.F2 FROM SMALL_TABLE T1 WHERE T1.F2 >:A AND T1.F2 <=:B)

执行计划显示如下：

之后我们以BIG_TABLE作为主表，SMALL_TABLE作为子查询中的表，采用EXISTS的写法，SQL语句如下：

SELECT * FROM BIG_TABLE T WHERE EXISTS (SELECT F2 FROM SMALL_TABLE T1 WHERE T.F2 = T1.F2 AND T1.F2 >:A AND T1.F2 <=:B)

执行计划显示如下：

WHAT?两者的执行计划竟然一样？这和网上的说法不一致啊。当然我们不能凭此就断定两者执行计划一定是一样的。因为前面所看到的执行计划叫预期的执行计划，也就是SQL可能这么执行，但是实际上不一定这么执行。

为了得到实际的执行计划，我们需要执行该SQL。分别将变量A和B代入实际的值。本例A使用100,B使用2000，为了方便搜索,因此在SQL上都加上了注释/*XYDXBG2021*/并执行SQL。然后通过以下SQL语句可查询SQL的运行状态select T.PLAN_HASH_VALUE,T.SQL_ID,T.SQL_TEXT from v$sql t where upper(t.sql_text) like '%XYDXBG2021%'

在这里我们可以看到两个SQL文本不一致，但是他们的PLAN_HASH_VALUE竟然一致，这说明两者使用的是同一个执行计划。也就意味着使用IN和EXISTS他们从效率上来说是一致的。

然后我们找到它的详细执行计划来看一下，可通过以下语句替换SQL_ID来寻找真实的执行计划

select * from table(dbms_xplan.display_cursor('bh6xzy3wq59u9'));

IN执行计划如下：

EXISTS执行计划如下：

两者真的完全一样所以我们可以得出结论

当BIG_TABLE作为主表而SMALL_TABLE作为子查询中的表时，不管使用IN还是使用EXISTS，两者效率是一致的。

我们再来看一下如果以SMALL_TABLEL作为主表而BIG_TABLE作为子查询中的表时两者是否一致呢？采用IN的写法，SQL语句如下：

SELECT * FROM SMALL_TABLE T WHERE T.F2 IN(SELECT T1.F2 FROM BIG_TABLE T1 WHERE T1.F2 >:A AND T1.F2 <=:B)

IN执行计划显示如下：

采用EXISTS的写法，SQL语句如下：

SELECT * FROM SMALL_TABLE T WHERE EXISTS (SELECT F2 FROM BIG_TABLE T1 WHERE T.F2 = T1.F2 AND T1.F2 >:A AND T1.F2 <=:B)

EXISTS执行计划显示如下：

两者预期的执行计划一致，接下来分别将变量A和B代入实际的值。本例A使用100,B使用2000，为了方便搜索,因此在SQL上都加上了注释/*XYDXBG2021*/并执行SQL。

两个SQL的PLAN_HASH_VALUE一致，意味着使用的是相同的执行计划。

EXISTS的执行计划如下：

IN的执行计划如下：

两者完全一样所以我们可以得出结论

当SMALL_TABLE 作为主表而BIG_TABLE作为子查询中的表时，不管使用IN还是使用EXISTS，两者效率是一致的。

除了以上两个DEMO外，也可以验证两个表都用BIG_TABLE，以及都用SMALL_TABLE的例子，以及NOT EXISTS和NOT IN你会发现它们的执行计划也都是一样的。

IN和EXISTS的结论

通过上述验证，我们看到IN和EXISTS的执行计划是相同的，也就意味着两者的性能是一致的。网上所说的EXISTS比IN更快的情况是不正确的。NOT EXISTS也不会比NOT IN更快。但NOT EXISTS和NOT IN在结果上确实可能不一样。所以使用NOT IN时需要特别注意NULL值。

为什么IN和EXISTS的执行计划会一致呢？这个问题的原因是在于Oracle的优化器模式。在基于成本的优化器中Oracle会评估多种访问路径并最终选取成本最低的执行计划，因此虽然SQL文本不一致，但是IN和EXISTS访问路径却极有可能相同。同时Oracle总是会进行查询转换，IN和EXISTS可能被查询转换，转换后两者可能等价。所以在基于成本的模式下EXISTS比IN高效是不成立的。

当然对于早期的优化器或者基于规则的优化器，IN和EXISTS的表现则可能不一致。这时网络上广泛流传的方案到也可能是正确的。但是现在基于规则的优化器已经极少使用，基本使用的都是基于成本的优化器。

IN和EXISTS如何选择

那么IN和EXISTS，NOT IN和NOT EXISTS该如何选择呢？

对于NOT IN 因为可能由于返回NULL值而导致结果和预期的不一致，因此请酌情考虑用NOT EXISTS代替NOT IN。

如果你的SQL比较简单，其实用IN和EXISTS都没关系，两者执行计划极大概率是一样的。但是如果你只是为了查询几行数据，以及关联条件上有高效的索引那么选用EXISTS是不错的选择。因为这可以让优化器偏向于生成嵌套循环的执行计划。

如果你的SQL非常复杂，EXISTS中嵌套了多层或者EXISTS中有多表关联，那么这种情况建议你使用IN。主要原因在于方便优化。

如果看完本文，您有所收获，欢迎扫码关注，您的支持是我创作的动力，我会以更优质的原创文章回报大家。