【Oracle11g SQL详解】UNION、UNION ALL、INTERSECT 和 MINUS 的应用场景

UNION、UNION ALL、INTERSECT 和 MINUS 的应用场景

SQL 中的 集合操作符（UNION、UNION ALL、INTERSECT 和 MINUS）可以将多个查询的结果集按照集合的数学运算规则进行处理。这些操作符通常用于复杂查询，通过合并、比较、过滤数据集，满足多样化的业务需求。

---

一、操作符简介与基本规则

操作符	功能	去重	常见用途
UNION	合并两个查询结果	是	去重后合并两个数据集
UNION ALL	合并两个查询结果（保留重复值）	否	显示所有结果，包括重复数据
INTERSECT	返回两个查询的交集（公共部分）	是	找出多个表或结果集的公共数据
MINUS	返回第一个查询与第二个查询的差集	是	获取存在于一个表而不在另一个表中的数据

基本规则：

1. 列数与数据类型需一致：
   所有查询结果必须有相同的列数，且每列数据类型需要匹配。
2. 结果排序：
   如果需要对最终结果排序，只能在最后一个结果集之后使用 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	查询第一个数据集的差集	是	较低	排查特定表的独有记录

---

六、性能优化建议

1. 优先使用 UNION ALL：
   如果数据去重不是必须的，选择 UNION ALL，其性能优于 UNION，因为去重需要额外的排序或哈希计算。

2. 减少数据量：
   在集合操作前对数据进行筛选，尽量减少传递到操作符的记录数。例如：

   SELECT country 
   FROM customers 
   WHERE region = 'North'
   UNION ALL
   SELECT country 
   FROM suppliers
   WHERE region = 'North';
   

3. 索引优化：
   为涉及的表创建合适的索引，提高集合操作的执行速度。

---

七、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 查询中灵活处理多表数据，提高业务逻辑的实现效率。