hive之间的各种join和使用

转自https://blog.youkuaiyun.com/qq_26442553/article/details/79465608
hive编程是整个数据仓库操作的核心，而各种业务之间的join是hive的核心，所以熟练明白滴掌握hive中的各种join是数据仓库开发工程师必备的技能。

hive中的join只支持等值join,也就是说join on中的on里面表之间连接条件只能是=，不能是<,>等符号。此外，on中的等值连接之间只能是and,不能是or. （如果在on 里添加非表之间的条件可以是非等号，下面有演示）

为了演示各种join之间的关系以及使用注意事项，准备如下三个表，以及表中数据。

表1：CREATE TABLE FDM_SOR.mytest_department(
dept_no int,
dept_name string) row format delimited fields terminated by ‘,’

表2：CREATE TABLE FDM_SOR.mytest_staffinfo(
id int,
name string,
sex string,
dept_no int
)row format delimited fields terminated by ‘,’

表3：CREATE TABLE FDM_SOR.mytest_deptaddr(
dept_no int,
addr string,
tel string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
如下对应的各表中load的数据
`FDM_SOR.mytest_staffinfo
001,‘tom’,‘man’,101
002,‘jerry’,‘man’,103
003,‘marry’,‘woman’,101
004,‘tom’,‘man’,104
005,‘jebby’,‘man’,102
006,‘smiiss’,‘man’,101
007,‘dosos’,‘man’,102
008,‘tiny’,‘woman’,102
009,‘feyy’,‘woman’,104
010,‘feyy’,‘woman’,103
011,‘cake’,‘man’,101
012,‘dogy’,‘man’,102
013,‘gaayy’,‘man’,105
014,‘kety’,‘man’,
015,‘caggyy’,‘man’,104
016,‘addy’,‘man’,106
017,‘nancy’,‘man’,104
018,‘tom’,‘man’,101

FDM_SOR.mytest_department
101,‘it’
102,‘finance’
103,‘mannager’
104,‘dining’
105,‘boss-office’
107,‘enjoy’
108,‘fun’

FDM_SOR.mytest_deptadd
109,‘new york109’,‘931232323’
108,‘new york108’,‘431232323’
107,‘new york107’,‘531232323’
106,‘new york106’,‘531232323’
101,‘new york101’,‘431232323’
104,‘new york104’,‘131232323’
103,‘new york103’,‘131232323’
102,‘new york102’,‘131232323’
105,‘new york105’,‘131232323’
1.inner join(内连接，只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来)
内连接是最常见的一种连接，它页被称为普通连接，而E.FCodd最早称之为自然连接。其中inner可以省略。inner join == join 等价于早期的连接语法
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a inner join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
等价于 inner join内连接的另外一种写法，效果一样。
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a ,FDM_SOR.mytest_department b
where a.dept_no = b.dept_no
1 ‘tom’ 101 ‘it’
2 ‘jerry’ 103 ‘mannager’
3 ‘marry’ 101 ‘it’
4 ‘tom’ 104 ‘dining’
5 ‘jebby’ 102 ‘finance’
6 'smiiss’101 ‘it’
7 ‘dosos’ 102 ‘finance’
8 ‘tiny’ 102 ‘finance’
9 ‘feyy’ 104 ‘dining’
10 ‘feyy’ 103 ‘mannager’
11 ‘cake’ 101 ‘it’
12 ‘dogy’ 102 ‘finance’
13 ‘gaayy’ 105 ‘boss-office’
15 'caggyy’104 ‘dining’
17 ‘nancy’ 104 ‘dining’
18 ‘tom’ 101 ‘it’
如上，表1中工号16的员工，所在106部门，因为department中没有，所以其没有留下，同理14号员工因为没有部门，也没有存在。 同理department中部分编号107,108也没有留下，因为其在表staffinfo中没有，故没有留下。

2.left join 是左外连接(Left Outer Jion)，其中outer可以省略，left outer join是早期的写法。
A left join B 是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
意思说,左表(A)的记录将会全部表示出来(不管右边的表中是否存在与它们匹配的行),而右表(B)只会显示符合搜索条件的记录，比如符合on,where中的条件。B表记录不足的地方均为NULL. A left join B 等价B right join A
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a left join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
1 ‘tom’ 101 ‘it’
2 ‘jerry’ 103 ‘mannager’
3 ‘marry’ 101 ‘it’
4 ‘tom’ 104 ‘dining’
5 ‘jebby’ 102 ‘finance’
6 'smiiss’101 ‘it’
7 ‘dosos’ 102 ‘finance’
8 ‘tiny’ 102 ‘finance’
9 ‘feyy’ 104 ‘dining’
10 ‘feyy’ 103 ‘mannager’
11 ‘cake’ 101 ‘it’
12 ‘dogy’ 102 ‘finance’
13 ‘gaayy’ 105 ‘boss-office’
14 ‘kety’ NULL NULL
15 'caggyy’104 ‘dining’
16 ‘addy’ NULL NULL
17 ‘nancy’ 104 ‘dining’
18 ‘tom’ 101 ‘it’
3.right join ,同理和left join相反，A right join B ,则显示B表中所有的记录，A表不足的用null填充
同样 right outer join = right join ,outer可以省略。
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a right outer join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
1 ‘tom’ 101 ‘it’
3 ‘marry’ 101 ‘it’
6 'smiiss’101 ‘it’
11 ‘cake’ 101 ‘it’
18 ‘tom’ 101 ‘it’
5 ‘jebby’ 102 ‘finance’
7 ‘dosos’ 102 ‘finance’
8 ‘tiny’ 102 ‘finance’
12 ‘dogy’ 102 ‘finance’
2 ‘jerry’ 103 ‘mannager’
10 ‘feyy’ 103 ‘mannager’
4 ‘tom’ 104 ‘dining’
9 ‘feyy’ 104 ‘dining’
15 'caggyy’104 ‘dining’
17 ‘nancy’ 104 ‘dining’
13 ‘gaayy’ 105 ‘boss-office’
NULL NULL 107 ‘enjoy’
NULL NULL 108 ‘fun’
4. full outer join 等价 full join ,全外连接

包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行 在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的并操作将上述两个结果集合并为一个结果集。

select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a full join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
order by a.id
NULL NULL 107 ‘enjoy’
NULL NULL 108 ‘fun’
1 ‘tom’ 101 ‘it’
2 ‘jerry’ 103 ‘mannager’
3 ‘marry’ 101 ‘it’
4 ‘tom’ 104 ‘dining’
5 ‘jebby’ 102 ‘finance’
6 ‘smiiss’ 101 ‘it’
7 ‘dosos’ 102 ‘finance’
8 ‘tiny’ 102 ‘finance’
9 ‘feyy’ 104 ‘dining’
10 ‘feyy’ 103 ‘mannager’
11 ‘cake’ 101 ‘it’
12 ‘dogy’ 102 ‘finance’
13 ‘gaayy’ 105 ‘boss-office’
14 ‘kety’ NULL NULL
15 ‘caggyy’ 104 ‘dining’
16 ‘addy’ NULL NULL
17 ‘nancy’ 104 ‘dining’
18 ‘tom’ 101 ‘it’
5.自连接是自身连接，指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据
6.外连接与条件配合使用
当在内连接（join或者inner join）查询中加入条件时，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，比如：
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a inner join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no and a.id > 5
等价于
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a inner join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
where a.id > 5
6 ‘smiiss’ 101 ‘it’
7 ‘dosos’ 102 ‘finance’
8 ‘tiny’ 102 ‘finance’
9 ‘feyy’ 104 ‘dining’
10 ‘feyy’ 103 ‘mannager’
11 ‘cake’ 101 ‘it’
12 ‘dogy’ 102 ‘finance’
13 ‘gaayy’ 105 ‘boss-office’
15 ‘caggyy’ 104 ‘dining’
17 ‘nancy’ 104 ‘dining’
18 ‘tom’ 101 ‘it’
但对于外连接情况就不同了。加入的条件在join子句中和在where子句中效果完全不一样。
当把条件加入到 join子句时，SQL Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。
如果将条件放到where子句中，SQL Server将会首先进行连接操作，然后使用where子句对连接后的行进行筛选。下面的两个查询展示了条件放置位子对执行结果的影响：

select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a left join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no and a.id > 5
1 ‘tom’ NULL NULL
2 ‘jerry’ NULL NULL
3 ‘marry’ NULL NULL
4 ‘tom’ NULL NULL
5 ‘jebby’ NULL NULL
6 ‘smiiss’ 101 ‘it’
7 ‘dosos’ 102 ‘finance’
8 ‘tiny’ 102 ‘finance’
9 ‘feyy’ 104 ‘dining’
10 ‘feyy’ 103 ‘mannager’
11 ‘cake’ 101 ‘it’
12 ‘dogy’ 102 ‘finance’
13 ‘gaayy’ 105 ‘boss-office’
14 ‘kety’ NULL NULL
15 ‘caggyy’ 104 ‘dining’
16 ‘addy’ NULL NULL
17 ‘nancy’ 104 ‘dining’
18 ‘tom’ 101 ‘it’
如上，当将条件加入到where 子句后
select a.id ,a.name,b.dept_no,b.dept_name
from FDM_SOR.mytest_staffinfo a left join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
where a.id > 5
6 ‘smiiss’ 101 ‘it’
7 ‘dosos’ 102 ‘finance’
8 ‘tiny’ 102 ‘finance’
9 ‘feyy’ 104 ‘dining’
10 ‘feyy’ 103 ‘mannager’
11 ‘cake’ 101 ‘it’
12 ‘dogy’ 102 ‘finance’
13 ‘gaayy’ 105 ‘boss-office’
14 ‘kety’ NULL NULL
15 ‘caggyy’ 104 ‘dining’
16 ‘addy’ NULL NULL
17 ‘nancy’ 104 ‘dining’
18 ‘tom’ 101 ‘it’
7.多表之间进行join时注意事项
select a.id ,a.name,b.dept_no,b.dept_name,c.addr,c.tel
from FDM_SOR.mytest_staffinfo a
inner join FDM_SOR.mytest_department b on a.dept_no = b.dept_no
inner join FDM_SOR.mytest_deptaddr c on a.dept_no = c.dept_no ;
1 ‘tom’ 101 ‘it’ ‘new york101’ ‘431232323’
2 ‘jerry’ 103 ‘mannager’ ‘new york106’ ‘131232323’
3 ‘marry’ 101 ‘it’ ‘new york101’ ‘431232323’
4 ‘tom’ 104 ‘dining’ ‘new york101’ ‘131232323’
5 ‘jebby’ 102 ‘finance’ ‘new york102’ ‘131232323’
6 'smiiss’101 ‘it’ ‘new york101’ ‘431232323’
7 ‘dosos’ 102 ‘finance’ ‘new york102’ ‘131232323’
8 ‘tiny’ 102 ‘finance’ ‘new york102’ ‘131232323’
9 ‘feyy’ 104 ‘dining’ ‘new york101’ ‘131232323’
10 ‘feyy’ 103 ‘mannager’ ‘new york106’ ‘131232323’
11 ‘cake’ 101 ‘it’ ‘new york101’ ‘431232323’
12 ‘dogy’ 102 ‘finance’ ‘new york102’ ‘131232323’
13 ‘gaayy’ 105 ‘boss-office’‘new york106’ ‘131232323’
15 'caggyy’104 ‘dining’ ‘new york101’ ‘131232323’
17 ‘nancy’ 104 ‘dining’ ‘new york101’ ‘131232323’
18 ‘tom’ 101 ‘it’ ‘new york101’ '431232323
注意：一般情况下，hive会给每个join对象启动一个mapreduce job进行执行，如上，一般会先启动一个mr job 进行a,b表的join，然后再启动一个mr job进行上面job产生的临时表与c表的join,但是对于3个及以上的多表join,join有所优化，如果每个join的子句on里的连接键一样，如上都是dept_no,那么这样的话，mr会启动一个job完成所有表的join.此外，多表进行join时，一般要把大表放到最后面，这样可以提高效率。
8.left semi join 左半开连接 ，会显示左半边表中记录，前提是其记录对于右半边表满足于on语句中判定条件。
left semi join 效果和inner join 效果差不多，比inner join 效率更高。
select a.id ,a.name,a.dept_no
from FDM_SOR.mytest_staffinfo a
where a.dept_no in (
select dept_no from FDM_SOR.mytest_department
where dept_no > 102
)
2 ‘jerry’ 103
4 ‘tom’ 104
9 ‘feyy’ 104
10 ‘feyy’ 103
13 ‘gaayy’ 105
15 'caggyy’104
17 ‘nancy’ 104
等价于下面的查询
select a.id ,a.name,a.dept_no
from FDM_SOR.mytest_staffinfo a left semi join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no and b.dept_no >102

2 ‘jerry’ 103
4 ‘tom’ 104
9 ‘feyy’ 104
10 ‘feyy’ 103
13 ‘gaayy’ 105
15 'caggyy’104
17 ‘nancy’ 104

select a.id ,a.name,a.dept_no
from FDM_SOR.mytest_staffinfo a left semi join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
where a.dept_no > 102

2 ‘jerry’ 103
4 ‘tom’ 104
9 ‘feyy’ 104
10 ‘feyy’ 103
13 ‘gaayy’ 105
15 'caggyy’104
17 ‘nancy’ 104

select a.id ,a.name,a.dept_no
from FDM_SOR.mytest_staffinfo a left semi join FDM_SOR.mytest_department b
on a.dept_no = b.dept_no
where b.dept_no > 102 -----报错。select 和where子句中不能引用右边表中的字段。
最后附上hive中各种join之间的关系一览图。