Hive SQL连接查询全解析：join与union的用法、坑与优化

做数据开发这行，写 Hive SQL 基本每天都要跟表连接打交道。不管是数仓分层加工、用户标签计算，还是临时取数，最绕不开的语法就是inner join、left join、full join以及union all、union。很多人刚开始学的时候，觉得不就是几个关键字嘛，真到了线上跑任务，发现结果多了一倍、少了数据、任务还一直卡住，基本都是因为这些连接的细节没吃透。这篇东西不聊理论摆谱，就按我实际写 HiveQL 的常用套路，把连接查询的语法、场景、坑和优化一次讲清楚，适合刚入门的朋友，也适合写过不少但偶尔被连接搞懵的同学。

1. 连接查询的整体思路：先想清楚要“拼”还是“叠”

1.1 连接（join）和集合（union）的本质区别

我习惯把 Hive 里的数据操作分成两类：一类是“横向拼字段”，一类是“纵向加行”。

连接查询（join）是横向拼字段。比如订单表里只有user_id，没有用户姓名，那我就用user_id去用户表里把姓名、年龄、城市这些字段带过来。拼完之后，列变多了，行数一般是根据关联关系变化的。

集合操作（union all / union）是纵向加行。比如一张表存了昨天的订单，另一张表存了今天的订单，两个表的结构一模一样，我想把它们上下合并成一个全量订单表。合并之后，行变多了，列数不变。

这个本质区别如果没搞清楚，后面写出来的 SQL 会非常别扭。有人把union all当成“另一种 join”来用，结果列怎么都对不上，其实一开始方向就错了。

1.2 关联键的选择决定结果正确性

做连接查询时，关联键（join key）就是表与表之间的“匹配依据”。比如a.user_id = b.uid，这里的user_id和uid就是关联键。选择关联键时，我通常先问自己三个问题：

• 这个键能不能唯一标识一行？如果两边都不唯一，结果就会膨胀。
• 这个键两边类型是否一致？string和bigint做比较，有时隐式转换会杀掉索引或导致结果异常。
• 这个键是否有空值？空值在 join 里通常匹配不上，会导致丢数据。

很多人写 SQL 从不看数据分布，上来就join，结果跑出来行数对不上。其实在写之前跑几条group by看看关联键的重复情况，可以省出大量排查时间。

1.3 Hive 连接查询与传统关系型数据库的差异

如果你之前玩的是 MySQL、PostgreSQL 这类数据库，刚写 Hive SQL 时会觉得语法很像，但底层逻辑差别很大。

Hive 本质上是一个数据仓库工具，它把 SQL 翻译成分布式计算任务，数据是按文件分块存在 HDFS 上的。因此，join 的时候不是数据库那种索引驱动的小规模匹配，而是会触发 Shuffle，数据会按照关联键重新分区发送到不同节点上再匹配。这也是为什么连接查询在 Hive 里更容易遇到数据倾斜——某个关联键的数据量特别大，所有数据都往一个节点上跑，这个节点就会变成瓶颈。

另外，Hive 对不等值连接（比如<、>）的支持不如传统数据库那么优雅，能不用尽量不用。实际开发中，我用得最多的是等值连接，也就是on a.id = b.id。

明白了这些差异，再来逐个看各种连接方式，思路会顺很多。

2. inner join：只留两边都有的数据

2.1 基本语法和运行逻辑

inner join翻译成“内连接”或者“等值连接”，逻辑很简单：取两个表的交集，只保留关联键在两个表中都能匹配上的行。

select a.order_id, a.user_id, b.user_name, b.city from temp.orders a inner join temp.users b on a.user_id = b.user_id;

这段 SQL 的结果是：订单表中所有能在用户表里找到对应用户的订单记录，才会出现在结果里。用户表里没有那些用户，订单数据不会显示；用户表里多出来的用户，因为没有任何订单，也不会显示。

inner关键字可写可不写，直接写join默认就是 inner join。但我建议在正式代码里写上inner join，明确表达意图，别人看你的代码时不用猜。

