MySQL 左連接多表查詢 WHERE 條件陷阱與正確寫法

現象：兩條語句結果不一致

-- 寫法 1：WHERE 直接過濾右表列
SELECT * FROM _test a 
LEFT JOIN _test b ON a.id = b.id 
WHERE a.level='20' AND a.month='04' 
  AND b.level='20' AND b.month='03';

-- 寫法 2：子查詢預過濾再 LEFT JOIN
SELECT a.*, b.* 
FROM (SELECT * FROM _test WHERE level='20' AND month='04') AS a
LEFT JOIN (SELECT * FROM _test WHERE level='20' AND month='03') AS b
ON a.id = b.id;

根本原因

寫法 1 中，右表列出現在 WHERE 條件中，導致 LEFT JOIN 退化為 INNER JOIN。

• LEFT JOIN 語義：保留左表所有行，右表無匹配則填 NULL。
• WHERE b.level='20' 要求右表列非 NULL，直接過濾掉了無匹配的左表行。
• 優化器識別出此邏輯，直接轉為內連接執行計劃。

正確模式：條件下推到 ON 子句或子查詢

模式 A：右表過濾條件移至 ON

SELECT * FROM _test a
LEFT JOIN _test b 
  ON a.id = b.id 
  AND b.level = '20' 
  AND b.month = '03'
WHERE a.level = '20' AND a.month = '04';

左表過濾放 WHERE，右表過濾放 ON，保留 LEFT JOIN 語義。

模式 B：子查詢預過濾（推薦，可讀性強）

SELECT a.*, b.*
FROM (SELECT * FROM _test WHERE level='20' AND month='04') a
LEFT JOIN (SELECT * FROM _test WHERE level='20' AND month='03') b
ON a.id = b.id;

子查詢物化後再 JOIN，邏輯清晰，優化器也易生成優質計劃。

驗證結果差異

原文配圖顯示：

• 寫法 1 返回 0 行（退化為內連接，雙邊條件無交集）。
• 寫法 2 正確返回左表獨有行 + 右表匹配行（含 NULL）。

最佳實踐清單

1. 左表過濾 → WHERE，右表過濾 → ON 子句。
2. 複雜多表 LEFT JOIN 推薦用 CTE (WITH 子句) 或派生表預過濾。
3. EXPLAIN 關注 join_type：ALL/ref 正常，出現 eq_ref 且無 NULL 擴展則可能退化。
4. 業務需「找出差異數據」時，WHERE b.id IS NULL 反向查詢比 NOT IN/EXISTS 更高效。

總結

LEFT JOIN 的核心是「保左表」。任何右表列出現在 WHERE 中（除 IS NULL 判斷），都會破壞外連接語義。養成「左 WHERE 右 ON」或「子查詢預過濾」習慣，從源頭杜絕此類 Bug。