SQL 集合查询

SQL 子查询，我们介绍了 SQL 中各种形式的子查询，以及与子查询相关的 IN、ALL、ANY/SOME、EXISTS 运算符。本文我们来讨论另一种从多个查询中返回组合结果的方法：集合运算。

SQL 中的集合操作符可以将多个查询的结果组合成一个结果。本篇讨论三种集合操作符：UNION [ALL]、INTERSECT 以及 EXCEPT，同时还演示了它们的执行优先级。有时候，可以利用连接查询实现与集合操作相同的效果。

集合运算

数据库中的表与集合理论中的集合非常类似，表是由行组成的集合。因此， SQL 支持基于行的各种集合操作：并集运算（UNION）、交集运算（INTERSECT）和差集运算（EXCEPT）。它们都用于将两个查询的结果集合并成一个结果集，但是合并的规则各不相同。

需要注意的是，SQL 集合操作中的两个查询结果需要满足以下条件：

• 结果集中字段的数量和顺序必须相同；
• 结果集中对应字段的类型必须匹配或兼容。

也就是说，对于参与运算的两个查询结果，要求它们的字段结构相同。如果一个查询返回 2 个字段，另一个查询返回 3 个字段，肯定无法合并。如果一个查询返回数字类型的字段，另一个查询返回字符类型的字段，通常也无法合并；不过数据库可能会尝试执行隐式的类型转换。

交集求同

INTERSECT 操作符用于返回两个查询结果中的共同部分，即同时出现在第一个查询结果和第二个查询结果中的数据，并且对最终结果进行了去重操作。交集运算的示意图如下：

其中，1 和 2 是两个查询中都存在的数据；因此交集运算的结果只包含 1 和 2。

我们创建一个年度优秀员工表（excellent_emp），用于演示集合操作：

CREATE TABLE excellent_emp(
    year   INT NOT NULL, 
    emp_id INTEGER NOT NULL,
    CONSTRAINT pk_excellent_emp PRIMARY KEY (YEAR, emp_id)
);

INSERT INTO excellent_emp VALUES (2018, 9);
INSERT INTO excellent_emp VALUES (2018, 11);
INSERT INTO excellent_emp VALUES (2019, 9);
INSERT INTO excellent_emp VALUES (2019, 20);

以下示例用于查找 2018 年和 2019 年都是优秀员工的员工编号：

-- Oracle、SQL Server 以及 PostgreSQL 实现
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018
INTERSECT
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019;

emp_id|
------|
     9|

其中，INTERSECT 表示交集运算。第一个查询语句返回了 9 和 11，第二个查询语句返回了 9 和 20，最终结果返回共同的 9。集合操作返回的字段名由第一个语句决定，此处两个语句拥有相同的字段名（emp_id）。

MySQL 不支持交集运算。

以上示例可以改写为等价的连接查询：

SELECT t1.emp_id
  FROM excellent_emp t1
  JOIN excellent_emp t2
    ON (t1.emp_id = t2.emp_id
        AND t1.year = 2018
        AND t2.year = 2019);

emp_id|
------|
     9|

交集运算都可以改写为等价的等值内连接查询。

并集存异

UNION 操作符用于将两个查询结果相加，返回出现在第一个查询结果或者第二个查询结果中的数据。并集运算的示意图如下：

其中，1 和 2 是两个查询结果中都存在的数据；不过它们在最终结果中各出现一次，UNION 操作符排除了查询结果中的重复记录。

以下语句用于查找 2018 年和 2019 年所有的优秀员工：

SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018
 UNION
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019;

emp_id|
------|
     9|
    11|
    20|

第一个查询返回了 9 和 11，第二个查询返回了 9 和 20，但最终的结果中只有一个 9。

UNION 操作符还支持 ALL 选项，表示保留查询结果中的重复记录：

SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018
 UNION ALL
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019;

emp_id|
------|
     9|
    11|
     9|
    20|

UNION ALL 保留了两个 9，因为该员工在 2018 年和 2019 年都是优秀员工。

因此，并集运算的完整语法如下：

SELECT col1, col2, ...
  FROM table1
 UNION [DISTINCT | ALL]
SELECT col1, col2, ...
  FROM table2;

其中，DISTINCT 表示将合并后的结果进行去重操作；ALL 表示保留结果集中的重复记录；默认为 DISTINCT。

通常来说，UNION ALL 不需要进行重复值的排除，性能比 UNION 更好；尤其是数据量比较大的情况下。

上面的 UNION 示例可以改写为等价的全外连接查询：

