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

最新推荐文章于 2025-04-01 08:42:24 发布
最新推荐文章于 2025-04-01 08:42:24 发布
阅读量2.5k 收藏 5
点赞数 1
文章标签： hive 大数据 hadoop 数据仓库
原文链接：https://mp.weixin.qq.com/s?__biz=MzUyMDA4OTY3MQ==&mid=2247508830&idx=1&sn=d071d55ff81ee6449410ca2a2c8632f8&chksm=f9ed5a76ce9ad360644e6028e04db8c2ecf47403afedee3990792049cf936751e3734cfa7da5&scene=126&&sessionid=0
版权

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}
;

  1. 子查询中的{subquery_where_condition}

  2. JOIN的{on_condition}的条件

  3. 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表达式的行。

  • 情况1：过滤条件在子查询，即分别提前过滤要关联的两个表格数据，然后在根据ON条件进行关联。

    注意：这种方式是规范的方式，在实际的开发过程中，我们尽量都按这种方式开发，避免多表多条件关联时导致代码比较混乱。

    SELECT  a.*
        ,b.*
FROM    (
            SELECT  *
            FROM    a
            WHERE   ds = '20220101'
        ) a
JOIN    (
            SELECT  *
            FROM    b
            WHERE   ds = '20220101'
        ) b
ON      a.id = b.id
;

    结果如下:

    iddsid_1ds_1
    120220101120220101

  • 情况2：过滤条件在JOIN的关联条件中

    SELECT  a.*
        ,b.*
FROM    a
JOIN    b
ON      a.id = b.id
AND     a.ds = '20220101'
AND     b.ds = '20220101'

    笛卡尔积结果为9条，满足关联条件的结果只有1条，如下。

    结果如下:

    iddsid_1ds_1
    120220101120220101

  • 情况3：过滤条件在JOIN结果集的WHERE子句中。

    SELECT  a.*
        ,b.*
FROM    a
JOIN    b
ON      a.id = b.id
WHERE   a.ds = '20220101'
AND     b.ds = '20220101'
;

    先进行笛卡尔积，结果为9条，满足关联条件的结果有3条，如下。

    iddsid_1ds_1
    120220101120220101
    220220102220220102
    22022010122020102

    对上述结果执行JOIN结果集中的过滤条件WHERE a.ds = '20220101'AND b.ds = '20220101'，结果只有1条，如下。

    iddsid_1ds_1
    120220101120220101
结论

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

LEFT JOIN

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

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

示例说明

  • 情况1：过滤条件在子查询

    此方式是规范的写法，建议使用此种方式

    SELECT  a.*
        ,b.*
FROM    (
            SELECT  *
            FROM    a
            WHERE   ds = '20220101'
        ) a
LEFT JOIN    (
            SELECT  *
            FROM    b
            WHERE   ds = '20220101'
        ) b
ON      a.id = b.id
;

    结果如下。

    iddsid_1ds_1
    120220101120220101
    220220101NULLNULL

  • 情况2：过滤条件在JOIN的关联条件

    注意，注意，注意，注意，注意，注意，注意，注意，注意，注意，此处容易出问题

    SELECT  a.*
        ,b.*
FROM    a
LEFT JOIN    b
ON      a.id = b.id
AND     a.ds = '20220101'
AND     b.ds = '20220101'
;

    笛卡尔积的结果有9条，满足关联条件的结果只有1条。左表输出剩余不满足关联条件的两条记录，右表输出NULL。

    由于是LEFT JOIN 对于左表需要全表输出，最终的结果可能跟我们预期的不一致，这个就是LEFT JOIN的语义，在写SQL的时候一定要注意。

    iddsid_1ds_1
    120220101120220101
    220220101NULLNULL
    220220102NULLNULL

  • 情况3：过滤条件在JOIN结果集的WHERE子句中。

    SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

    笛卡尔积的结果为9条，满足ON条件的结果有3条。

    iddsid_1ds_1
    120220101120220101
    220220101220220102
    220220102220220102

    对上述结果执行JOIN结果集中的过滤条件a.ds='20220101' and b.ds='20220101'，结果只有1条。

    a.keya.dsb.keyb.ds
    120220101120220101
