Hive 各种Join总计汇总

关于hive中的各种join,下边做个总结

先准备数据

1
2
3
4
5
6
7
8
9
10
11
12
13
# a.txt
1,a
2,b
3,c
4,d
7,y
8,u

# b.txt
2,bb
3,cc
7,yy
9,pp

建表:

1
2
3
4
5
create table a(id int,name string)
row format delimited fields terminated by ',';

create table b(id int,name string)
row format delimited fields terminated by ',';

导入数据:

1
2
3

load data local inpath '/home/hadoop/a.txt' into table a;
load data local inpath '/home/hadoop/b.txt' into table b;

实验:

inner join

select * from a inner join b on a.id=b.id;

snapshot.png

left join

select * from a left join b on a.id=b.id;

snapshot.png

right join

select * from a right join b on a.id=b.id;

snapshot.png

outer join

select * from a full outer join b on a.id=b.id;

outer.png

left semi join

select * from a left semi join b on a.id = b.id;

snapshot.png