MySQL JOIN 類型完全圖解：LEFT/RIGHT/INNER/CROSS/FULL

MySQL JOIN 類型完全圖解：LEFT/RIGHT/INNER/CROSS/FULL

JOIN 是多表查詢的核心，掌握各類 JOIN 的語義與結果集差異，能寫出正確且高效的 SQL。本文用文字「圖解」五大 JOIN 類型，並附帶避坑指南。

一、五大 JOIN 對照表

二、語法範例

-- 範例表
CREATE TABLE `dept` (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE `emp`  (id INT PRIMARY KEY, name VARCHAR(20), dept_id INT);

INSERT INTO dept VALUES (1,'研發'),(2,'市場'),(3,'運營');
INSERT INTO emp VALUES (101,'Alice',1),(102,'Bob',1),(103,'Cathy',2),(104,'David',NULL);

1. INNER JOIN（交集）

SELECT e.name, d.name AS dept
FROM emp e
INNER JOIN dept d ON e.dept_id = d.id;
-- 結果：Alice-研發, Bob-研發, Cathy-市場  (David 無 dept_id 被過濾)

2. LEFT JOIN（左表全保）

SELECT e.name, d.name AS dept
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.id;
-- 結果：Alice-研發, Bob-研發, Cathy-市場, David-NULL

3. RIGHT JOIN（右表全保）

SELECT e.name, d.name AS dept
FROM emp e
RIGHT JOIN dept d ON e.dept_id = d.id;
-- 結果：Alice-研發, Bob-研發, Cathy-市場, NULL-運營

4. CROSS JOIN（笛卡爾積）

SELECT e.name, d.name
FROM emp e
CROSS JOIN dept d;
-- 結果：4 員工 × 3 部門 = 12 行

5. FULL OUTER JOIN（MySQL 模擬）

SELECT e.name, d.name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.id
UNION
SELECT e.name, d.name
FROM emp e
RIGHT JOIN dept d ON e.dept_id = d.id;
-- 結果：左右表所有行並集，無匹配處為 NULL

三、效能與避坑指南

1. 優先用 INNER JOIN：結果集最小，優化器選擇空間大；驅動表應選小結果集的表。
2. LEFT JOIN 驅動表固定為左表：若右表大且無索引，會導致大量 NULL 填充 + 全表掃描；必要時改寫為 RIGHT JOIN 或調換表順序。
3. ON 條件 vs WHERE 條件：LEFT JOIN ... ON a.id=b.id AND b.status=1 保留左表所有行；LEFT JOIN ... ON a.id=b.id WHERE b.status=1 會隱性轉為 INNER JOIN。
4. 避免 CROSS JOIN：除非極小配置表（如字典表 < 100 行），否則極易產生爆炸性結果集。
5. 索引覆蓋 ON/USING 列：被驅動表的關聯欄位必須有索引，否則每行都要全掃描。
6. 多表 JOIN 限制：MySQL 單查詢最多 61 張表；超過建議拆分或用臨時表。

四、Venn 圖心智模型（文字版）

INNER JOIN     :  (A ∩ B)
LEFT JOIN      :  (A ∩ B) ∪ (A - B)   -- A 全部
RIGHT JOIN     :  (A ∩ B) ∪ (B - A)   -- B 全部
FULL OUTER JOIN:  (A ∪ B)             -- 全部
CROSS JOIN     :  A × B               -- 笛卡爾積

熟練掌握 JOIN 語義，是寫出正確、高效多表查詢的基石。