MySQL 聯合查詢與連接查詢深度解析：UNION、JOIN 實戰指南

MySQL 聯合查詢與連接查詢深度解析：UNION、JOIN 實戰指南

多表查詢是 SQL 核心技能。本文系統講解 UNION/INTERSECT/EXCEPT 聯合查詢 以及 CROSS/INNER/OUTER/NATURAL JOIN 連接查詢 的語法、差異、適用場景與效能考量。

一、聯合查詢（UNION 家族）

將多個 SELECT 結果集垂直合併（行數增加，欄位數不變）。

基本語法

SELECT 列表 FROM 表1
UNION [ALL]
SELECT 列表 FROM 表2
[UNION [ALL] SELECT 列表 FROM 表3 ...]

核心規則

• 每個 SELECT 欄位數量、順序、資料型別必須相容（可隱式轉換）
• 結果集欄位名以第一個 SELECT 為準
• ORDER BY、LIMIT 僅能套用於最終結果，需用第一個 SELECT 的別名/位置
• 預設 UNION 去重（相當 DISTINCT），UNION ALL 保留所有行，效能較佳
• 執行順序自左至右，可用括號改變優先級

INTERSECT / EXCEPT（MySQL 8.0+ 支援）

• INTERSECT：交集（兩邊都有）
• EXCEPT：差集（左有右無）

-- 有玩具的孩子
SELECT user_id FROM user INTERSECT SELECT user_id FROM toy;

-- 沒玩具的孩子
SELECT user_id FROM user EXCEPT SELECT user_id FROM toy;

二、連接查詢（JOIN 家族）

橫向合併多表（欄位數增加），依據關聯條件匹配行。

1. CROSS JOIN（交叉連接/笛卡爾積）

無 ON 條件，表 A 每行與表 B 每行組合。A 有 m 行，B 有 n 行 → 結果 m×n 行。

SELECT u.username, t.toyname FROM user u CROSS JOIN toy t;

慎用：資料量大時極易撐爆記憶體。

2. INNER JOIN（內連接/等值/非等值連接）

僅保留雙方都滿足條件的行。

-- 等值連接（最常用）
SELECT u.username, t.toyname 
FROM user u INNER JOIN toy t ON u.user_id = t.user_id;
-- 或 WHERE u.user_id = t.user_id

-- 非等值連接
SELECT u.username, t.toyname 
FROM user u INNER JOIN toy t ON u.user_id <> t.user_id;

3. NATURAL JOIN（自然連接）

自動以同名欄位為連接條件。需確保同名欄位語義一致，否則易產生錯誤結果。

SELECT u.username, t.toyname FROM user u NATURAL JOIN toy t;

4. OUTER JOIN（外連接）

保留單側所有行，另一側無匹配則填 NULL。

• LEFT [OUTER] JOIN：以左表為基準
• RIGHT [OUTER] JOIN：以右表為基準

-- 所有用戶及其玩具（無玩具顯示 NULL）
SELECT u.username, t.toyname 
FROM user u LEFT JOIN toy t ON u.user_id = t.user_id;

-- 所有玩具及擁有者（無主玩具顯示 NULL）
SELECT u.username, t.toyname 
FROM user u RIGHT JOIN toy t ON u.user_id = t.user_id;

注意：外連接的條件必須寫在 ON 子句，不能移到 WHERE，否則退化為內連接。

三、效能與最佳實踐

1. 優先使用 INNER JOIN：結果集較小，優化器選擇空間大。
2. 小表驅動大表：用小表結果集去大表匹配（或用 STRAIGHT_JOIN 強制順序）。
3. 連接欄位建立索引：且型別、字元集、長度完全一致。
4. 避免 SELECT *：只取需要欄位，減少網路與記憶體開銷。
5. UNION ALL 替代 UNION：業務允許重複時，省去去重排序成本。
6. 大資料量分頁：配合子查詢或延遲關聯優化（見文章 172796）。

四、常見陷阱

• 外連接條件誤放 WHERE 導致結果錯誤
• NATURAL JOIN 隱含同名欄位變更時未同步修改
• UNION 欄位型別不一致導致隱式轉換錯誤或索引失效
• CROSS JOIN 無限制產生海量中間結果