Hive 各种Join总计汇总

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

先准备数据

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

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

建表：

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 ',';

导入数据：

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;

left join

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

right join

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

outer join

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

left semi join

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