2022-10-17 09:25:19

一、in关键字

确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

select * from A where id in (select id from B)

#等价于

for select id from B：先执行；
子查询 for select id from A where A.id = B.id：再执行外面的查询；

执行过程：in是先查询内表【select id from B】，再把内表结果与外表【select * from A where id in …】匹配，对外表使用索引，而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

小总结：当A表的数据集大于B表的数据集时，用in优于exists。【in适合外部表数据大于子查询的表数据的业务场景】

二、exists关键字

指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

语法格式：

select ... from table where exists (subquery)；

可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或者 FALSE）来决定主查询数据结果是否得到保留。

如下:

select * from A where exists (select 1 from B where B.id = A.id)

#等价于

for select id from A：先执行外层的查询；
for select id from B where B.id = A.id：再执行子查询；

执行过程：exists是对外表【select * from A where exists …】做loop循环，每次loop循环再对内表（子查询）【select 1 from B where B.id = A.id】进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（所以尽量用小表），故内表大的使用exists，可加快效率。

例如：

select * from A where exists (select 1 from B where B.id = A.id)

提示

1. T 清单，因此没有区别；EXISTS (subquery) 只返回 True 或 False ，因此查询的 SELET * 也可以是SELET 1 或其他，官方说法是执行时会忽略SELEC

2. EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解的逐条比对，如果担忧效率问题，可以进行实际检验以确定是否有效率问题；

3. EXISTS 子查询往往也可以使用条件表达式、其他子查询或者 JOIN 来代替，何种最优化需要具体分析；

小总结：当A表的数据集小于B表的数据集时，用exists优于in。【exist适合子查询中表数据大于外查询表中数据的业务场景】

三、in 与 exists 的区别

1、exists、not exists 一般都是与子查询一起使用，In 可以与子查询一起使用，也可以直接in (a,b.....)

2、exists 会针对子查询的表使用索引，not exists 会对主子查询都会使用索引。in 与子查询一起使用的时候，只能针对主查询使用索引，not in 则不会使用任何索引。

　　注意：一直以来认为 exists 比 in 效率高的说法是不准确的。

　　in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询。

如果查询的两个表大小相当，那么用 in 和 exists 差别不大。

　　如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

四、总结

select * from A where id in (select id from B)
select * from A where exists (select 1 from B where B.id = A.id)

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

　　其实我们区分 in 和 exists 主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了，另外IN时不对NULL进行处理。(都是以小表驱动大表);

2、in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询。一直以来认为 exists 比 in 效率高的说法是不准确的。

3、如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。