SQL从入门到出门第12篇集合运算

程序员文章站 2022-04-15 11:36:20

...

本篇介绍SQL:2016（ISO/IEC 9075:2016）标准中定义的集合操作符（UNION、INTERSECT和EXCEPT），以及六种主流数据库中的实现及差异：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite。

我们知道，SQL 基于关系代数；因此，SQL 中的表或查询结果与集合理论中的集合非常类似，表或查询结果中的行相当于集合中的元素。不过，它们之间也存在一些不同之处：

集合理论中的集合不允许存在重复的数据，但是 SQL 允许。因此，SQL 中的集合也被称为多重集合（multiset）；
多重集合与集合理论中的集合都是无序的，但是 SQL 查询结果可以通过ORDER BY子句进行排序。

在 SQL 标准中，提供了针对多重集合的集合操作符：UNION、INTERSECT和EXCEPT。它们分别表示集合的并集（ $\cup$ ）、交集（ $\cap$ ）和差集（ $∖$ ）运算。

SQL 中的集合操作符要求参与运算的表或查询结果具有相同数量的列，以及对应列的类型必须匹配或兼容。某些数据库产品可能会为此尝试执行类型转换。

并集（UNION）

UNION操作符用于将两个查询结果合并成一个结果集，包含了第一个查询结果以及第二个查询结果中的数据：

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

其中，DISTINCT 表示将合并后的结果集进行去重；ALL 表示保留结果集中的重复记录；如果省略，默认为 DISTINCT。参考以下示意图：
SQL从入门到出门第12篇集合运算

MySQL、PostgreSQL 支持UNION DISTINCT语法，其他 4 个数据库只支持简写的UNION。

来看一个示例：

SELECT d.department_id
  FROM departments d
 UNION 
SELECT e.department_id
  FROM employees e;

首选，分别执行两个查询，获取 departments 表中的部门编号和 employees 表中的部门编号；然后将它们进行合并操作，并且排除重复的记录。最终结果返回了 28 条记录。

以下语句执行了类似的操作，只是不进行重复值的删除：

SELECT d.department_id
  FROM departments d
 UNION ALL
SELECT e.department_id
  FROM employees e;

查询返回了 134 条记录，包括重复值。

UNION基于行进行纵向组合，JOIN基于列进行横向组合。

如果要对集合运算后的数据进行排序，需要将ORDER BY子句写在最后；UNION之前的查询中不能出现排序操作：

-- Error case
SELECT d.department_id
  FROM departments d
 ORDER BY d.department_id
 UNION
SELECT e.department_id
  FROM employees e;

正确的写法如下：

SELECT d.department_id
  FROM departments d
 UNION
SELECT e.department_id
  FROM employees e
 ORDER BY department_id;

对于分组操作，每个 SELECT 查询都可以包含一个GROUP BY，但是它们只针对各自进行分组；如果想要对最终结果进行分组，需要在外层增加一个 SELECT 语句：

SELECT tmp.department_id
  FROM (SELECT d.department_id
          FROM departments d
         UNION
        SELECT e.department_id
          FROM employees e) tmp
 ORDER BY tmp.department_id;

最后，我们可以使用多个集合操作符合并多个结果集：

SELECT column1, column2
  FROM table1 
 UNION [DISTINCT | ALL]
SELECT col1, col2
  FROM table2
 UNION [DISTINCT | ALL]
SELECT c1, c2
  FROM table3;

交集（INTERSECT）

INTERSECT操作符用于返回两个查询结果中的共同部分，即同时出现在第一个查询结果和第二个查询结果中的数据：

SELECT column1, column2
  FROM table1 
INTERSECT [DISTINCT | ALL]
SELECT col1, col2
  FROM table2;

其中，DISTINCT 表示将最终的结果集进行去重；ALL 表示保留结果集中的重复记录；如果省略，默认为 DISTINCT。参考以下示意图：
SQL从入门到出门第12篇集合运算

注意中间的示意图，虽然 result1 中包含两个 1，但是最终结果只有一个 1；因为 result2 中的 1 只能和 result1 中的一个 1 匹配。

MySQL 不支持INTERSECT操作符；
只有 PostgreSQL 支持INTERSECT DISTINCT语法，其他 4 个数据库只支持简写的INTERSECT；
只有 PostgreSQL 和 Db2 支持INTERSECT ALL语法。

以下示例使用INTERSECT获取所有经理的员工编号：

SELECT m.employee_id
  FROM employees m
INTERSECT
SELECT e.manager_id
  FROM employees e
 ORDER BY employee_id;

对于 MySQL，以上示例可以通过JOIN实现：

SELECT DISTINCT
       m.employee_id
  FROM employees m
  JOIN employees e
    ON (m.employee_id = e.manager_id)
 ORDER BY employee_id;

差集（EXCEPT）

