SQL实现：按col1大小规则合并共享id的同结构两张表

实现思路 #

不用一开始就嵌套复杂的连接判断，先把两张表的所有id做全量去重集合，再关联回原始表逐列匹配规则取值，逻辑更清晰，也不容易出现冗余行。

• 全量id集合：通过UNION取两张表的id并集，自动覆盖“id仅存在于单表”的场景
• 逐行关联：把全量id集合分别左连接两张原始表，拿到每个id对应的两边数据
• 规则匹配：通过CASE语句逐列判断取值，完全对齐给定的4条合并规则

通用实现代码 #

WITH all_exist_ids AS (
    -- 取所有不重复的id，作为合并结果的主键
    SELECT id FROM table1
    UNION
    SELECT id FROM table2
)
SELECT
    id,
    -- 逐列按规则判断取值
    CASE
        WHEN t2.id IS NULL THEN t1.col1 -- id仅在table1存在，取table1值
        WHEN t1.id IS NULL THEN t2.col1 -- id仅在table2存在，取table2值
        WHEN t1.col1 >= t2.col1 THEN t1.col1 -- 两边都存在时，按col1大小选
        ELSE t2.col1
    END AS col1,
    CASE
        WHEN t2.id IS NULL THEN t1.col2
        WHEN t1.id IS NULL THEN t2.col2
        WHEN t1.col1 >= t2.col1 THEN t1.col2
        ELSE t2.col2
    END AS col2,
    CASE
        WHEN t2.id IS NULL THEN t1.col3
        WHEN t1.id IS NULL THEN t2.col3
        WHEN t1.col1 >= t2.col1 THEN t1.col3
        ELSE t2.col3
    END AS col3
FROM all_exist_ids
LEFT JOIN table1 t1 ON all_exist_ids.id = t1.id
LEFT JOIN table2 t2 ON all_exist_ids.id = t2.id

注意：如果表的列数较多，逐列写CASE会比较繁琐，在PostgreSQL、CockroachDB等支持行值引用的数据库中，可以简化为判断完需要取哪张表的整行后，直接输出对应行的所有列值，避免重复写CASE逻辑。这个逐列判断的写法兼容所有主流SQL数据库，没有语法依赖。

示例数据匹配验证 #

对照给出的样例数据：

• 重叠id A：table1.col1=3 >= table2.col1=2，输出table1的A行值
• 重叠id B：table1.col1=1 < table2.col1=5，输出table2的B行值
• 单表存在的id C：仅在table1出现，直接输出table1的C行值
• 单表存在的id D：仅在table2出现，直接输出table2的D行值
  最终输出结果和预期完全一致。

之前尝试方案的常见问题 #

• 直接用UNION/UNION ALL：是对整行做纵向拼接去重，不会基于col1的比较结果做行级筛选，很容易出现重叠id重复输出的问题
• 直接用FULL OUTER JOIN：如果没有提前处理单表存在的空值分支，很容易出现左侧/右侧数据不匹配的冗余行，CASE判断如果没覆盖单表存在的场景，会输出null值

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