2.2 典型场景与示例

我常用inner join的场景是“过滤加补字段一起做”。

比如统计有效订单的用户分布，前提是订单必须能匹配到用户。如果匹配不到，说明用户数据可能有问题，或者订单是测试单，这类数据你不想留在结果里。这时候inner join是最合适的，因为在连接过程中就把没匹配上的行过滤掉了。

再比如，数仓里常见的“拉宽表”：事实表关联维度表。事实表是订单流水，维度表是商品信息，我只需要保留商品维度存在的数据，因为商品信息都不存在的订单，后续统计分析没有意义。此时用 inner join 即可。

2.3 使用 inner join 时最容易踩的坑

• 关联键重复导致行数爆炸。这是最常见的坑。假设订单表里同一个user_id有 3 条订单，用户表里同一个user_id有 2 条记录，那么 join 之后会产生 3×2=6 条结果。你说离谱不离谱？所以在 join 之前，最好先确认关联键在两边是否唯一，或者想清楚重复之后结果是否还能接受。
• 关联键为 null。Hive 中两个 null 做等值比较，结果是“未知”，不是相等。所以订单表里user_id为 null 的记录，永远匹配不上用户表里的 null。这会导致“明明两张表都有 null，但 join 之后就是没有”。如果你确实想用 null 做关联，需要特殊处理，比如用nvl(user_id, '未知')把 null 替换成一个普通字符串。
• 先过滤再接还是先接再过滤。理论上可以先用子查询把两边都过滤干净再做 join，能减少 Shuffle 的数据量。实际我一般在内层 where 过滤，再 join，效率会好不少。

3. left join：左表为尊，右表只负责补字段

3.1 基本语法与数据保留规则

left join应该是日常用得最多的连接方式。它的核心规则是：左表的所有行都要出现在结果里，右表只负责补充字段。如果左表的行在右表中找不到匹配，右表字段就用 null 填充。

select a.order_id, a.user_id, b.user_name, b.city from temp.orders a left join temp.users b on a.user_id = b.user_id;

这条 SQL 的结果是：订单表里所有订单都保留，不管有没有匹配到用户。用户表里有人没订单？没关系，结果里看不到他们。这就是 left join 和 inner join 最大的区别：left join 不会因为右表缺数据而丢左表行。

我写取数 SQL 时特别喜欢 left join，因为主表是哪个，哪些数据要全保留，一目了然。如果业务上“订单必须全部列出来，用户信息能补就补”，那必然选 left join。

3.2 on 条件里过滤右表和在 where 里过滤的差异

这个点太关键了，很多线上事故就是这么出的。

看这两段 SQL：

-- 写法一：在 on 里过滤右表 select a.order_id, b.user_name from temp.orders a left join temp.users b on a.user_id = b.user_id and b.city = '上海'; -- 写法二：在 where 里过滤右表 select a.order_id, b.user_name from temp.orders a left join temp.users b on a.user_id = b.user_id where b.city = '上海';

写法一的意思是：我拿订单关联用户，但关联的时候只关联“上海”的用户。不在上海的用户，相当于匹配不到，b.user_name会是 null，但订单行仍然保留。

写法二的意思是：先做 left join，得到所有订单和其用户信息，然后再用 where 过滤，只保留用户城市为“上海”的行。这样一来，非上海用户的订单会被整个过滤掉，左表不再是“全保留”了。

菜鸟经常在这上面栽跟头。如果你想让左表全部保留，右表的过滤条件必须放在 on 里；如果你确实只想要满足条件的明细，那放在 where 里也没问题，但你得意识到它其实是先 join 后过滤，效果已经接近 inner join。

3.3 实战案例：如何用 left join 做“无则补零”

统计每天每个用户的订单金额，但要求没有订单的用户也要出现，金额置为 0。这种需求很常见，比如用户活跃度报表。

