如何基于多组唯一标识符合并两张表并实现增量数据upsert

问题根因 #

• 匹配字段存在NULL值：SQL标准中NULL和任意值（包括另一个NULL）的等值比较结果为UNKNOWN，不会被判定为匹配，因此只要id/text_id/last_sent/recent_sent四个匹配字段有任意一个为NULL，对应行就会被判定为未匹配直接插入，这是最常见的诱因。
• 临时表存在重复行：如果communicated_tmp_表本身就存在四个匹配字段完全相同的重复数据，MERGE操作会将所有重复行都判定为未匹配插入。
• VALUES子句字段来源歧义：部分SQL引擎对未指定表别名的字段会优先识别为主表字段，导致插入逻辑不符合预期。

修复方案 #

适配支持 IS NOT DISTINCT FROM 语法的引擎（BigQuery、PostgreSQL 15+等）

该语法会将两个NULL值判定为相等，完美解决NULL匹配问题，同时明确指定字段来源、提前对临时表去重：

MERGE
  `project.map.communicated` CURRENT_TABLE
USING
  -- 提前对临时表按匹配字段去重，避免重复插入
  (SELECT DISTINCT * FROM `project.map.communicated_tmp_`) NEW_OR_UPDATED
ON
  (CURRENT_TABLE.id IS NOT DISTINCT FROM NEW_OR_UPDATED.id
    AND CURRENT_TABLE.text_id IS NOT DISTINCT FROM NEW_OR_UPDATED.text_id
    AND CURRENT_TABLE.last_sent IS NOT DISTINCT FROM NEW_OR_UPDATED.last_sent
    AND CURRENT_TABLE.recent_sent IS NOT DISTINCT FROM NEW_OR_UPDATED.recent_sent)
WHEN NOT MATCHED THEN
INSERT
  (`id`, `text_id`, `last_sent`, `recent_sent`, `updated_at`, `date_created`)
VALUES
  (NEW_OR_UPDATED.id, NEW_OR_UPDATED.text_id, NEW_OR_UPDATED.last_sent, NEW_OR_UPDATED.recent_sent, NEW_OR_UPDATED.updated_at, NEW_OR_UPDATED.date_created)

适配不支持 IS NOT DISTINCT FROM 语法的引擎

将匹配条件替换为等值判断+NULL同时存在的判断即可：

ON
  (
    (CURRENT_TABLE.id = NEW_OR_UPDATED.id OR (CURRENT_TABLE.id IS NULL AND NEW_OR_UPDATED.id IS NULL))
    AND (CURRENT_TABLE.text_id = NEW_OR_UPDATED.text_id OR (CURRENT_TABLE.text_id IS NULL AND NEW_OR_UPDATED.text_id IS NULL))
    AND (CURRENT_TABLE.last_sent = NEW_OR_UPDATED.last_sent OR (CURRENT_TABLE.last_sent IS NULL AND NEW_OR_UPDATED.last_sent IS NULL))
    AND (CURRENT_TABLE.recent_sent = NEW_OR_UPDATED.recent_sent OR (CURRENT_TABLE.recent_sent IS NULL AND NEW_OR_UPDATED.recent_sent IS NULL))
  )

前置验证建议 #

执行MERGE前可以先运行以下查询，确认匹配行数是否符合预期，避免误插入全量数据：

SELECT COUNT(*) AS 匹配行数
FROM `project.map.communicated` c
JOIN `project.map.communicated_tmp_` t
ON
  -- 替换为上面你使用的匹配条件
  (c.id IS NOT DISTINCT FROM t.id
    AND c.text_id IS NOT DISTINCT FROM t.text_id
    AND c.last_sent IS NOT DISTINCT FROM t.last_sent
    AND c.recent_sent IS NOT DISTINCT FROM t.recent_sent)

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