SQL优化 —— in与not in

1 in

文章从三方面介绍：查询集合，查询出错率，查询效率。主要讲什么时候可以用in，用in效果不佳时，用什么替代。总结出两点：

• in后的查询集合不确定
  例如in (select…)，应判断内查询与外查询的关系。当内查询的表小时（小于外查询的表），用in效率高。当外查询的表小时（小于内查询的表），用exists效率高。（因为in 先执行内查询，再执行外查询；而 exists先执行外查询再执行内查询）
• in后的查询集合是确定且有限
  集合内的值连续时，应尽可能使用between …and 。
  集合内的值不连续时，可以用in，例如in (1,3,7)。

1.1 查询集合

in后的查询集合不确定时，例如in (select…)，应判断内查询与外查询的关系。当内查询的表小时（小于外查询的表），用in效率高。当外查询的表小时（小于内查询的表），用exists效率高。（因为in 先执行内查询，再执行外查询；而 exists先执行外查询再执行内查询）
in后的查询集合是确定且有限的集合，且集合内的值连续时，应尽可能使用between …and 。
in后的查询集合是确定且有限的集合，但集合内的值不连续时，可以用in，例如in (1,3,7)。

1.2 查询出错率

SQL中用in 或 not in 容易出错，所以应尽量避免使用in或not in。什么时候可以使用？确定且有限的集合时，可以使用。如 IN （0，1，2）。

下面的例子参考自：SQL性能优化 - 避免使用 IN 和 NOT IN


一位大神曾经说过，如果是确定且有限的集合时，可以使用。如 IN （0，1，2）。
参考文献：[笔记] SQL性能优化 - 避免使用 IN 和 NOT IN

1.3 查询效率

一句话总结：in先执行子查询，再执行主查询；而exists先执行主查询，再执行子查询。

具体的：

in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。
exists先执行主查询，再根据主查询的结果，执行子查询。
具体例子，见参考文献。
应用场景：
当子查询的结果较小，且主查询的表较大且有索引时，应用in。
当主查询的结果较小，而子查询的表较大且有索引时，应用exists。

即先把大表减小，然后再去匹配另一个表。

in和exists的不同，即驱动顺序的不同(这是性能变化的关键)。如果是exists，那么以外层表为驱动表，先被访问。如果是IN，那么先执行子查询，以内层表为驱动表。
所以我们以驱动表的快速返回为目的（即越快获得驱动表，越好），那么就会考虑到索引及结果集的关系了 。另外IN时不对NULL进行处理。

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

参考文献：SQL中in和exists的区别

2 not in

2.1 查询集合中有null，不使用not in

如果查询集合中有null，不适合用not in，容易出错（查询结果为空）。例如：x not in (1,2,null)。

1. 为什么x not in (1,2,null)会出错呢？
   因为x not in (1,2,null) 等价于x!=1 and x!=2 and x!=null ，而在sql中，对于任意x，x!=null永远为false，所以整体结果永远为false，所以查询结果永远为空。

2. 为什么在sql中，x!=null为false呢？
   参考文献：SQL条件!=null查不出数据
   sql的逻辑表达式有三种可能值：true、false、unknown。
   其他语言的逻辑表达式都是只有两种可能值，而sql中有三种，这是sql特有的。从字面上理解unknown就是：什么都不知道。在sql中将任何值（包括null本身）与null作比较，都会返回unknown。而在查询表达式（having、where）中， unknown会被视为false。
   但是并不是所有情况下，都会将unknown视为false，在 check约束中，就会将unknown视为true。所以，在用check约束将字段设置为>=0时，还可以向该字段中插入null值，因为在check中，null>=0的结果unknown，会被视为true。

2.2 查询集合中有null，不使用not in，那用什么代替呢？

三种解决办法：

1. 仍使用not in ，但修改sql语句。
   将in后的查询结果中的null过滤掉。
   例如：

select name from table1 where name not in (
 select name from table2 where name is not null
);

2. 使用join 代替

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

用and：
select Table_A.ID,Table_A.name 
from Table_A left join Table_B on Table_A.ID=Table_B.ID and Table_B.ID is null
用where：
select Table_A.ID,Table_A.name 
from Table_A left join Table_B on Table_A.ID=Table_B.ID where Table_B.ID is null

3. 使用not exists代替

共有的不要了，要独有的。
select * from a 
where not exists(
     select 1 from b where a.col = b.col
);