Oracle UNION 與 UNION ALL 差異深度解析與效能實測

UNION 與 UNION ALL 皆用於合併多個 SELECT 結果集，但去重與排序行為差異巨大，直接影響查詢效能與結果正確性。

核心差異對照表

範例資料與結果

-- TAB1
XiaoWang
XiaoZhang

-- TAB2
XiaoWang
XiaoLi

-- UNION 結果（3 列，已去重、排序）
XiaoLi
XiaoWang
XiaoZhang

-- UNION ALL 結果（4 列，保留重複、不排序）
XiaoWang
XiaoZhang
XiaoWang
XiaoLi

擴展集合運算子

• INTERSECT：交集，去重且排序
• MINUS（Oracle）/ EXCEPT（ANSI/SQL Server）：差集，去重且排序

使用限制與注意事項

1. 欄位數、型別、順序必須一致： 第一個 SELECT 的欄位名稱將成為結果集欄位名
2. 可串接多個集合： SELECT ... UNION SELECT ... UNION SELECT ...
3. 排序只能在最後： ORDER BY 僅允許出現在最終結果集，且須使用第一個 SELECT 的欄位別名或位置編號
4. CLOB/BLOB 不支援： 含大物件欄位會報 ORA-00932，需轉 TO_CHAR 或改用 UNION ALL + 應用層去重

實戰效能建議

• 明確業務不需要去重時，**永遠用 UNION ALL**
• 需去重且資料量大時，考慮先 UNION ALL 進暫存表，再 CREATE TABLE AS SELECT DISTINCT ... 並建立索引
• 避免在視圖定義中使用 UNION，導致每次查詢視圖都觸發昂貴排序

完整範例

-- 部門 20 以上 + 部門 30 以下（重複部門 20~30）
SELECT * FROM emp WHERE deptno >= 20
UNION ALL
SELECT * FROM emp WHERE deptno <= 30;

-- 跨表合併，欄位名不同但型別相容
SELECT empno, ename FROM emp
UNION
SELECT deptno, dname FROM dept
ORDER BY 2;  -- 依第二欄（ename/dname）排序

掌握集合運算子語義與效能特徵，是撰寫高效 SQL 的基本功。