课程 5.10 · 阅读时间：~8分钟

本课程介绍SQL数据集操作。您将学习如何组合来自多个查询的结果，查找公共行，并排除您不需要的值。我们将通过Sakila示例来查看UNION、UNION ALL、INTERSECT和EXCEPT。到课程结束时，您将能够为不同的分析场景选择正确的操作符。

数据集操作

在之前的课程中，您学习了如何使用JOIN连接表以及引擎如何执行这些连接。现在我们转向一个不同的概念：有时您不想通过键连接行，而是想组合和比较整个结果集。

数据集操作在您想要合并来自多个查询的数据、查找受众之间的重叠或删除已经出现在另一个列表中的行时非常有用。在实践中，这种情况经常出现在报告、数据质量检查和最终列表准备中。

什么是数据集操作

数据集操作不是处理来自一个表的行，而是处理两个或多个查询的结果。在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在分支在逻辑上不同或依赖于不同列时最有用。

面试问题

UNION和UNION ALL之间有什么区别？

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

为什么集合操作需要兼容的SELECT查询？

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

何时使用INTERSECT，何时使用EXCEPT？

当您需要出现在两个列表中的行时，INTERSECT是最佳选择。当您想从第一个列表中减去第二个列表并仅保留剩余值时，EXCEPT非常有用。

集合操作与JOIN有什么不同？

JOIN通过键连接行，通常从另一个表中添加列。集合操作处理整个查询结果并将其作为集合进行比较，这对于合并列表、查找重叠和识别差异非常有用。

本课要点

• UNION组合结果并删除重复项。
• UNION ALL组合结果而不删除重复项。
• INTERSECT仅保留两个集合的公共行。
• EXCEPT返回存在于第一个集合但不在第二个集合中的行。
• 所有集合操作都需要兼容的SELECT查询，列数相同。
• 最终结果的ORDER BY必须放在整个表达式的最后。
• 在实际分析中，这些操作对于合并列表、查找重叠和比较数据源之间的数据非常有用。

在下一课中，我们将转向子查询，看看如何使用嵌套的SELECT语句进行更灵活的条件和计算。