測試資料準備

CREATE TABLE `a` (
  `aID` INT PRIMARY KEY,
  `aNum` VARCHAR(20)
);
INSERT INTO `a` VALUES (1,'a20110111'),(2,'a20110112'),(3,'a20110113'),(4,'a20110114'),(5,'a20110115');

CREATE TABLE `b` (
  `bID` INT PRIMARY KEY,
  `bName` VARCHAR(20)
);
INSERT INTO `b` VALUES (1,'bName0111'),(2,'bName0112'),(3,'bName0113'),(4,'bName0114'),(8,'bName0118');

三大 JOIN 視覺化對比

JOIN 類型	基準表	結果行數	不匹配處理	Venn 圖
LEFT JOIN	左表（A）	≥ 左表行數	右表列填 NULL	左圓完整 + 交集
RIGHT JOIN	右表（B）	≥ 右表行數	左表列填 NULL	右圓完整 + 交集
INNER JOIN	無（僅交集）	≤ MIN(左,右)	直接丟棄	僅交集

實際執行結果

LEFT JOIN

SELECT * FROM a LEFT JOIN b ON a.aID = b.bID;
-- 結果
aID  aNum        bID  bName
1    a20110111   1    bName0111
2    a20110112   2    bName0112
3    a20110113   3    bName0113
4    a20110114   4    bName0114
5    a20110115   NULL NULL   -- A 有，B 無

RIGHT JOIN

SELECT * FROM a RIGHT JOIN b ON a.aID = b.bID;
-- 結果
aID  aNum        bID  bName
1    a20110111   1    bName0111
2    a20110112   2    bName0112
3    a20110113   3    bName0113
4    a20110114   4    bName0114
NULL NULL        8    bName0118  -- B 有，A 無

INNER JOIN

SELECT * FROM a INNER JOIN b ON a.aID = b.bID;
-- 等價於
SELECT * FROM a, b WHERE a.aID = b.bID;
-- 結果：僅 1-4 共 4 行

進階用法與技巧

USING 簡寫（同名列）

SELECT * FROM a LEFT JOIN b USING (id);  -- 需雙邊列名同為 id

多表鏈式 JOIN

SELECT * FROM a
LEFT JOIN b ON a.id = b.a_id
LEFT JOIN c ON b.id = c.b_id
LEFT JOIN d ON c.id = d.c_id;

反向查找「僅在左表不在右表」

SELECT a.* FROM a
LEFT JOIN b ON a.id = b.a_id
WHERE b.a_id IS NULL;  -- 判斷列必須 NOT NULL

強制 JOIN 順序優化

-- 優化器可能選錯驅動表，使用 STRAIGHT_JOIN 強制順序
SELECT * FROM b
STRAIGHT_JOIN a LEFT JOIN c ON c.key = a.key
WHERE b.key = d.key;

MySQL 8.0+ 新特性：LATERAL JOIN

-- 類似 CROSS APPLY，右表可引用左表列
SELECT a.*, top_b.*
FROM a
LEFT JOIN LATERAL (
  SELECT * FROM b WHERE b.a_id = a.id ORDER BY b.created_at DESC LIMIT 1
) top_b ON true;

效能優化清單

1. 驅動表選擇：小表驅動大表，左表（LEFT JOIN）應為較小結果集。
2. ON 條件列建索引：雙邊 JOIN KEY 均需索引，複合索引列序匹配 ON 條件。
3. WHERE 過濾下推：LEFT JOIN 時，右表條件放 ON 不放 WHERE，否則退化為 INNER JOIN。
4. 避免 SELECT *：只取需要列，利用覆蓋索引減少回表。
5. 關閉衍生表合併：optimizer_switch='derived_merge=off' 針對複雜子查詢優化。

常見陷阱

• LEFT JOIN ... WHERE right.col = 'val' 會過濾掉右表 NULL 行，實際變 INNER JOIN。正確寫法：LEFT JOIN ... ON right.col = 'val'。
• COUNT(*) 配合 LEFT JOIN 計數時，改用 COUNT(right.pk) 避免把 NULL 行計入。
• MySQL 不支援 FULL OUTER JOIN，用 LEFT JOIN UNION RIGHT JOIN 模擬。