识记 | MySQL、hive中的join与union

实际上，

MySQL根本就不识别outer关键字,

hive才支持。

——数据准备：

表1_ jn1 _ name,id

tom,1
jey,2
lilly,7
lilly,8

表2_ jn2 _ name,id

tom,1
lilly,3
may,4
bob,5

INNER JOIN的简写。

会把两个表共有的部分筛选出来，一般用于A表和B表都存在的记录进行关联。

select *
from jn1
join jn2
on jn1.name=jn2.name;

结果：

jn1.name  jn1.id  jn2.name  jn2.id
tom         1         tom     1
lilly       7        lilly    3
lilly       8        lilly    3

如果不写连接条件on，MySQL和hive均会产生笛卡尔乘积。但hive会排序，而MySQL不会。

hive中 LEFT OUTER JOIN的简写。

会把左边的表所有数据列出来，当左边表有而右边表没有的时候，就会用null代替.

一般用于A表有而B表没有的记录进行关联，然后用where过滤掉B表中有null的记录行

select *
from jn1
left join jn2
on jn1.name=jn2.name;

结果：

jn1.name  jn1.id  jn2.name  jn2.id
tom          1       tom      1
jey          2       NULL    NULL
lilly        7       lilly    3
lilly        8       lilly    3

会把两者没有的、有的全部数据都选出来，没有的显示空值null

MySQL不支持full join...on；此时的full join等同于join（笛卡尔乘积）

select *
from jn1
full join jn2
on jn1.name = jn2.name;

结果：

jn1.name  jn1.id  jn2.name  jn2.id
tom          1       tom       1
NULL        NULL     bob       5
jey          2       NULL     NULL
lilly        7       lilly     3
lilly        8       lilly     3
NULL        NULL     may       4

会把查询结果拼接起来，但是要求两个查询结果的列数、列名（支持别名，具体没试过）必须保持一致。

否则会报错：

FAILED: SemanticException Schema of both sides of union should match

hive：

去重且排序：作用等价于 先distinct去重、再UNION ALL表合并、再order by排序。 

（查询出的结果集不知道按什么规则排序，待研究）

select *
from jn1
union
select * 
from jn2;

hive结果：

name  id
jey    2
lilly  7
bob    5
lilly  3
lilly  8
tom    1
may    4

MySQL：去重、拼接。

MySQL结果：

name  id
tom   1
jey   2
lilly 7
lilly 8
lilly 3
may   4
bob   5

hive同MySQL一致：

基本同union，但不去重、不排序，原表直接合并

select *
from jn1
union all
select * 
from jn2;

结果：

name  id
tom    1
jey    2
lilly  7
lilly  8
tom    1
lilly  3
may    4
bob    5

——总结：

部分素材来自于网络

http://t.zoukankan.com/Formulate0303-p-15796992.html

https://blog.csdn.net/weixin_46429290/article/details/123358552