Hive中 join on筛选条件 与 where筛选条件的区别

测试两张表结构:

create table px_data_test_temp.test_score(
	id int comment '学生id', 
	grade int comment '学生成绩'
) comment '学生成绩表';

create table px_data_test_temp.test_stu(
	id int comment '学生id', 
	name string comment '学生姓名'
) comment '学生信息表';

插入测试数据:

insert into table px_data_test_temp.test_score 
(id,grade) values 
(1, 78),
(2, 90),
(3, 80),
(4, 70),
(5, 85);


insert into table px_data_test_temp.test_stu 
(id,name) values 
(1, "xiaoming"),
(2, "xiaohei"),
(3, "xiaohong"),
(4, "xiaobai"),
(5, "xiaolv");

测试内容

• 1、两张left join且同时查询分数大于80的数据
• 2、两张表right join且查询分数大于80分的数据
• 3、两张join且同时查询分数大于80的数据
• 4、两张full join且同时查询分数大于80的数据

1、两张left join且同时查询分数大于80的数据

条件放到on上

select a.id, name, grade 
from 
px_data_test_temp.test_score a 
left join 
px_data_test_temp.test_stu b 
on a.id = b.id and a.grade > 80;

结果:

条件放到where上:

select a.id, name, grade 
from 
px_data_test_temp.test_score a 
left join 
px_data_test_temp.test_stu b 
on a.id = b.id 
where a.grade > 80;

或者(这种方式会产生笛卡尔积之后，再进行筛选)
select a.id, name, grade 
from 
px_data_test_temp.test_score a 
left join 
px_data_test_temp.test_stu b 
where a.id = b.id and a.grade > 80;

结果:

区别：
on 和 where 在筛选条件的时候，on 会显示所有满足 | 不满足条件的数据而 where 只显示满足条件的数据。

2、两张表right join且查询分数大于80分的数据

条件放到on上

select a.id, name, grade 
from 
px_data_test_temp.test_score a 
right join 
px_data_test_temp.test_stu b 
on a.id = b.id and a.grade > 80;

结果:

条件放到where上:

select a.id, name, grade 
from 
px_data_test_temp.test_score a 
right join 
px_data_test_temp.test_stu b 
on a.id = b.id 
where a.grade > 80;

或者(这种方式会产生笛卡尔积之后，再进行筛选)
select a.id, name, grade 
from 
px_data_test_temp.test_score a 
right join 
px_data_test_temp.test_stu b 
where a.id = b.id and a.grade > 80;

结果:

区别: 无

3、两张join且同时查询分数大于80的数据

条件放到on上

select a.id, name, grade 
from 
px_data_test_temp.test_score a 
join 
px_data_test_temp.test_stu b 
on a.id = b.id and a.grade > 80;

结果:

条件放到where上:

select a.id, name, grade 
from 
px_data_test_temp.test_score a 
join 
px_data_test_temp.test_stu b 
on a.id = b.id 
where a.grade > 80;

或者(这种方式会产生笛卡尔积之后，再进行筛选)
select a.id, name, grade 
from 
px_data_test_temp.test_score a 
join 
px_data_test_temp.test_stu b 
where a.id = b.id and a.grade > 80;

结果:

区别: 无

4、两张full join且同时查询分数大于80的数据

条件放到on上

select a.id, name, grade 
from 
px_data_test_temp.test_score a 
full join 
px_data_test_temp.test_stu b 
on a.id = b.id and a.grade > 80;

结果:

条件放到where上:

select a.id, name, grade 
from 
px_data_test_temp.test_score a 
full join 
px_data_test_temp.test_stu b 
on a.id = b.id 
where a.grade > 80;

或者(这种方式会产生笛卡尔积之后，再进行筛选)
select a.id, name, grade 
from 
px_data_test_temp.test_score a 
full join 
px_data_test_temp.test_stu b 
where a.id = b.id and a.grade > 80;

结果:

区别：
on 和 where 在筛选条件的时候，on 会显示所有满足 | 不满足条件的数据而 where 只显示满足条件的数据。

结论

• 如果是left join 在on上写主表a的条件不会生效，全表扫描。
• 如果是left join 在on上写副表b的条件会生效，但是语义与写到where 条件不同。
• 如果是inner join 在on上写主表a、副表b的条件都会生效。
• 如果是full join 全外连接, 是在等值连接的基础上将左表和右表的未匹配数据都加上。