结论

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

RIGHT JOIN

参考LEFT JOIN

FULL JOIN

示例说明

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

  • 情况1：过滤条件在子查询，规范写法

    SELECT  a.*
        ,b.*
FROM    (
            SELECT  *
            FROM    a
            WHERE   ds = '20220101'
        ) a
FULL JOIN    (
            SELECT  *
            FROM    b
            WHERE   ds = '20220101'
        ) b
ON      a.id = b.id
;

    结果如下。

    iddsid_1ds_1
    120220101120220101
    220220101NULLNULL
    NULLNULL320220101

  • 情况2：过滤条件在JOIN的关联条件

    SELECT  a.*
        ,b.*
FROM    a
FULL JOIN    b
ON      a.id = b.id
AND     a.ds = '20220101'
AND     b.ds = '20220101'

    笛卡尔积的结果有9条，满足关联条件的结果只有1条。对于左表不满足关联条件的两条记录输出左表数据，右表输出NULL。对于右表不满足关联条件的两条记录输出右表数据，左表输出NULL。

    iddsid_1ds_1
    120220101120220101
    220220101NULLNULL
    220220102NULLNULL
    NULLNULL320220101
    NULLNULL220220102

  • 情况3：过滤条件在JOIN结果集的WHERE子句中。

    SELECT  a.*
        ,b.*
FROM    a
FULL JOIN    b
ON      a.id = b.id
WHERE   a.ds = '20220101'
AND     b.ds = '20220101'

    对于不满足关联条件的表输出数据，另一表输出NULL。

    iddsid_1ds_1
    120220101120220101
    220220101220220102
    220220102220220102
    NULLNULL320220101

    对上述结果执行JOIN结果集中的过滤条件a.ds='20220101' and b.ds='20220101'，结果只有1条。

    iddsid_1ds_1
    120220101120220101
结论

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

推荐写法

28b869e32ad4d195ff97cb35ef623412.png

总结

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

7e30761a9177a27f645ff9cf9ff1820e.png

