HiveQL中left join误区和多join的执行顺序

已于 2022-08-21 11:22:52 修改
阅读量3.5k 收藏 12
点赞数 3
分类专栏： Spark/Hive 文章标签： 数据仓库 hive
于 2022-08-21 11:21:12 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33446500/article/details/126448817
版权

文章目录

1. join中的on条件和where条件的区别

--建表
create table t1(id int, value int) partitioned by (ds string);
create table t2(id int, value int) partitioned by (ds string);
create table t3(c1 int, c2 int, c3 int);

--数据装载，t1表
insert overwrite table t1 partition(ds='20220120') select '1','2022';
insert overwrite table t1 partition(ds='20220121') select '2','2022';
insert overwrite table t1 partition(ds='20220122') select '2','2022';

--数据装载，t2表
insert overwrite table t2 partition(ds='20220120') select '1','120';
insert overwrite table t2 partition(ds='20220121') select '1','120';
insert into table t2 partition(ds='20220121') select '3','120';

--数据装载，t3表
insert into table t3 select '1','33','33';
insert into table t3 select '1','34','33';
insert into table t3 select '3','33','33';
insert into table t3 select '4','33','33';

1.1. left join

SQL案例

SELECT  *
FROM t1
LEFT JOIN t2
ON t1.id = t2.id 
where t1.ds = '20220120';
-- 执行结果为
+--------+-----------+-----------+--------+-----------+-----------+
| t1.id  | t1.value  |   t1.ds   | t2.id  | t2.value  |   t2.ds   |
+--------+-----------+-----------+--------+-----------+-----------+
| 1      | 2022      | 20220120  | 1      | 120       | 20220120  |
| 1      | 2022      | 20220120  | 1      | 120       | 20220121  |
+--------+-----------+-----------+--------+-----------+-----------+

对于上述执行结果相信并没有任何的问题，实际执行的结果和我们的预期是完全一致的。

SELECT  *
FROM t1
LEFT JOIN t2
ON t1.id = t2.id AND t1.ds = '20220120';
-- 执行结果为，t1.ds = '20220120'条件未生效
+--------+-----------+-----------+--------+-----------+-----------+
| t1.id  | t1.value  |   t1.ds   | t2.id  | t2.value  |   t2.ds   |
+--------+-----------+-----------+--------+-----------+-----------+
| 1      | 2022      | 20220120  | 1      | 120       | 20220120  |
| 1      | 2022      | 20220120  | 1      | 120       | 20220121  |
| 2      | 2022      | 20220121  | NULL   | NULL      | NULL      |
| 2      | 2022      | 20220122  | NULL   | NULL      | NULL      |
+--------+-----------+-----------+--------+-----------+-----------+

执行结果中返回了t1表中的全部数据(是不是和预期的结果并不一样)，从结果看 join 条件中的 AND t1.ds = '20220120' 并没有“生效”，为什么这样？

而如下所示，在on条件中对t2表进行筛选 t2.ds = '20220120'，则返回结果中过滤了指定的数据。

SELECT  *
FROM t1
LEFT JOIN t2
ON t1.id = t2.id AND t2.ds = '20220120';
-- 执行结果为，t2.ds = '20220120'条件生效
+--------+-----------+-----------+--------+-----------+-----------+
| t1.id  | t1.value  |   t1.ds   | t2.id  | t2.value  |   t2.ds   |
+--------+-----------+-----------+--------+-----------+-----------+
| 1      | 2022      | 20220120  | 1      | 120       | 20220120  |
| 2      | 2022      | 20220121  | NULL   | NULL      | NULL      |
| 2      | 2022      | 20220122  | NULL   | NULL      | NULL      |
+--------+-----------+-----------+--------+-----------+-----------+

分别查看上述三个SQL的执行计划

SELECT  *
FROM t1
LEFT JOIN t2
ON t1.id = t2.id 
where t1.ds = '20220120';

