集合运算

Q: MySQL 支持 INTERSECT 和 EXCEPT 吗？

支持，从 MySQL 8.0.31 开始；MariaDB 从 10.3 版开始支持。在更早的版本中，INTERSECT 可以用 IN、EXISTS 或 INNER JOIN 实现，EXCEPT 可以用 NOT EXISTS 或 LEFT JOIN ... WHERE ... IS NULL 实现。Oracle 传统上用 MINUS 代替 EXCEPT。

Q: UNION 结果的列名是什么？

列名来自第一个 SELECT。第二个及之后查询中的别名会被忽略，所以请在第一个查询中给列起最终的名字。语句末尾的 ORDER BY 引用的就是这些名字。

Q: 如何只对 UNION 的某一部分排序或限制行数？

把这个 SELECT 放进括号：(SELECT title FROM film ORDER BY length DESC LIMIT 5) UNION ALL (SELECT ...)。不加括号时，ORDER BY 和 LIMIT 作用于整个合并结果。某一部分中没有 LIMIT 的 ORDER BY 可能会被忽略，因为最终结果集的顺序本来就没有保证。

第 5.10 课 · 阅读时间：约 8 分钟 · 练习题 ↓

本课介绍 SQL 中的集合运算。你将学习如何合并多个查询的结果、找出共同的行，以及排除不需要的值。我们会通过 Sakila 示例讲解 UNION、UNION ALL、INTERSECT 和 EXCEPT。学完本课后，你将能够为不同的分析场景选择合适的运算符。

在前面的课程中，你学习了如何用 JOIN 连接表，以及数据库引擎如何执行这些连接。现在我们来看另一种思路：有时你并不想按键把行连接起来，而是想合并和比较整个结果集。

当你想合并多个查询的数据、找出两类人群的交集，或者去掉已经出现在另一个列表中的行时，集合运算就很有用。在实践中，它们经常出现在报表、数据质量检查和最终名单的整理中。

SQL 集合运算：UNION、INTERSECT 和 EXCEPT

什么是集合运算

集合运算处理的不是一张表中的行，而是两个或多个查询的结果。用 SQL 的话说，每个 SELECT 返回一个行集合，而 UNION 或 INTERSECT 等运算符按照特定规则组合这些集合。

最常用的四个运算符是：

UNION：合并结果并去除重复项；
UNION ALL：合并结果并保留重复项；
INTERSECT：只保留同时出现在两个集合中的行；
EXCEPT：返回第一个集合中有、而第二个集合中没有的行。

重要： 并非所有数据库都以完全相同的方式支持这些运算符。在不同数据库引擎之间迁移查询时，务必检查版本和兼容性。

通用规则

要使用集合运算，两个 SELECT 查询必须返回兼容的结果。

对查询的要求

列数相同；
对应位置上的数据类型兼容；
列的顺序相同；
尽可能保证值的含义相同。

如果需要对最终结果排序，请把 ORDER BY 写在整个表达式的最后。

SELECT column1, column2
FROM table_a
UNION
SELECT column1, column2
FROM table_b
ORDER BY column1;

UNION 和 UNION ALL

UNION 和 UNION ALL 看起来很像，但解决的是不同的问题。

UNION 会去除最终结果中的重复项。
UNION ALL 保留所有行，即使它们重复。

示例：客户和员工所在城市的统一列表

假设我们想要一份 Sakila 中客户和员工所在城市的统一列表。

SELECT
    ci.city
FROM customer AS c
JOIN address AS a ON c.address_id = a.address_id
JOIN city AS ci ON a.city_id = ci.city_id
UNION
SELECT
    ci.city
FROM staff AS s
JOIN address AS a ON s.address_id = a.address_id
JOIN city AS ci ON a.city_id = ci.city_id
ORDER BY city;

结果：得到一份不重复的城市列表，即使客户和员工住在同一个城市。

如果想保留两个来源的所有行，请使用 UNION ALL：

SELECT
    ci.city
FROM customer AS c
JOIN address AS a ON c.address_id = a.address_id
JOIN city AS ci ON a.city_id = ci.city_id
UNION ALL
SELECT
    ci.city
FROM staff AS s
JOIN address AS a ON s.address_id = a.address_id
JOIN city AS ci ON a.city_id = ci.city_id
ORDER BY city;

说明：当重复项有意义时，UNION ALL 很有用，例如你之后要统计合并列表中的行数。

什么时候选择 UNION ALL

UNION ALL 通常比 UNION 更快，因为数据库不需要花时间去除重复项。所以如果你不需要唯一性，UNION ALL 通常是更好的选择。

INTERSECT

INTERSECT 只返回在两个结果集中都出现的行。当你需要两个列表的交集时，它很有用。

示例：既有客户又有员工居住的城市

SELECT
    ci.city
FROM customer AS c
JOIN address AS a ON c.address_id = a.address_id
JOIN city AS ci ON a.city_id = ci.city_id
INTERSECT
SELECT
    ci.city
FROM staff AS s
JOIN address AS a ON s.address_id = a.address_id
JOIN city AS ci ON a.city_id = ci.city_id
ORDER BY city;

