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