2021-09-13

hive中的join

hive有很多join，这些join之间是有差别的，总结这些join可以让我们在不同场景下更充分更灵活的去使用，从而使我们在日常工作中更加效率。

---

---

hive中有哪些join？

以下几种：hive中大致包含6种join，分别是inner join、left join、full join、right join、left semi join、cross join这6种。其中left join、right join、full join也叫做left outer join、right outer join、full outer join。

一、hive中的各个join

1.left join

如果说join中哪个最重要，哪个用的最多，那必定是left join。left join是left outer join的缩写，两者功能完全一样，也就是我们通常所说的左连接，这个是目前生产上用的最为广泛的join之一。下面我们来详细介绍一下左连接，左连接顾名思义以左表为主表去关联其他表，通过关联条件关联上的就可以从从表中取从表中的值，如果关联不上那么从表中取的值就会是NULL,也就是我们通常说的空。
下面我们举例说明（示例）：
假设有两张表分别为 tableA 和 tableB ,两张表的分别有id和name，两者结合构成联合主键
首先准备测试数据

创建表A tableA
create table tableA(
id int
,name varchar(20)
)；
想表A中插入数据
insert into tableA values
(1,‘张三’)
,(2,‘李四’)
,(3,‘王五’)
,(4,‘小航’)
,(5,‘小刘’)
,(6,‘小张’);
–创建表B tableB
create table tableB(
id int
,name varchar(20)
);
–向表B中插入数据
insert into tableB values
,(1,‘小张’)
,(2,‘李四’)
,(3,‘王五’)
,(4,‘航航’)
,(5,‘刘小’)
,(6,‘小兵张’);

select * from tableA a 
left join tableB b
on a.id = b.id
and a.name = b.name 

A表和B表通过id和name关联，只有A和B中id和name同时相等时才能同时取到两者都不为NULL的情况，如A表中的id为2，name为’李四’的的这条数据，在B表中存在一条一模一样的数据，所以如下结果中会发现id为2,name为‘李四’的这条数据A表和 B表都取到了。A表中id为1,name为’张三’的这条数据去和B表中的数据关联，A表中的id和B表中的id相同但是name不一样，两者必须同时相等才算关联上，A表为主表作为主表，B表作为从表，所以出现取到A表中数据为本来数据，取到B表中的数据为NULL这种情况。

a.id	a.name	b.id	b.name
1	张三	NULL	NULL
2	李四	2	李四
3	王五	3	王五
4	小航	NULL	NULL
5	小刘	NULL	NULL
6	小张	NULL	NULL

2.right join

右连接其实和左连接是一样的，就像数学中加减法一样，其实加减法可以归为一类，加法可以转化为减法，就比如说 1+1=2，我们转化为减法就可以写成1-（-1）=2。
同理。right join
a right join b 就可以写成 b left join a，只需考虑换一下两张表的位置即可。多数人更倾向于直接将right join改写成left join。

3.full join

full join其实等价于full outer join，full join其实相当于left join 和right join的结合。
我们还以A表和B表为例子，看一下结果

select * from tableA a 
full join tableB b
on a.id = b.id
and a.name = b.name 

结果如下：

a.id	a.name	b.id	b.name
NULL	NULL	1	小张
1	张三	NULL	NULL
2	李四	2	李四
3	王五	3	王五
4	小航	NULL	NULL
NULL	NULL	4	航航
NULL	NULL	5	刘小
5	小刘	NULL	NULL
NULL	NULL	6	小兵张
6	小张	NULL	NULL

4.inner join

inner join 其实和 join是一样的，有时我们会省略到中间的inner。inner join会通常用来求交集，就比如说:求tableA和tableB的交集。

select * from tableA a 
inner join tableB b
on a.id = b.id
and a.name = b.name 

inner join的结果如下：

a.id	a.name	b.id	b.name
2	李四	2	李四
3	王五	3	王五

我们再来试一下直接join

select * from tableA a 
join tableB b
on a.id = b.id
and a.name = b.name 

直接join的结果如下：

a.id	a.name	b.id	b.name
2	李四	2	李四
3	王五	3	王五

扩展：通过where连接的隐形连接也能起到同样的效果。

select * from tableA a ,tableB b
where a.id = b.id
and a.name = b.name 

5.cross join

笛卡尔积连接，假设左表有n行，右表有m行，那么最终的输出结果有n*m行结果。

select * from tableA a 
cross join tableB b

cross join的数据结果如下（36行即6*6）：

a.id	a.name	b.id	b.name
1	张三	1	小张
2	李四	1	小张
3	王五	1	小张
4	小航	1	小张
5	小刘	1	小张
6	小张	1	小张
1	张三	2	李四
2	李四	2	李四
3	王五	2	李四
4	小航	2	李四
5	小刘	2	李四
6	小张	2	李四
1	张三	3	王五
2	李四	3	王五
3	王五	3	王五
4	小航	3	王五
5	小刘	3	王五
6	小张	3	王五
1	张三	4	航航
2	李四	4	航航
3	王五	4	航航
4	小航	4	航航
5	小刘	4	航航
6	小张	4	航航
1	张三	5	刘小
2	李四	5	刘小
3	王五	5	刘小
4	小航	5	刘小
5	小刘	5	刘小
6	小张	5	刘小
1	张三	6	小兵张
2	李四	6	小兵张
3	王五	6	小兵张
4	小航	6	小兵张
5	小刘	6	小兵张
6	小张	6	小兵张

6.left semi join

LEFT SEMI JOIN 左半连接 相当于 in/exist的优化.


注：以上图片转载自：https://blog.csdn.net/HappyRocking/article/details/79885071

1、left semi join 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

2、left semi join 是只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表。

3、因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。

原文链接：https://blog.csdn.net/HappyRocking/article/details/79885071