SQL 从多个表中返回缺失值|极客教程

SQL 从多个表中返回缺失值，你想从多个表中返回缺失值。找到存在于 DEPT 表而不存在于 EMP 表的数据（即没有员工的部门）需要使用外连接。考虑下面的查询语句，该查询返回了 DEPT 表中所有的 DEPTNO 和 DNAME，以及每个部门里全部员工的名字（如果这个部门有员工的话）。

SQL 从多个表中返回缺失值问题描述

你想从多个表中返回缺失值。找到存在于 DEPT 表而不存在于 EMP 表的数据（即没有员工的部门）需要使用外连接。考虑下面的查询语句，该查询返回了 DEPT 表中所有的 DEPTNO 和 DNAME，以及每个部门里全部员工的名字（如果这个部门有员工的话）。

select d.deptno,d.dname,e.ename
  from dept d left outer join emp e
    on (d.deptno=e.deptno)
　
    DEPTNO DNAME          ENAME
---------- -------------- ----------
        20 RESEARCH       SMITH
        30 SALES          ALLEN
        30 SALES          WARD
        20 RESEARCH       JONES
        30 SALES          MARTIN
        30 SALES          BLAKE
        10 ACCOUNTING     CLARK
        20 RESEARCH       SCOTT
        10 ACCOUNTING     KING
        30 SALES          TURNER
        20 RESEARCH       ADAMS
        30 SALES          JAMES
        20 RESEARCH       FORD
        10 ACCOUNTING     MILLER
        40 OPERATIONS

最后一行是 OPERATIONS 部门，这个部门虽然没有员工，却也出现在了查询结果中，这是因为 DEPT 表外连接了 EMP 表。现在假设有一个员工不属于任何部门，你将如何返回以上结果集，并且包含那个不属于任何部门的员工呢？换句话说，你希望在同一个查询语句里既外连接到 EMP 表又外连接到 DEPT 表。在创建了新的员工数据之后，第一次尝试可能如下所示。

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
select 1111,'YODA','JEDI',null,hiredate,sal,comm,null
  from emp
 where ename = 'KING'
　
select d.deptno,d.dname,e.ename
  from dept d right outer join emp e
    on (d.deptno=e.deptno)
　
    DEPTNO DNAME          ENAME
---------- -------------- ----------
        10 ACCOUNTING     MILLER
        10 ACCOUNTING     KING
        10 ACCOUNTING     CLARK
        20 RESEARCH       FORD
        20 RESEARCH       ADAMS
        20 RESEARCH       SCOTT
        20 RESEARCH       JONES
        20 RESEARCH       SMITH
        30 SALES          JAMES
        30 SALES          TURNER
        30 SALES          BLAKE
        30 SALES          MARTIN
        30 SALES          WARD
        30 SALES          ALLEN
                          YODA

以上外连接查询包含了那个新的员工，却丢失了先前结果集里的 OPERATIONS 部门。最终的结果集应该既包括 YODA，也包括 OPERATIONS，如下所示。

    DEPTNO DNAME          ENAME
---------- -------------- ----------
        10 ACCOUNTING     CLARK
        10 ACCOUNTING     KING
        10 ACCOUNTING     MILLER
        20 RESEARCH       ADAMS
        20 RESEARCH       FORD
        20 RESEARCH       JONES
        20 RESEARCH       SCOTT
        20 RESEARCH       SMITH
        30 SALES          ALLEN
        30 SALES          BLAKE
        30 SALES          JAMES
        30 SALES          MARTIN
        30 SALES          TURNER
        30 SALES          WARD
        40 OPERATIONS
                           YODA

SQL 从多个表中返回缺失值解决方案

使用全外连接（full outer join），基于一个共同值从两个表中返回缺失值。
DB2、MySQL、PostgreSQL 和 SQL Server
使用显式的全外连接命令从两个表中返回缺失的行以及相匹配的行。

1 select d.deptno,d.dname,e.ename
2   from dept d full outer join emp e
3     on (d.deptno=e.deptno)

或者，也可以合并两个外连接的查询结果。

1 select d.deptno,d.dname,e.ename
2   from dept d right outer join emp e
3     on (d.deptno=e.deptno)
4  union
5 select d.deptno,d.dname,e.ename
6   from dept d left outer join emp e
7     on (d.deptno=e.deptno)

Oracle
对于 Oracle 9i 数据库及其后续版本，上述解决方案仍然适用。除此之外，我们也可以使用 Oracle 专有的外连接语法。对于 Oracle 8i 数据库及更早的版本，只能使用专有语法实现外连接。

1 select d.deptno,d.dname,e.ename
2   from dept d, emp e
3  where d.deptno = e.deptno(+)
4  union
5 select d.deptno,d.dname,e.ename
6   from dept d, emp e
7  where d.deptno(+) = e.deptno

SQL 从多个表中返回缺失值扩展知识

全外连接查询其实就是合并两个表的外连接查询的结果集。为了理解全外连接背后的运行原理，直接执行每一个外连接查询，然后合并其查询结果集即可。下面的查询找出了 DEPT 表里与 EMP 表相匹配的所有行（如果存在的话）。

select d.deptno,d.dname,e.ename
  from dept d left outer join emp e
    on (d.deptno = e.deptno)
　
DEPTNO DNAME          ENAME
------ -------------- ----------
    20 RESEARCH       SMITH
    30 SALES          ALLEN
    30 SALES          WARD
    20 RESEARCH       JONES
    30 SALES          MARTIN
    30 SALES          BLAKE
    10 ACCOUNTING     CLARK
    20 RESEARCH       SCOTT
    10 ACCOUNTING     KING
    30 SALES          TURNER
    20 RESEARCH       ADAMS
    30 SALES          JAMES
    20 RESEARCH       FORD
    10 ACCOUNTING     MILLER
    40 OPERATIONS

接下来的这个查询找出了 EMP 表里与 DEPT 表相匹配的所有行（如果存在的话）。

select d.deptno,d.dname,e.ename
  from dept d right outer join emp e
    on (d.deptno = e.deptno)

DEPTNO DNAME          ENAME
------ -------------- ----------
    10 ACCOUNTING     MILLER
    10 ACCOUNTING     KING
    10 ACCOUNTING     CLARK
    20 RESEARCH       FORD
    20 RESEARCH       ADAMS
    20 RESEARCH       SCOTT
    20 RESEARCH       JONES
    20 RESEARCH       SMITH
    30 SALES          JAMES
    30 SALES          TURNER
    30 SALES          BLAKE
    30 SALES          MARTIN
    30 SALES          WARD
    30 SALES          ALLEN
                      YODA

合并上面的两个查询结果，就可以得到最终的结果集。