Oracle SQL 集合操作详解

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

---

1. 概述

SQL 集合操作合并结果集[1]：

详细见：Oracle SQL 集合操作。

---

2. UNION

2.1 UNION

-- 去重
SELECT id, name FROM employees WHERE dept_id = 10
UNION
SELECT id, name FROM employees WHERE dept_id = 20;

2.2 UNION ALL

-- 不去重（推荐）
SELECT id, name FROM employees WHERE dept_id = 10
UNION ALL
SELECT id, name FROM employees WHERE dept_id = 20;

2.3 性能

- UNION ALL：无排序，快
- UNION：排序去重，慢
- 优先 UNION ALL

---

3. INTERSECT

-- 交集
SELECT id FROM employees WHERE dept_id = 10
INTERSECT
SELECT id FROM employees WHERE salary > 5000;

-- 即 dept_id = 10 AND salary > 5000

---

4. MINUS

-- 差集
SELECT id FROM employees
MINUS
SELECT id FROM employees WHERE status = 'INACTIVE';

-- 即 status != 'INACTIVE'（含 NULL）

---

5. 规则

5.1 列数相同

-- 必须列数相同
SELECT id, name FROM t1
UNION
SELECT id, name FROM t2;

5.2 类型兼容

-- 类型兼容
SELECT id, name FROM employees  -- NUMBER, VARCHAR2
UNION
SELECT emp_id, emp_name FROM contractors;  -- NUMBER, VARCHAR2

5.3 顺序

-- ORDER BY 在最后
SELECT id, name FROM t1
UNION
SELECT id, name FROM t2
ORDER BY id;

5.4 列名

-- 第一个 SELECT 决定列名
SELECT id AS employee_id, name FROM employees
UNION
SELECT emp_id, emp_name FROM contractors
ORDER BY employee_id;

---

6. 应用场景

6.1 合并

-- 多表合并
SELECT 'EMP' AS type, id, name FROM employees
UNION ALL
SELECT 'CON' AS type, id, name FROM contractors
UNION ALL
SELECT 'VENDOR' AS type, id, name FROM vendors;

6.2 比较

-- 找出在不同表的数据
SELECT id, name FROM employees
MINUS
SELECT id, name FROM employees_backup;

6.3 报表

-- 汇总
SELECT 'IT' AS dept, COUNT(*) AS cnt FROM employees WHERE dept_id = 10
UNION ALL
SELECT 'Sales', COUNT(*) FROM employees WHERE dept_id = 20
UNION ALL
SELECT 'Other', COUNT(*) FROM employees WHERE dept_id NOT IN (10, 20);

6.4 分页

-- 多源分页
SELECT * FROM (
  SELECT id, name FROM employees
  UNION ALL
  SELECT id, name FROM contractors
)
ORDER BY id
OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

---

7. 替代

7.1 OR

-- UNION ALL 替代 OR
SELECT * FROM employees WHERE id = 1
UNION ALL
SELECT * FROM employees WHERE salary > 10000 AND id != 1;

-- 等效
SELECT * FROM employees WHERE id = 1 OR salary > 10000;

7.2 IN

-- INTERSECT
SELECT id FROM t1
INTERSECT
SELECT id FROM t2;

-- 等效
SELECT id FROM t1 WHERE id IN (SELECT id FROM t2);

7.3 NOT EXISTS

-- MINUS
SELECT id FROM t1
MINUS
SELECT id FROM t2;

-- 等效
SELECT id FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);

详细见：Oracle 子查询与 EXISTS。

---

8. 性能

8.1 UNION ALL

- 无排序
- 无去重
- 快

8.2 UNION / INTERSECT / MINUS

- 排序去重
- 内存
- 慢

8.3 优化

- 索引
- 减少列
- 限制行
- 替代

---

9. 复杂示例

9.1 多表

-- 三个表合并
SELECT id, name, 'EMP' AS type FROM employees
UNION ALL
SELECT id, name, 'CON' FROM contractors
UNION ALL
SELECT id, name, 'VENDOR' FROM vendors
ORDER BY type, name;

9.2 聚合

-- 各表统计
SELECT 'EMP' AS type, COUNT(*) AS cnt, SUM(salary) AS total FROM employees
UNION ALL
SELECT 'CON', COUNT(*), SUM(rate) FROM contractors;

9.3 对比

-- 同期对比
SELECT '2024' AS year, dept_id, SUM(amount) AS total FROM sales_2024 GROUP BY dept_id
UNION ALL
SELECT '2025', dept_id, SUM(amount) FROM sales_2025 GROUP BY dept_id
ORDER BY dept_id, year;

---

10. 12c+ 增强

10.1 MATCH_RECOGNIZE

-- 模式匹配
SELECT *
FROM stock_prices
MATCH_RECOGNIZE (...);

详细见：Oracle SQL 模式匹配。

10.2 FETCH

-- 集合后分页
SELECT ... UNION ... 
ORDER BY ...
OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

详细见：Oracle 12c 新 SQL 特性。

---

11. 常见坑与排错

11.1 列数不匹配

- ORA-01789
- 检查列

11.2 类型不兼容

- ORA-01790
- 转换

11.3 ORDER BY

- 仅最后
- 列名第一个

11.4 NULL

- UNION 视 NULL 相同
- UNION ALL 保留

---

12. 最佳实践

1. UNION ALL 优先：性能
2. 列数相同：规则
3. 类型兼容：转换
4. ORDER BY 最后：语法
5. 限制行：性能
6. 替代 OR / IN：性能
7. 索引：优化
8. 测试：验证
9. 执行计划：检查
10. 文档化：说明

---

13. 参考资料

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