hive left join on 和where的区别 实际运行结果

1：建表导入数据

创建两张表（用户表和商品表，分别个有两个分区"2022-10-02"，“2022-10-01”）

CREATE TABLE `t_user`(
  `userid` string, 
  `pid` string)
partitioned by (dt string);
CREATE TABLE `t_pro`(
  `proid` string, 
  `proname` string)
partitioned by (dt string);

2：导入数据

导入t_user：dt = '2022-10-01'
insert into table t_user partition (dt = '2022-10-01')
select
'001' as userid,'p001' as pid 
union all
select
'002' as userid,'p002' as pid 
union all 
select
'003' as userid,'p004' as pid; 
导入t_user：dt = '2022-10-02'
insert into table t_user partition (dt = '2022-10-02')
select
'004' as userid,'p001' as pid 
union all
select
'005' as userid,'p002' as pid 
union all 
select
'006' as userid,'p003' as pid;
t_pro:
insert into table t_pro partition (dt = '2022-10-01')
select
'p001' ,'苹果'
union all
select
'p002','香蕉' 
union all 
select
'p003','西瓜'
union all
select
'p004','草莓'; 

insert into table t_pro partition (dt = '2022-10-02')
select
'p005' ,'芒果'
union all
select
'p006','柠檬'; '''

``

3： 执行sql

1:
select * from t_user l
left join t_pro p
on l.pid = p.proid and l.dt = "2022-10-01" and p.dt = "2022-10-01";

2：执行结果同1语句
select * from t_user l
left join t_pro p
on l.pid = p.proid and l.dt = p.dt and p.dt = "2022-10-01";

3：select * from t_user l
left join t_pro p
on l.pid = p.proid where l.dt = "2022-10-01" and p.dt = "2022-10-01";

4：同3 执行结果一样
select * from t_user l
left join t_pro p
on l.pid = p.proid and l.dt = p.dt where p.dt = "2022-10-01";

5： 左表条件不是关联字段情况
select * from t_user l
left join t_pro p
on l.pid = p.proid and l.dt = p.dt and p.dt = "2022-10-01"
and l.userid = "002";

6： 右表件不是关联字段情况，
select * from t_user l
left join t_pro p
on l.pid = p.proid and l.dt = p.dt and p.dt = "2022-10-01"
and p.proname = "苹果";

7： 左表条件不是关联字段情况
select * from t_user l
left join t_pro p
on l.pid = p.proid and l.dt = p.dt and p.dt = "2022-10-01"
where l.userid = "002";

8： 右表条件不是关联字段情况，
select * from t_user l
left join t_pro p
on l.pid = p.proid and l.dt = p.dt and p.dt = "2022-10-01"
where p.proname = "苹果";

4：总结（待补充）