【MySQL】not in遇上null的坑

今天遇到一个问题：

1、当 in 内的字段包含 null 的时候，正常过滤；

2、当 not in 内的字段包含 null 的时候，不能正常过滤，即使满足条件，最终结果也为 空。

测试如下：

select * from emp e;

当 in 内的字段包含 null 的时候，结果正常：

select * from emp e where e.mgr in (select comm from emp t);

当 not in 内的字段包含 null 的时候，结果为空，实际应为下面加上 is not null 条件时的结果才算正常：

select * from emp e where e.mgr not in (select comm from emp t);

加上 is not null 条件时，结果正常（由于NULL不等于NULL，也去除了 MGR 为 NULL 的那条记录，所以是12条）：

select * from emp e where e.mgr not in (select comm from emp t where t.comm is not null);

根据以上测试，得出用 not in 条件过滤时应首先排除 is not null 的记录，否则可能会出现意想不到的结果。

其实，当not in中包含null

select * from t where class not in ('1','2',null)

上面的sql相当于：

select * from t where class !='1'and !='2'and !=null

在SQL中，NULL值与任何其它值的比较（即使是NULL）永远不会为“真”。not in 相当于and条件，只要有一个false，那么所有的都为false，所以查出来的数据固定为空

解决方案：not in中的数据过滤掉空值 或 使用not exists

---

exists用法

select * from 表A where id in (select id from 表B)-- 上面的sql可以改写为：
select * from 表A where exists(select 1 from 表B where 表B.id=表A.id)

in以子查询表B的结果集为驱动，在表A中依次遍历查询id是否在子查询的结果集中存在

exists以外表表A为驱动表，若括号内的子查询有任意数据返回，表示当前行匹配成功

exists用于检查子查询是否至少会返回一行数据，强调的是是否返回结果集，不要求知道返回什么