select a.user_id, nvl(b.total_amount, 0) as total_amount from temp.users a left join ( select user_id, sum(amount) as total_amount from temp.orders where dt = '2025-01-01' group by user_id ) b on a.user_id = b.user_id;

这里用户表为主表，所有用户都保留。有订单的用户能关联到聚合后的金额，没订单的用户total_amount是 null，再用nvl转成 0。

这种“以一侧全量为主，另一侧做统计后补充”的模式，在数仓里非常普遍。需要注意，右表先做了group by再 join，能避免一对多导致行数爆炸。经验之谈：能用子查询预先聚合的，不要直接 join 明细表。

3.4 左表重复和右表重复的连锁反应

left join 同样会遇到重复问题。左表重复，结果会跟着重复；右表重复，左表每一行会和右表多个匹配行分别组合，结果行数膨胀。

举个例子，订单表左关联用户表，订单表其实每个订单只对应一个用户，但用户表因为某种原因有两条相同user_id的记录，那么每个订单就会出两条结果。这比 inner join 的重复更隐蔽，因为左表数据一条不少，只是莫名其妙变多了。

所以你在跑完 left join 后，建议用count(*)和左表行数对比一下。如果结果行数比左表多很多，八成是右表关联键重复。排查方法很简单：对右表关联键做group by having count(*) > 1，看看重复项有多少。

4. full join：左右都想留，谁也不丢

4.1 语法与实现原理

full join在 Hive 里也写成full outer join，它的规则是：左表和右表的行都完整保留。左表在右表没有匹配，右表字段为 null；右表在左表没有匹配，左表字段为 null。两边都对不上？结果里会分别出现两行，一边是“左表有右表无”，另一边是“右表有左表无”。

select a.user_id, a.order_cnt as left_cnt, b.user_id, b.browse_cnt as right_cnt from temp.orders a full join temp.user_browse b on a.user_id = b.user_id;

实现上，full join 通常会把两端数据都做一次分发，然后做连接，最后把两边未匹配的行补全。所以在分布式环境里，full join 的 Shuffle 数据量一般比 left join 大，对资源消耗也更高。能用 left join 解决问题时，我不会优先选 full join。

4.2 适用场景：两张表互为补集

我习惯用 full join 做“数据对账”和“补数”。

比如订单主题表和用户行为表，都是从不同源头加工出来的，可能存在部分用户只出现在其中一张表里。这时候想看看两张表各自哪些用户缺失，直接 full join 一下，再根据两边字段是否为 null 判断归属即可。

select coalesce(a.user_id, b.user_id) as user_id, case when a.user_id is not null and b.user_id is null then 'only_in_orders' when a.user_id is null and b.user_id is not null then 'only_in_browse' else 'both' end as tag from temp.orders a full join temp.user_browse b on a.user_id = b.user_id;

运行结果会清楚地把数据分成“只在订单表”“只在行为表”“两边都有”三类。这种写法在临时排查数据质量问题时非常管用。

另外，做“补数”任务时会用 full join：两套并行计算的数据各自有部分缺失，把两边的数据都合起来，缺失的字段互相补上。比如 A 表有 1 号、2 号的用户数据，B 表有 2 号、3 号的用户数据，full join 后就能得到 1、2、3 号的全量数据。

4.3 和 left join + union all 的方案对比

full join 很有用，但它不是不可替代的。当你想保留两边全量，但又想分别控制两边的过滤逻辑时，也可以拆成left join + union all来做，效果等价。

-- left join 部分：保留左表全部 select a.user_id, a.field_a, b.field_b from temp.tb_a a left join temp.tb_b b on a.user_id = b.user_id union all -- 右表有但左表没有的部分 select b.user_id, cast(null as string) as field_a, b.field_b from temp.tb_b b left join temp.tb_a a on b.user_id = a.user_id where a.user_id is null;

这种做法比较繁琐，但好处是可以对每一半的过滤条件单独控制，也更容易在不同引擎上优化。如果 Hive 的 full join 出现资源问题，或者你的 Hive 版本对 full join 支持不够稳定，可以退而求其次用这个方案。

