Exists、 In 、Not Exists、Not In 区别和效率
Exists和in的基本工作原理就不说了可以看这篇博文:https://blog.csdn.net/qq_38238296/article/details/86601765
关于exists和in的效率问题
很多博客上说in适用于外表大内表小的情况,exists适用于外表小内表大的情况
如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
例如:表A(小表),表B(大表)
1:
select * from A where cc in (select cc from B) 效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc) 效率高,用到了B表上cc列的索引。
相反的
2:
select * from B where cc in (select cc from A) 效率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc) 效率低,用到了A表上cc列的索引。
总结上面的描述,主要原因就是索引问题,可以看出只要使用了大表的索引那么效率就会高。但实际上in和exists的执行计划完全是一样的!
还有就是说in是驱动的内表,exists是驱动的外表,在mysql中应该用小表驱动大表,这样效率就会高!但是mysql会自行分析,会从中选择小表来做驱动
关于IN和EXISTS的主要区别在于三值逻辑的判断上。EXISTS总是返回TRUE或FALSE,而对于IN,除了TRUE、FALSE值外,还有可能对NULL值返回UNKNOWN。但是在过滤器中,UNKNOWN的处理方式与FALSE相同,因此使用IN与使用EXISTS一样,SQL优化器会选择相同的执行计划,对于exists和in使用的都是semi join(半连接)。
所以结论就是,在现在的oracle版本中,in和exists在性能上已经基本等效了,不需要在考虑要使用其中一种替换另外一种了
关于not exists 和 not in的区别:
有关于not exists和not in的区别,其主要原因就是关于null的处理,首先我们的知道对于null是不能参加算术运算的,判断是否为null是IS NULL 或者IS NOT NULL。
首先说明in和exists运行原理:对于in检索是从外部查询开始,然后匹配子查询,当符合匹配条件,返回true,然后将符合条件的元素加入结果集,最后输出。
对于exists:是从子查询开始的,当满足子查询的条件时。exists(subquery)为true。官方文档中是这样说明的TRUE if a subquery returns at least one row:至少返回一行数据就为true。当为true,则会返回数据。
not in:就是对in表达式取非,换成了另一种等价的表达式。并不是对in的结果取非!
not exists:是对exists()结果取非,也就是exists的结果为false那么not exists为true。
直接看例子:有3张表
mysql> select * from test_3;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| NULL | 空 |
mysql> select * from test_;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 4 | 王五 |
| 5 | 王多余 |
+----+--------+
mysql> select * from test_4;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| 2 | 李四 |
| 7 | 摊牌 |
| NULL | 王多余 |
+------+--------+
IN 和NOT IN:
因为null值不能参加算术运算 这里的in其实就是=,所以运算结果并没有null这一行数据。
mysql> select * from test_3 where id in (select id from test_4);
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 李四 |
+----+------+
2 rows in set
mysql> select * from test_3 where id in (select id from test_);
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 李四 |
+----+------+
2 rows in set
当使用not in时,无论如何不会返回空值。当子查询中有null值时,没有任何返回结果。
mysql> select * from test_3 where id not in (select id from test_);
+----+------+
| id | name |
+----+------+
| 3 | 王五 |
+----+------+
1 row in set
子查询中有null,not in 没有返回结果。
mysql> select * from test_3 where id not in (select id from test_4);
Empty set
EXISTS 和NOT EXISTS:
和in一样,null不参加运算。
mysql> select * from test_3 where exists (select 1 from test_ where test_3.id=test_.id);
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 李四 |
+----+------+
2 rows in set
mysql> select * from test_3 where exists (select 1 from test_4 where test_3.id=test_4.id);
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 李四 |
+----+------+
2 rows in set
not exist取exists非值!
mysql> select * from test_3 where not exists (select 1 from test_4 where test_3.id=test_4.id);
+------+------+
| id | name |
+------+------+
| 3 | 王五 |
| NULL | 空 |
+------+------+
2 rows in set
同上
mysql> select * from test_3 where not exists (select 1 from test_ where test_3.id=test_.id);
+------+------+
| id | name |
+------+------+
| 3 | 王五 |
| NULL | 空 |
+------+------+
2 rows in set
关于exists和in的效率测试:https://www.cnblogs.com/lyhabc/p/3279056.html