核心語法回顧

SELECT col1, col2 FROM table1 WHERE condition1
UNION ALL
SELECT col1, col2 FROM table2 WHERE condition2
ORDER BY col1;  -- 排序只能在最外層

場景一：同表不同條件分組合併 (原文案例)

-- 表: table (id, username, ytime)
-- 需求: id>20 取前 5 筆升序 + id<20 取前 5 筆降序，再整體按 id 降序

-- ❌ 錯誤：子查詢排序被外層 UNION ALL 破壞
SELECT * FROM (SELECT TOP 5 * FROM table WHERE id > 20 ORDER BY id ASC) 
UNION ALL
SELECT TOP 5 * FROM table WHERE id < 20 ORDER BY id DESC;

-- ✅ 正確：外層包裹統一排序
SELECT * FROM (
    SELECT TOP 5 * FROM table WHERE id > 20 ORDER BY id ASC
    UNION ALL
    SELECT TOP 5 * FROM table WHERE id < 20 ORDER BY id DESC
) t ORDER BY id DESC;

關鍵：ORDER BY 必須在**最外層**，子查詢內部排序僅為取 Top-N 服務，最終順序以外層為準。

場景二：分表/分庫歸檔查詢

-- 當前表 + 歷史歸檔表 統一查詢
SELECT id, user_id, amount, created_at FROM orders_2024 WHERE user_id = 100
UNION ALL
SELECT id, user_id, amount, created_at FROM orders_2023 WHERE user_id = 100
UNION ALL
SELECT id, user_id, amount, created_at FROM orders_history WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 20;

場景三：多來源異構資料整合 (加來源標識)

SELECT 'mysql' AS source, id, name, email, created_at FROM mysql_users WHERE active = 1
UNION ALL
SELECT 'ldap' AS source, uid AS id, cn AS name, mail AS email, createTimestamp AS created_at 
FROM ldap_users WHERE accountStatus = 'active'
UNION ALL
SELECT 'api' AS source, user_id AS id, username AS name, email, registered_at AS created_at
FROM api_users WHERE status = 1
ORDER BY created_at DESC;

場景四：UNION ALL 分頁 (大資料集)

-- 方案 1：應用層分頁 (推薦)
-- 第一頁
(SELECT ... FROM table1 LIMIT 20)
UNION ALL
(SELECT ... FROM table2 LIMIT 20)
ORDER BY created_at DESC LIMIT 20;

-- 方案 2：包裹子查詢 + LIMIT
SELECT * FROM (
    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table2
) combined
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

場景五：巢狀 UNION ALL (分組再合併)

-- 先按類別合併，再全類別合併
SELECT * FROM (
    SELECT * FROM sales_east_2024
    UNION ALL
    SELECT * FROM sales_west_2024
) regional_2024
UNION ALL
SELECT * FROM (
    SELECT * FROM sales_east_2023
    UNION ALL
    SELECT * FROM sales_west_2023
) regional_2023
ORDER BY sale_date DESC;

UNION vs UNION ALL 效能實測 (100 萬行測試)

MySQL 8.0+ 窗口函數替代方案

-- 使用 ROW_NUMBER() 替代 UNION ALL 分組取 Top-N
WITH combined AS (
    SELECT *, 'table1' AS src FROM table1
    UNION ALL
    SELECT *, 'table2' AS src FROM table2
),
ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY src ORDER BY created_at DESC) AS rn
    FROM combined
)
SELECT * FROM ranked WHERE rn <= 10 ORDER BY src, created_at DESC;

避坑指南

• 欄位順序必須一致：按位置對應，非名稱。建議顯式列出欄位
• 型別隱式轉換：INT UNION ALL VARCHAR 可能導致精度損失或報錯，用 CAST 統一
• NULL 語義：NULL = NULL 為 UNKNOWN，去重時視為不相等
• LIMIT 不能直接用在 UNION 子句中：需包裹子查詢
• 字元集校對衝突：不同表欄位校對規則不同會報錯，用 COLLATE 統一

最佳實踐清單

• [ ] 預設使用 UNION ALL，除非業務明確要求去重
• [ ] 顯式列出欄位，避免 SELECT * 導致結構變更報錯
• [ ] 加上來源標識欄位便於追蹤
• [ ] 外層統一 ORDER BY、LIMIT
• [ ] 大資料量考慮分批/游標/窗口函數替代
• [ ] 監控 Created_tmp_tables、Created_tmp_disk_tables 指標