SQL向：用 NOT EXISTS 解决 NOT IN 和 NULL 之间的问题

数据介绍

假设现在有一张名为 DEPT 的表，它存在两列，分别代表部门名称与编号，如下所示，

ENAME_AND_DNAME	DEPTNO
ACCOUNTING	10
RESEARCH	20
SALES	30
OPERATIONS	40

还有一张名为 EMP 的表，它也存在两列，分别代表部门名称与编号，如下所示，

ENAME_AND_DNAME	DEPTNO
CLARK	10
KING	10
MILLER	10

 

目的

你想找出在 DEPT 表中存在而在 EMP 表里却不存在的部门编号（如果有的话）。

 

方法

这里主要讲一下 MySQL 的方法

SELECT deptno 
from DEPT
WHERE deptno not in (SELECT deptno from EMP)

这里需要注意的几点是，

• 使用 MySQL 的方法时，要注意这种方法保留了重复项，如果想要去除重复项，可以考虑使用 DISTINCT 关键字；
• 在使用 NOT IN 时，要注意 Null 值；

此处着重讲一下第二点。

现在我们新建立一张名为 new_dept 的表。

deptno

10

50

null

如果你试着查询在 DEPT表里的数据，而不在 new_dept 表里的数据时，你会发现查不到任何值。

SELECT * 
from DEPT
WHERE deptno not in (SELECT deptno from new_dept)

这其中的原因在于 new_dept 表里存在 null 值。

上述 SQL 语句中的子查询会返回3条结果，它们的 deptno 分别为 10、50 和 null。IN 和 NOT IN 本质上是 OR 运算，由于 Null 值 参与 OR 逻辑运算的方式不同，IN 和 NOT IN 将会产生不同结果。针对这个结论，我们来看几个例子，

Example 1

select deptno
from dept
where deptno in ( 10,50,null )

返回结果为

 DEPTNO
 -------
 10

 

Example 2

select deptno
from dept
where (deptno=10 or deptno=50 or deptno=null)

返回结果为

 DEPTNO
 -------
 10

 

再来看看使用 NOT IN 和 NOT OR 的例子。

Example 3

select deptno
from dept
where deptno not in ( 10,50,null )

返回结果为

Empty set (0.00 sec)

Example 4

select deptno
from dept
where not (deptno=10 or deptno=50 or deptno=null)

返回结果为

Empty set (0.00 sec)

stackoverflow 上针对 NOT IN 和 NULL 连用存在的问题提出的疑问（https://stackoverflow.com/questions/129077/not-in-clause-and-null-values）。

为了避免 NOT IN 和 NULL 连用存在的问题，我们可以考虑结合使用 NOT EXISTS 和 关联子查询。关联子查询指的是外层查询执行后获得的结果集会被内层子查询引用。

select d.deptno
from dept d
where not exists ( select null
from emp e
where d.deptno = e.deptno )

返回结果为

+--------+
| deptno |
+--------+
|     10 |
|     50 |
|   NULL |
+--------+

上述语句遍历并评估 dept 表的每一行。针对每一行，都会有如下操作。

1. 执行子查询并检查当前的部门编号是否存在于 EMP 表。要注意关联条件 D.DEPTNO = E.DEPTNO，它通过部门编号把两个表连接起来。

2. 如果子查询有结果返回给外层查询，那么 EXISTS (...) 的评估结果是 TRUE，这样 NOT EXISTS (...) 就是 FALSE，如此一来，外层查询就会舍弃当前行。

3. 如果子查询没有返回任何结果，那么 NOT EXISTS (...) 的评估结果是 TRUE，由此外层查 询就会返回当前行(因为它是一个不存在于 EMP 表中的部门编号)。

Note：把 EXISTS/NOT EXISTS 和关联子查询一起使用时，SELECT 列表里的项目并不重要，因此这个例子中用了 SELECT NULL，这是为了把注意力放到子查询的连接操作上，而非 SELECT 列表的项目上。