Hive/Impala中合并两表并去重ID的最快SQL方法咨询

针对你需要生成包含两表所有无重复Id记录的Table3需求，下面逐个分析三种方案的适用性和性能：

1. 直接排除CROSS JOIN #

CROSS JOIN会生成两个表的笛卡尔积，比如Table1有N条记录、Table2有M条记录，结果会有N×M条数据，完全不符合"无重复Id"的要求，直接排除，无需考虑性能。

2. Full OUTER JOIN：最优最快的选择 #

这是最贴合需求的标准语法，数据库优化器对这类JOIN的优化逻辑非常成熟，尤其是当Id字段存在主键或索引时，性能表现最佳。

实现代码 #

SELECT
    COALESCE(t1.Id, t2.Id) AS Id,
    t1.C1,
    t2.C2
FROM Table1 t1
FULL OUTER JOIN Table2 t2 
    ON t1.Id = t2.Id;

性能分析 #

• 若Id有索引，数据库会快速定位匹配的记录，避免全表扫描；
• 执行计划通常采用高效的嵌套循环连接或哈希连接（取决于数据量规模），无需额外的聚合或去重操作，开销最小。

3. UNION ALL + 聚合：性能逊于Full OUTER JOIN #

这种方法需要先通过UNION ALL合并多组数据，再通过聚合函数（如MAX）合并同一Id的字段值，写法繁琐且性能通常不如前者。

实现代码（两种常见写法） #

写法1：拆分三类记录后合并聚合

SELECT
    Id,
    MAX(C1) AS C1,
    MAX(C2) AS C2
FROM (
    -- 两表共有的记录
    SELECT t1.Id, t1.C1, t2.C2
    FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.Id = t2.Id
    UNION ALL
    -- Table1独有的记录
    SELECT Id, C1, NULL AS C2
    FROM Table1 t1
    WHERE NOT EXISTS (SELECT 1 FROM Table2 t2 WHERE t2.Id = t1.Id)
    UNION ALL
    -- Table2独有的记录
    SELECT Id, NULL AS C1, C2
    FROM Table2 t2
    WHERE NOT EXISTS (SELECT 1 FROM Table1 t1 WHERE t1.Id = t2.Id)
) AS combined
GROUP BY Id;

写法2：直接合并所有记录后聚合

SELECT
    Id,
    MAX(C1) AS C1,
    MAX(C2) AS C2
FROM (
    SELECT Id, C1, NULL AS C2 FROM Table1
    UNION ALL
    SELECT Id, NULL AS C1, C2 FROM Table2
) AS combined
GROUP BY Id;

性能分析 #

• 需要先扫描两个表1~3次，生成临时结果集后再执行GROUP BY聚合；
• 聚合操作会带来额外的计算开销，尤其是数据量较大时，性能明显低于Full OUTER JOIN；
• 仅在极少数数据库对Full OUTER JOIN优化不佳的场景下，可能有接近的表现，但不推荐作为首选。

总结 #

Full OUTER JOIN是满足需求的最佳最快方案，它直接匹配业务逻辑，数据库优化器能生成最高效的执行计划；UNION ALL+聚合写法冗余且性能更差；CROSS JOIN完全不符合需求，直接排除。

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