EXCEPT操作符用于返回出现在第一个查询结果中，但不在第二个查询结果中的数据：

SELECT column1, column2
  FROM table1 
EXCEPT [DISTINCT | ALL]
SELECT col1, col2
  FROM table2;

对于EXCEPT，只要 result2 中出现的数据（1），result1 中所有匹配的数据（重复出现的 1）都会被去除；对于EXCEPT ALL，result2 中出现的数据，在 result1 中只会消除一次（保留了一个 1 和两个 2）。

MySQL 不支持EXCEPT操作符；
Oracle 使用关键字MINUS替代EXCEPT，Db2 同时支持MINUS和EXCEPT；
只有 PostgreSQL 支持EXCEPT DISTINCT语法，其他 4 个数据库只支持简写的EXCEPT或MINUS；
只有 PostgreSQL 和 Db2 支持EXCEPT ALL语法。

以下查询用于检索那些目前没有员工的部门编号：

SELECT d.department_id
  FROM departments d
EXCEPT -- For Oracle, use MINUS
SELECT e.department_id
  FROM employees e
 ORDER BY department_id;

对于MySQL，可以通过LEFT JOIN实现以上功能：

SELECT d.department_id
  FROM departments d
  LEFT JOIN employees e
    ON (e.department_id = d.department_id)
 WHERE e.department_id IS NULL
 ORDER BY department_id;

查询中的WHERE子句正好找出左表（departments）在右表（employees）中不存在对应数据的那些记录。

集合运算优先级

我们已经介绍了 SQL 中的 3 种集合操作符：UNION、INTERSECT以及EXCEPT。实际使用中，我们可以通过多个集合操作符对多个查询结果进行组合。此时，需要注意它们之间的优先级和执行顺序：

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

首先，看一下相同的操作符的执行顺序。以下示例使用了两个UNION操作符，其中一个增加了 ALL 选项：

SELECT 1 FROM employees
UNION ALL
SELECT 1 FROM employees
UNION 
SELECT 1 FROM employees;

最终的结果只有一条记录（一个 1），因为最后的UNION去除了重复的数据。与此不同的是，下面的示例返回了多条重复的记录（108 个 1）：

SELECT 1 FROM employees
UNION 
SELECT 1 FROM employees
UNION ALL
SELECT 1 FROM employees;

接下来，看一个不同的操作符示例：

-- Not for MySQL
SELECT 1 FROM employees
UNION ALL
SELECT 1 FROM employees
INTERSECT 
SELECT 1 FROM employees;

除了Oracle 和 SQLite 之外，其他 3 个数据库的查询结果返回了多条重复的数据（108 个 1），因为INTERSECT先执行，UNION ALL最后保留的重复值。

Oracle 和 SQLite 中，所有集合操作符优先级相同。

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

-- Not for MySQL or SQLite
(
SELECT 1 FROM employees
UNION ALL
SELECT 1 FROM employees
)
INTERSECT 
SELECT 1 FROM employees;

-- For SQLite only
SELECT *
  FROM (SELECT 1 FROM employees
         UNION ALL
        SELECT 1 FROM employees)
INTERSECT 
SELECT 1 FROM employees;

查询结果只返回一条记录。

数据库	集合操作符	描述
Oracle	OK	Oracle 差集运算使用`MINUS`替代`EXCEPT` Oracle 不支持 `UNION` 的 DISTINCT 选项，使用默认行为 Oracle 不支持`INTERSECT`和`MINUS`的 DISTINCT \| ALL 选项 Oracle 中所有集合操作符优先级相同
MySQL	OK	MySQL 不支持`INTERSECT`操作符 MySQL 不支持`EXCEPT`操作符
SQL Server	OK	SQL Server 不支持 `UNION` 的 DISTINCT 选项，使用默认行为 SQL Server 不支持`INTERSECT`和`EXCEPT`的 DISTINCT \| ALL 选项
PostgreSQL	OK
Db2	OK	Db2 同时支持`EXCEPT`和`MINUS` Db2 不支持 `UNION` 的 DISTINCT 选项，使用默认行为
SQLite	OK	SQLite 不支持 `UNION` 的 DISTINCT 选项，使用默认行为 SQLite 不支持`INTERSECT`和`EXCEPT`的 DISTINCT \| ALL 选项 SQLite 中所有集合操作符优先级相同

欢迎留言讨论！

上一篇： Java到Groovy初瞰

下一篇：代码报错了！怎么办怎么办！快来见识下Python中的异常处理try/except

SQL从入门到出门第12篇集合运算

并集（UNION）

交集（INTERSECT）

差集（EXCEPT）

集合运算优先级

SQL从入门到出门第7篇分组与汇总

SQL从入门到出门第4篇查询条件

SQL从入门到出门第8篇多表连接查询

SQL从入门到出门第13章简单视图