Oracle LEFT JOIN 過濾條件放置位置對查詢結果與效能的影響分析

問題背景

在 Oracle 資料庫開發中，使用 LEFT JOIN 進行多表關聯查詢時，將過濾條件寫在 ON 子句與 WHERE 子句中，會產生截然不同的執行計劃與查詢結果。本文透過實際案例，深入剖析這一核心差異，幫助開發者避免常見陷阱。

案例重現：錯誤寫法導致資料遺漏

原始需求：從 t_base_succprouser（主表）查詢符合條件的用戶，並排除在 t_base_vip_customes（從表）中存在有效記錄的用戶。

初始 SQL 使用子查詢 + LEFT JOIN + WHERE 過濾：

SELECT a.f_username
FROM (
    SELECT /*+parallel(gu,4)*/ DISTINCT gu.f_username
    FROM t_base_succprouser gu
    WHERE gu.f_expectenddate > TRUNC(SYSDATE, 'Y')
      AND gu.f_lotid = 1
      AND gu.f_playid = 4
      AND gu.f_paymoney >= 1500
) A
LEFT JOIN (
    SELECT vu.f_username
    FROM t_base_vip_customes vu
    WHERE (vu.f_passeddate IS NULL OR vu.f_passeddate > TRUNC(SYSDATE, 'Y'))
      AND (vu.f_lotid IS NULL OR vu.f_lotid = 1)
      AND (vu.f_playid IS NULL OR vu.f_playid = 4)
      AND (vu.f_condtionid IS NULL OR vu.f_condtionid = 3)
) B ON A.f_username = B.f_username
WHERE B.f_username IS NULL;

嘗試改寫為單層 LEFT JOIN 時，將從表過濾條件放入 WHERE 子句：

SELECT /*+parallel(gu,4)*/ DISTINCT gu.f_username
FROM t_base_succprouser gu
LEFT JOIN t_base_vip_customes vu ON gu.f_username = vu.f_username
WHERE gu.f_expectenddate > TRUNC(SYSDATE, 'Y')
  AND gu.f_lotid = rec_viplotplay.f_lotid
  ...
  AND vu.f_username IS NULL
  AND ((vu.f_passeddate IS NULL) OR (vu.f_passeddate > TRUNC(SYSDATE, 'Y')))
  ...

結果：只能查出部分用戶，大量符合條件的資料被錯誤過濾掉。執行計劃顯示為 NESTED LOOPS OUTER 配合 VIEW + FILTER，效能低下。

根因分析：WHERE 子句將 OUTER JOIN 隱性轉為 INNER JOIN

當 LEFT JOIN 的右表（從表）過濾條件寫在 WHERE 子句中時，Oracle 優化器會在 JOIN 完成後再套用條件。由於 vu.f_passeddate > ... 等條件要求右表欄位有具體值，而 LEFT JOIN 特性是右表無匹配時欄位為 NULL，這導致所有「右表無匹配」的行被 WHERE 條件過濾掉，實際上將 OUTER JOIN 退化為 INNER JOIN。

正確寫法：將右表過濾條件前置到 ON 子句

SELECT /*+parallel(gu,4)*/ DISTINCT gu.f_username
FROM t_base_succprouser gu
LEFT JOIN t_base_vip_customes vu ON gu.f_username = vu.f_username
    AND ((vu.f_passeddate IS NULL) OR (vu.f_passeddate > TRUNC(SYSDATE, 'Y')))
    AND ((vu.f_lotid IS NULL) OR (vu.f_lotid = rec_viplotplay.f_lotid))
    AND ((vu.f_playid IS NULL) OR (vu.f_playid = rec_viplotplay.f_playid))
    AND ((vu.f_condtionid IS NULL) OR (vu.f_condtionid = rec_viplotplay.f_condtionid))
WHERE gu.f_expectenddate > TRUNC(SYSDATE, 'Y')
  AND gu.f_lotid = rec_viplotplay.f_lotid
  AND gu.f_playid = rec_viplotplay.f_playid
  AND gu.f_paymoney >= rec_viplotplay.f_conditon_valuesa
  AND vu.f_username IS NULL;

執行計劃變化：從 NESTED LOOPS OUTER + VIEW/FILTER 變為 TABLE ACCESS BY GLOBAL INDEX ROWID + INDEX RANGE SCAN，改用索引存取，效能大幅提升，且結果完整正確。

核心原則總結

• 左表（驅動表）過濾條件：放在 WHERE 子句，優先縮小驅動集。
• 右表（被驅動表）過濾條件：必須放在 ON 子句，保證 OUTER JOIN 語義不被破壞。
• 連接鍵條件：放在 ON 子句。
• IS NULL 判斷：用於檢測無匹配行，放在 WHERE 子句。

效能優化建議

1. 善用 /*+ PARALLEL */ 提示針對大表掃描。
2. 確保 JOIN 鍵與過濾欄位有適當索引（如案例中的 IX_BASE_PROUSER_LOWEX）。
3. 定期收集統計資訊，讓 CBO 生成最優計劃。
4. 對於複雜條件，優先考慮 NOT EXISTS 或 NOT IN（注意 NULL 值陷阱）作為替代方案。

掌握 LEFT JOIN 條件放置規則，不僅能修正查詢結果，更能讓執行計劃走上索引存取路徑，實現雙贏。