目录
介绍
语法
例子
创建顾客表:customers
创建订单表:orders
?full outer join语句
? 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
结果
可以看出结果是一摸一样的。
结语
本次的分享就到这里了,下一篇博客博主将带来hive之left semi join(左半连接)使用方法,敬请期待!受益的朋友或对大数据技术感兴趣的伙伴记得点赞关注支持一波!
最后
深知大多数初中级Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则近万的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《Java开发全套学习资料》送给大家,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
小编已加密:aHR0cHM6Ly9kb2NzLnFxLmNvbS9kb2MvRFVrVm9aSGxQZUVsTlkwUnc==出于安全原因,我们把网站通过base64编码了,大家可以通过base64解码把网址获取下来。