Hive中使用JOIN时自动过滤掉为NULL的数据

 

最近工作写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 = '2021-05-14';
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage