SQL UNION 與 UNION ALL 合併查詢結果完整指南

核心差異

基本語法

-- 去重合併
SELECT * FROM A
UNION
SELECT * FROM B;

-- 保留重複
SELECT * FROM A
UNION ALL
SELECT * FROM B;

排序合併結果

需包裝子查詢，並使用第一個查詢的欄位名/別名排序：

SELECT *
FROM (
  SELECT * FROM A
  UNION ALL
  SELECT * FROM B
) AS T
ORDER BY column_name;

實戰案例：分類取最新 N 筆

SQL Server (TOP)

SELECT * FROM (
  SELECT TOP 2 id, adddate, title, url FROM bArticle WHERE ClassId='1' ORDER BY adddate DESC
) A
UNION ALL
SELECT * FROM (
  SELECT TOP 2 id, adddate, title, url FROM bArticle WHERE ClassId='2' ORDER BY adddate DESC
) B
UNION ALL
SELECT * FROM (
  SELECT TOP 2 id, adddate, title, url FROM bArticle WHERE ClassId='3' ORDER BY adddate DESC
) C
UNION ALL
SELECT * FROM (
  SELECT TOP 2 id, adddate, title, url FROM bArticle WHERE ClassId='4' ORDER BY adddate DESC
) D;

MySQL (LIMIT)

SELECT * FROM (
  SELECT id, adddate, title, url FROM bArticle WHERE ClassId='1' ORDER BY adddate DESC LIMIT 0,2
) A
UNION ALL
SELECT * FROM (
  SELECT id, adddate, title, url FROM bArticle WHERE ClassId='2' ORDER BY adddate DESC LIMIT 0,2
) B
UNION ALL
SELECT * FROM (
  SELECT id, adddate, title, url FROM bArticle WHERE ClassId='3' ORDER BY adddate DESC LIMIT 0,2
) C
UNION ALL
SELECT * FROM (
  SELECT id, adddate, title, url FROM bArticle WHERE ClassId='4' ORDER BY adddate DESC LIMIT 0,2
) D;

使用限制與注意事項

• 所有參與查詢的欄位數、順序、資料型別必須相容。
• ORDER BY、COMPUTE 僅能放在最後；GROUP BY/HAVING 僅能用於單獨查詢內部。
• INTO 子句只能出現在最終結果集。
• 某些 DBMS 不支援 UNION CORRESPONDING（依欄位名自動對齊）。

最佳實務

1. 確定不需去重時，永遠優先使用 UNION ALL。
2. 子查詢中明確列出欄位名，避免 SELECT * 導致結構不一致。
3. 大量資料合併時，考慮使用暫存表或 INSERT INTO ... SELECT 分批處理。