四大 JOIN 類型圖解與語法

假設兩表：

-- T1: 用戶表
userid | username | password
1      | jack     | jackpwd
2      | owen     | owenpwd

-- T2: 積分表
userid | jifen | dengji
1      | 20    | 3
3      | 50    | 6

1. INNER JOIN（內連接）

SELECT * FROM T1 INNER JOIN T2 ON T1.userid = T2.userid;
-- 等價於
SELECT * FROM T1, T2 WHERE T1.userid = T2.userid;  -- 但 JOIN 可讀性更強、優化器更優

結果：僅匹配行（userid=1）

userid | username | password | userid | jifen | dengji
1      | jack     | jackpwd  | 1      | 20    | 3

2. LEFT JOIN（左外連接）

SELECT * FROM T1 LEFT JOIN T2 ON T1.userid = T2.userid;

結果：左表全量 + 右表匹配或 NULL

userid | username | password | userid | jifen | dengji
1      | jack     | jackpwd  | 1      | 20    | 3
2      | owen     | owenpwd  | NULL   | NULL  | NULL

3. RIGHT JOIN（右外連接）

SELECT * FROM T1 RIGHT JOIN T2 ON T1.userid = T2.userid;

結果：右表全量 + 左表匹配或 NULL

userid | username | password | userid | jifen | dengji
1      | jack     | jackpwd  | 1      | 20    | 3
NULL   | NULL     | NULL     | 3      | 50    | 6

4. FULL JOIN（全外連接）

MySQL 不直接支援 FULL OUTER JOIN，用 UNION 模擬：

SELECT * FROM T1 LEFT JOIN T2 ON T1.userid = T2.userid
UNION
SELECT * FROM T1 RIGHT JOIN T2 ON T1.userid = T2.userid;

結果：兩表全量，去重

userid | username | password | userid | jifen | dengji
1      | jack     | jackpwd  | 1      | 20    | 3
2      | owen     | owenpwd  | NULL   | NULL  | NULL
NULL   | NULL     | NULL     | 3      | 50    | 6

性能對比與優化建議

連接類型	典型用途	性能特徵	優化建議
INNER JOIN	核心業務關聯查詢	最優，優化器可自由調整驅動表順序	小表驅動大表，被驅動表關聯列有索引
LEFT JOIN	主表帶從表屬性（如訂單+商品詳情）	左表全掃，右表索引查找	左表加 WHERE 過濾減少驅動行，右表關聯列建索引
RIGHT JOIN	極少用，可改寫為 LEFT JOIN 交換表順序	同 LEFT JOIN	統一用 LEFT JOIN，減少認知負擔
FULL JOIN (UNION模擬)	數據對賬、全量合併	兩次全掃 + 去重，極慢	避免大表 FULL JOIN，改用應用層合併或分批處理

UNION / UNION ALL 合併結果集

-- UNION ALL：直接合併，保留重複，速度快
(SELECT id, subject FROM article WHERE active=1 AND subject LIKE '%調整圖片%' ORDER BY add_time DESC)
UNION ALL
(SELECT id, class_name AS subject FROM web_class WHERE active=1 AND class_name LIKE '%調整圖片%' ORDER BY class_id DESC)

-- UNION：合併後去重（排序+唯一），較慢
(SELECT id, subject FROM article WHERE active=1 AND subject LIKE '%調整%')
UNION
(SELECT id, subject FROM article WHERE active=1 AND subject LIKE '%圖片%')

選擇原則：業務允許重複或確知無重複 → UNION ALL；需去重 → UNION（或應用層去重更高效）。

多表連接優先級陷阱（MySQL 5.0+）

問題代碼

SELECT t1.id, t2.id, t3.id
FROM t1, t2
LEFT JOIN t3 ON t3.id = t1.id
WHERE t1.id = t2.id;

MySQL 實際解析為：

SELECT t1.id, t2.id, t3.id
FROM t1, (t2 LEFT JOIN t3 ON t3.id = t1.id)  -- t1 在子查詢外，t3 關聯 t1 失效！
WHERE t1.id = t2.id;

導致 t3.id = t1.id 中 t1.id 不可見，語義錯誤。

正確寫法：顯式括號控制優先級

SELECT t1.id, t2.id, t3.id
FROM (t1, t2)          -- 強制 t1,t2 先笛卡爾積/內連接
LEFT JOIN t3 ON t3.id = t1.id
WHERE t1.id = t2.id;

-- 或更清晰的標準 JOIN 語法（推薦）
SELECT t1.id, t2.id, t3.id
FROM t1
INNER JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t3.id = t1.id;

EXPLAIN 關鍵列解讀（JOIN 優化核心）

EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.status = 1;

列	關鍵值	含義
type	system/const/eq_ref/ref/range/index/ALL	訪問類型，越左越好，ALL=全表掃描
possible_keys	可能用到的索引	候選索引
key	實際使用的索引	最終選擇
key_len	索引鍵長度	判斷聯合索引用了幾列
ref	const/列名/func	關聯比較的值來源
rows	預估掃描行數	越小越好
Extra	Using index/Using where/Using temporary/Using filesort	覆蓋索引/臨時表/文件排序警示

最佳實踐清單

1. 優先使用 標準 JOIN 語法（INNER/LEFT），避免舊式逗號連接。
2. 小表驅動大表：驅動表（左表或 INNER JOIN 任一邊）應為過濾後行數少的表。
3. 被驅動表關聯列必須有索引，否則每行掃描全表（Nested Loop Join 災難）。
4. 只選需要的列：SELECT * 禁止，覆蓋索引可避免回表。
5. 條件下推：WHERE 條件盡量寫在 JOIN ON 子句中，減少中間結果集。
6. 大表分頁：避免 LIMIT 100000, 20，改用 WHERE id > last_max_id LIMIT 20（游標分頁）。