先给个结论:

总结:Hive SQL 中 LEFT JOIN 单独针对左表的过滤条件必须放在WHERE上,放在ON上的效果是不可预期的,单独针对右表的查询条件放在ON上是先过滤右表,再和左表联表,放在WHERE条件上则是先联表再过滤,语义上存在差别。

补充:对于MYSQL中的LEFT JOIN和Hive中表现相似,更深层次分析可以看看 https://developer.aliyun.com/article/718897


----------------

现象

​left join​​​在我们使用mysql查询的过程中可谓非常常见,比如博客里一篇文章有多少条评论、商城里一个货物有多少评论、一条评论有多少个赞等等。但是由于对​​join​​​、​​on​​​、​​where​​等关键字的不熟悉,有时候会导致查询结果与预期不符,所以今天我就来总结一下,一起避坑。

这里我先给出一个场景,并抛出两个问题,如果你都能答对那这篇文章就不用看了。

假设有一个班级管理应用,有一个表classes,存了所有的班级;有一个表students,存了所有的学生,具体数据如下(感谢廖雪峰的​​在线SQL​​):

​SELECT * FROM classes;​

id    name
1 一班
2 二班
3 三班
4 四班

​SELECT * FROM students;​

id  class_id  name   gender
1 1 小明 M
2 1 小红 F
3 1 小军 M
4 1 小米 F
5 2 小白 F
6 2 小兵 M
7 2 小林 M
8 3 小新 F
9 3 小王 M
10 3 小丽 F

那么现在有两个需求:

  1. 找出每个班级的名称及其对应的女同学数量
  2. 找出一班的同学总数

对于需求1,大多数人不假思索就能想出如下两种sql写法,请问哪种是对的?

SELECT c.name, count(s.name) as num 
FROM classes c left join students s
on s.class_id = c.id
and s.gender = 'F'
group by c.name

或者

SELECT c.name, count(s.name) as num 
FROM classes c left join students s
on s.class_id = c.id
where s.gender = 'F'
group by c.name

对于需求2,大多数人也可以不假思索的想出如下两种sql写法,请问哪种是对的?

SELECT c.name, count(s.name) as num 
FROM classes c left join students s
on s.class_id = c.id
where c.name = '一班'
group by c.name

或者

SELECT c.name, count(s.name) as num 
FROM classes c left join students s
on s.class_id = c.id
and c.name = '一班'
group by c.name

请不要继续往下翻 !!先给出你自己的答案,正确答案就在下面。

.

.

.

.

.

.

.

.

答案是两个需求都是第一条语句是正确的,要搞清楚这个问题,就得明白mysql对于​​left join​​的执行原理,下节进行展开。

根源

mysql 对于​​left join​​的采用类似嵌套循环的方式来进行从处理,以下面的语句为例:

SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)

其中​​P1​​​是​​on​​​过滤条件,缺失则认为是​​TRUE​​​,​​P2​​​是​​where​​​过滤条件,缺失也认为是​​TRUE​​,该语句的执行逻辑可以描述为:

FOR each row lt in LT {// 遍历左表的每一行
BOOL b = FALSE;
FOR each row rt in RT such that P1(lt, rt) {// 遍历右表每一行,找到满足join条件的行
IF P2(lt, rt) {//满足 where 过滤条件
t:=lt||rt;//合并行,输出该行
}
b=TRUE;// lt在RT中有对应的行
}
IF (!b) { // 遍历完RT,发现lt在RT中没有有对应的行,则尝试用null补一行
IF P2(lt,NULL) {// 补上null后满足 where 过滤条件
t:=lt||NULL; // 输出lt和null补上的行
}
}
}

当然,实际情况中MySQL会使用buffer的方式进行优化,减少行比较次数,不过这不影响关键的执行流程,不在本文讨论范围之内。