4.4 使用 full join 的注意事项

• full join 结果里可能会有“行为主键为 null”的情况。最好用coalesce(a.id, b.id)重新生成一个统一主键，方便下游使用。
• 两边关联键都重复时，full join 的膨胀效果是乘法级的，写之前一定要测一下重复度。
• full join 和 where 配合时也要警惕：你在 where 里加了对单侧字段的过滤，很可能把另一侧补出来的行删掉，导致 full join 失效。比如你写where a.field > 0，那右表独有的行因为 a 字段是 null 就被过滤了，结果不再“全量”。
• Hive 的 full join 在部分执行引擎下可能出现分发不均匀的情况，我一般会在跑大表前先小范围验证一把，再跑全量。

5. union 与 union all：纵向拼接的正确姿势

5.1 集合操作和连接操作的本质区别

再强调一遍：union 系列是“上下拼行”，不是“左右拼列”。你需要合并的几张表，列数、列顺序和列类型要一致，否则跑起来不是报错就是数据错位。

比如：

select col1, col2 from temp.tb_a union all select col1, col2 from temp.tb_b;

这里只是把 tb_a 和 tb_b 的行依次放到一起。如果第一个 select 查了两列，第二个 select 查了三列，Hive 会直接报错。

这样设计的原因不难理解：纵向合并，相当于把一个一个文件里的结构相同的记录追加进同一个结果集。列不一样，就无法定义每一列到底该放什么。

5.2 两者的区别与性能差异

union all和union的区别只有一个：去重。

• union all：把两个结果集直接拼接，不去重，有多少行就返回多少行。
• union：拼接之后对全部结果做一次去重，逻辑上相当于union distinct。

性能差异也很明显。union all只是文件的简单追加，基本不需要额外的全局去重操作；union则要把所有列都当作去重键，做一次全局 Shuffle。所以绝大多数场景，能用union all就不要用union。你要是没有去重需求，却写了 union，等于白白增加了一轮分布式运行时间。

还有一种情况：你其实想去重，但关联键里包含很多字段、数据量又大，用 union 会很慢。我一般会先union all合并，再在合并结果上做group by或者row_number()去重，虽然多了一步，但可控性更好。

5.3 实战：多分区表汇总与业务表合并

最典型的场景是“多天分区合并”。比如数仓里误删了某个分区，需要从备份表把多天的数据恢复到一张表里：

insert overwrite table temp.orders_full partition (dt) select order_id, user_id, dt from temp.orders_20241201 where dt = '20241201' union all select order_id, user_id, dt from temp.orders_20241202 where dt = '20241202' union all select order_id, user_id, dt from temp.orders_20241203 where dt = '20241203';

这里必须用union all，因为是不同分区的数据，理论上不会有重复主键，没必要去重；用 union 反而会白白跑一次全局去重，浪费时间。

另一个场景是“多业务模块统一出口”。比如好几个业务线要汇总成一张用户标签大表，每张表的用户可能重复，但标签口径不同。如果你只想要用户集合，可以考虑 union 去重；如果后面还要知道每个用户来自哪条业务线，就用 union all，并且人工加一个来源字段。

5.4 常见坑：列顺序、类型转换、排序失效

写 union 系列时我栽过几个跟头，这里列一下：

• 列顺序不一致。第一段 select 的 a 列是 user_id，第二段 select 的 a 列变成了 user_name，语法不报错，但结果完全错位。解决办法是每一段 select 都显式列出字段，最好保持完全一致的顺序。
• 数据类型不一致。一个表里该字段是bigint，另一个表里是string，Hive 有时会隐式转换，有时会报错。稳妥做法是在 select 里手动cast。
• 对 union 结果做 order by。很多人写成：

select col1 from temp.tb_a union all select col1 from temp.tb_b order by col1;

