MySQL LEFT JOIN 進階：ON 條件 vs WHERE 條件、IS NULL 反模式與多條件匹配深度解析

核心概念：匹配階段 vs 過濾階段

A LEFT JOIN B ON 條件 執行邏輯分兩階段：

1. 匹配階段：依據 ON 條件從 B 表檢索匹配行。若 B 無匹配行，**生成一行全 NULL 的虛擬行** 與 A 表行拼接
2. 過濾階段：匹配完成後，應用 WHERE 條件過濾結果集

**關鍵差異：ON 條件決定「如何匹配」，WHERE 條件決定「保留哪些結果」。**

一、經典對比：ON vs WHERE

測試數據

查詢 1：ON 中加額外條件

結果： product 4 行全保留，只有 id=2 匹配到詳情，其餘 3 行詳情列為 NULL。

查詢 2：WHERE 過濾

結果： 僅 1 行（id=2）。WHERE 過濾掉了詳情為 NULL 的行，**實際退化為 INNER JOIN**。

二、ON 中引用左表列 vs 右表列

三、WHERE ... IS NULL 反模式深度解析

查找「在 B 表中**不存在**匹配行」的 A 表記錄：

邏輯等價轉換（德摩根律）

當 ON 條件為多條件 ON (A AND B AND C) 時，WHERE b.id IS NULL 等價於：

實戰驗證

四、Matching-Conditions vs Where-Conditions 等價重寫

將基本匹配條件放 ON，否定條件放 WHERE，結果相同：

**僅查詢左表列時兩者等價**。但若 **SELECT \*** 包含右表列，結果不同：

• 寫法 1：右表列全為 NULL（因 WHERE 過濾後只保留虛擬行）
• 寫法 2：右表列保留實際匹配行的值（weight=44 或 exist=1 的行會顯示）

五、性能與優化器行為

• MySQL 優化器會嘗試將 WHERE b.id IS NULL 轉為 NOT EXISTS 子查詢
• LEFT JOIN ... WHERE IS NULL 通常比 NOT IN 子查詢快（避開 NULL 陷阱）
• 大表關聯時，確保 ON 條件列有索引，WHERE 條件列盡量不參與匹配

六、避坑清單

1. ❌ 把本應在 ON 的匹配條件寫在 WHERE → 導致 LEFT JOIN 退化為 INNER JOIN
2. ❌ WHERE b.col IS NULL 中 b.col 非主鍵/非 NOT NULL 列 → 可能誤判
3. ❌ 多條件 ON + WHERE IS NULL 邏輯混淆 → 用德摩根律拆解驗證
4. ✅ 明確語義：ON=怎麼關聯，WHERE=留哪些行
5. ✅ 需要右表列信息時，**不要**用 WHERE IS NULL 過濾
6. ✅ EXPLAIN 關注 Using where、Not exists 標記

總結

LEFT JOIN 的靈魂在於 **ON 決定匹配邊界，WHERE 決定最終輸出**。理解兩階段執行模型，善用德摩根律拆解複雜 IS NULL 邏輯，是寫出正確、高效關聯查詢的關鍵。