数据源:
a表
1 zhangsan
2 santi
3 wangwu
4 lisi
b表
1 zhangsan
2 Tian
3 wanglu
4 si
内连接
等值连接,这意味着连接的谓语条件只能使用等号。
HIve只允许在FROM子句中出现一张表,要进行连接操作,必须执行JOIN操作.
select * from emp_a a join emp_b b on (a.aName=b.bName);
结果:
+--------+-----------+--------+-----------+
| a.aid | a.aname | b.bid | b.bname |
+--------+-----------+--------+-----------+
| 1 | zhangsan | 1 | zhangsan |
| 4 | si | 4 | si |
+--------+-----------+--------+-----------+
外连接
内连接只显示两张表都存在的相同数据,而外连接则显示所有数据
其中外连接又分为左连接、右链接与全连接
左连接:显示左外面的表输出所有数据
右链接:显示右外面的表输出所有数据
左连接:Left Outer Join
select * from emp_a a left outer join emp_b b on (a.aName=b.bName);
结果:
+--------+-----------+--------+-----------+
| a.aid | a.aname | b.bid | b.bname |
+--------+-----------+--------+-----------+
| 1 | zhangsan | 1 | zhangsan |
| 2 | wangwu | NULL | NULL |
| 3 | sanni | NULL | NULL |
| 4 | si | 4 | si |
+--------+-----------+--------+-----------+
右连接:Right Outer Join
select * from emp_a a right outer join emp_b b on (a.aName=b.bName);
结果:
+--------+-----------+--------+-----------+
| a.aid | a.aname | b.bid | b.bname |
+--------+-----------+--------+-----------+
| 1 | zhangsan | 1 | zhangsan |
| NULL | NULL | 2 | Tian |
| NULL | NULL | 3 | wanglu |
| 4 | si | 4 | si |
+--------+-----------+--------+-----------+