hive 四种join的区别

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的数据**
            **2、两张表right join且查询分数大于80分的数据**
            **3、两张join且同时查询分数大于80的数据**
            **4、两张full join且同时查询分数大于80的数据**
        **4中join区别的结论：**
            **PS：**
            **on与where的区别：**

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 只显示满足条件的数据。
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来说。
---------------------
作者：aaaaajiboke
来源：CSDN
原文：https://blog.csdn.net/aaaaajiboke/article/details/82730012
版权声明：本文为博主原创文章，转载请附上博文链接！