如何基于Snowflake表的Id字段更新行并获取无重复的合并查询结果

解法1：使用FULL OUTER JOIN（最简洁，适合字段较少的场景） #

你可以通过全外连接加COALESCE函数实现需求，Id匹配时优先取Table2的字段值，未匹配时保留两张表各自的独有行：

SELECT
  COALESCE(t2.Id, t1.Id) AS Id,
  COALESCE(t2.name, t1.name) AS name,
  COALESCE(t2.number, t1.number) AS number,
  COALESCE(t2.value, t1.value) AS value
FROM Table1 t1
FULL OUTER JOIN Table2 t2
  ON t1.Id = t2.Id
ORDER BY Id;

解法2：使用UNION ALL + 窗口函数（扩展性更强，适合字段多、后续可能调整优先级的场景） #

给两张表的行设置优先级（Table2优先级高于Table1），同一个Id下只保留优先级最高的行即可：

SELECT Id, name, number, value
FROM (
  SELECT 
    *,
    ROW_NUMBER() OVER (
      PARTITION BY Id 
      ORDER BY priority
    ) AS rn
  FROM (
    SELECT *, 2 AS priority FROM Table1
    UNION ALL
    SELECT *, 1 AS priority FROM Table2
  ) AS combined_data
) AS filtered_data
WHERE rn = 1
ORDER BY Id;

两种写法在Snowflake环境下都可以直接运行，输出结果和你给出的预期完全一致。

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