深入理解 MySQL LEFT JOIN：ON 與 WHERE 語義差異實戰

核心結論：ON 決定「怎麼關聯」，WHERE 決定「留哪些行」

LEFT JOIN 執行順序：

1. 從左表讀取行
2. 根據 ON 條件去右表匹配
3. 右表無匹配 → 生成全 NULL 行
4. 對步驟 3 結果集應用 WHERE 過濾

經典陷阱：條件放 ON vs WHERE 結果截然不同

測試數據

CREATE TABLE product(id INT PRIMARY KEY, amount INT);
CREATE TABLE product_details(id INT PRIMARY KEY, weight INT, exist INT);

INSERT INTO product VALUES (1,100),(2,200),(3,300),(4,400);
INSERT INTO product_details VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1);

案例 1：右表條件放 ON

SELECT * FROM product 
LEFT JOIN product_details ON product.id = product_details.id 
    AND product_details.id = 2;

結果：左表 4 行全保留，只有 id=2 關聯上數據，其餘右表列全 NULL。

案例 2：同條件放 WHERE

SELECT * FROM product 
LEFT JOIN product_details ON product.id = product_details.id 
WHERE product_details.id = 2;

結果：僅 1 行（id=2），因 WHERE 過濾掉了右表為 NULL 的行 → 退化為 INNER JOIN。

WHERE ... IS NULL 反模式：查找「不存在」的行

-- 找 product 中無對應 details 的行
SELECT p.* FROM product p
LEFT JOIN product_details d ON p.id = d.id
WHERE d.id IS NULL;
-- 結果：id=1,3

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

-- ON 條件：p.id=d.id AND d.weight!=44 AND d.exist=0
-- WHERE d.id IS NULL
-- 等價於：
WHERE NOT EXISTS (SELECT 1 FROM product_details d 
                  WHERE d.id=p.id AND d.weight!=44 AND d.exist=0)
-- 即：
WHERE d.id IS NULL OR d.weight=44 OR d.exist=1

實戰驗證：SELECT * vs 只選左表列

當 SELECT * 時，WHERE 過濾後右表列可能非 NULL（如 d.weight=44 行被保留），導致結果與 ON+IS NULL 不同。只查左表列時兩者等價。

最佳實踐規範

1. 關聯條件（兩表如何連接）放 ON
2. 業務過濾（保留哪些業務行）放 WHERE
3. 需保留左表所有行時，右表業務條件必須放 ON
4. 避免在 ON 中混入純左表列條件（如 p.amount=100），移至 WHERE 更清晰
5. 用 EXPLAIN 觀察 type=ALL / Using where 判斷是否退化