sql替换in 和 not in的方法

1. in和exists

  in 是把外表和内表作hash连接，

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

  如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in。

  一般情况下，主表中的数据要少，从表的数据要多。

  例：table a(小表) 、table b(大表)

select * from a where id in(select in from b)　　-->效率低，用到了a表上id列的索引；

select * from a where exists(select id from b where id=a.id)　　-->效率高，用到了b表上id列的索引。

  与之相反：

select * from b where id in(select id from a)　　-->效率高，用到了b表上id列的索引

select * from b where exists(select id from a where id=b.id)　　-->效率低，用到了a表上id列的索引。

  (1)性能的考虑 此时就按 子表大主表小用exist, 子表小主表大用in的原则就可以.

  (2)写法的不同, exist的where条件是: "...... where exist (..... where a.id=b.id)"

         in的where条件是: " ...... where id in ( select id from......)"

2. not in和not exists

在做查询时，想要查询有联系的两张表，想得到结果是一张表有而另外一张表没有的数据时，我们通常会用not in：

select * from a where a.id not in (select id from b)

通常，我们会习惯性的使用not in，在数据比较少的时候是可以的，但是一旦数据量大了，not in的效率就会显得差了点。

因为not in 和not exists 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。

所以无论那个表大，用not exists都比not in要快。

所以推荐使用not exists或者外连接来代替：

select * from a where not exists(select id from b where id=a.id)

或者

select * from a left join b on a.id = b.id where b.id is null;

原文链接：https://blog.csdn.net/weixin_32562973/article/details/113279732