Oracle SQL 集合操作（UNION/INTERSECT/MINUS）

适用版本：Oracle Database 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

集合操作符组合多个查询结果[1]：

操作符	说明
UNION	并集（去重）
UNION ALL	并集（不去重）
INTERSECT	交集
MINUS	差集

---

2. UNION

2.1 语法

SELECT ... FROM table1
UNION
SELECT ... FROM table2;

2.2 示例

SELECT name FROM employees WHERE dept_id = 10
UNION
SELECT name FROM contractors;

-- 去重，默认排序

---

3. UNION ALL

3.1 语法

SELECT ... FROM table1
UNION ALL
SELECT ... FROM table2;

3.2 示例

SELECT 'EMP' AS type, name FROM employees
UNION ALL
SELECT 'CON' AS type, name FROM contractors;
-- 不去重，不排序，性能好

---

4. INTERSECT

4.1 语法

SELECT ... FROM table1
INTERSECT
SELECT ... FROM table2;

4.2 示例

SELECT product_id FROM sales_2025
INTERSECT
SELECT product_id FROM sales_2026;
-- 两年都销售的产品

---

5. MINUS

5.1 语法

SELECT ... FROM table1
MINUS
SELECT ... FROM table2;

5.2 示例

SELECT product_id FROM products
MINUS
SELECT product_id FROM sales_2026;
-- 2026 年未销售的产品

---

6. 规则

6.1 列数相同

-- 错误
SELECT id, name FROM emp
UNION
SELECT id FROM dept;

6.2 类型兼容

-- 类型需兼容
SELECT id, name FROM emp  -- id NUMBER, name VARCHAR2
UNION
SELECT code, title FROM dept;  -- code NUMBER, title VARCHAR2

6.3 ORDER BY 在最后

SELECT ... FROM emp
UNION
SELECT ... FROM dept
ORDER BY name;  -- 仅最后

6.4 列名取自第一个

SELECT id AS emp_id, name FROM emp
UNION
SELECT id, title FROM dept
ORDER BY emp_id;  -- 使用 emp_id

---

7. 性能对比

操作符	排序	去重	性能
UNION	是	是	慢
UNION ALL	否	否	快
INTERSECT	是	是	慢
MINUS	是	是	慢

---

8. 应用场景

8.1 多表汇总

-- 全公司人员
SELECT id, name, 'EMP' AS type FROM employees
UNION ALL
SELECT id, name, 'CON' AS type FROM contractors
UNION ALL
SELECT id, name, 'VEN' AS type FROM vendors;

8.2 对比数据

-- 找差异
SELECT id, name, 'OLD' AS source FROM employees_old
MINUS
SELECT id, name, 'NEW' AS source FROM employees_new;

8.3 分类汇总

-- 各类统计
SELECT 'TOTAL' AS metric, COUNT(*) AS value FROM employees
UNION ALL
SELECT 'AVG_SAL', AVG(salary) FROM employees
UNION ALL
SELECT 'MAX_SAL', MAX(salary) FROM employees;

8.4 同表对比

-- 本月 vs 上月
SELECT 'THIS_MONTH' AS period, dept_id, SUM(amount) AS total
FROM sales WHERE sale_date >= TRUNC(SYSDATE, 'MM')
GROUP BY dept_id
UNION ALL
SELECT 'LAST_MONTH', dept_id, SUM(amount)
FROM sales 
WHERE sale_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
  AND sale_date < TRUNC(SYSDATE, 'MM')
GROUP BY dept_id;

---

9. 常见坑与排错

9.1 ORA-01789: 查询块列数不同

-- 检查列数
-- 使用 NULL 填充
SELECT id, name, NULL AS dept FROM emp
UNION
SELECT id, name, dept_id FROM dept;

9.2 ORA-01790: 类型不匹配

-- 类型转换
SELECT TO_CHAR(id), name FROM emp
UNION
SELECT code, title FROM dept;

9.3 ORDER BY 错误

-- ORDER BY 只能最后
-- 列名来自第一个查询

9.4 性能慢

-- 优先 UNION ALL
-- 加索引
-- 限制数据量

---

10. 最佳实践

1. UNION ALL 优先：不去重性能好
2. 明确列名：第一个 SELECT 命名
3. 类型转换：兼容
4. NULL 填充：对齐
5. ORDER BY 最后：全局排序
6. 类型标识：区分来源
7. 限制数据量：性能
8. 加索引：提升
9. 测试结果：验证
10. 使用 CTE 替代：复杂场景

---

11. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Set Operators” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html