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

tableA的结构及数据：

id   name 	  grade	 dept 
1    lijie1   100.0   10
2    lijie2   90.0    20
3    lijie3   60.0    10
4    lijie4   80.0    10
5    lijie5   70.0    20

tableB的结构及数据：

id    name 
10    IT1
20    IT2

---

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

条件放在on上面

select a.id,a.name,a.grade,a.dept,b.id,b.name from tableA a left join tableB b on a.dept=b.id and a.grade>80;

on结果为：

a.id    a.name  a.grade a.dept  b.id    b.name
1       lijie1  100.0   10      10      IT1
2       lijie2  90.0    20      20      IT2
3       lijie3  60.0    10      NULL    NULL
4       lijie4  80.0    10      NULL    NULL
5       lijie5  70.0    20      NULL    NULL

条件放在where上面：

select a.id,a.name,a.grade,a.dept,b.id,b.name from tableA a left join tableB b where a.dept=b.id and a.grade>80;

where结果为：

a.id    a.name  a.grade a.dept  b.id    b.name
1       lijie1  100.0   10      10      IT1
2       lijie2  90.0    20      20      IT2

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

【更新】
原因是没加on的话两张表是笛卡尔积，后面用where筛选出来复合条件的数据。
on后面加关联条件，可以加上where对关联出的数据进行一个筛选显示。

---

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

条件放在on上：

select a.id,a.name,a.grade,a.dept,b.id,b.name from tableA a right join tableB b on a.dept=b.id and a.grade>80;

结果为：

a.id    a.name  a.grade a.dept  b.id    b.name
1       lijie1  100.0   10      10      IT1
2       lijie2  90.0    20      20      IT2

条件放在where上：

select a.id,a.name,a.grade,a.dept,b.id,b.name from tableA a right join tableB b where a.dept=b.id and a.grade>80;

结果为：

a.id    a.name  a.grade a.dept  b.id    b.name
1       lijie1  100.0   10      10      IT1
2       lijie2  90.0    20      20      IT2

区别：无

---

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

条件放在on上：

select a.id,a.name,a.grade,a.dept,b.id,b.name from tableA a join tableB b on a.dept=b.id and a.grade>80;

结果为：

a.id    a.name  a.grade a.dept  b.id    b.name
1       lijie1  100.0   10      10      IT1
2       lijie2  90.0    20      20      IT2

条件放在where上：

select a.id,a.name,a.grade,a.dept,b.id,b.name from tableA a join tableB b where a.dept=b.id and a.grade>80;

结果为：

a.id    a.name  a.grade a.dept  b.id    b.name
1       lijie1  100.0   10      10      IT1
2       lijie2  90.0    20      20      IT2

区别：无

---

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

条件放在on上：

select a.id,a.name,a.grade,a.dept,b.id,b.name from tableA a full join tableB b on a.dept=b.id and a.grade>80;

结果为：

a.id    a.name  a.grade a.dept  b.id    b.name
4       lijie4  80.0    10      NULL    NULL
3       lijie3  60.0    10      NULL    NULL
1       lijie1  100.0   10      10      IT1
5       lijie5  70.0    20      NULL    NULL
2       lijie2  90.0    20      20      IT2

条件放在where上：

select a.id,a.name,a.grade,a.dept,b.id,b.name from tableA a full join tableB b where a.dept=b.id and a.grade>80;

结果为：

a.id    a.name  a.grade a.dept  b.id    b.name
1       lijie1  100.0   10      10      IT1
2       lijie2  90.0    20      20      IT2

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

---

4中join区别的结论：

在on条件下
join 内连接： 筛选条件严格，只要不满足条件的这一项数据就不会显示出来，不论是哪个表的这一行数据-等值连接。

left join 左外连接： 左外连接就是在等值连接的基础上加上主表中的未匹配数据。

right join 右外连接： 右外连接是在等值连接的基础上加上被连接表的不匹配数据 。

full join 满外连接： 全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上。

PS：

在这里我也不清楚为什么我右表的数据会显示NULL，按理说应该显示数据内容的。

where条件免疫，所有的join类型都按照内连接处理。

---

on与where的区别：

on对join类型的改变而会有反应而where没有，只是当个连接作用对where来说。