HiveSQL中的JOIN ON条件，你理解对了吗？

HiveSQL很常用的一个操作就是关联(Join)。Hive为用户提供了多种JOIN类型，可以满足不同的使用场景。但是，对于不同JOIN类型的语义，或许有些人对此不太清晰。简单的问题，往往是细节问题，而这些问题恰恰也是重要的问题。本文将围绕不同的JOIN类型，介绍JOIN的语义，并对每种JOIN类型需要注意的问题进行剖析，希望本文对你有所帮助。

JOIN类型 类型含义

Inner Join

输出符合关联条件的数据

Left Join

输出左表的所有记录，对于右表符合关联的数据，输出右表，没有符合的，右表补null

Right Join

输出右表的所有记录，对于左表符合关联的数据，输出左表，没有符合的，左表补null

Full Join

输出左表和右表的所有记录，对于没有关联上的数据，未关联的另一侧补null

Left Semi Join

对于左表中的一条数据，如果右表存在符合关联条件的行，则输出左表

Left Anti Join

对于左表中的一条数据，如果对于右表所有的行，不存在符合关联条件的数据，则输出左表

JOIN的通用格式

SELECT?
????????a.*?
????????,b.*?
FROM?
????????(
????????????????SELECT?*?
????????????????FROM?a?
????????????????WHERE?{subquery_where_condition}?
????????)?a?
{LEFT/RIGHT/FULL/LEFT?SEMI/LEFT?ANTI}?JOIN?
????????(
????????????????SELECT?*?
????????????????FROM?b
????????????????WHERE?{subquery_where_condition}?
????????)?b?
ON?{on_condition}?
WHERE?{where_condition}
;

子查询中的{subquery_where_condition}

JOIN的{on_condition}的条件

JOIN结果集合{where_condition}的计算

尖叫提示：

对于不同的JOIN类型，过滤语句放在{subquery_where_condition}、{on_condition}和{where_condition}中，有时结果是一致的，有时候结果又是不一致的。下面分情况进行讨论：

数据准备

create?table?a(id?int,ds?string);
insert?into?table?a?VALUES?(1,?20220101),(2,?20220101),(2,?20220102);
create?table?b(id?int,ds?string);
insert?into?table?b?VALUES?(1,?20220101),(3,?20220101),(2,?20220102)?;

set?hive.mapred.mode?=?'nonstrict';
set?hive.strict.checks.cartesian.product?=?'false';
select?*?from?a?join?b;
1?20220101?1?20220101
2?20220101?1?20220101
2?20220102?1?20220101
1?20220101?3?20220101
2?20220101?3?20220101
2?20220102?3?20220101
1?20220101?2?20220102
2?20220101?2?20220102
2?20220102?2?20220102

场景说明 INNER JOIN 示例说明

INNER JOIN对左右表执行笛卡尔乘积，然后输出满足ON表达式的行。

结论

过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}中时，查询结果是一致的。INNER JOIN比较特殊，由于只匹配能关联上的数据，所以无论过滤条件怎么写，最终的结果都是一致的。即便是这样，在实际的开发过程中建议使用情况1的方式进行书写，避免不必要的问题出现。

LEFT JOIN

LEFT JOIN对左右表执行笛卡尔乘积，输出满足ON表达式的行。对于左表中不满足ON表达式的行，输出左表，右表输出NULL。

注意：输出满足ON表达式的行，输出满足ON表达式的行理解大数据，输出满足ON表达式的行，只是ON条件，不是WHERE条件，此处最容易出问题

示例说明 结论

过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}中时，查询结果不一致。牢记LEFT JOIN的语义，对于左表中不满足ON表达式的行，输出左表，右表输出NULL

RIGHT JOIN

参考LEFT JOIN

FULL JOIN 示例说明

FULL JOIN对左右表执行笛卡尔乘积，然后输出满足关联条件的行。对于左右表中不满足关联条件的行，输出有数据表的行，无数据的表输出NULL。

结论

过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}时，查询结果不一致。

推荐写法

总结

本文主要结合具体的使用示例，对HiveSQL的LEFT JOIN操作进行了详细解释。主要包括两种比较常见的LEFT JOIN方式，一种是正常的LEFT JOIN，也就是只包含ON条件，这种情况没有过滤操作，即左表的数据会全部返回。另一种方式是有谓词下推，即关联的时候使用了WHERE条件，这个时候会会对数据进行过滤。所以在写SQL的时候，尤其需要注意这些细节问题，以免出现意想不到的错误结果。

（编辑：成都站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!