MySQL JOIN 語法全解析與性能優化實戰：從內連接到全連接模擬

JOIN 語法基礎

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON condition

• table1：左表（驅動表）
• table2：右表（被驅動表）

三大核心 JOIN 類型

⚠️ MySQL **不支援 FULL JOIN**，可用 LEFT JOIN UNION RIGHT JOIN 模擬。

實戰案例演示（表 A、B）

A 表: (1,Pirate) (2,Monkey) (3,Ninja) (4,Spaghetti)
B 表: (1,Rutabaga) (2,Pirate) (3,Darth Vader) (4,Ninja)

INNER JOIN

SELECT * FROM A INNER JOIN B ON A.name = B.name;
-- 結果: (1,Pirate,2,Pirate) (3,Ninja,4,Ninja)

LEFT JOIN

SELECT * FROM A LEFT JOIN B ON A.name = B.name;
-- 結果: A 全量，B 匹配行或 NULL

衍生用法：

• 僅左表無匹配：WHERE B.id IS NULL
• 模擬 INNER JOIN：WHERE A.id IS NOT NULL AND B.id IS NOT NULL

RIGHT JOIN

鏡像 LEFT JOIN，結果以 B 表全量為主。

CROSS JOIN

笛卡爾積（16 行 = 4×4）。MySQL 中 CROSS JOIN = INNER JOIN（無 ON 條件時）。

SELECT * FROM A CROSS JOIN B;
-- 等價於: SELECT * FROM A JOIN B; (無 ON)

FULL JOIN 模擬

SELECT * FROM A LEFT JOIN B ON A.name = B.name
UNION
SELECT * FROM A RIGHT JOIN B ON A.name = B.name;

性能優化七大法則

1. 顯式 INNER JOIN vs 隱式 JOIN

-- 顯式 (推薦，可讀性強)
SELECT * FROM a INNER JOIN b ON a.id = b.id;

-- 隱式 (舊語法，MySQL 優化器同等對待)
SELECT a.*, b.* FROM a, b WHERE a.id = b.id;

實測 10 萬數據性能幾乎相同，推薦顯式語法。

2. 優先 INNER JOIN，避免 LEFT/RIGHT JOIN + NULL

外連接產生 NULL 需額外過濾，增加開銷。

3. ON 條件 vs WHERE 條件執行順序（關鍵！）

• ON：決定如何從右表檢索數據，**匹配階段**執行
• WHERE：從匹配結果中過濾，**匹配後**執行

優化原則：盡可能將條件下推到 ON 子句，減少 WHERE 過濾量。

-- PASS: WHERE 過濾
SELECT * FROM A
INNER JOIN B ON B.name = A.name
LEFT JOIN C ON C.name = B.name
LEFT JOIN D ON D.id = C.id
WHERE C.status > 1 AND D.status = 1;

-- GREAT: ON 下推
SELECT * FROM A
INNER JOIN B ON B.name = A.name
LEFT JOIN C ON C.name = B.name AND C.status > 1
LEFT JOIN D ON D.id = C.id AND D.status = 1;

4. ON 子句與 WHERE 子句語義差異實例

-- ON 過濾：左表全量，右表僅 status>1 的行參與匹配
SELECT * FROM product LEFT JOIN product_details
ON product.id = product_details.id AND product_details.id = 2;

-- WHERE 過濾：先全量 LEFT JOIN，再過濾
SELECT * FROM product LEFT JOIN product_details
ON product.id = product_details.id
WHERE product_details.id = 2;

前者保留左表所有行（右表無匹配則 NULL），後者相當於 INNER JOIN。

5. 子查詢轉 JOIN（大數據量必備）

-- PASS: 子查詢 NOT EXISTS
INSERT INTO t1(a1) 
SELECT b1 FROM t2 
WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE t1.id = t2.r_id);

-- GREAT: LEFT JOIN + IS NULL
INSERT INTO t1(a1)
SELECT b1 FROM t2
LEFT JOIN (SELECT DISTINCT id FROM t1) t1 ON t1.id = t2.r_id
WHERE t1.id IS NULL;

JOIN 可利用索引、避免相關子查詢逐行執行、優化器更易生成高效計劃。

總結

• 掌握三大 JOIN 語義與結果集差異
• MySQL 無 FULL JOIN，用 UNION 模擬
• 優化核心：**條件下推到 ON**、**外連接轉內連接**、**子查詢轉 JOIN**
• 實際優化需結合 EXPLAIN 分析執行計劃