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

IMezZ

已于 2022-04-07 16:29:33 修改

阅读量3.5w

点赞数 15

分类专栏： Hive 文章标签：大数据 hive sql

于 2022-04-07 16:23:23 首次发布

本文链接：https://blog.csdn.net/qq_36249352/article/details/124017263

版权

Hive 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了Hive中的Full Outer Join用法，通过创建和操作`customers`和`orders`表，展示其与LEFT JOIN + UNION + RIGHT JOIN等效性。并预告下篇将探讨Hive的Left Semi Join。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

left join + union + right 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

结果