Hive hql语句 join 内连接 外连接 左连接 右连接

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任务。