hive的left/left outer和 left semi/right outer/full outer join

以下为两个测试数据表建表语句：

usetest;

 

DROPTABLEIF EXISTStable1;

createtabletable1(

    student_no      bigint  comment'学号',

    student_name    string  comment'姓名'

)

COMMENT'test 学生信息'

ROWFORMATDELIMITED

FIELDSTERMINATED BY'\t'

LINESTERMINATED BY'\n'

STOREDASTEXTFILE;

 

 

DROPTABLEIF EXISTStable2;

createtabletable2(

    student_no      bigint  comment'学号',

    class_no        bigint  comment'课程号'

)

COMMENT'test 学生选课信息'

ROWFORMATDELIMITED

FIELDSTERMINATED BY'\t'

LINESTERMINATED BY'\n'

STOREDASTEXTFILE;

 

load datalocalinpath'data_table1.txt'overwriteintotabletable1;

load datalocalinpath'data_table2.txt'overwriteintotabletable2;

测试数据为：

hive left join测试数据

测试1：left join

语句：

select * from table1 left outer join table2 on(table1.student_no=table2.student_no);

结果：

FAILED: Parse Error: line 1:22 cannot recognize input near ‘left’ ‘join’ ‘table2’ in join type specifier

我用的HIVE版本是0.8，不支持直接的left join写法；

测试2：left outer join

语句：

select * from table1 left outer join table2 on(table1.student_no=table2.student_no);

结果：

1 name1 1 11
1 name1 1 12
1 name1 1 13
2 name2 2 11
2 name2 2 14
3 name3 3 15
3 name3 3 12
4 name4 4 13
4 name4 4 12
5 name5 5 14
5 name5 5 16
6 name6 NULL NULL

可以看到left outer join左边表的数据都列出来了，如果右边表没有对应的列，则写成了NULL值。

同时注意到，如果左边的主键在右边找到了N条，那么结果也是会叉乘得到N条的，比如这里主键为1的显示了右边的3条。

测试3：right outer join

select * from table1 right outer join table2 on(table1.student_no=table2.student_no);

和left 相反，它是列出右边的表，如果左边没有与其对应的，则用NULL补齐。

测试4：left semi join

语句：

select * from table1 left semi join table2 on(table1.student_no=table2.student_no);

结果：

1 name1
2 name2
3 name3
4 name4
5 name5

可以看到，只打印出了左边的表中的列，规律是如果主键在右边表中存在，则打印，否则过滤掉了。

测试5：full outer join

select * from dave a full outer join bl b on (a.id = b.id);
左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充。

结论：

• hive的left outer join：如果右边有多行和左边表对应，就每一行都映射输出；如果右边没有行与左边行对应，就输出左边行，右边表字段为NULL；
• hive的left semi join：相当于SQL的in语句，比如上面测试3的语句相当于“select * from table1 where table1.student_no in (table2.student_no)”，注意，结果中是没有B表的字段的。