结果：只显示同时出现在客户和员工中的城市。

适用场景

INTERSECT 适合找出两类人群的共同部分、比较来自不同系统的列表，或者检查两份数据提取是否有重叠。

EXCEPT

EXCEPT 返回第一个集合中有、而第二个集合中没有的行。它是求差集的运算符。

示例：有客户居住但没有员工居住的城市

SELECT
    ci.city
FROM customer AS c
JOIN address AS a ON c.address_id = a.address_id
JOIN city AS ci ON a.city_id = ci.city_id
EXCEPT
SELECT
    ci.city
FROM staff AS s
JOIN address AS a ON s.address_id = a.address_id
JOIN city AS ci ON a.city_id = ci.city_id
ORDER BY city;

结果：得到有客户但没有员工的城市列表。

重要说明

在某些数据库中，EXCEPT 可能叫做 MINUS，或者只在特定版本中可用。如果要编写可移植的 SQL，应单独确认这一点。

实际应用

集合运算在分析和数据校验中特别有用。

UNION 帮助你从多个来源构建一份参考列表。
UNION ALL 适合在后续聚合之前合并数据流。
INTERSECT 显示重叠部分或匹配的行。
EXCEPT 帮助你找出不一致、缺失和多余的值。

有时集合运算可以用 JOIN 代替，但并不总是方便。如果你需要比较的是查询结果，而不是按键连接表，集合运算通常更易读。

什么时候最好用 `UNION` 改写多个 `OR` 条件

有时一个带有多个 OR 条件的长 WHERE 子句会变得难以阅读和维护。这时可以把逻辑拆成独立的分支，再用 UNION 合并。

这种做法在以下情况下尤其有用：

每个分支代表一条不同的业务规则；
各个条件的含义差别很大；
你希望查询更易读、更易维护。

示例： 查找分级为 R 或时长超过 180 分钟的电影。

SELECT
    title,
    rating,
    length
FROM film
WHERE rating = 'R'

UNION

SELECT
    title,
    rating,
    length
FROM film
WHERE length > 180
ORDER BY title;

结果：你得到的不是一个冗长的 WHERE ... OR ... 子句，而是两条清晰的查询，更容易阅读、修改和测试。如果某一行同时满足两个分支，UNION 会自动去除重复。如果重复不是问题，且两个分支不重叠，可以使用 UNION ALL。

说明：如果条件作用于同一列，通常用 IN (...) 就足够了。当各分支在逻辑上不同或依赖不同的列时，UNION 最有用。

常见问题

MySQL 支持 INTERSECT 和 EXCEPT 吗？

支持，从 MySQL 8.0.31 开始；MariaDB 从 10.3 版开始支持。在更早的版本中，INTERSECT 可以用 IN、EXISTS 或 INNER JOIN 实现，EXCEPT 可以用 NOT EXISTS 或 LEFT JOIN ... WHERE ... IS NULL 实现。Oracle 传统上用 MINUS 代替 EXCEPT。

UNION 结果的列名是什么？

列名来自第一个 SELECT。第二个及之后查询中的别名会被忽略，所以请在第一个查询中给列起最终的名字。语句末尾的 ORDER BY 引用的就是这些名字。

如何只对 UNION 的某一部分排序或限制行数？

把这个 SELECT 放进括号：(SELECT title FROM film ORDER BY length DESC LIMIT 5) UNION ALL (SELECT ...)。不加括号时，ORDER BY 和 LIMIT 作用于整个合并结果。某一部分中没有 LIMIT 的 ORDER BY 可能会被忽略，因为最终结果集的顺序本来就没有保证。

面试问题

`UNION` 和 `UNION ALL` 有什么区别？

UNION 合并两个查询的结果并去除重复项，而 UNION ALL 保留每一行。在实践中，UNION ALL 通常更快，因为它不需要额外查找重复项。

为什么集合运算要求 `SELECT` 查询相互兼容？

因为 SQL 按列的位置而不是列名来合并结果。如果两个查询返回的列数不同或数据类型不兼容，数据库就无法构建有效的最终集合。

什么时候用 `INTERSECT`，什么时候用 `EXCEPT`？

当你需要两个列表中都出现的行时，INTERSECT 最合适。当你想从第一个列表中减去第二个列表、只保留剩下的值时，EXCEPT 很有用。

集合运算和 `JOIN` 有什么不同？

JOIN 按键连接行，通常会加入另一张表的列。集合运算处理的是完整的查询结果，并把它们当作集合进行比较，适合用于合并列表、找出重叠和识别差异。

本课要点

UNION 合并结果并去除重复项。
UNION ALL 合并结果但不去除重复项。
INTERSECT 只保留两个集合的共同行。
EXCEPT 返回第一个集合中有而第二个集合中没有的行。
所有集合运算都要求 SELECT 查询兼容且列数相同。
最终结果的 ORDER BY 必须写在整个表达式的最后。
在实际分析中，这些运算适合用于合并列表、找出重叠以及比较不同来源的数据。