Oracle MERGE INTO无匹配时未执行插入操作问题咨询

需求：无匹配记录时执行插入，有匹配记录时执行更新

用户提供的SQL语句：

merge INTO employee_identity tgt USING (
  select 
    e.employee_id, 
    eid.emp_cus_value, 
    eid.emp_doj 
  from 
    employee e 
    join employee_channel ec on e.employee_id = ec.employee_id 
    left join employee_identity eid on e.employee_id = eid.employee_id 
  where 
    ec.employee_channel_id = 53
) src ON (
  tgt.employee_id = src.employee_id
) WHEN NOT matched THEN INSERT (
  tgt.employee_identity_id, tgt.employee_id, 
  tgt.emp_cus_value, tgt.emp_doj
) 
VALUES 
  (3, 1121, 404, CURRENT_TIMESTAMP)
 WHEN matched THEN UPDATE 
SET 
  tgt.emp_cus_value = tgt.emp_cus_value + 10;

问题：当SELECT子查询无返回结果时，插入操作未执行，但更新功能正常。

问题原因 #

MERGE的核心逻辑是基于源数据集（src）和目标表（tgt）的逐行匹配。如果源查询没有返回任何行，就没有数据可以和目标表做匹配对比，自然不会触发WHEN NOT MATCHED的插入逻辑——毕竟连源数据都没有，根本无从判断“匹配”或“不匹配”。

解决方案 #

要实现“即使源查询无结果也执行插入”，必须保证源数据集至少存在一行数据。可以通过UNION ALL结合NOT EXISTS，让源查询在原条件无结果时返回一行预设的插入数据，修改后的SQL如下：

merge INTO employee_identity tgt 
USING (
  -- 原查询逻辑
  select 
    e.employee_id, 
    eid.emp_cus_value, 
    eid.emp_doj 
  from 
    employee e 
    join employee_channel ec on e.employee_id = ec.employee_id 
    left join employee_identity eid on e.employee_id = eid.employee_id 
  where 
    ec.employee_channel_id = 53
  -- 原查询无结果时，返回插入用的默认数据
  UNION ALL
  select 
    1121 as employee_id, 
    404 as emp_cus_value, 
    CURRENT_TIMESTAMP as emp_doj 
  from dual
  where not exists (
    -- 检查原查询是否有结果
    select 1 from employee e 
    join employee_channel ec on e.employee_id = ec.employee_id 
    where ec.employee_channel_id = 53
  )
) src ON (
  tgt.employee_id = src.employee_id
) 
WHEN NOT matched THEN INSERT (
  employee_identity_id, employee_id, 
  emp_cus_value, emp_doj
) 
VALUES 
  (3, src.employee_id, src.emp_cus_value, src.emp_doj)
WHEN matched THEN UPDATE 
SET 
  tgt.emp_cus_value = tgt.emp_cus_value + 10;

方案说明 #

1. 当原查询（ec.employee_channel_id = 53）有结果时，UNION ALL后面的部分会因为NOT EXISTS不满足而不返回数据，源数据集就是原查询的结果，正常执行匹配更新/插入。
2. 当原查询无结果时，UNION ALL会返回预设的employee_id=1121这行数据，此时如果目标表中没有该employee_id的记录，就会触发WHEN NOT MATCHED执行插入；如果已有该记录，则触发WHEN MATCHED执行更新。

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