join语句
hql中的join语句和sql中的语句相似,但是在Hive中只支持等值连接,不支持非等值连接。
案例:
select s.s_id,s.s_score,stu.s_name,stu.s_birth
from score s join student stu
on s.s_id = stu.s_id;
如上命令 通过等值连接建立俩张表之间的关系,join使得俩张表进行“连接合并” , 然后在连接后的“新表”基础上在查询id,分数,名字,生日等。
别名
- 别名可以简化查询
- 表名前缀可以提高执行效率
案例:
select * from techer t join course c on t.t_id = c.t_id;
1.内连接 inner join
内连接简单理解,就是俩张表的交集
案例演示:
- 这是原始的俩张表 a、b
±------±--------±-+
| a.id | a.name |
±------±--------±-+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 7 | y |
| 8 | u |
±------±--------±-+
±------±--------±-+
| b.id | b.name |
±------±--------±-+
| 2 | bb |
| 3 | cc |
| 7 | yy |
| 9 | pp |
±------±--------±-+
我们通过内连接查询返回结果
select * from a inner join b on a.id=b.id;
结果如下:
select * from a inner join b on a.id=b.id;
±------±--------±------±--------±-+
| a.id | a.name | b.id | b.name |
±------±--------±------±--------±-+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
±------±--------±------±--------±-+
2.外连接
还是上方案例的a、b表演示
select * from a full outer join b on a.id=b.id;
结果如下:
±------±--------±------±--------±-+
| a.id | a.name | b.id | b.name |
±------±--------±------±--------±-+
| 3 | c | 3 | cc |
| NULL | NULL | 9 | pp |
| 1 | a | NULL | NULL |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 2 | b | 2 | bb |
| 8 | u | NULL | NULL |
±------±--------±------±--------±-+
3.左连接 && 右连接
select * from a left join b on a.id=b.id;
结果如下 :
优先返回左表的记录信息,右表有交集的则返回,无交集的则由空值NULL补全。
±------±--------±------±--------±-+
| a.id | a.name | b.id | b.name |
±------±--------±------±--------±-+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
±------±--------±------±--------±-+
同理,,右连接则相反。
优先返回右表大的记录信息,左表有交集则返回,无交集则由空值NULL补全。
select * from a right join b on a.id=b.id;
结果如下:
±------±--------±------±--------±-+
| a.id | a.name | b.id | b.name |
±------±--------±------±--------±-+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
| NULL | NULL | 9 | pp |
±------±--------±------±--------±-+
4.多表连接
注意事项:
连接n个表,至少需要n-1个条件。
案例:
多表连接查询,查询老师对应的课程,以及对应的分数,对应的学生。
命令如下
select * from teacher t
left join course c
on t.t_id = c.t_id
left join score s
on s.c_id = c.c_id
left join student stu
on s.s_id = stu.s_id;
大多数情况下,Hive会对每个JOIN连接对象启动一个MapReduce任务。上面例子中,首先会启动一个MapReduce job对表teacher和表course进行连接操作,然后会在启动一个MapReduce job将第一个MapReduce job的输出和表score进行连接操作。所以说在多表连接的时候,会启动多个MapReduce任务。