MySQL 多表無關聯查詢：UNION ALL 實戰模式

適用場景

業務表與歷史表、分庫分表、異構系統同步查詢，表結構相同或相容，無外鍵關聯，需合併結果集。

基礎語法

(SELECT name, sex, age FROM user WHERE name LIKE '王%')
UNION ALL
(SELECT name, sex, age FROM user_history WHERE name LIKE '王%');

• UNION ALL：保留重複行，效能優於 UNION（去重需排序）
• 各 SELECT 欄位數、順序、型別必須一致
• 可外層包裹 ORDER BY/LIMIT

進階用法

1. 排序分頁

SELECT * FROM (
  (SELECT name, sex, age FROM user WHERE name LIKE '王%')
  UNION ALL
  (SELECT name, sex, age FROM user_history WHERE name LIKE '王%')
) AS combined
ORDER BY age DESC
LIMIT 0, 50;

2. 來源標識

SELECT 'current' AS src, name, sex, age FROM user WHERE name LIKE '王%'
UNION ALL
SELECT 'history' AS src, name, sex, age FROM user_history WHERE name LIKE '王%';

3. 聚合統計

SELECT src, COUNT(*), AVG(age) FROM (
  SELECT 'current' AS src, age FROM user WHERE name LIKE '王%'
  UNION ALL
  SELECT 'history' AS src, age FROM user_history WHERE name LIKE '王%'
) t GROUP BY src;

效能提示

• 各子查詢獨立使用索引，確保 WHERE 條件命中
• 大表建議先 LIMIT 再 UNION ALL，減少網路傳輸
• 避免在外層對海量 UNION ALL 結果排序，考慮應用層合併或 ClickHouse/ES

對比：UNION vs UNION ALL

掌握 UNION ALL 模式，輕鬆應對「多表無關聯合併查詢」常見需求。