Hive中join方法总结--有这一篇就够了

1. 常见的四种join的区别

常见的join主要有下面四种，join,outer join, semi join和inner join，下面对这四个join的关系进行说明

（1.1）join等价于inner join,也就是只会将两表都存在的join在一起

（1.2）outer join分为：

left outer join（等价于left join），right outer join（right join）和full outer join（full join）

Left join是以左表为基准，右表不存在的key均赋值为null

Right join是以右表为基准，左表不存在的key均赋值为null

Full join是取出现在左右两表的key的并集，某张表里面不存在的key赋值为null

（1.3）semi join：semi join只有left semi join，不存在semi join 和 right semi join

left semi join跟join的逻辑是一样的，但是有着三点不同

综上所述：join语句其实只有五种：join（inner join）、left join（left outer join）、right join（right outer join）、full join（full outer join）、left semi join

2. 对于各种join的使用举例

我们假设有表tab1和tab2:

tab1:                                        tab2:

stuNo1     stuName1               stuNo2       stuName2

1              a                              1                 x

2              b                              1                 y

3              c                              2                 z

（2.1）join（inner join）就是取交集的关系

select  tab1.stuNo1, tab1.stuName1, tab2.stuNo2,tab2.stuName2
from(
select * from tab1
) t1
join 
(
select * from tab2
) t2
on t1.stuNo1 = t2.stuN02

结果如下：

stuNo1     stuName1               stuNo2       stuName2

1              a                             1                  x

1              a                             1                  y

2              b                            2                  z

如果没有on，那么就会进行笛卡尔积操作，比如tab1有m行，tab2有n行，那么join之后的结果就会有m*n行。

（2.2）left join

两表关联，左表的key全部保留，右表关联不上的赋值null

select  tab1.stuNo1, tab1.stuName1, tab2.stuNo2,tab2.stuName2
from(
select * from tab1
) t1
left join 
(
select * from tab2
) t2
on t1.stuNo1 = t2.stuN02

结果如下：

stuNo1     stuName1               stuNo2       stuName2

1              a                             1                 x

1              a                             1                 y

2              b                             2                 z

3              c                             null              null

（2.3）right join同理，不再赘述

（2.4）full join

全表关联，即是左外连接和右外连接结果集合求并集 ,左右表均可赋值为null。当前例子下跟left join的结果相同。

如果full join不加on过滤条件，计算结果也是笛卡尔积。

（2.5）left semi join

逻辑跟join一样，只join左右表中同时出现的key。跟join的不同有三点：

（2.5.1）比join高效，因为对于右表中重复出现的行只遍历一次

（2.5.2）最后 select 的结果只许出现左表的那些列

（2.5.3）JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。举例如下：

SELECT * FROM table1 LEFT SEMI JOIN table2 on ( table1.student_no =table2.student_no) where table2.student_no>3

上面这样会报错，应该写成：

SELECT * FROM table1 LEFT SEMI JOIN table2 on ( table1.student_no =table2.student_no and  table2.student_no>3)

（2.6）其他常用方法

（2.6.1）交集去并集

select * from table1 full outer join table2 on table1.student_no=table2.student_no where table1.student_no is null or table2.student_no is null

（2.6.2）左表独有

select * from table1 left outer join table2 on table1.student_no=table2.student_no where table2.student_no is null

参考链接：

Hive中HSQL中left semi join和INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN区别 - 程序员大本营

HQL中join、 inner join、left join、left outer join、left semi join 、right join 、right outer join_酒伴千觞的博客-CSDN博客_hql中使用join

SQL、HQL中left join、right join、inner join的区别_VipMao的博客-CSDN博客