== Physical Plan ==
*(2) BroadcastHashJoin [id#24], [id#27], LeftOuter, BuildRight
:- *(2) FileScan orc zhanglei.t1[id#24,value#25,ds#26] Batched: true, Format: ORC, Location: PrunedInMemoryFileIndex[hdfs://bdptest/warehouse/tablespace/managed/hive/zhanglei.db/t1/ds=20220120], PartitionCount: 1, PartitionFilters: [isnotnull(ds#26), (ds#26 = 20220120)], PushedFilters: [], ReadSchema: struct<id:int,value:int>
+- BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)))
   +- *(1) Project [id#27, value#28, ds#29]
      +- *(1) Filter isnotnull(id#27)
         +- *(1) FileScan orc zhanglei.t2[id#27,value#28,ds#29] Batched: true, Format: ORC, Location: CatalogFileIndex[hdfs://bdptest/warehouse/tablespace/managed/hive/zhanglei.db/t2], PartitionCount: 2, PartitionFilters: [], PushedFilters: [IsNotNull(id)], ReadSchema: struct<id:int,value:int>

从上述执行计划中得知where条件中t1.ds = '20220120'生效，体现在对表t1的读取上只读取了ds=20220120分区。

SELECT  *
FROM t1
LEFT JOIN t2
ON t1.id = t2.id AND t1.ds = '20220120';

== Physical Plan ==
*(2) BroadcastHashJoin [id#32], [id#35], LeftOuter, BuildRight, (ds#34 = 20220120)
:- *(2) FileScan orc zhanglei.t1[id#32,value#33,ds#34] Batched: true, Format: ORC, Location: CatalogFileIndex[hdfs://bdptest/warehouse/tablespace/managed/hive/zhanglei.db/t1], PartitionCount: 3, PartitionFilters: [], PushedFilters: [], ReadSchema: struct<id:int,value:int>
+- BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)))
   +- *(1) Project [id#35, value#36, ds#37]
      +- *(1) Filter isnotnull(id#35)
         +- *(1) FileScan orc zhanglei.t2[id#35,value#36,ds#37] Batched: true, Format: ORC, Location: CatalogFileIndex[hdfs://bdptest/warehouse/tablespace/managed/hive/zhanglei.db/t2], PartitionCount: 2, PartitionFilters: [], PushedFilters: [IsNotNull(id)], ReadSchema: struct<id:int,value:int>

从上述执行计划中得知on条件中t1.ds = '20220120'并没有生效，因为并没有过滤条件，仅仅在BroadcastHashJoin中进行了体现。

SELECT  *
FROM t1
LEFT JOIN t2
ON t1.id = t2.id AND t2.ds = '20220120';

== Physical Plan ==
*(2) BroadcastHashJoin [id#40], [id#43], LeftOuter, BuildRight
:- *(2) FileScan orc zhanglei.t1[id#40,value#41,ds#42] Batched: true, Format: ORC, Location: CatalogFileIndex[hdfs://bdptest/warehouse/tablespace/managed/hive/zhanglei.db/t1], PartitionCount: 3, PartitionFilters: [], PushedFilters: [], ReadSchema: struct<id:int,value:int>
+- BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, true] as bigint)))
   +- *(1) Project [id#43, value#44, ds#45]
      +- *(1) Filter isnotnull(id#43)
         +- *(1) FileScan orc zhanglei.t2[id#43,value#44,ds#45] Batched: true, Format: ORC, Location: PrunedInMemoryFileIndex[hdfs://bdptest/warehouse/tablespace/managed/hive/zhanglei.db/t2/ds=20220120], PartitionCount: 1, PartitionFilters: [isnotnull(ds#45), (ds#45 = 20220120)], PushedFilters: [IsNotNull(id)], ReadSchema: struct<id:int,value:int>

从上述执行计划中得知where条件中t2.ds = '20220120'生效，体现在对表t2的读取上只读取了ds=20220120分区。

1.2. right join

SELECT  *
FROM t1
RIGHT JOIN t2
ON t1.id = t2.id AND t1.ds = '20220121';
-- 执行结果，t1.ds = '20220121'的条件生效
+--------+-----------+--------+--------+-----------+-----------+
| t1.id  | t1.value  | t1.ds  | t2.id  | t2.value  |   t2.ds   |
+--------+-----------+--------+--------+-----------+-----------+
| NULL   | NULL      | NULL   | 1      | 120       | 20220120  |
| NULL   | NULL      | NULL   | 1      | 120       | 20220121  |
| NULL   | NULL      | NULL   | 3      | 120       | 20220121  |
+--------+-----------+--------+--------+-----------+-----------+


SELECT  *
FROM t1
RIGHT JOIN t2
ON t1.id = t2.id AND t2.ds = '20220120';
-- 执行结果为，t2.ds = '20220120' 的条件未生效
+--------+-----------+-----------+--------+-----------+-----------+
| t1.id  | t1.value  |   t1.ds   | t2.id  | t2.value  |   t2.ds   |
+--------+-----------+-----------+--------+-----------+-----------+
| 1      | 2022      | 20220120  | 1      | 120       | 20220120  |
| NULL   | NULL      | NULL      | 1      | 120       | 20220121  |
| NULL   | NULL      | NULL      | 3      | 120       | 20220121  |
+--------+-----------+-----------+--------+-----------+-----------+

!!! note “”
从上述执行结果中看，left join的on中t1(左表)的条件并不会生效，但是t2(右表)的条件会生效。right join的on中(右表)的条件并不会生效，但是左表的条件会生效。

问题：在left join的on条件中为什么左表的筛选条件不会生效，而右表的筛选条件会生效？？？

1.3. inner join

SELECT  *
FROM t1
JOIN t2
ON t1.id = t2.id 
where t1.ds = '20220120';
--
SELECT  *
FROM t1
JOIN t2
ON t1.id = t2.id AND t1.ds = '20220120';
-- 以上两个SQL执行结果相同
+--------+-----------+-----------+--------+-----------+-----------+
| t1.id  | t1.value  |   t1.ds   | t2.id  | t2.value  |   t2.ds   |
+--------+-----------+-----------+--------+-----------+-----------+
| 1      | 2022      | 20220120  | 1      | 120       | 20220120  |
| 1      | 2022      | 20220120  | 1      | 120       | 20220121  |
+--------+-----------+-----------+--------+-----------+-----------+

在inner join中 on 和 where 条件中的条件都会正常生效。

SELECT  *
FROM t1
JOIN t2
ON t1.id = t2.id AND t2.ds = '20220120';
-- 执行结果如下
+--------+-----------+-----------+--------+-----------+-----------+
| t1.id  | t1.value  |   t1.ds   | t2.id  | t2.value  |   t2.ds   |
+--------+-----------+-----------+--------+-----------+-----------+
| 1      | 2022      | 20220120  | 1      | 120       | 20220120  |
+--------+-----------+-----------+--------+-----------+-----------+

!!! note “”
在inner join中on中的条件无论左右表的条件都会生效。

2. 多个left join执行顺序

继续给t2和t3表中插入测试数据

insert into table t2 partition(ds='20220121') select '3','120';

insert into table t3 select '1','33','33';
insert into table t3 select '1','34','33';
insert into table t3 select '3','33','33';
insert into table t3 select '4','33','33';

案例SQL

SELECT  *
FROM t1
LEFT JOIN t2
ON t1.id = t2.id
LEFT JOIN t3
ON t2.id = c1;
-- 执行结果为
+--------+-----------+-----------+--------+-----------+-----------+--------+--------+--------+
| t1.id  | t1.value  |   t1.ds   | t2.id  | t2.value  |   t2.ds   | t3.c1  | t3.c2  | t3.c3  |
+--------+-----------+-----------+--------+-----------+-----------+--------+--------+--------+
| 1      | 2022      | 20220120  | 1      | 120       | 20220120  | 1      | 33     | 33     |
| 1      | 2022      | 20220120  | 1      | 120       | 20220120  | 1      | 34     | 33     |
| 1      | 2022      | 20220120  | 1      | 120       | 20220121  | 1      | 33     | 33     |
| 1      | 2022      | 20220120  | 1      | 120       | 20220121  | 1      | 34     | 33     |
| 2      | 2022      | 20220122  | NULL   | NULL      | NULL      | NULL   | NULL   | NULL   |
| 2      | 2022      | 20220121  | NULL   | NULL      | NULL      | NULL   | NULL   | NULL   |
+--------+-----------+-----------+--------+-----------+-----------+--------+--------+--------+

从结果得知，多个表进行left join时，是按照join顺序进行的。即先由t1和t2表left join形成一个虚拟表后，再和t3表进行left join成最终结果。

3. 同时有inner join和left join时的执行顺序和结果

SELECT  *
FROM t2
JOIN t3
ON t2.id = t3.c1;
-- 执行结果如下
+--------+-----------+-----------+--------+--------+--------+
| t2.id  | t2.value  |   t2.ds   | t3.c1  | t3.c2  | t3.c3  |
+--------+-----------+-----------+--------+--------+--------+
| 1      | 120       | 20220121  | 1      | 33     | 33     |
| 1      | 120       | 20220120  | 1      | 33     | 33     |
| 1      | 120       | 20220121  | 1      | 34     | 33     |
| 1      | 120       | 20220120  | 1      | 34     | 33     |
| 3      | 120       | 20220121  | 3      | 33     | 33     |
+--------+-----------+-----------+--------+--------+--------+

SELECT  *
FROM t1
left join t2
on t1.id = t2.id
JOIN t3
ON t2.id = t3.c1;
-- 执行结果如下
+--------+-----------+-----------+--------+-----------+-----------+--------+--------+--------+
| t1.id  | t1.value  |   t1.ds   | t2.id  | t2.value  |   t2.ds   | t3.c1  | t3.c2  | t3.c3  |
+--------+-----------+-----------+--------+-----------+-----------+--------+--------+--------+
| 1      | 2022      | 20220120  | 1      | 120       | 20220120  | 1      | 33     | 33     |
| 1      | 2022      | 20220120  | 1      | 120       | 20220121  | 1      | 33     | 33     |
| 1      | 2022      | 20220120  | 1      | 120       | 20220120  | 1      | 34     | 33     |
| 1      | 2022      | 20220120  | 1      | 120       | 20220121  | 1      | 34     | 33     |
+--------+-----------+-----------+--------+-----------+-----------+--------+--------+--------+

SELECT  *
FROM t2
JOIN t3
ON t2.id = t3.c1
left join t1
on t1.id = t2.id;
-- 执行结果如下
+--------+-----------+-----------+--------+--------+--------+--------+-----------+-----------+
| t2.id  | t2.value  |   t2.ds   | t3.c1  | t3.c2  | t3.c3  | t1.id  | t1.value  |   t1.ds   |
+--------+-----------+-----------+--------+--------+--------+--------+-----------+-----------+
| 1      | 120       | 20220120  | 1      | 33     | 33     | 1      | 2022      | 20220120  |
| 1      | 120       | 20220121  | 1      | 33     | 33     | 1      | 2022      | 20220120  |
| 1      | 120       | 20220120  | 1      | 34     | 33     | 1      | 2022      | 20220120  |
| 1      | 120       | 20220121  | 1      | 34     | 33     | 1      | 2022      | 20220120  |
| 3      | 120       | 20220121  | 3      | 33     | 33     | NULL   | NULL      | NULL      |
+--------+-----------+-----------+--------+--------+--------+--------+-----------+-----------+

SELECT  *
FROM t2
JOIN t3
ON t2.id = t3.c1
right join t1
on t1.id = t2.id;
-- 执行结果如下
+--------+-----------+-----------+--------+--------+--------+--------+-----------+-----------+
| t2.id  | t2.value  |   t2.ds   | t3.c1  | t3.c2  | t3.c3  | t1.id  | t1.value  |   t1.ds   |
+--------+-----------+-----------+--------+--------+--------+--------+-----------+-----------+
| 1      | 120       | 20220120  | 1      | 33     | 33     | 1      | 2022      | 20220120  |
| 1      | 120       | 20220120  | 1      | 34     | 33     | 1      | 2022      | 20220120  |
| 1      | 120       | 20220121  | 1      | 33     | 33     | 1      | 2022      | 20220120  |
| 1      | 120       | 20220121  | 1      | 34     | 33     | 1      | 2022      | 20220120  |
| NULL   | NULL      | NULL      | NULL   | NULL   | NULL   | 2      | 2022      | 20220121  |
| NULL   | NULL      | NULL      | NULL   | NULL   | NULL   | 2      | 2022      | 20220122  |
+--------+-----------+-----------+--------+--------+--------+--------+-----------+-----------+

4. SQL中存在多个join时的join执行顺序和结果

从上述的示例中可以得知，当SQL中存在多个join(无论join类型))时，执行时按照join的前后顺序，前两个表join出一个虚拟的表，再和第三个表进行join，依次往后执行。

有数的编程笔记
关注
  • 3
    点赞
  • 踩
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hive sql + left join 数据缺失
02-29
hive sql + left join 数据缺失
SQL中的left join right join
12-15
以下是它们的共同点： 1. 关于左右表的概念。左表指的是在SQL语句中排在left join左边的表，右表指的是排在left join右边的表。 2. 在拼成的大表中，左表排在左边，右表排在右边。 3. on条件语句最好用=号对两表相应的主外键进行连接。当然，也可以用其他操作符，如>, <, 来连接两表的任一字段，此时的关系将非常复杂，连接后的记录数也随之而变得不确定。如果在一些特殊的场合中需要用到这种方式，必须通过简单的实例加以确认，否则，连接结果很可能不是我们所想要的！ 4. on条件语句不能省略。 5. 可以连锁使用join，每次使用join都令另一表与当前的表或连接的结果相连接。 在下
如何同时使用多个join——多个连续的left join连接顺序验证
最新发布
kennyharris
10-18 2434
在实际工作中，很多情况下会用到外连接，尤其是做主页面的数据展示查询时，连续用到多个left join会很常见，这样的多个left join会怎么样执行呢，之前在网上查到的执行顺序是，顺序的从坐到右的连接表，例如有 a,b,c三张表的数据都需要查到，就需要进行连接查询，如 select * from a left join b on a.id = b.id left join a.id = c.id，这时是怎么顺序进行执行的呢？
hiveSQL基本语句三-----连接查询--inner join、left join、full join、union all、union
抱富的博客
02-08 6765
一、(inner) join 连接2个表，取出公共部分 必须重命名 on后连接条件键值唯一 连接前注意去重，提高效率 inner可省略 select * from user_list_1 as a inner join user_list_2 as b on a.user_id=b.user_id; 二、left /right join ...
LEFT JOIN的细节
qq_36180997的博客
12-29 99
概念 left join 左连接以左表为主表，连接右表，如果右表为空，也能正常连接。假设主表有十条数据，那么得到的结果最少有十条。 sql SELECT df1.tradeNo, t.tradeNo, t.* FROM distribute_friend df1 LEFT JOIN trade t on df1.tradeNo=t.tradeNO 结果 分析 id和activity可以为空在理解范围之内，但是tradeNo(1)为什么也为空？在sql里面，LEFT JOIN trade t on
join语句的执行顺序
qq_42640067的博客
08-19 5999
前言 通常我们写的一个完整的SQL语句都可以拆分成多个子句，子句的执行过程通常会产生虚拟表。什么是虚拟表呢？虚拟表，英文为virtual table，简称为vt。顾名思义，就是实际上并不存在，至少在物理上是不存在的，但是在逻辑上却是存在的表。在MySQL中，有三种虚拟表：临时表、内存表和视图，这里不作详细的介绍，有兴趣的话可以自行查阅资料。回到正题，一条完整的SQL语句在执行的过程中会被拆分为多个SQL子句，每个子句执行时都会产生虚拟表vt，但是最终结果只会返回最后一个虚拟表。从这个思路入手，来理解一下jo
多个left join怎么执行
zhaojk0815的博客
11-08 3135
@[ 转载 多个left join 怎么执行? 2015-10-13 14:46:51 九洲江 阅读数 3152收起 分类专栏： mySql 源址：http://bbs.csdn.net/topics/390741161 先说明2点： 1、left&...
多个left join on使用时的顺序问题
热门推荐
weixin_38972910的博客
10-31 3万+
版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/zkdceo/article/details/70244004 &nbsp; 连续多个left join on使用时候的顺序 在实际工作中，很多情况下会用到外连接，尤其是做主页面的数据展示查询...
Inner Join 的使用和多条 Inner Join 的使用顺序要求
小羊的博客
06-01 5420
本文介绍了 MySQL 中 Inner Join 的使用方法，以及在连接多个表时需要注意的顺序。同时，我们提供了一个使用 Inner Join 的例子，展示如何在两个表之间进行连接，并输出查询结果。希望这篇文章对您有所帮助。
left join on 和where条件的放置以及多个left join连接的执行顺序
一个搬砖工人
10-24 6865
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。1 中间表 on条件: t1.size=t2.size and t2.name=‘AAA’ (条件不为真也会返回左表中的记录)2 再对中间表过滤 where 条件：where t2.name=‘AAA’1 中间表 on条件: t1.size=t2.size。顺序是先a,b组合成一个虚拟表，然后虚拟表再和C表关联。
oracle中left join和right join的区别浅谈
09-10
oracle中left join和right join的区别浅谈，需要的朋友可以参考一下
sql left join 命令详解
09-11
左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
sql中left join的效率分析与提高效率方法
09-09
网站随着数据量与访问量越来越大，访问的速度变的越来越慢，于是开始想办法解决优化速度慢的原因，下面是对程序中一条sql的分析与提高效率的过程
mysql多个left join连接查询用法分析
12-16
本文实例讲述了mysql多个left join连接查询用法。分享给大家供大家参考，具体如下： mysql查询时需要连接多个表时，比如查询订单的商品表，需要查询商品的其他信息，其他信息不在订单的商品表，需要连接其他库的表，但是连接的条件基本都是商品ID就可以了，先给一个错误语句（查询之间的嵌套，效率很低）： SELECT A.order_id, A.wid, A.work_name, A.supply_price, A.sell_price, A.total_num, A.sell_profit, A.sell_percent, A.goods_id,
hiveSql正则方式提取字段中的内容
初阳
10-24 4247
regexp_extract方法正则提取string中的内容
hive中collect_list和concat_ws函数对null值的处理
初阳
08-21 3810
hive中collect_list和concat_ws函数在处理null值（或包含）时的结果
Hive中分区数函数使用详情和误区
初阳
10-24 2783
percentile和percentile_approx函数使用详情和误区
SUM(A)+SUM(B)和SUM(A+B)不相等问题踩坑
初阳
12-14 2401
null值在聚合函数中的处理和影响
mybatis多表查询inner join和left join执行顺序
07-29
在mybatis多表查询中，inner join和left join的执行顺序是根据表的连接顺序来确定的。在使用mybatis-plus插件进行连表查询时，可以通过leftJoin、rightJoin和innerJoin方法来指定连接的表和连接条件。[1]根据引用[2]中的示例SQL语句，可以看出leftJoin和innerJoin是按照它们在查询语句中的顺序执行的。在这个例子中，先执行了leftJoin连接user表和user_address表，然后再执行了innerJoin连接user_address表和area表。因此，inner join和left join的执行顺序是根据它们在查询语句中的顺序来确定的。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值