这样排序是会对最终结果生效的。但要注意，order by必须放在最后一个 select 之后，不能放在每段 select 里，否则每段分别排序会被 union 打乱。

• 两边字段里有临时别名。比如第一段select order_id as id，第二段select order_id，Hive 看字段名不同，可能会把结果列名改成第一个 select 的别名。建议每一段都保持相同别名，避免下游取数时列名对不上。
• union 不能保证最终数据顺序。哪怕每段都 order by，union all 之后顺序也可能变化，要用外部查询再排序。

6. 连接查询的调优与故障排查

6.1 数据倾斜：关联键集中导致的任务跑不动

写 join 最头疼的就是跑着跑着卡住，某个 Reduce 任务要跑几个小时，其他 Reduce 早就结束了。这大概率是数据倾斜。

数据倾斜的典型场景是关联键里有“热点值”。比如订单表里大量订单的user_id是空字符串或者一个特殊的测试值，用户表里根本没有这些用户，但在 join 时，Hive 还是会把这些空值分到同一个节点去匹配，导致这个节点负载巨大。

我一般这么做：

• 先查关联键分布：select user_id, count(*) from temp.orders group by user_id order by count(*) desc limit 20。看看是不是有几个键特别多。
• 如果热点值是 null 或空串，干脆在 join 前过滤掉，或者把 null 替换成随机值，分摊到不同节点。
• 如果是正常业务热点，比如“超级大用户”的数据量特别大，可以把它拆出来单独处理，再用union all合回结果。

6.2 MapJoin：小表与大表的连接优化

Hive 里有一个很实用的优化：MapJoin。当 join 的右边是一张小表时，可以让它在 Map 阶段直接加载进内存，在 map 端完成匹配，这样就不需要 Shuffle，速度会快很多。

一般 Hive 会自动判断是否启用 MapJoin，但如果小表比较大或某个参数设置不对，自动优化可能没触发。我遇到小表关联大表时，会显式加 hint：

select /*+ mapjoin(b) */ a.col1, b.col2 from temp.big_table a left join temp.small_table b on a.id = b.id;

注意，MapJoin 要求小表能放进内存，我通常控制在几十 MB 到几百 MB 级别。如果小表有几 GB，硬塞会导致 OOM。这种时候还是老老实实跑普通 join，或者把小表先过滤聚合得足够小再说。

6.3 null 关联键导致的丢数据

前面提到 Hive 等值比较时 null 不相等。如果你用 null 关联，结果两边都出不来。实际业务里，user_id有可能是 null，或者被清洗成了空字符串。

应对方法看需求：

• 如果 null 用户不需要参与统计，就在 join 前过滤掉：where user_id is not null。
• 如果 null 用户需要保留在左表，left join 时右表匹配不上就行，左表自动保留，右表字段为 null。
• 如果两张表的 null 你希望彼此“视作相等”，需要手动转换，比如on nvl(a.user_id, '-1') = nvl(b.user_id, '-1')。但要注意，转换之后 null 可能集中到‘-1’上，容易造成数据倾斜，需要配合随机打散技巧。

6.4 常见问题速查表

这张表我写 SQL 时会时常回看，排查一次，胜读文档十遍。

最后再分享一点个人体会

连接查询用多了之后，你会发现真正难的不是语法，而是对数据形态的判断。我每次写 join 前会先问：主表是谁？关联键有没有重复？右表会不会带过来多行？空值怎么处理？这几个问题想清楚了，SQL 一般不会有太大问题。

很多新手上来就喜欢用union distinct或者 full join，觉得“全要”更安全。但实际运行下来，union all和 left join 往往更快、更可控。线上任务稳定第一，能少一次大 Shuffle 就少一次。

如果你正在做数仓报表或者临时取数，建议把本文里的例子都自己跑一遍，不要只看不练。特别是on和where的过滤位置差异，自己造点数据跑一跑，印象绝对比看十篇文章都深。数据开发这条路，靠的就是一个个坑踩出来，然后把坑变成自己的经验。