-- Oracle、SQL Server 以及 PostgreSQL 实现
SELECT COALESCE(t1.emp_id,t2.emp_id) emp_id
  FROM (SELECT emp_id  
          FROM excellent_emp
         WHERE year = 2018) t1
  FULL JOIN (SELECT emp_id  
               FROM excellent_emp
              WHERE year = 2019) t2
    ON (t1.emp_id = t2.emp_id);

emp_id|
------|
     9|
    11|
    20|

其中，t1 代表了 2018 年的优秀员工，t2 代表了 2019 年的优秀员工； 全外连接返回了 2018 年和 2019 年所有的优秀员工，注意 COALESCE 函数的作用。

差集排他

EXCEPT 操作符用于返回出现在第一个查询结果中，但不在第二个查询结果中的记录，并且对最终结果进行了去重操作。差集运算的示意图如下：

第一个查询的结果中只有 3 没有出现在第二个查询的结果中，因此差集运算的结果只保留了 3。

以下语句查找 2019 年被评为优秀，但是 2018 年不是优秀的员工：

-- SQL Server 以及 PostgreSQL 实现
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019
EXCEPT
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018;

emp_id|
------|
    20|

-- Oracle 实现
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019
 MINUS
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018;

EMP_ID|
------|
    20|

查询结果显示，只有 20 号员工是 2019 年新晋的优秀员工。Oracle 使用关键字 MINUS 表示差集运算。

MySQL 不支持差集运算。

差集运算也可以通过左外连接实现：

SELECT t1.emp_id
  FROM excellent_emp t1
  LEFT JOIN excellent_emp t2 ON (t1.emp_id = t2.emp_id AND t2.year = 2018)
 WHERE t1.year = 2019
   AND t2.emp_id IS NULL;

emp_id|
------|
    20|

其中，左外连接返回了所有的优秀员工；然后利用 WHERE 条件找出其中 2019 年是优秀但 2018 年不是优秀的员工。

在使用集合运算符的时候，还需要注意几个事项；首先是排序操作。

集合操作中的排序

如果要对集合操作的结果进行排序，需要将 ORDER BY 子句写在最后；集合操作符之前的查询语句中不能出现排序操作。以下是一个错误的语法示例：

-- 集合操作中的错误排序示例
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019
 ORDER BY emp_id DESC
 UNION
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018;

执行以上语句将会返回一个语法错误。在集合操作之前进行排序并没有实际意义，因为最终结果的顺序可能会发生变化。正确的做法是在整个语句的最后指定排序操作：

SELECT emp_id
  FROM excellent_emp
 WHERE year = 2019
 UNION
SELECT emp_id
  FROM excellent_emp
 WHERE year = 2018
 ORDER BY emp_id DESC;

emp_id|
------|
    20|
    11|
     9|

除了 ORDER BY 子句的位置，还有一个常见的问题就是集合操作符的优先级。

集合操作符的优先级

SQL 提供了 3 种集合操作符：UNION [ALL]、INTERSECT 以及 EXCEPT。我们可以通过多个集合操作符将多个查询的结果进行组合。此时，需要注意它们之间的优先级和执行顺序：

• INTERSECT 的优先级高于 UNION 和 EXCEPT，但是 Oracle 中所有集合操作符的优先级相同；
• 相同的集合操作符按照从左至右的顺序执行；
• 使用括号可以明确指定执行的顺序。

以下示例说明了不同集合操作符的执行顺序：

-- Oracle、SQL Server 以及 PostgreSQL 实现
SELECT 1 FROM department
UNION ALL
SELECT 1 FROM department
INTERSECT 
SELECT 1 FROM department;

该语句在 SQL Server 和 PostgreSQL 中返回了 7 个重复的 1；因为 INTERSECT 先执行，然后 UNION ALL 保留了重复值。该语句在 Oracle 中则返回了 1 个 1；因为 UNION ALL 先执行，然后 INTERSECT 去除了重复值。

以下语句演示了相同集合操作符的执行顺序：

SELECT 1 FROM department
UNION ALL
SELECT 1 FROM department
UNION
SELECT 1 FROM department;

该查询的结果中只有 1 个 1，因为最后的 UNION 去除了重复的数据。与此不同的是，下面的示例返回了 7 个重复的 1：

SELECT 1 FROM department
UNION
SELECT 1 FROM department
UNION ALL
SELECT 1 FROM department;

最后，我们可以使用括号来修改多个集合操作符的执行顺序：

-- Oracle、SQL Server 以及 PostgreSQL 实现
SELECT 1 FROM department
UNION ALL
(SELECT 1 FROM department
INTERSECT 
SELECT 1 FROM department);

该语句括号内的集合操作先执行，UNION ALL 后执行；因此查询的结果返回了 7 个重复的 1。