SQL LEFT JOIN 與 INNER JOIN 條件位置差異深度解析

問題現象

兩條 SQL 查詢，僅 JOIN 類型不同，WHERE 條件位置不同，結果卻大相徑庭：

-- 查詢 1：LEFT JOIN + ON 條件包含過濾
SELECT a.* FROM auctioncommendity a
LEFT JOIN auctionbid b
  ON a.id = b.auctioncommendityid AND a.id = '000000004ef2f390014ef3024f230015';

-- 查詢 2：INNER JOIN + ON 條件包含過濾
SELECT a.* FROM auctioncommendity a
INNER JOIN auctionbid b
  ON a.id = b.auctioncommendityid AND a.id = '000000004ef2f390014ef3024f230015';

查詢 1 返回所有 auctioncommendity 記錄（不滿足 ID 條件的 b 列為 NULL），查詢 2 僅返回目標 ID 且有匹配 bid 的記錄。

核心原理：ON vs WHERE 在 LEFT JOIN 中的語義差異

圖解執行邏輯

LEFT JOIN 執行流程：
1. 取左表所有行 (a)
2. 對每行 a，按 ON 條件 (a.id=b.aid AND a.id='target') 在右表找匹配
   - 滿足條件：保留 b 列數據
   - 不滿足：b 列全部置 NULL
3. 結果集包含**所有 a 行**，無論是否匹配
4. 最後應用 WHERE (若有) 過濾最終結果

因此：ON 中的 a.id='target' 只是限制「哪些 a 行去匹配 b」，
     但不滿足該條件的 a 行依然在結果中（b 列為 NULL）。

正確寫法：LEFT JOIN 實現 INNER JOIN 效果

-- 將過濾條件移至 WHERE
SELECT a.* FROM auctioncommendity a
LEFT JOIN auctionbid b
  ON a.id = b.auctioncommendityid
WHERE a.id = '000000004ef2f390014ef3024f230015';

此時：先 LEFT JOIN 產生中間表（所有 a + 匹配 b），再用 WHERE 過濾 a.id，等價於 INNER JOIN。

最佳實踐建議

1. 連接條件放 ON，業務過濾放 WHERE：語義清晰，避免歧義。
2. LEFT JOIN 時，右表的過濾條件必須放 ON 中：若放 WHERE，隱式轉為 INNER JOIN（因 NULL 不滿足任何條件）。
3. 調試技巧：先跑 SELECT * FROM a LEFT JOIN b ON ... 觀察中間結果，再加 WHERE。
4. 性能提示：現代優化器會自動將 WHERE 條件下推，但寫法規範有助於優化器生成最優計劃。

總結對比

-- 等價組合
INNER JOIN ... ON cond1 AND cond2
≡
LEFT JOIN ... ON cond1 WHERE cond2  (cond2 只涉及左表列)
≡
LEFT JOIN ... ON cond1 AND cond2 WHERE 1=1  (cond2 只涉及左表列)

-- 非等價
LEFT JOIN ... ON cond1 AND cond2  -- cond2 只涉及左表列時，不會過濾左表行