合并同结构Table1与Table2的SQL逻辑实现及优化咨询

需要对结构一致的Table1和Table2执行以下SQL处理逻辑：

• 合并两表：当Table1与Table2的ID1、ID2完全匹配时，仅保留Table1的数据；不匹配的行则保留各自的数据。
• 已知Table2的value字段均为NULL，若合并后结果表中某行value为NULL，且该行ID1在Table1中存在对应记录，则用Table1中该ID1对应的value值填充。

本人尝试了以下SQL语句，但无法完成去重及填充逻辑，且认为该方案效率较低，寻求优化指导：

SELECT ID1, ID2, value
FROM table1
UNION 
SELECT ID1, ID2, value
FROM table2

测试数据 #

Table1 #

Table2 #

期望输出 #

任务#1输出（仅合并去重） #

任务#2输出（合并+填充NULL） #

解决方案 #

任务1：合并去重（保留Table1匹配行） #

使用UNION ALL+NOT EXISTS替代UNION，避免不必要的去重排序开销，效率更高：

SELECT ID1, ID2, value
FROM table1
UNION ALL
SELECT t2.ID1, t2.ID2, t2.value
FROM table2 t2
WHERE NOT EXISTS (
    SELECT 1 FROM table1 t1
    WHERE t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2
);

优化点：UNION ALL不会对结果集去重排序，配合NOT EXISTS精准筛选Table2中与Table1无匹配的行，避免了UNION的额外性能消耗。

任务2：合并+填充NULL值 #

在任务1的基础上，提前预存每个ID1对应的填充值，再通过关联完成填充：

WITH combined_data AS (
    -- 先完成任务1的合并逻辑
    SELECT ID1, ID2, value
    FROM table1
    UNION ALL
    SELECT t2.ID1, t2.ID2, t2.value
    FROM table2 t2
    WHERE NOT EXISTS (
        SELECT 1 FROM table1 t1
        WHERE t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2
    )
),
id1_fill_map AS (
    -- 预存每个ID1对应的非NULL value（若同一ID1有多个value，可根据需求用MAX/MIN/AVG等）
    SELECT ID1, MAX(value) AS fill_value
    FROM table1
    GROUP BY ID1
)
SELECT 
    cd.ID1,
    cd.ID2,
    COALESCE(cd.value, ifm.fill_value) AS value
FROM combined_data cd
LEFT JOIN id1_fill_map ifm ON cd.ID1 = ifm.ID1;

简化版（无CTE）：

SELECT 
    COALESCE(t1.ID1, t2.ID1) AS ID1,
    COALESCE(t1.ID2, t2.ID2) AS ID2,
    COALESCE(t1.value, (SELECT MAX(value) FROM table1 WHERE ID1 = t2.ID1)) AS value
FROM table1 t1
FULL OUTER JOIN table2 t2 
    ON t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2;

优化点：通过预分组获取填充值，避免每行执行子查询；COALESCE函数快速优先取非NULL值，逻辑简洁高效。

通用效率建议 #

• 给Table1和Table2的(ID1, ID2)建立复合索引，大幅提升NOT EXISTS和关联查询的速度。
• 若同一ID1对应多个不同的value，需明确填充规则（如取最大值、平均值等），避免逻辑歧义。

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