SQL 从一个表检索与另一个表不相关的行|极客教程

SQL 从一个表检索与另一个表不相关的行，两个表有相同的键，你想在一个表里查找与另一个表不相匹配的行。例如，你想找出哪些部门没有员工。结果集如下所示。

SQL 从一个表检索与另一个表不相关的行问题描述

两个表有相同的键，你想在一个表里查找与另一个表不相匹配的行。例如，你想找出哪些部门没有员工。结果集如下所示。

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

如果想要找到每一个员工就职的部门，需要基于 EMP 表和 DEPT 表的 DEPTNO 列进行相等连接查询。DEPTNO 是两个表都有的列。不幸的是，相等连接无法找到哪些部门没有员工。这是因为，针对 EMP 表和 DEPT 表做相等连接操作，将返回满足连接条件的所有行。相反，你只想从 DEPT 表里找出那些不满足连接条件的行。
本问题乍看起来和前一个实例相同，但其实它们之间有微妙的差别。不同之处在于，前一个实例仅仅返回了没有出现在 EMP 表中的部门编号。然而，本实例可以很方便地从 DEPT 表中获取其他列。

SQL 从一个表检索与另一个表不相关的行解决方案

基于共同列把两个表连接起来，返回一个表的所有行，不论这些行在另一个表里是否存在匹配行。然后，只保留那些不匹配的行即可。
DB2、MySQL、PostgreSQL 和 SQL Server
使用外连接并过滤掉 Null 值（关键字 OUTER 是可选的）。

1 select d.*
2   from dept d left outer join emp e
3     on (d.deptno = e.deptno)
4  where e.deptno is null

Oracle
对于 Oracle 9i 及其后续版本，上述解决方案仍然适用。当然，你也可以使用 Oracle 专有的外连接语法。

1 select d.*
2   from dept d, emp e
3  where d.deptno = e.deptno (+)
4    and e.deptno is null

Oracle 8i 数据库及更早的版本只能使用上述专有语法（注意，圆括号里是 +）来完成外连接操作。

SQL 从一个表检索与另一个表不相关的行扩展知识

这个解决方案使用了外连接，并且只保留不匹配的行。这种操作有时候被称为反连接（anti-join）。为了更好地理解反连接，我们先来看一下没有过滤掉 Null 值的结果集。

select e.ename, e.deptno as emp_deptno, d.*
  from dept d left join emp e
    on (d.deptno = e.deptno)
　
ENAME      EMP_DEPTNO     DEPTNO DNAME          LOC
---------- ---------- ---------- -------------- -------------
SMITH              20         20 RESEARCH       DALLAS
ALLEN              30         30 SALE           CHICAGO
WARD               30         30 SALES          CHICAGO
JONES              20         20 RESEARCH       DALLAS
MARTIN             30         30 SALES          CHICAGO
BLAKE              30         30 SALES          CHICAGO
CLARK              10         10 ACCOUNTING     NEW YORK
SCOTT              20         20 RESEARCH       DALLAS
KING               10         10 ACCOUNTING     NEW YORK
TURNER             30         30 SALES          CHICAGO
ADAMS              20         20 RESEARCH       DALLAS
JAMES              30         30 SALES          CHICAGO
FORD               20         20 RESEARCH       DALLAS
MILLER             10         10 ACCOUNTING     NEW YORK
                              40 OPERATIONS     BOSTON

注意，最后一行的 EMP.ENAME 和 EMP_DEPTNO 都是 Null 值。这是因为没有员工在编号为 40 的部门工作。该解决方案使用 WHERE 子句，只保留了 EMP_DEPTNO 是 Null 值的行（这样只留下 DEPT 表中无法与 EMP 表相匹配的行）。