最近工作写hive sql的时候发现了一个问题 left join和where一块用时,会出现null值数据丢失的问题
研究了一下,发现 where 写的位置不同 会有不同的结果
首先准备两张表 tableA 和 tableB,数据如下图
下面三个sql语句来来进行一下演示
SELECT
a.col1,
b.col2
FROM
tableA a
LEFT JOIN
tableB b ON a.id = b.id
WHERE
a.dt = '${bizdate}'
AND b.dt = '${bizdate}';
得出结果如下图
这种写法,left join 执行的效果会和 join 执行的效果一样,丢失为 null 的数据
SELECT
a.col1,
b.col2
FROM
tableA a
LEFT JOIN
tableB b ON a.id = b.id and b.dt = '${bizdate}'
WHERE a.dt = '${bizdate}';
这种方法不会丢失 主表 的数据
SELECT
a.col1,
b.col2
FROM
(SELECT id,col1 FROM tableA WHERE dt = '${bizdate}') a
LEFT JOIN
(SELECT id,col2 FROM tableB WHERE dt = '${bizdate}') b
ON a.id = b.id
第三种方法也不会丢失 主表 的数据
以上三种情况,第一种写法看着是left join,实际执行的是join,所以会丢失数据,使用的时候要注意
我们可以通过Explain语法,查看三句代码底层执行过程来进行原理的深究
hive> EXPLAIN
> SELECT
> a.col1,
> b.col2
> FROM
> tableA a
> LEFT JOIN
> tableB b ON a.id = b.id
> WHERE
> a.dt = '2021-05-14'
> AND b.dt