从这个伪代码中,我们可以看出两点:

  1. 如果想对右表进行限制,则一定要在​​on​​​条件中进行,若在​​where​​​中进行则可能导致数据缺失,导致左表在右表中无匹配行的行在最终结果中不出现,违背了我们对​​left join​​​的理解。因为对左表无右表匹配行的行而言,遍历右表后​​b=FALSE​​​,所以会尝试用​​NULL​​​补齐右表,但是此时我们的​​P2​​​对右表行进行了限制,NULL若不满足​​P2​​​(​​NULL​​​一般都不会满足限制条件,除非​​IS NULL​​这种),则不会加入最终的结果中,导致结果缺失。
  2. 如果没有​​where​​​条件,无论​​on​​​条件对左表进行怎样的限制,左表的每一行都至少会有一行的合成结果,对左表行而言,若右表若没有对应的行,则右表遍历结束后​​b=FALSE​​​,会用一行​​NULL​​来生成数据,而这个数据是多余的。所以对左表进行过滤必须用where。

下面展开两个需求的错误语句的执行结果和错误原因:

需求1

name    num
一班 2
二班 1
三班 2

需求2

name    num
一班 4
二班 0
三班 0
四班 0
  1. 需求1由于在where条件中对右表限制,导致数据缺失(四班应该有个为​​0​​的结果)
  2. 需求2由于在on条件中对左表限制,导致数据多余(其他班的结果也出来了,还是错的)

总结

通过上面的问题现象和分析,可以得出了结论:在​​left join​​语句中,左表过滤必须放where条件中,右表过滤必须放on条件中,这样结果才能不多不少,刚刚好。

SQL 看似简单,其实也有很多细节原理在里面,一个小小的混淆就会造成结果与预期不符,所以平时要注意这些细节原理,避免关键时候出错。

-------------------------

我们通常的认知是联表查询时ON是作用于联表前(时)的过滤条件,WHERE是对联表后的数据的过滤条件,但hive sql查询有个特殊点需要关注,那就是对于left join中的单独针对左表的ON过滤条件有可能会被忽略,比如

SELECT DISTINCT a.dt FROM

wlxdw.dws_tuid_cheat_di a

LEFT JOIN

wlxdw.apps_tuid_device_map_ds b

ON a.tuid = b.tuid AND a.dt='2021-10-18' AND b.dt='2021-10-19'

1

2

3

4

5

查询结果

可以看到a.dt='2021-10-18’这个过滤条件并没有生效,有人可能觉得b.dt='2021-10-19’是不是也没有生效,把上述SQL稍作修改来验证一下

SELECT DISTINCT b.dt FROM

wlxdw.dws_tuid_cheat_di a

LEFT JOIN

wlxdw.apps_tuid_device_map_ds b

ON a.tuid = b.tuid AND a.dt='2021-10-18' AND b.dt='2021-10-19'

1

2

3

4

5


显而易见,右表上的ON条件 b.dt=xxxx 生效了,那如果想要左表的 a.dt=‘2021-10-18’ 生效,可以放在WHERE条件上

而如果把右表的b.dt=‘2021-10-19’ 放到WHERE条件,语义则变的不一样,由于WHERE是先联表再进行过滤,这时查询结果中b.dt就不存在值为NULL的情况了,LEFT JOIN 相当于变成了 INNER JOIN

从上面的结果可以看出ON和WHERE查询条件语义上的差别

再来看一组SQL对比


神奇的一幕出现了,此时ON中的过滤条件a.dt=‘2021-10-18’ 时而生效时而不生效

总结:Hive SQL 中 LEFT JOIN 单独针对左表的过滤条件必须放在WHERE上,放在ON上的效果是不可预期的,单独针对右表的查询条件放在ON上是先过滤右表,再和左表联表,放在WHERE条件上则是先联表再过滤,语义上存在差别。

补充:对于MYSQL中的LEFT JOIN和Hive中表现相似,更深层次分析可以看看 https://developer.aliyun.com/article/718897