SQL 集合运算符全指南

什么是 SQL 中的集合运算？

SQL 中的集合运算是用于合并或比较两个或多个 SELECT 语句结果的技术。它们的行为类似数学上的集合运算，使我们能够找出查询返回行的并集、交集或差集。这让它们在从多个来源或视角分析数据时不可或缺。

以下是核心集合运算的快速概览：

• UNION：合并两个或多个 SELECT 语句中的所有唯一行，去除重复。

• UNION ALL：合并两个或多个 SELECT 语句中的所有行，保留重复。

• INTERSECT：仅返回同时出现在两个 SELECT 结果中的行。

• EXCEPT：返回第一个 SELECT 结果中未出现在第二个结果中的行。

与关系代数运算的对比

关系代数是理解数据库查询的理论框架。它提供了投影、选择和连接等抽象运算，根植于数学原理且独立于具体的数据库系统。可以将其视为支撑我们数据库交互的“幕后逻辑”。

SQL 中的集合运算符是在数据库环境中对这些概念的实践实现。它们使我们能够直接对 SQL 查询的结果集执行并、交、差等集合运算。

关系代数为数据库操作提供了形式化基础，而 SQL 中的集合运算符则为数据处理任务提供了标准化、用户友好的接口。

理解集合运算符与关系代数运算之间的关系，有助于洞悉 SQL 的理论根基。了解这种联系，能帮助您在编写查询时更清晰地把握底层发生了什么。

SQL 中的集合运算符类型

SQL 中主要有三种集合运算符：

• UNION

• INTERSECT

• EXCEPT（在某些方言中为 MINUS）

这些运算分别对应数学中的并集、交集与差集概念。

使用集合运算符的规则

在使用集合运算符合并查询之前，必须满足四个要求。任一出错，查询就会报错。

• 列数相同：每个 SELECT 必须返回相同数量的列。如果一个查询返回三列而另一个返回两列，数据库会报错。

• 数据类型兼容：对应列必须类型匹配。您不能在同一位置将 VARCHAR 的姓名列与 INTEGER 的 ID 列合并。

• 列顺序相同：SQL 按位置而非名称映射列。无论列名为何，查询一的第一列会映射到查询二的第一列。

• 仅在末尾使用 ORDER BY：ORDER BY 只能在合并后的完整查询末尾出现一次。不能在各个 SELECT 语句内部使用。

还有一个细节：输出中的列名来自第一个 SELECT 语句。后续查询中的别名会被忽略，因此如需自定义输出列名，只给第一个查询添加别名。

如何在 SQL 中使用 UNION 运算符

UNION 运算符将两个或多个 SELECT 查询的结果合并为一个结果集，默认会去除重复行。

例如，假设我们有两张表 employees 和 contractors，它们都有类似的列，如 contractors、department 和 salary。为便于学习，先看这两张示例表：

employees：

contractors：

我们可以使用以下命令合并两张表的结果：

-- Using UNION to combine all employees and contractors
SELECT name, department, salary FROM employees
UNION
SELECT name, department, salary FROM contractors;

该查询从 employees 和 contractors 两张表中选择 name、department 和 salary 列，并将它们合并为一个结果集。UNION 运算符会自动从最终结果集中移除重复行。

请注意，Carol 同时出现在两张表中，但在结果中只出现一次。如果我们希望保留 Carol 的两条记录（具有不同的薪资），应使用 UNION ALL。

UNION 与 UNION ALL

UNION 运算符不会移除 NULL 值。如果某一结果集的某列包含 NULL 值，而另一结果集中对应列包含非 NULL 值，那么在 UNION 产生的最终结果集中，这些 NULL 值会被保留。

如果我们希望在结果集中包含 NULL 值，并且不让 UNION 去除它们，可以改用 UNION ALL 运算符。该运算符会合并多个 SELECT 查询的结果，包含每个结果集的所有行，无论是否重复或是否包含 NULL 值。

如何在 SQL 中使用 INTERSECT 运算符

INTERSECT 运算符仅返回同时出现在两个结果集中的行。可以将其理解为找出同时属于两个群体的人。

让我们对上述表使用 INTERSECT。为便于示例，仅查询 name 和 department 列：

-- Using INTERSECT to find common employees
SELECT name, department FROM employees
INTERSECT
SELECT name, department FROM contractors;

该查询从 employees 与 contractors 表中选择 name 和 department 列，并仅基于所有被选列返回同时存在于两表中的行。

INTERSECT 运算符根据标准比较规则处理 NULL 值，在比较对应列时将 NULL 视为相等。当处理空结果集时，其结果也为空集。

换言之，如果一个结果集的某列为 NULL，而另一个结果集中对应列为非 NULL，这些行不被视为相等——不会包含在交集结果中。

此外，如果提供给 INTERSECT 的结果集之一为空，那么总体结果也为空。空集与任何集合都没有公共行。

如何在 SQL 中使用 UNION 运算符

EXCEPT 运算符返回第一个结果集中不在第二个结果集中的行。

在 Oracle Database 中，EXCEPT 写作 MINUS；其他主流方言（PostgreSQL、SQL Server、MySQL 8.0.31+、SQLite）使用 EXCEPT。

例如，假设我们执行以下查询：

