这里写自定义目录标题
- 1 in与exists原理解析
- （1） in的原理
- （2）exists原理
- 2 in与exists适用场景
- 3 not in 和not exists
1 in与exists原理解析
作为一个后端程序猿，有必要对经常使用到的in与exists原理进行了解。
假设：A表中的id与B表中的uid关联
（1） in的原理
in语句：
select * from A where id in(select uid from B)in原理：
in中语句select uid from B只执行一次，然后将B表中查询出的uid结果存储到内存中(以hash集合的方式)，注意：内存中。然后判断A表中的id是否在该集合中存在，如果存在就将A表中的记录加入到结果集里面去。直到A表中所有记录遍历完为止。
in伪代码：
List resultSet =Lists.newArrayList();
RecordArray[] A=(select * from A);
IdArray[] B=(select id from B);
// 遍历A表的所有记录
for(int i=0;i<A.length;i++) {
// 判断A总某个记录的id是否存在与B集合中
for(int j=0;j<B.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
return resultSet;in讨论：
由于要遍历B集合，当B表数据较大时，可能会将B中uid全部遍历完
如：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历100001000000次，效率有点差。
如：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000100次，遍历次数大大减少，效率将会有较大的提升。
（2）exists原理
exists语句：
下面的语句与上面的in语句实现同样的效果
select * from A where exists(select id from B where id=A.id)exists原理：
exists()是根据A中某个记录的当前id对B进行select id from B where id=A.id查询操作，但是该查询的结果集内容并不重要，重要的是判断其结果集是否为空，空则返回false；非空则返回true，然后保存当前符合要求的A记录。
exists伪代码
List resultSet={};
RecordArray[] A=(select * from A);
for(int i=0;i<A.length;i++) {
if(exists(A[i].id) { //执行select id from B where B.id=A.id是否有记录返回
resultSet.add(A[i]);
}
}
return resultSet;exists讨论
与in一样，都会对最外层的A表进行遍历，他们的主要区别是A表遍历时候的内部操作。
exists()时候内部不做遍历操作，只需要再执行一次查询就行。
如：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。
如：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。
注意：
in()是在内存里遍历比较，而exists()中的语句查询数据库，我们都知道查询数据库所消耗的性能更高，而内存比较很快。
2 in与exists适用场景
很多人多说：
外表大，内表小用in
外表小，内表大用exists
但是：这个多大范围又没有很明确的界限。在mysql里面查询，如果外表比内部小，但是差距不是很大，还是建议用in，因为in毕竟是在内存里面操作。
如：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库，而连接数据库本来就是一个io操作，对cpu资源，数据库资源都是一种损耗，而内存比较很快。
3 not in 和not exists
not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的bug。
请看下面的例子：
A表：
c1 | c2 |
1 | 2 |
1 | 3 |
B表
c1 | c2 |
1 | 2 |
1 | null |
select * from A where c2 not in( select c2 from B); -->执行结果：无
select * from A where not exists(select c2 from B where A.c2=B.c2); -->执行结果：
返回 A中的第一行记录not in出现了不期望的结果集，存在逻辑错误。如果not in中含有空值，则查询将不返回任何记录。如果子查询字段有非空限制，这时可以使用not in。或者在子查询后面做一个空判断
select * from A where c2 not in( select c2 from B where c2 is not null);下面是官方的解释：
如果查询语句使用了not in，那么对内外表都进行全部扫描，外部表也无法利用索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。
