UNION、UNION ALL、INTERSECT 和 MINUS 的应用场景
SQL 中的 集合操作符（UNION、UNION ALL、INTERSECT 和 MINUS）可以将多个查询的结果集按照集合的数学运算规则进行处理。这些操作符通常用于复杂查询，通过合并、比较、过滤数据集，满足多样化的业务需求。
一、操作符简介与基本规则
| 操作符 | 功能 | 去重 | 常见用途 |
|---|---|---|---|
| UNION | 合并两个查询结果 | 是 | 去重后合并两个数据集 |
| UNION ALL | 合并两个查询结果（保留重复值） | 否 | 显示所有结果，包括重复数据 |
| INTERSECT | 返回两个查询的交集（公共部分） | 是 | 找出多个表或结果集的公共数据 |
| MINUS | 返回第一个查询与第二个查询的差集 | 是 | 获取存在于一个表而不在另一个表中的数据 |
基本规则：
- 列数与数据类型需一致：
所有查询结果必须有相同的列数，且每列数据类型需要匹配。 - 结果排序：
如果需要对最终结果排序，只能在最后一个结果集之后使用ORDER BY。
二、UNION 和 UNION ALL
1. UNION
定义：
UNION 用于合并两个查询的结果集，并自动去除重复值。
适用场景：
- 合并来自不同数据源的数据，确保结果集中不包含重复记录。
- 查询唯一值，例如统计用户活动或分析唯一数据集。
示例：
查询所有客户和供应商的国家名称（去重）：
SELECT country FROM customers
UNION
SELECT country FROM suppliers;
结果：
返回 customers 和 suppliers 中的国家列表，不包含重复值。
2. UNION ALL
定义：
UNION ALL 用于合并两个查询的结果集，但保留所有重复值。
适用场景：
- 数据分析场景下，需要完整展示所有数据记录，包括重复数据。
- 不关心去重，或性能优先时使用（
UNION ALL无需去重，性能更高）。
示例：
查询所有客户和供应商的国家名称（包括重复）：
SELECT country FROM customers
UNION ALL
SELECT country FROM suppliers;
结果：
返回所有国家名称，包括重复的记录。
三、INTERSECT
1. 定义
INTERSECT 返回两个查询结果的交集，仅包含两者共有的数据。
2. 适用场景
- 用于查找两个表中同时存在的数据。
- 比如用户管理中，查询同时属于两个权限组的用户。
3. 示例
查询同时是客户和供应商的国家名称：
SELECT country FROM customers
INTERSECT
SELECT country FROM suppliers;
结果：
只返回既是客户又是供应商的国家。
四、MINUS
1. 定义
MINUS 返回第一个查询中有但第二个查询中没有的记录。
2. 适用场景
- 用于查找差异，例如找出某表特有的数据。
- 常用于异常检测或数据排查。
3. 示例
查询是客户但不是供应商的国家名称：
SELECT country FROM customers
MINUS
SELECT country FROM suppliers;
结果：
返回只出现在 customers 表而未出现在 suppliers 表中的国家。
五、操作符之间的区别与比较
| 操作符 | 主要功能 | 是否去重 | 性能 | 适用场景 |
|---|---|---|---|---|
| UNION | 合并去重 | 是 | 较低 | 查询唯一记录 |
| UNION ALL | 合并不去重 | 否 | 较高 | 查询所有记录 |
| INTERSECT | 查询两个数据集的交集 | 是 | 较低 | 查找公共记录 |
| MINUS | 查询第一个数据集的差集 | 是 | 较低 | 排查特定表的独有记录 |
六、性能优化建议
-
优先使用 UNION ALL：
如果数据去重不是必须的，选择UNION ALL，其性能优于UNION，因为去重需要额外的排序或哈希计算。 -
减少数据量：
在集合操作前对数据进行筛选，尽量减少传递到操作符的记录数。例如：SELECT country FROM customers WHERE region = 'North' UNION ALL SELECT country FROM suppliers WHERE region = 'North'; -
索引优化：
为涉及的表创建合适的索引，提高集合操作的执行速度。
七、ORDER BY 与集合操作符
ORDER BY 必须位于所有集合操作之后，用于对最终结果排序。例如：
SELECT country FROM customers
UNION
SELECT country FROM suppliers
ORDER BY country ASC;
八、小结
UNION和UNION ALL用于合并数据集，UNION去重，而UNION ALL保留重复。INTERSECT用于查找数据集的交集（共有部分）。MINUS用于计算差集，找出特定表中独有的数据。
通过合理运用集合操作符，可以在 SQL 查询中灵活处理多表数据，提高业务逻辑的实现效率。