-- Using EXCEPT to find employees who are not contractors
SELECT name, department, salary FROM employees
EXCEPT
SELECT name, department, salary FROM contractors;

该查询从 employees 表选择 name、department 和 salary 列，并仅返回在 contractors 表中不存在的行。

EXCEPT 运算符在处理 NULL 值时也遵循标准比较规则。对于空结果集，如果第一个结果集为空，则结果为空；如果第二个结果集为空，则结果包含第一个结果集的所有行。

SQL 集合运算符：性能与优化

集合运算对 SQL 查询性能的影响，取决于所涉数据集的规模、查询的复杂度以及所使用的数据库管理系统（DBMS）等因素。

下面分解关键因素与优化策略。

数据量与查询复杂度

在处理大量数据时，集合运算会显著影响查询性能，因为需要合并、求交或比较的结果集越大，执行这些操作所需的处理时间就越长。

包含多个子查询、连接或集合运算的复杂查询会带来额外开销并影响性能。串联或嵌套的集合运算可能进一步加剧性能问题。

索引与优化技术

对参与集合运算的列进行恰当的索引，可显著提升查询性能。索引有助于数据库引擎快速定位并检索相关行，减少全表扫描并缩短执行时间。

为提升涉及集合运算的查询性能，数据库管理员和开发者可以采用查询改写、执行计划分析与模式优化等技术。还可使用查询缓存与物化视图，预计算并存储复杂查询结果，从而降低集合运算的计算开销。

数据库引擎与硬件资源

集合运算的性能会因底层数据库引擎及其优化能力而异。不同 DBMS 在处理集合运算时可能采用不同的优化策略与算法，导致性能差异。

CPU、内存与磁盘 I/O 等硬件资源的可用性同样会影响涉及集合运算的查询性能。充足的硬件资源有助于缓解瓶颈，确保高效执行。

SQL 集合运算符的实践

集合运算不仅是理论工具，它们在真实业务中也有重要应用，能够显著影响决策。下面通过一个简化示例，看看公司如何使用集合运算对客户群进行细分，以开展目标营销活动。

场景

设想一家同时经营线上与线下门店的公司。他们有两份独立的数据集：

1. 在线购买：线上消费者的客户 ID、购买历史、人口统计信息和所在地。
2. 门店交易：到店消费者的类似信息。

使用集合运算

为获得所有客户的完整画像，公司首先可以使用 UNION 将两份数据集合并为一张表，并去除重复。这样就得到统一视图。

接着，可用 INTERSECT 识别既在线上又在线下购物的客户。该细分群体尤为重要，因为他们在多个渠道与品牌高度互动。

为寻找跨渠道推广机会，公司可以使用 EXCEPT。例如，SELECT * FROM online_purchases EXCEPT SELECT * FROM in_store_transactions 可以找出只在网上购物、未到店的客户。随后公司可针对这部分客户投放引导到店的促销活动。

超越细分

在识别出这些人群后，公司还能进一步结合人口统计或购买历史进行细化。对客户的这种精细化理解，使其能更精准地定制营销活动。

集合运算符 vs. JOIN

集合运算符与 SQL JOIN 都能合并来自多个查询的数据，但方式不同。核心问题在于您是要合并行，还是合并列。

集合运算符的局限与注意事项

在使用 SQL 集合运算符时，需要考虑多种限制与因素，这些会影响查询性能、结果准确性与整体可用性。

数据类型兼容性与 NULL 值

结果集中对应列必须具有兼容的数据类型。在合并查询前，请检查对应列的数据类型是否兼容。类型不匹配会导致难以察觉的错误。

集合运算符对 NULL 值的处理可能因 DBMS 和具体运算符而异。为避免错误，开发者需要了解 NULL 值的处理方式。

性能影响与重复行

集合运算会对查询性能产生显著影响，特别是在处理大型或复杂数据集时。索引、查询优化和硬件资源等因素会影响性能。采用优化技术与性能调优策略对缓解瓶颈至关重要。

默认情况下，集合运算符会从结果集中移除重复行。但在某些场景下，保留重复行是必要的。理解集合运算符对重复行的处理，并在需要时采用合适的方法处理重复，十分重要。

结果排序与内存约束

集合运算不保证最终输出的顺序。要对合并结果排序，请在整个查询的最末尾、最后一个 SELECT 语句之后添加一个 ORDER BY 子句。

在处理大型数据集时，集合运算可能消耗大量内存和资源。必须考虑内存与资源限制，以避免性能下降或系统不稳定。

复杂性、可维护性与跨 DBMS 兼容性

涉及多个集合运算、子查询与连接的复杂查询，往往难以理解、维护和调试。为提升可读性与可维护性，应保持查询简洁、文档充分并尽量模块化。

不同数据库管理系统（DBMS）在语法与行为上可能存在差异。编写可跨平台兼容的 SQL 查询时，了解这些差异至关重要。

结语

集合运算解决的是一个特定问题：在事先并不清楚哪些行重叠的情况下，合并或比较结果集。

它们涵盖 UNION、INTERSECT 和 EXCEPT：这三种用于合并、比较和相减结果集的运算符。

如果您想进一步学习，请查看这门课程：Joining Data in SQL。