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

于 2022-11-26 10:10:17 发布
阅读量2.2k 收藏 3
点赞数 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

浪尖聊大数据-浪尖
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值