HiveSQL中的JOIN ON条件
JanLEE
11-26 1853
HiveSQL很常用的一个操作就是关联(Join)。Hive为用户提供了多种JOIN类型，可以满足不同的使用场景。但是，对于不同JOIN类型的语义，或许有些人对此不太清晰。简单的问题，往往是细节问题，而这些问题恰恰也是重要的问题。本文将围绕不同的JOIN类型，介绍JOIN的语义，并对每种JOIN类型需要注意的问题进行剖析，希望本文对你有所帮助。
你真的了解HiveSql吗？真实的HiveSql执行顺序是长这样的
石榴姐yyds
09-01 2261
通过上面的研究我们发现在hivesql中其执行顺序大致如下： from->where(谓词下推)->join->on->select(select中的字段与group by只要不一致就会有)->group by->select(为having准备数据，因而having中可以使用select别名)->having->select(过滤后的结果集)->distinct->select->order by ->select->limit 实际上上述需要注意的是select在group by前和group by
SQL:JOIN ON
weixin_33912638的博客
01-21 5172
JOIN连接组合两个表中的字段记录，包括三种： INNER JOIN运算式：连接组合两个表中的字段记录。 LEFT JOIN运算式：连接组合两个表中的字段记录，并将包含了LEFT JOIN左边表中的全部记录。 RIGHT JOIN运算式：连接组合两个表中的字段记录，并将包含了RIGHT JOIN右边表中的全部记录。 INNER JOIN设定两个表相关连的运算式，以连接组合两个表中的字段记...
SQL Server JOIN ON语法详解
最新发布
啥都没有的博客简介
04-01 1103
JOIN操作是SQL Server处理关系型数据的核心功能，在医药流通行业中可有效整合分散的业务数据。通过合理选择连接类型、优化索引策略、结合行业场景，可显著提升数据查询效率和业务分析能力。建议定期分析执行计划，确保关键查询的响应时间控制在500ms以内。
hive join on的条件
zcc_0015的专栏
04-17 1501
如果是left join 在on上写主表a的条件不会生效，全表扫描。 如果是left join 在on上写副表b的条件会生效，但是语义与写到where 条件不同 如果是inner join 在on上写主表a、副表b的条件都会生效 建议这么写，大家写sql大部分的语义都是先过滤数据然后再join ，所以在不了解 join on + 条件的情况下，条件尽量别写道on 后，直接写到where厚就ok了 ...
hive join
weixin_30500473的博客
10-22 179
语法 join_table: table_reference JOIN table_factor [join_condition] | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition | table_reference LEFT SEMI JOIN table_...
SQL语法——left join on 多条件
热门推荐
minixuezhen的博客
03-30 36万+
left join on +多条件与where区别 重点 先匹配，再筛选where条件。 本文将通过几个例子说明两者的差别。 表1：product id amount 1 100 2 200 3 300 4 400 表2：product_details id weight exist ...
hive sql + left join 数据缺失
02-29
### Hive SQL Left Join 数据缺失问题解析 #### 一、问题背景 在大数据处理场景中，Hive 是一种广泛使用的工具，用于对存储在 Hadoop 文件系统中的数据进行数据汇总、查询和分析。随着Hive版本的不断更新，部分用户...
hivesql中使用join 关联表时where 和 on、join 的执行先后顺序
weixin_42903419的博客
05-05 1万+
环境：hive 0.13.1版本 select t1.cust_pty_no ,t2.amt from a t1 left join b t2 on t1.cust_pty_no = t2.cust_pty_no and t2.busi_date='2020-04-17' where t1.busi_date='2020-04-17' 使用expalin 查看执行计划如下： 看红色圈住部分，n...
Hive SQL之FULL JOIN优化
胜利的曙光的博客
03-16 4596
记一次工作中SQL的FULL JOIN优化过程。
MySQL SQL 多个Join on(表连接) 和Where间的执行顺序（nest loop join机制）
点滴之积
11-05 4万+
这篇博客写的不好，一些内容有误，最新的可以看 https://blog.youkuaiyun.com/qq_27529917/article/details/87904179， 里面的应该是值得看的
一张图轻松搞懂Hive中的join（内连接、左外连接、右外连接、满外连接）
weixin_43230682的博客
08-19 1万+
join语句 1 等值 join Hive支持通常的SQL JOIN语句，但是只支持等值连接，==不支持非等值连接==。 案例实操 select * from stu left join score on stu.id = score.s_id; 根据学生和成绩表，查询学生姓名对应的成绩 2 表的别名 好处 使用别名可以简化查询。 使用表名前缀可以提高执行效率。 案例实操：合并老师与课程表 -- hive当中创建course表并加载数据 create table ..
hive中的join
鸿爸爸
11-14 370
CREATE TABLE t_2(id int,skuid string,price float,amount int) partitioned by (day string,city string) row format delimited fields terminated by ‘,’; LOAD DATA LOCAL INPATH ‘/root/t2.1’ into TABLE t_2 P...
hive join 多条件on后，where后面怎么写
xuehuagongzi000的博客
05-20 5932
1、建表 （1）、 create external table if not exists t_xiaoming_test_for_dau( uid string , platform int , ...
Sql join
Mirror_w的博客
06-23 633
User表 CREATE TABLE `user` ( `id` varchar(255) NOT NULL DEFAULT '', `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Score表 CREATE TABLE `score` (...
sql语句联表查询之（join....on）
u014796999的博客
12-31 2772
表1： -- -- 表的结构 `bumen` -- CREATE TABLE IF NOT EXISTS `bumen` (   `bid` int(10) NOT NULL AUTO_INCREMENT,   `mingcheng` varchar(30) DEFAULT NULL,   `jingli` varchar(30) DEFAULT NULL,   `tel` v
sql join on 三个条件_有趣的SQL二：两个表没连接条件的JOIN
weixin_30621429的博客
01-22 2575
文章节选自松华老师《SQL开发与优化》专栏，想阅读更多内容请扫码订阅今天逛论坛看到了一个，奇葩需求，他想把两个表的结果连在一起显示我把需求整理如下 ：下面有两个表 a 和 t12 数据分别如下现在想要的结果是现在我们来分析一下，怎样满足上述需求1，如果我们想把不同的表中的列横向展示，那肯定只有JOIN或者标量子查询2，如果用了1的方案，那必须得有连接条件，不然会产生笛卡尔积，或者是空先...
HiveJoin语句
@小刘同学nice
05-25 904
等值Join Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。 案例实操 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称； hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno; ...
Hive SQL的各种join总结
biqidaoer的专栏
12-05 2299
join时的最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此，将大表放置在最后有助于减少reducer阶段缓存数据所需要的内存。包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行 在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用。左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回。（LEFT SEMI JOIN）会返回左边表的记录，前提是其记录对于右边的表满足ON语句中的判定条件。左指的是join关键字左边的表，简称左表。
hive sql left join 过程
01-10
### Hive SQL Left Join 使用教程 #### 左连接的基础概念 在Hive SQL中，`LEFT JOIN`用于返回左表中的所有记录以及右表中存在的匹配记录。如果右表中不存在匹配，则结果集中来自右表的列将包含NULL值[^1]。 #### `ON` 条件与 `WHERE` 条件的区别 当执行带有`LEFT JOIN`的操作时，在`ON`子句中指定的条件仅影响如何关联两表的数据行。而`WHERE`子句则是在完成联接之后进一步过滤最终的结果集。这意味着： - 当条件放置于`ON`语句内时，该条件只应用于决定哪些右边表格的行应该链接到左边表格上； - 若相同条件下移至`WHERE`部分，则会先做完整的外连接操作后再依据此条件删除不符合要求的整行数据[^2]。 #### 示例代码展示两种不同写法的效果差异 ```sql -- SQL 语句 1: ON 子句中含有额外条件的情况 SELECT t1.id, t2.id FROM t1 LEFT JOIN t2 ON t1.id = t2.id AND t2.id <> 2; -- SQL 语句 2: WHERE 子句中含有额外条件的情况 SELECT t1.id, t2.id FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id <> 2; ``` 在这两个例子中，第一个SQL语句会在`t2.id=2`的情况下保留`t1`的所有记录，并给`t2`对应的列为null；第二个SQL语句则不会显示任何`t2.id=2`的相关记录，即使存在这样的`t1`记录也会因为后续的`WHERE`过滤掉[^3]。 #### 执行过程解释 对于`LEFT JOIN`来说，其工作流程大致如下： 1. **读取左侧输入**：从左表（即`t1`）开始处理每一行作为当前行。 2. **查找右侧对应项**：尝试找到满足`JOIN ... ON`条件下的右表（即`t2`）里的相配对行。 3. **组合输出**：根据是否找到了符合条件的右表条目来构建新的联合后的行。如果没有找到合适的右表项目，则用NULL填充那些本应来自于右表的位置。 4. **应用附加筛选器**：如果有定义了`WHERE`子句或其他形式的后期过滤逻辑的话，此时会对之前得到的结果再次施加这些约束以获得最后想要看到的内容列表。 这种机制确保了无论何时都会至少保持住整个原始左表的信息完整性，同时允许灵活地加入更多关于另一侧参与者的特定信息或限制。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值