Hadoop Impala中合并两表保留TABLE2全量及TABLE1差集的SQL问题

原代码问题分析 #

• 关联表书写错误：你代码里LEFT JOIN后面跟的还是TABLE1，没有正确关联目标表TABLE2
• 关联条件逻辑错误：用TABLE1.id <> TABLE2.id作为JOIN条件会产生笛卡尔积，完全无法筛选出TABLE1中未匹配TABLE2的记录。正确的左连接找无匹配记录的逻辑是用相等关联，再通过右表主键为NULL过滤
• 缺少TABLE2全量记录的拼接逻辑：你现有逻辑即使修正JOIN也只能拿到TABLE1独有的记录，没有包含TABLE2的全量数据

正确实现方案 #

默认两张表id为唯一主键，id、name、surname字段类型一致：

方案1：UNION ALL写法（Impala中性能更优，逻辑直观） #

String sq = "SELECT id, name, surname FROM TABLE2 " +
            "UNION ALL " +
            "SELECT t1.id, t1.name, t1.surname FROM TABLE1 t1 " +
            "LEFT JOIN TABLE2 t2 ON t1.id = t2.id " +
            "WHERE t2.id IS NULL";

如果id是全局唯一不会重复，用UNION ALL比UNION省去了去重开销，性能更好。

方案2：FULL OUTER JOIN写法（适合TABLE2同主键记录需要优先覆盖TABLE1的场景） #

如果存在TABLE2和TABLE1同id但其他字段值不同，需要优先保留TABLE2字段值的情况，可以用全外连接加COALESCE函数实现：

String sq = "SELECT " +
            "  COALESCE(t2.id, t1.id) AS id, " +
            "  COALESCE(t2.name, t1.name) AS name, " +
            "  COALESCE(t2.surname, t1.surname) AS surname " +
            "FROM TABLE1 t1 " +
            "FULL OUTER JOIN TABLE2 t2 ON t1.id = t2.id";

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