Hive SQL查询left join下on条件和where条件区别

我们通常的认知是联表查询时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'

查询结果

可以看到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'

显而易见，右表上的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