left join on 筛选与where筛选的比较

join on后面也能进行条件筛选，where后面也能加条件进行筛选，但两者的结果是不一样的。下面通过实际数据案例来说明问题。

 

数据准备：

建表与写入数据

A 表

hive >create table tempTableAzw(id int,name string ,grade int ,dept int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|';
hive >load data local inpath '/data0/VR/Compare7AndTotal/tempTableAzw.txt' into table tempTableAzw;

tempTableAzw.txt文件形如：

1|lijie1|100|10
2|lijie2|90|20
3|lijie3|60|10
4|lijie4|80|10
5|lijie5|70|20

B表

create table tempTableBzw(id int  ,name string ) row format delimited fields terminated by "|";
load data local inpath "/data0/VR/Compare7AndTotal/tempTableBzw.txt" into table tempTableBzw; 

tempTableBzw.txt文件形如

10      IT1
20      IT2

join on 后面进行条件筛选

A表与B表进行left join 

select A .id ,A.name ,A.grade,A.dept,B.id,B.name
from tempTableAzw  A left outer join tempTableBzw  B
--  上面outer 其实可以省略
on A.dept =B.id   and A.grade >=80; 

结果为

注意上结果图中不符合on 后面的条件的行，B的值为NULL！  如果on 后面不带 and A.grade >=80 这个条件，结果为

结论： 当把过滤条件写在left join on 上面，会让基表所有数据都能显示，不满足的右表会以null 填充。

如果将上面的left join 改为join 则显示如下。同下面的where 结果一致。

 

筛选大于80的这个条件放到where 后面

代码

select A .id ,A.name ,A.grade,A.dept,B.id,B.name
from tempTableAzw  A left  join tempTableBzw  B
on A.dept =B.id   
where  A.grade >=80; 

结果：

 

结论： 

当把过滤条件写在where上，只会让符合筛选条件的数据显示。

 

鸣谢与参考：

https://blog.csdn.net/qq_20641565/article/details/52950087

https://blog.csdn.net/wangwangstone/article/details/112550095