=Start=

缘由：

简单整理记录一下Hive SQL中的 full outer join 的使用方法，方便后面有需要的时候参考。平时我用的最多的是表关联是 left outer join ，用来基于起始表1中的特定列去其它的表中关联、扩展字段，出发点在于起始表1中的行数据已经是全的了，剩下的只需要基于特定条件去扩展列，补充更多的信息即可。

但其实有些情况下，起始表1不一定是一张表，而是需要从多张表中进行 union 合并才能组成一个较为完整的数据，这时就可以用 full outer join 来实现这个功能。因为之前基本没用过这个关联，所以这里根据网上的示例实际执行验证，好有一个更深的理解和记忆。

正文：

参考解答：

全关联（full outer join、full join）的原理是先左关联再右关联，然后把结果用 union 合并去重再返回（union会对两个子查询的结果去重合并，而union all不会对子查询结果去重处理），关联不到的字段被自动置为 NULL。是否指定 outer 关键字，对查询结果无影响。

注意：full join时候，Hive不会使用MapJoin来优化。

创建2张表用于测试

# test.user_info
c_id,c_name,c_age
1,'zhangsan',22
2,'lisi',34
3,'wangwu',21
7,'zhaoliu',33
8,'jiuba',44
9,'liuqi',44

# test.order_info
order_id,c_id,order_date
1,1,'2022-03-01'
2,2,'2022-03-02'
3,3,'2022-03-03'
4,4,'2022-03-04'
5,5,'2022-03-05'
6,6,'2022-03-06'
7,1,'2022-03-11'

以上2个表之间通过 c_id 字段进行关联。

几种关联语法的效果如下

select
*
from
test.user_info -- 6 行
;

select
*
from
test.order_info -- 7 行
;


select
t1.*,t2.*
from
test.user_info t1
-- left outer join -- 6+1 行
-- right outer join -- 7 行
full outer join -- 10 行
test.order_info t2
on t1.c_id = t2.c_id
;


/*left outer join
t1_c_id    t1_c_name   t1_c_age    t2_order_id t2_c_id t2_order_date   
1    zhangsan    22  7   1   2022-03-11
1    zhangsan    22  1   1   2022-03-01
2    lisi    34  2   2   2022-03-02
3    wangwu  21  3   3   2022-03-03
7    zhaoliu 33          
8    jiuba   44          
9    liuqi   44          
*/

/*right outer join
t1_c_id    t1_c_name   t1_c_age    t2_order_id t2_c_id t2_order_date   
            4   4   2022-03-04
            5   5   2022-03-05
3    wangwu  21  3   3   2022-03-03
1    zhangsan    22  7   1   2022-03-11
1    zhangsan    22  1   1   2022-03-01
            6   6   2022-03-06
2    lisi    34  2   2   2022-03-02
*/


/* full outer join
t1_c_id    t1_c_name   t1_c_age    t2_order_id t2_c_id t2_order_date   
8    jiuba   44          
9    liuqi   44          
2    lisi    34  2   2   2022-03-02
7    zhaoliu 33          
1    zhangsan    22  7   1   2022-03-11
1    zhangsan    22  1   1   2022-03-01
3    wangwu  21  3   3   2022-03-03
            5   5   2022-03-05
            4   4   2022-03-04
            6   6   2022-03-06
*/

-- 和上面的 full outer join 效果等价
select
t1.*,t2.*
from
test.user_info t1
left outer join -- 6+1 行
test.order_info t2
on t1.c_id = t2.c_id

union

select
t1.*,t2.*
from
test.user_info t1
right outer join -- 7 行
test.order_info t2
on t1.c_id = t2.c_id
;

参考链接：

hive之full outer join（全连接）使用方法 #很详细
https://blog.csdn.net/qq_36249352/article/details/124017263

Hive Join 的原理与机制
https://www.hadoopdoc.com/hive/hive-join

[一起学Hive]之十一-Hive中Join的类型和用法
http://lxw1234.com/archives/2015/06/315.htm

=END=