join和union的区别

union和join是需要联合多张表时常见的关联词，
join:
两张表做交连后里面条件相同的部分记录产生一个记录集，
union:
union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集 。
二者区别：
join和union的主要区别就一条，join是将拼接内容变成一行（左右拼接），根据共同字段将数据拼接成一行一行数据；union是将表内容拼接成一列（上下拼接），也是根据字段共同属性进行将表与表之间数据进行上下拼接。

join的连接方式

注意：下面展示的是Hive Sql语句查询方式

(left join\left outer join) \ right join \right outer join \inner join \ full outer join
特殊类型： left semi join

两个Hive表数据相关情况，如下所示：
表1

GuoYijun,5
YuanJing,10
LiYuan,20

表2

YuanJing,male
LiYuan,male
LiuYang,female
Lilei,male

inner join

inner jion为内连接，只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。内连接也被称为普通连接。

select * from table1 a inner join table2 b on a.name = b.name;
--或者
select * from table1 a join table2 b on a.name = b.name;

通过inner join查询出来的数据信息为：

OK
LiYuan  20      LiYuan  male
YuanJing        10      YuanJing        male

通过实现结果我们可以看出，inner join可以实现将两个表中具有相同属性的字段进行拼接。

left join

left jion为左连接，以左边表为主，返回是左表数据关联右表数据，右表无法关联左表的舍弃，左边无法关联的在右表显示为null。

select * from table1 a left join table2 b on a.name = b.name;

通过left join连查询的数据结果如下:

GuoYijun	5	null	null
YuanJing        10      YuanJing        male	
LiYuan  20      LiYuan  male

reght join

reght join为右连接，以右表为主，返回是右表数据关联左表数据，左表无法关联右表的舍弃，右边无法关联的在左表显示为null。

select * from table1 a right join table2 b on a.name = b.name;

右连接查询数据结果如下：

LiYuan  20      LiYuan  male
NULL    NULL    Lilei   male
NULL    NULL    LiuYang female
YuanJing        10      YuanJing        male

full join

full join为全连接，全连接将左右两个表数据全部返回显示，无法关联的全部以null显示。

select * from table1 a fulljoin table2 b on a.name = b.name;

右连接查询数据结果如下：

GuoYijun        5       NULL    NULL
LiYuan  20      LiYuan  male
NULL    NULL    Lilei   male
NULL    NULL    LiuYang female
YuanJing        10      YuanJing        male

left semi join

left semi join自我感觉与join一样，但是这个是以左表为主，返回的是左表中在右表中数据信息。

select * from table1 a left semi join table2 b on a.name = b.name;

查询出来的数据信息为：

LiYuan  20      LiYuan  male
YuanJing        10      YuanJing        male

Common Join与Map Join

利用hive进行join连接操作，相较于MR有两种执行方案，一种为common join，另一种为map join ，map join是相对于common join的一种优化，省去shullfe和reduce的过程，大大的降低的作业运行的时间。
Common Join（也称之为shufflejoiin/reducejoin）
过程：

• 首先会启动一个Task，Mapper会去读表HDFS上两张X/Y表中的数据
• Mapper处理过数据再经过shuffle处理
• 最后由reduce输出join结果

缺点 :

• 存在shuffle过程，效率低
• 每张表都要去磁盘读取，磁盘IO大

Map Join
过程：

• mapjoin首先会通过本地MapReduce Task将要join的小表转成Hash Table Files，然后加载到分布式缓存中
• Mapperh会去缓存中读取小表数据来和Big Table数据进行join
• Map直接给出结果

优点： 没有shuffle/Reduce过程，效率提高
缺点 ： 由于小表都加载到内存当中，读内存的要求提高了

hive中专门有个参数来设置是否自动将commmon join 转化为map join：hive.auto.convert.join。
当hive.auto.convert.join=true hive会为我们自动转换。

union

union和join类似，都是对表数据进行拼接在一起后返回，但是union使用比较少，几乎不用，主要还是掌握join相关的拼接方式。

union ：将多个结果集合并，去重，排序
union all ：将多个结果集合并，不去重，不排序。

union与union all 需要注意的地方是 上下表的的字段个数要保持一致，类型尽量保持一致

简单例子说明

类似咸鱼订单中，一张表bill_order中字段如下

字段	说明
order_id	订单ID
seller_id	卖方ID
buyer_id	买方ID
order_amt	订单金额
order_date	订单完成时间

求：7月份用户买卖双方成交笔数和成交金额

select 
corp_id,
sum(deal_count) as deal_count,
sum(deal_amt)
from 
(
select 
seller_id as corp_id,
count(1) as deal_count,
sum(order_amt) as deal_amt
from bill_order
where order_date like '2022-07%'
group by seller_id 

union all 

select 
buyer_id as corp_id,
count(1) as deal_count,
sum(order_amt) as deal_amt
from bill_order
where order_date like '2022-07%'
group by buyer_id 
)t 
group by corp_id 
;

注意：如果要求出 用户的 买方、卖方、买+卖的数据，可以在上下表中的添加一个新字段用于汇总得时候判断是 买方还是卖方

求：7月份成交用户名单

select 
corp_id,
from 
(
select 
distinct 
seller_id as corp_id
from bill_order
where order_date like '2022-07%'

union

select 
distinct 
buyer_id as corp_id
from bill_order
where order_date like '2022-07%'
)t 
group by corp_id 
;