如何使用FULL JOIN合并两表并从另一表填充NULL字段？

需求说明 #

对两张数据表执行FULL JOIN操作，Table2中存在大量可从Table1获取的缺失值：

• 当Column1、Column2、Column3的值完全匹配时，合并两表数据并追加Table2的信息
• 用Table1中的值填充Table2维度字段的NULL值

数据表结构 #

TABLE1 #

TABLE2 #

期望结果 #

解决方案（SQL实现） #

以下SQL通过预处理填充Table2的缺失维度值，再执行FULL JOIN完成数据合并，兼容大多数关系型数据库：

WITH table2_filled AS (
    SELECT 
        t2.Column1,
        -- 用Table1中同Column1的非空Column2值填充Table2的空值
        COALESCE(t2.Column2, t1_fill.Column2) AS Column2,
        t2.Column3,
        t2.measure1,
        t2.measure2
    FROM Table2 t2
    LEFT JOIN (
        -- 提取Table1中各Column1对应的非空Column2值（假设同Column1对应唯一Column2）
        SELECT DISTINCT Column1, Column2 
        FROM Table1 
        WHERE Column2 IS NOT NULL
    ) t1_fill ON t2.Column1 = t1_fill.Column1
)
SELECT 
    COALESCE(t1.Column1, t2_filled.Column1) AS Column1,
    COALESCE(t1.Column2, t2_filled.Column2) AS Column2,
    COALESCE(t1.Column3, t2_filled.Column3) AS Column3,
    -- 优先取Table1的measure1，无值则用Table2的
    COALESCE(t1.measure1, t2_filled.measure1) AS measure1,
    -- 优先取Table2的measure2，无值则用Table1的（匹配需求中的追加逻辑）
    COALESCE(t2_filled.measure2, t1.measure2) AS measure2
FROM Table1 t1
FULL JOIN table2_filled t2_filled 
    ON t1.Column1 = t2_filled.Column1
    AND t1.Column2 = t2_filled.Column2
    AND t1.Column3 = t2_filled.Column3
ORDER BY Column3;

逻辑说明 #

1. 预处理Table2：通过CTE table2_filled，用Table1中相同Column1的非空Column2值填充Table2里的Column2空值（如例子中DAY3行的Column2被填充为B）
2. FULL JOIN合并：将预处理后的Table2与Table1按三个维度字段匹配，合并后用COALESCE函数取非空值完成字段合并
3. 排序：最终结果按Column3排序，与期望结果一致

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