Oracle SQL过滤NULL或'00000'字段的查询问题求助

核心问题分析 #

1. 左连接被强制转为内连接：你使用左外连接，但在WHERE子句中对C表字段添加非空/不等于判断，会直接过滤掉所有C表无匹配的记录（这类记录中C表字段全为NULL），相当于把左连接改成了内连接，丢失了M表中原本应保留的无匹配数据。
2. 条件逻辑完全错误：你用OR组合IS NOT NULL和!= '00000'，这和“排除NULL或'00000'”的需求完全相反。正确逻辑应该用AND：只有当字段**既不是NULL，也不等于'00000'**时才保留，单字段正确条件为(C.SP_AGTNMBR1 IS NOT NULL AND C.SP_AGTNMBR1 != '00000')。

针对业务需求的两种解决方案 #

根据“过滤掉三个字段中代理编号为NULL或'00000'的记录”的需求，分两种场景处理：

场景1：保留M表所有记录，仅过滤C表中三个字段都无效的匹配项 #

把过滤条件放到LEFT JOIN的ON子句中，这样只会筛选符合要求的C表匹配项，不会影响M表的无匹配记录：

SELECT
    TRIM(UPPER(M.SC_CNT_PREF)) || TRIM(UPPER(M.SC_CNT_NO)) || TRIM(UPPER(M.SC_CNT_SUF)) AS POLICY_NUMBER,
    'CL000' || C.SP_AGTNMBR1 AS AGENT_NUMBER_1,
    TO_NUMBER(C.SP_AGTPCNT1) AS AGNT_PCT_RT_1,
    'CL000' || C.SP_AGTNMBR2 AS AGENT_NUMBER_2,
    TO_NUMBER(C.SP_AGTPCNT2) AS AGNT_PCT_RT_2,
    'CL000' || C.SP_AGTNMBR3 AS AGENT_NUMBER_3,
    TO_NUMBER(C.SP_AGTPCNT3) AS AGNT_PCT_RT_3,
    NULL AS SITUATION
FROM
    EODS_STG.STG1_EODS_SCIS_MASTER M
    LEFT OUTER JOIN EODS_STG.STG1_EODS_SCIS_SPIA_CONTRACT C ON (
            M.SC_CNT_PREF = C.SP_CNTRPREF
        AND M.SC_CNT_NO = C.SP_CNTRNMBR
        AND M.SC_CNT_SUF = C.SP_CNTRSUFF
        -- 将过滤条件移至ON子句
        AND C.SP_AGTNMBR1 IS NOT NULL AND C.SP_AGTNMBR1 != '00000'
        AND C.SP_AGTNMBR2 IS NOT NULL AND C.SP_AGTNMBR2 != '00000'
        AND C.SP_AGTNMBR3 IS NOT NULL AND C.SP_AGTNMBR3 != '00000'
    );

场景2：排除C表三个字段全为无效值的记录，保留M表其他所有记录 #

如果需求是：只要C表存在匹配且至少有一个字段是有效代理编号（非NULL且非'00000'），就保留；若C表三个字段全无效，则过滤这条记录。此时条件放在WHERE子句中，调整逻辑如下：

SELECT
    TRIM(UPPER(M.SC_CNT_PREF)) || TRIM(UPPER(M.SC_CNT_NO)) || TRIM(UPPER(M.SC_CNT_SUF)) AS POLICY_NUMBER,
    'CL000' || C.SP_AGTNMBR1 AS AGENT_NUMBER_1,
    TO_NUMBER(C.SP_AGTPCNT1) AS AGNT_PCT_RT_1,
    'CL000' || C.SP_AGTNMBR2 AS AGENT_NUMBER_2,
    TO_NUMBER(C.SP_AGTPCNT2) AS AGNT_PCT_RT_2,
    'CL000' || C.SP_AGTNMBR3 AS AGENT_NUMBER_3,
    TO_NUMBER(C.SP_AGTPCNT3) AS AGNT_PCT_RT_3,
    NULL AS SITUATION
FROM
    EODS_STG.STG1_EODS_SCIS_MASTER M
    LEFT OUTER JOIN EODS_STG.STG1_EODS_SCIS_SPIA_CONTRACT C ON (
            M.SC_CNT_PREF = C.SP_CNTRPREF
        AND M.SC_CNT_NO = C.SP_CNTRNMBR
        AND M.SC_CNT_SUF = C.SP_CNTRSUFF
    )
WHERE
    -- 保留C表无匹配的记录，或C表至少有一个字段有效
    C.SP_AGTNMBR1 IS NULL
    OR (C.SP_AGTNMBR1 IS NOT NULL AND C.SP_AGTNMBR1 != '00000')
    OR (C.SP_AGTNMBR2 IS NOT NULL AND C.SP_AGTNMBR2 != '00000')
    OR (C.SP_AGTNMBR3 IS NOT NULL AND C.SP_AGTNMBR3 != '00000');

更简洁的写法：

WHERE
    COALESCE(C.SP_AGTNMBR1, C.SP_AGTNMBR2, C.SP_AGTNMBR3) IS NULL
    OR NOT (
        (C.SP_AGTNMBR1 IS NULL OR C.SP_AGTNMBR1 = '00000')
        AND (C.SP_AGTNMBR2 IS NULL OR C.SP_AGTNMBR2 = '00000')
        AND (C.SP_AGTNMBR3 IS NULL OR C.SP_AGTNMBR3 = '00000')
    );

内容的提问来源于stack exchange，提问作者karthik