SQL 中的 in 与 not in、exists 与 not exists 的区别以及性能分析

end loop

对于in 和 exists的性能区别:

---

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了

另外IN时不对NULL进行处理

如：select 1 from dual where null in (0,1,2,null) 为空

2.NOT IN 与NOT EXISTS:

NOT EXISTS的执行流程

select … from rollup R where not exists ( select ‘Found’ from title T where R.source_id = T.Title_ID);

可以理解为:

for x in ( select * from rollup ) loop

if ( not exists ( that query ) ) then

OUTPUT

end if;

end loop;

注意:NOT EXISTS 与 NOT IN 不能完全互相替换，看具体的需求。如果选择的列可以为空，则不能被替换。

例如下面语句，看他们的区别：

select x,y from t;

查询x和y数据如下：

x y

---

1 3

3 1

1 2

1 1

3 1

5

使用not in 和not exists查询结果如下：

select * from t where x not in (select y from t t2 ) ;

查询无结果：no rows

select * from t where not exists (select null from t t2 where t2.y=t.x ) ;

查询结果为：

x y

---

5 NULL

所以要具体需求来决定

对于not in 和 not exists的性能区别：

---

not in 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in,并使用anti hash join.

如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null

NOT IN 在基于成本的应用中较好

比如:

select …

from rollup R

where not exists ( select ‘Found’ from title T

where R.source_id = T.Title_ID);

改成（佳）

select …

from title T, rollup R

where R.source_id = T.Title_id(+)

and T.Title_id is null;

或者（佳）

sql> select /*+ HASH_AJ */ …

from rollup R

where ource_id NOT IN ( select ource_id

from title T

where ource_id IS NOT NULL )

讨论IN和EXISTS。

select * from t1 where x in ( select y from t2 )

事实上可以理解为：

select *

from t1, ( select distinct y from t2 ) t2

where t1.x = t2.y;

——如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是 不可忍受的。但是t1可以很大，为什么呢？最通俗的理解就是因为t1.x=t2.y可以走索引。

但这并不是一个很好的解释。试想，如果t1.x和t2.y 都有索引，我们知道索引是种有序的结构，因此t1和t2之间最佳的方案是走merge join。另外，如果t2.y上有索引，对t2的排序性能也有很大提高。

select * from t1 where exists ( select null from t2 where y = x )

可以理解为：

for x in ( select * from t1 )

loop

if ( exists ( select null from t2 where y = x.x )

then

OUTPUT THE RECORD!

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）

总结

阿里伤透我心，疯狂复习刷题，终于喜提offer 哈哈~好啦，不闲扯了

1、JAVA面试核心知识整理（PDF）：包含JVM，JAVA集合，JAVA多线程并发，JAVA基础，Spring原理，微服务，Netty与RPC，网络，日志，Zookeeper，Kafka，RabbitMQ，Hbase，MongoDB，Cassandra，设计模式，负载均衡，数据库，一致性哈希，JAVA算法，数据结构，加密算法，分布式缓存，Hadoop，Spark，Storm，YARN，机器学习，云计算共30个章节。

2、Redis学习笔记及学习思维脑图

3、数据面试必备20题+数据库性能优化的21个最佳实践

MongoDB**，Cassandra，设计模式，负载均衡，数据库，一致性哈希，JAVA算法，数据结构，加密算法，分布式缓存，Hadoop，Spark，Storm，YARN，机器学习，云计算共30个章节。

[外链图片转存中…(img-MBoMvlPd-1711963618381)]

2、Redis学习笔记及学习思维脑图

[外链图片转存中…(img-aRAKr4mi-1711963618382)]

3、数据面试必备20题+数据库性能优化的21个最佳实践

[外链图片转存中…(img-7oUOF8GO-1711963618382)]