Hive之——Join操作

转载请注明出处：http://blog.csdn.net/l1028386804/article/details/78308571

两个字段，第一个字段是学生名字，第二个是所选课程的ID号，表名叫stu

name courseId
zs1,1
zs2,2
zs3,3
zs4,4

create table stu(name string, id int) row format delimited fields terminated by ',';
load data local inpath '/home/zkpk/test/hivetest/join/stu.txt' into table stu;

课程表course，第一个字段是课程的ID号，第二个是课程的名字

courseId courseName
2,math
3,english
5,chinese
6,physic

create table course(id int comment 'course id number', courseName string comment 'course name') row format delimited fields terminated by ',';
load data local inpath '/home/zkpk/test/hivetest/join/course.txt' into table course;

1、inner join

select stu.name, stu.id, course.coursename from stu join course on stu.id=course.id;
select stu.name, stu.id, course.coursename from stu inner join course on stu.id=course.id;

2、left outer join

select stu.name, stu.id, course.coursename from stu left outer join course on stu.id=course.id;

3、right outer join

select stu.name, course.id, course.coursename from stu right outer join course on stu.id=course.id;

4、Left semi join

left semi join 的运行结果：

Total MapReduce CPU Time Spent: 1 seconds 30 msec
OK
zs2	2
zs3	3

inner join 的运行结果：

Total MapReduce CPU Time Spent: 1 seconds 40 msec
OK
zs2	2	math
zs3	3	english

full outer join
select stu.name, stu.id, course.id, course.coursename from stu full outer join course on stu.id=course.id;

5、full outer join

select stu.name, stu.id, course.id, course.coursename from stu full outer join course on stu.id=course.id;