hive中join与union的语法

1.类型

(left join\left outer join) \ right join \right outer join \inner join \ full outer join
特殊类型： left semi join

2.实例

2.1 数据准备

1,a
2,b
3,c
4,d
7,y
8,u

2,bb
3,cc
7,yy
9,pp

create table if not exists u1(
id int,
name string
)
row format delimited fields terminated by ','
;

create table if not exists u2(
id int,
name string
)
row format delimited fields terminated by ','
;

load data local inpath '/root/test/data1' into table u1;
load data local inpath '/root/test/data2' into table u2;

2.2 内连接(join,inner join,逗号连接)

//join加条件(on或者where)
select * 
from u1 
join u2 where u1.id =u2.id;

//inner join
select * 
from u1 
inner join u2 on u1.id =u2.id;

//逗号连接
select * 
from u1,u2
where u1.id=u2.id;

//以上三种结果一样

注意:join不加任何on或者where条件过滤时,称之为笛卡尔积
笛卡尔积的记录条数为两个表记录条数之积,数据过多,就不在此显示

2.2 左外连接(left [outer] join / left semi join)

左外连接  left outer join 又称为 left join

//left join以左表为基本表,右表关联不上用null代替
//left join

select * 
from u1 
left join u2 on u1.id =u2.id;

//left semi join以左表为基本表,右表关联不上的数据则查询不出来
//并且left semi join只能查出左表信息
//left semi hoin

select * 
from u1 
left semi join u2 on u1.id =u2.id;

2.3 全外连接(full [outer] join)

全外连接 full outer join 又称为 full join
//full join会查询出两个表所有的信息,无论字段是否为空 

select * 
from u1 
full  join u2 on u1.id =u2.id; 

3.联合(union/union all)

union ：将多个结果集合并，去重，排序
union all ：将多个结果集合并，不去重，不排序。

//union
select
d.id as deptno,
d.name as dname
from u1 d
union
select
e.id as deptno,
e.name as dname
from u2 e
;

//union all
select
d.id as deptno,
d.name as dname
from u1 d
union all
select
e.id as deptno,
e.name as dname
from u2 e
;

单个union 语句不支持：orderBy、clusterBy、distributeBy、sortBy、limit
单个union语句字段的个数要求相同，字段的顺序要求相同、字段类型需要一样(union)。
union子句不支持distribute by\order by (每个union子句嵌套中可以使用)

//只要加了limit,就必须嵌套使用
select
*
from(
select
*
from u1 uu1
limit 4)a
union
select 
* 
from(
select
*
from u2 uu2
limit 2)b
;