MySQL 多表關聯查詢（JOIN）完整教學

JOIN 類型總覽

實戰範例：用戶與動作表

資料準備

-- user 表
id | name
---|-----
1  | libk
2  | zyfon
3  | daodao

-- user_action 表
user_id | action
--------|-------
1       | jump
1       | kick
1       | jump
2       | run
4       | swim

LEFT JOIN 查詢

SELECT id, name, action
FROM user AS u
LEFT JOIN user_action AS a ON u.id = a.user_id;

結果分析

id | name  | action
---|-------|-------
1  | libk  | jump      ← 匹配
1  | libk  | kick      ← 匹配
1  | libk  | jump      ← 匹配（重複行）
2  | zyfon | run       ← 匹配
3  | daodao| NULL      ← 左表有、右表無
-- user_action 中 user_id=4 的 swim 不出現（右表無匹配左表）

運作邏輯：讀取左表一行 → 找出所有 ON 匹配的右表行 → 產生 N 行結果 → 繼續下一行左表。

反向查詢：找出左表有、右表無的記錄

SELECT id, name, action
FROM user AS u
LEFT JOIN user_action AS a ON u.id = a.user_id
WHERE a.user_id IS NULL;  -- 注意：用 IS NULL，且欄位必須 NOT NULL

id | name  | action
---|-------|-------
3  | daodao| NULL

連接條件子句

1. WHERE：SELECT * FROM t1, t2 WHERE t1.id=t2.id
2. ON：SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id（多表鏈式）
3. USING：當連接欄位同名時簡寫 USING (col) 等同 ON t1.col=t2.col

多表連接範例

SELECT artists.Artist, cds.title, genres.genre
FROM cds
LEFT JOIN genres ON cds.genreID = genres.genreID
LEFT JOIN artists ON cds.artistID = artists.artistID
WHERE genres.genre = 'Pop';

進階優化提示

• USE INDEX (key_list)：強制使用指定索引
• IGNORE INDEX (key_list)：忽略指定索引
• STRAIGHT_JOIN：強制先讀右表（適合右表行數極少時）
• NATURAL JOIN：自動匹配同名欄位（慎用）

MySQL 5.0+ 運算優先級變更注意

舊版：SELECT ... FROM t1, t2 LEFT JOIN t3 ON ... WHERE ... 實際執行為 FROM t1, (t2 LEFT JOIN t3 ...)

新版需明確括號：SELECT ... FROM (t1, t2) LEFT JOIN t3 ON ...

基礎 JOIN 快速參考

關鍵字語法速查

• USING (c1,c2,c3) 等同 ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
• NATURAL [LEFT] JOIN 相當於包含所有同名欄位的 USING 子句
• 多表 JOIN 建議：以小表驅動大表，優先用 LEFT JOIN 減少全掃描