【Hive-基础】HiveSql 连接查询【inner join、left join、right join、full join、union all、union】
1)inner join
1、连接 2 个表,取出公共部分
2、必须重命名
3、on后连接条件键值唯一
4、连接前注意去重,提高效率
5、inner可省略
select * from user_list_1 as a
inner join user_list_2 as b
on a.user_id=b.user_id;
三表连接
select a.user_name
from
(select distinct user_name from data1)as a
inner join (select distinct user_name from data2) as b
on a.user_name=b.user_name
inner join (select distinct user_name from data3) as c
on b.user_name=c.user_name;
2)left join、right join
left join:以左表
为全集
,返回能匹配上的匹配结果,没匹配上的显示 NULL;
right join:以右表
为全集
,返回能匹配上的匹配结果,没匹配上的显示 NULL;
应用:取出表1中存在,表2中不存在的项
select *
from user_list_1 a
left join user_list_2 b
on a.user_id=b.user_id;
3)full join
并列将2个表的信息展示
select *
from user_list_1 a
full join user_list_2 b
on a.user_id=b.user_id;
4)union all、union
增加行
1、字段名称必须一致
2、字段顺序必须一致
3、无连接条件
union all和union的区别: union 会去重且排序
- union all
select a.user_id,a.user_name
from user_list_1 a
union all
select b.user_id,b.user_name
from user_list_2 b;
- union
select a.user_id,a.user_name
from user_list_1 a
union
select b.user_id,b.user_name
from user_list_2 b;