MySQL JOIN 語法全解析與性能優化實戰：Inner/Left/Right/Cross/Full Join

JOIN 是多表關聯查詢的核心。本文系統講解 MySQL 五種 JOIN 類型語法、視覺化集合關係、執行機制、以及 7 大性能優化實戰技巧。

JOIN 分類與語法

SELECT select_list
FROM table1
[INNER | LEFT | RIGHT] JOIN table2 ON join_condition
[WHERE where_condition];

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

五種 JOIN 視覺化對比 (以表 A、B 為例)

實戰範例數據

-- 表 A
id | name
1  | Pirate
2  | Monkey
3  | Ninja
4  | Spaghetti

-- 表 B
id | name
1  | Rutabaga
2  | Pirate
3  | Darth Vader
4  | Ninja

各 JOIN 執行結果示例

-- 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;
-- 1 Pirate  | 2 Pirate
-- 2 Monkey  | NULL NULL
-- 3 Ninja   | 4 Ninja
-- 4 Spaghetti| NULL NULL

-- RIGHT JOIN
SELECT * FROM A RIGHT JOIN B ON A.name = B.name;
-- NULL NULL | 1 Rutabaga
-- 1 Pirate  | 2 Pirate
-- NULL NULL | 3 Darth Vader
-- 3 Ninja   | 4 Ninja

-- CROSS JOIN (笛卡爾積 4×4=16 行)
SELECT * FROM A CROSS JOIN B;

-- 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;

進階技巧：LEFT JOIN 實現差集/非匹配查詢

-- 僅 A 有、B 無 (差集)
SELECT * FROM A LEFT JOIN B ON A.name = B.name WHERE B.id IS NULL;

-- 對稱差集 (A有B無 + B有A無)
SELECT * FROM A LEFT JOIN B ON A.name = B.name WHERE B.id IS NULL
UNION
SELECT * FROM A RIGHT JOIN B ON A.name = B.name WHERE A.id IS NULL;

MySQL 特有：CROSS JOIN = INNER JOIN (無 ON 條件時)

-- 以下三者在 MySQL 中等價 (無 ON 時均為笛卡爾積)
SELECT * FROM A CROSS JOIN B;
SELECT * FROM A INNER JOIN B;
SELECT * FROM A JOIN B;

-- 加上 ON 條件後，CROSS JOIN 退化為 INNER JOIN
SELECT * FROM A CROSS JOIN B ON A.name = B.name;  -- 等同 INNER JOIN

7 大性能優化實戰原則

1. 顯式 INNER JOIN vs 隱式逗號連接 (效能相近，但顯式更清晰)

-- 顯式 (推薦)
SELECT * FROM a INNER JOIN b ON a.id = b.id;

-- 隱式 (舊語法)
SELECT a.*, b.* FROM a, b WHERE a.id = b.id;

2. 優先用 INNER JOIN，避免 LEFT JOIN + NULL 判斷

INNER JOIN 可由優化器自由調整驅動表順序；LEFT JOIN 強制左表為驅動表，限制優化空間。

3. ON 條件 vs WHERE 條件執行順序差異 (關鍵！)

-- 邏輯不同！
-- ON: 決定如何從右表匹配行，無匹配則生成 NULL 行
-- WHERE: 在匹配完成後過濾結果行

-- 範例：查詢商品及其最新價格 (若無價格仍顯示商品)
SELECT p.*, pr.price
FROM product p
LEFT JOIN price pr ON pr.product_id = p.id AND pr.is_latest = 1  -- 正確：ON 中過濾
-- 錯誤寫法：
LEFT JOIN price pr ON pr.product_id = p.id
WHERE pr.is_latest = 1  -- 將 LEFT JOIN 退化為 INNER JOIN，無價格商品被過濾掉

4. 將過濾條件下推到 ON 子句 (減少中間結果集)

-- 一般寫法
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;

-- 優化：條件下推 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;

5. 避免子查詢，改用 JOIN (衍生表優化)

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

-- JOIN 改寫 (效能好)
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;

6. 索引覆蓋 JOIN 條件列

• 被驅動表 (右表) 的 JOIN 條件列**必須有索引**
• 複合索引遵循最左前綴
• 使用 EXPLAIN 確認 type=ref、key=索引名

7. 小表驅動大表 (優化器通常自動處理，但可用 STRAIGHT_JOIN 強制)

SELECT /*+ MAX_EXECUTION_TIME(1000) */ STRAIGHT_JOIN
    small_table.*, big_table.*
FROM small_table
INNER JOIN big_table ON small_table.id = big_table.small_id;

EXPLAIN 關鍵欄位解讀