08_Hive中的各种Join操作

1.关于hive中的各种join

　　Hive中有许多的Join操作，例如：LEFT、RIGHT和FULL OUTER JOIN，INNER JOIN，LEFT SEMI JOIN等；

1.1.准备两组数据：

a.txt  　　 b.txt           

1,a    　　2,bb
2,b    　　3,cc
3,c    　　7,yy
4,d    　　9,pp
7,y
8,u

 1.2.在Hive中建表：

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.3.在Hive表中导入数据：

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

1.4.inner join内连接：select * from a inner join b on a.id=b.id;

　　

　　相当于是求交集

1.5.left join左连接：select * from a left join b on a.id=b.id;(左边的表的记录都打印出来)

　　

1.6.right join右连接：select * from a right join b on a.id=b.id;(右边的表的记录都打印出来)

1.7.full outer join右连接：select * from a full outer join b on a.id=b.id;

　　

　　两边的记录都会出来，如果没有的就填NULL

1.7.left semi outer join连接：select * from a left semi join b on a.id = b.id;

　　

　　SEMI在Hive中用的很广泛，用于实现Exist IN 子查询的。LEFT SEMI JOIN相当于Inner Join中取一半

----LEFT SEMI JOIN ：Hive 当前没有实现 IN/EXISTS 子查询，可以用 LEFT SEMI JOIN 重写子查询语句

 例如：重写以下子查询为LEFT SEMI JOIN

　　SELECT a.key, a.value FROM a WHERE a.key exist in (SELECT b.key FROM B);

 　 可以被重写为：SELECT a.key, a.value FROM a LEFT SEMI JOIN b on (a.key = b.key);

查询与“刘晨”在同一个系学习的学生:
　　hive> select s1.Sname from student s1 left semi join student s2 on s1.Sdept=s2.Sdept and s2.Sname='刘晨';