PostgreSQL如何基于ref_id合并表行 替代union all拼接操作？

方案1：全外连接+COALESCE取值（推荐） #

适合两张表ref_id一一对应的场景，执行效率更高：

SELECT
  COALESCE(t1.old_user, t2.old_user) AS old_user,
  COALESCE(t1.new_user, t2.new_user) AS new_user,
  COALESCE(t1.old_address, t2.old_address) AS old_address,
  COALESCE(t1.new_address, t2.new_address) AS new_address,
  COALESCE(t1.Ref_ID, t2.Ref_ID) AS ref_id
FROM dummy_table1 t1
FULL OUTER JOIN dummy_table2 t2
ON t1.Ref_ID = t2.Ref_ID;

说明：COALESCE函数会返回传入参数中第一个非空的值，FULL OUTER JOIN会保留两张表所有的ref_id记录，不会遗漏仅在单表存在的关联ID。

方案2：UNION ALL+分组聚合 #

适合多表合并、或同个ref_id在单表存在多条记录的场景：

SELECT
  MAX(old_user) AS old_user,
  MAX(new_user) AS new_user,
  MAX(old_address) AS old_address,
  MAX(new_address) AS new_address,
  Ref_ID AS ref_id
FROM (
  SELECT * FROM dummy_table1
  UNION ALL
  SELECT * FROM dummy_table2
) AS combined_table
GROUP BY Ref_ID;

说明：因为同ref_id下每个字段最多只有一个非空值，使用MAX/MIN聚合时会自动忽略NULL值，最终提取到唯一的非空字段内容。

以上两种方案均可输出你预期的合并结果。

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