SQL 查找只存在于一个表中的数据|极客教程

SQL 查找只存在于一个表中的数据，你希望从一个表（可以称之为源表）里找出那些在某个目标表里不存在的值。例如，你想找出在 DEPT 表中存在而在 EMP 表里却不存在的部门编号（如果有的话）。在示例数据中，DEPT 表里 DEPTNO 为 40 的数据并不存在于 EMP 表里，因此结果集应该如下所示。

SQL 查找只存在于一个表中的数据问题描述

你希望从一个表（可以称之为源表）里找出那些在某个目标表里不存在的值。例如，你想找出在 DEPT 表中存在而在 EMP 表里却不存在的部门编号（如果有的话）。在示例数据中，DEPT 表里 DEPTNO 为 40 的数据并不存在于 EMP 表里，因此结果集应该如下所示。

    DEPTNO
----------
        40

SQL 查找只存在于一个表中的数据解决方案

计算差集的函数对解决本问题尤其有用。DB2、PostgreSQL 和 Oracle 支持差集运算。如果你所使用的数据库管理系统没有提供差集函数，那么就要采用 MySQL 和 SQL Server 解决方案介绍的子查询技巧。
DB2 和 PostgreSQL
使用集合运算 EXCEPT。

1 select deptno from dept
2 except
3 select deptno from emp

Oracle
使用集合运算 MINUS。

1 select deptno from dept
2 minus
3 select deptno from emp

MySQL 和 SQL Server
使用子查询得到 EMP 表中所有的 DEPTNO，并将该结果传入外层查询，然后外层查询会检索 DEPT 表，找出没有出现在子查询结果里的 DEPTNO 值。

1 select deptno
2   from dept
3  where deptno not in (select deptno from emp)

SQL 查找只存在于一个表中的数据扩展知识

DB2 和 PostgreSQL

DB2 和 PostgreSQL 提供的内置函数使得该操作非常简单。EXCEPT 运算符获取第一个结果集的数据，然后从中删除第二个结果集的数据。这种运算非常像减法。
包括 EXCEPT 在内的集合运算符在使用上都有一些限制条件。参与运算的两个 SELECT 列表要有相同的数据类型和值个数。而且，EXCEPT 不返回重复项；并且 Null 值不会产生问题，这与 NOT IN 子查询不同（参考对 MySQL 和 SQL Server 的讨论）。EXCEPT 运算符会返回只存在于第一个查询（EXCEPT 前面的查询）结果里而不存在于第二个查询（EXCEPT 后面的查询）结果里的行。

Oracle

Oracle 解决方案除了集合运算符叫作 MINUS 而不是 EXCEPT，其他方面与 DB2 和 PostgreSQL 的解决方案相同。另外，上述解释也适用于 Oracle。

MySQL 和 SQL Server

这个子查询会获取 EMP 表中所有的 DEPTNO。外层查询会返回 DEPT 表中“不存在于”或“未被包含在”子查询结果集里的所有的 DEPTNO 值。
当你使用 MySQL 和 SQL Server 的解决方案时，需要考虑排除重复项。其他数据库基于 EXCEPT 或者 MINUS 的解决方案已经从结果集中排除了重复的行，确保每个 DEPTNO 只出现一次。当然，之所以能这样做，是因为示例数据中的 DEPTNO 是表的主键。如果 DEPTNO 不是主键，你可以使用 DISTINCT 来确保每个在 EMP 表里缺少的 DEPTNO 值只出现一次，如下所示。

select distinct deptno
  from dept
where deptno not in (select deptno from emp)

在使用 NOT IN 时，要注意 Null 值。考虑如下的表 NEW_DEPT。

create table new_dept(deptno integer)
insert into new_dept values (10)
insert into new_dept values (50)
insert into new_dept values (null)

如果你试着使用 NOT IN 子查询检索存在于 DEPT 表却不存在于 NEW_DEPT 表的 DEPTNO，会发现查不到任何值。

select *
  from dept
 where deptno not in (select deptno from new_dept)

DEPTNO 为 20、30 和 40 的数据虽然不在 NEW_DEPT 表中，却没被上述查询检索到。原因就在于 NEW_DEPT 表里有 Null 值。子查询会返回 3 行 DEPTNO，分别为 10、50 和 Null 值。IN 和 NOT IN 本质上是 OR 运算，由于 Null 值参与 OR 逻辑运算的方式不同，IN 和 NOT IN 将会产生不同的结果。考虑以下分别使用 IN 和 OR 的例子。

select deptno
  from dept
 where deptno in ( 10,50,null )
　
 DEPTNO
-------
     10
　
select deptno
  from dept
 where (deptno=10 or deptno=50 or deptno=null)
　
 DEPTNO
-------
     10

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

select deptno
  from dept
 where deptno not in ( 10,50,null )
　
( no rows )
　
select deptno
  from dept
 where not (deptno=10 or deptno=50 or deptno=null)
　
( no rows )

如你所见，条件 DEPTNO NOT IN (10, 50, NULL) 等价于：

not (deptno=10 or deptno=50 or deptno=null)

对于 DEPTNO 是 50 的情况，下面是这个表达式的展开过程。

not (deptno=10 or deptno=50 or deptno=null)
(false or false or null)
(false or null)
null

在 SQL 中，TRUE or NULL 的运算结果是 TRUE，但 FALSE or NULL 的运算结果却是 Null ！一旦混入了 Null，结果就会一直保持为 Null（除非你使用实例 1.11 介绍的技巧特意测试是否含有 Null）。必须谨记，当使用 IN 谓词以及当执行 OR 逻辑运算的时候，你要想到是否会涉及 Null 值。
为了避免 NOT IN 和 Null 值带来的问题，需要结合使用 NOT EXISTS 和关联子查询。关联子查询指的是外层查询执行后获得的结果集会被内层子查询引用。下面的例子给出了一个免受 Null 值影响的替代方案（回到“问题”部分给出的那个原始查询语句）。

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

上述查询语句遍历并评估 DEPT 表的每一行。针对每一行，会有如下操作。
(1) 执行子查询并检查当前的部门编号是否存在于 EMP 表。要注意关联条件 D.DEPTNO = E.DEPTNO，它通过部门编号把两个表连接起来。
(2) 如果子查询有结果返回给外层查询，那么 EXISTS (...) 的评估结果是 TRUE，这样 NOT EXISTS (...) 就是 FALSE，如此一来，外层查询就会舍弃当前行。
(3) 如果子查询没有返回任何结果，那么 NOT EXISTS (...) 的评估结果是 TRUE，由此外层查询就会返回当前行（因为它是一个不存在于 EMP 表中的部门编号）。
把 EXISTS/NOT EXISTS 和关联子查询一起使用时，SELECT 列表里的项目并不重要，因此我在这个例子中用了 SELECT NULL，这是为了让你把注意力放到子查询的连接操作上，而非 SELECT 列表的项目上。