hive之full outer join（全连接）使用方法

介绍

full outer join结合了 LEFT JOIN 和 RIGHT JOIN 的结果，并使用NULL值作为两侧缺失匹配结果。

语法

SELECT 
    table1.column_name(s),table2.column_name(s) 
FROM table1 
    FULL OUTER JOIN table2 
ON table1.column_name = table2.column_name;

相当于:left join + union + right join

SELECT 
    table1.column_name(s),table2.column_name(s)
FROM table1 
    LEFT OUTER JOIN table2 
ON table1.column_name = table2.column_name;
UNION
SELECT 
    table1.column_name(s),table2.column_name(s)
FROM table1 
    RIGHT OUTER JOIN table2 
ON table1.column_name = table2.column_name;

例子

创建顾客表：customers

CREATE TABLE `default.customers`(
  `customer_id` varchar(25) COMMENT '顾客id',
  `customer_name` varchar(25) COMMENT '顾客姓名',
  `customer_age` varchar(25) COMMENT '顾客年龄')
PARTITIONED BY ( 
  `date_time` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://master:9000/user/hive/warehouse/default.db/customers'

插入数据

insert into table default.customers partition(date_time='2022-04-01')
select '1','zhangsan','22'
union all
select '2','lisi','34' 
union all
select '3','wangwu','21' 
union all
select '7','zhaoliu','33' 
union all
select '9','liuqi','44' 

创建订单表：orders

CREATE TABLE `default.orders`(
  `order_id` varchar(25) COMMENT '订单id', 
  `customer_id` varchar(25) COMMENT '顾客id', 
  `order_date` varchar(25) COMMENT '订单日期')
PARTITIONED BY ( 
  `date_time` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://master:9000/user/hive/warehouse/default.db/orders'

插入数据

insert into table default.orders partition(date_time='2022-04-01')
select '1','1','2022-04-01'
union all
select '2','2','2022-03-01' 
union all
select '3','3','2022-03-02' 
union all
select '4','4','2022-03-03' 
union all
select '5','5','2022-03-02' 
union all
select '6','6','2022-03-03'

full outer join语句

select a.customer_id,a.customer_name,a.customer_age,b.order_id,b.order_date from default.customers a
full outer join default.orders b
on a.customer_id=b.customer_id

结果

left join + union + right join语句

select a.customer_id,a.customer_name,a.customer_age,b.order_id,b.order_date from default.customers a
left join default.orders b
on a.customer_id=b.customer_id
union 
select a.customer_id,a.customer_name,a.customer_age,b.order_id,b.order_date from default.customers a
right join default.orders b
on a.customer_id=b.customer_id

结果

可以看出结果是一摸一样的。

结语

     本次的分享就到这里了，下一篇博客博主将带来**hive之left semi join（左半连接）使用方法**，敬请期待！**受益的朋友或对大数据技术感兴趣的伙伴记得点赞关注支持一波！**