比较NULL值时的数据插入重复问题排查

你的问题核心是NULL值不能用=进行比较，SQL中NULL代表未知值，NULL = NULL的结果为FALSE，这导致原语句的NOT EXISTS条件永远无法匹配已存在的NULL记录，进而重复插入。

原语句的问题分析 #

原WHERE子句里的条件存在两处逻辑错误：

DFS.sch_time_in = SFS.sch_time_in
and DFS.sch_time_out = SFS.sch_time_out
and dfs.sch_time_in is null and dfs.sch_time_out is null

1. 当sch_time_in和sch_time_out为NULL时，DFS.sch_time_in = SFS.sch_time_in的结果是FALSE，直接导致整个匹配条件不成立。
2. 额外添加的dfs.sch_time_in is null and dfs.sch_time_out is null仅判断了目标表的字段为NULL，未关联临时表的对应字段是否也为NULL，逻辑不完整。

修正后的SQL写法 #

方法1：使用IS NOT DISTINCT FROM（支持PostgreSQL、SQL Server 2022+、Oracle 12c+等数据库）

该运算符会自动处理NULL值匹配，当两边都是NULL或值相等时，结果为TRUE：

insert into fact_jpm_lea_temp 
select
    SFS.employee_id,    
    SFS.shift_date,     
    SFS.sch_time_in, 
    SFS.sch_time_out,   
    now() as dwh_ins_ts ,
    now() as dwh_updt_ts
from
    debug_fact_jpm_lea_temp1 as SFS
where
    not exists (
    select
        *
    from
        fact_jpm_lea_temp DFS
    where
        DFS.employee_id = SFS.employee_id
        and DFS.shift_date = SFS.shift_date 
        and DFS.sch_time_in IS NOT DISTINCT FROM SFS.sch_time_in
        and DFS.sch_time_out IS NOT DISTINCT FROM SFS.sch_time_out
    )

方法2：手动处理NULL比较（兼容所有数据库）

通过OR明确判断“两边都是NULL”或者“值相等”：

insert into fact_jpm_lea_temp 
select
    SFS.employee_id,    
    SFS.shift_date,     
    SFS.sch_time_in, 
    SFS.sch_time_out,   
    now() as dwh_ins_ts ,
    now() as dwh_updt_ts
from
    debug_fact_jpm_lea_temp1 as SFS
where
    not exists (
    select
        *
    from
        fact_jpm_lea_temp DFS
    where
        DFS.employee_id = SFS.employee_id
        and DFS.shift_date = SFS.shift_date 
        and (DFS.sch_time_in = SFS.sch_time_in OR (DFS.sch_time_in IS NULL AND SFS.sch_time_in IS NULL))
        and (DFS.sch_time_out = SFS.sch_time_out OR (DFS.sch_time_out IS NULL AND SFS.sch_time_out IS NULL))
    )

补充建议 #

如果数据库支持，可给(employee_id, shift_date, sch_time_in, sch_time_out)创建唯一约束，从根本上防止重复插入——即使SQL逻辑存在问题，数据库也会抛出错误阻止重复数据写入。

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