关于 HiveSQL 常见的 Left Join 误区,你知道吗

写在前面

很多时候,由于SQL逻辑复杂,加之对SQL执行逻辑理解不透彻,很容易产生一些莫名其妙的结果,这些结果看似不符合预期,殊不知这就是真实结果。本文整理了几个常见的SQL问题,我们在实际书写SQL脚本时,需要多加注意,希望本文对你有所帮助。

关于LEFT JOIN

外连接是我们书写SQL时经常使用的多表连接方式,使用起来也是十分的简单。值得注意的是,越是简单的东西,越是容易被忽略细节。通常我们都是这样理解LEFT
JOIN的:

语义是满足Join on条件的直接返回,但不满足情况下,需要返回Left Outer Join的left 表所有列,同时右表的列全部填null

上述对于LEFT JOIN的理解是没有任何问题的,但是里面有一个误区:谓词下推。具体看下面的实例:

假设有如下的三张表:

--建表

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;

当我们执行如下的SQL查询时,会返回什么数据呢?

SELECT *

FROM t1

LEFT JOIN t2

ON t1.id = t2.id

AND t1.ds = 20220120

;

结果1:

1 2022 20220120 1 120 20220120

结果2:

1 2022 20220120 1 120 20220120

2 2022 20220121 NULL NULL NULL

1 2022 20220122 NULL NULL NULL

相信对于很多初学者,甚至是一个有开发经验的人来说,会认为结果1是正确的返回结果。其实结果1的并不是正确的结果,真正的返回值是结果2.

关于 HiveSQL 常见的 Left Join 误区,你知道吗插图亿华云

是不是跟预期的结果不一致呢?很多初学者会认为上述查询SQL中AND t1.ds =
20220120会进行谓词下推,从而得到结果2。其实,SQL本身的语义不是这样的,如果需要获取结果1的数据,正确的查询方式是下面这样:

--方式1:

SELECT *

FROM t1

LEFT OUTER JOIN t2

ON t1.id = t2.id

WHERE t1.ds = 20220120

;

--方式2:

SELECT *

FROM (

SELECT *

FROM t1

WHERE ds = 20220120

) t1

LEFT OUTER JOIN t2

ON t1.id = t2.id

;

细心的你看出差异了吗?重点是在WHERE t1.ds = 20220120过滤条件上,最上面的查询方式是ON t1.ds =
20220120,所以按照LEFT JOIN的语义,如果没有过滤条件,那么左表的数据应该全部返回,右表匹配不上则补null。

执行计划

我们先来看看没有谓词下推的查询SQL的执行计划

正常LEFT JOIN

查看执行计划

EXPLAIN

SELECT *

FROM t1

LEFT JOIN t2

ON t1.id = t2.id

AND t1.ds = 20220120

;

执行计划结果

hive

THE END
Copyright © 2024 亿华云