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

最新推荐文章于 2024-01-06 14:19:36 发布

原创最新推荐文章于 2024-01-06 14:19:36 发布 · 2.5k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#hive #sql #mysql

特征工程与数据分析同时被 2 个专栏收录

31 篇文章

订阅专栏

大数据处理hadoop/spark

30 篇文章

订阅专栏

本文探讨了在SQL中使用LEFT JOIN ON和WHERE子句进行条件筛选的区别。LEFT JOIN ON允许在连接条件中指定筛选，即使左表记录不符合条件，也会显示为NULL。而将条件放在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.youkuaiyun.com/qq_20641565/article/details/52950087

https://blog.youkuaiyun.com/wangwangstone/article/details/112550095