hive-3 表连接和查询语句简介

 

 

1 表连接:
 
Hive只支持等值连接， 诸如 > < 都不支持。
如果连接语句中有WHERE子句，会先执行JOIN子句，再执行WHERE子句, 即 where是过滤join之后的内容。

 

数据：

select * from user;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
+------+----------+

 select * from job;
+------+----------+--------+
| id   | job      | userid |
+------+----------+--------+
|    1 | engineer |      1 |
|    2 | painter  |      2 |
|    3 | painter  |      4 |
+------+----------+--------+

a) 内连接:

内连接指的是把符合两边连接条件的数据查询出来。
执行以下语句
select * from user join job on user.id=job.user_id;
+------+----------+------+----------+--------+
| id   | name     | id   | job      | userid |
+------+----------+------+----------+--------+
|    1 | zhangsan |    1 | engineer |      1 |
|    2 | lisi     |    2 | painter  |      2 |
+------+----------+------+----------+--------+

 

b) 左边表的数据全部显示，如果右边表没有对应数据的话，则右侧用null表示。

select * from user left outer join job on user.id=job.userid;

+------+----------+------+----------+--------+
| id   | name     | id   | job      | userid |
+------+----------+------+----------+--------+
|    1 | zhangsan |    1 | engineer |      1 |
|    2 | lisi     |    2 | painter  |      2 |
|    3 | wangwu   | NULL | NULL     |   NULL |
+------+----------+------+----------+--------+

 

c) 右外连接: 右侧表的数据全部显示，左侧表的数据如果查询条件不符合，则用null表示
执行以下语句
select * from user right outer join job on user.id=job.user_id;
+------+----------+------+----------+--------+
| id   | name     | id   | job      | userid |
+------+----------+------+----------+--------+
|    1 | zhangsan |    1 | engineer |      1 |
|    2 | lisi     |    2 | painter  |      2 |
| NULL | NULL     |    3 | painter  |      4 |
+------+----------+------+----------+--------+

 

d)
全外连接
执行以下语句， 左右两侧数据全部显示
select * from user full outer join job on user.id=job.user_id;
+------+----------+------+----------+--------+
| id   | name     | id   | job      | userid |
+------+----------+------+----------+--------+
|    1 | zhangsan |    1 | engineer |      1 |
|    2 | lisi     |    2 | painter  |      2 |
|    3 | wangwu   | NULL | NULL     |   NULL |
| NULL | NULL     |    3 | painter  |      4 |
+------+----------+------+----------+--------+

 

 e) 左半连接
执行以下语句
select * from user left semi join job on user.id=job.user_id;

虽然语法看着像左外连接的语法，但是显示中只有user表的信息，没有job表的信息。

以上语句相当于如下语句
select * from user where user.id in (select user_id from job);
    在where子句使用谓词in的时候，in前面的字段一定带有表名,.因为查询很懒，想不到会将第一个表自动加

到查询条件id中，因此id就不知道是哪个表的id会报错

 

f) 笛卡尔积
    执行以下语句
    select * from user join job;

    笛卡尔积的结果一般不会被直接使用，而是使用其子集。比如在员工考勤统计中，可以做所有员工与所有日期的笛卡尔积，可以非常直观的看到缺勤情况。

 

g) join和where的顺序：

Where子句的执行顺序是位于join子句之后的。大家考虑一下如下两个语句的输出不同。
select * from user left  join job on user.id=job.user_id where job.id =3;
select * from user left  join (select * from job where id =3) job on user.id=job.user_id;
第一条语句无任何输出，因为执行select * from user left  join job on user.id=job.user_id产生的两个记录在执行
过滤 where job.id =3时，没有符合条件的记录。

第二条语句产生三条记录，select * from job where id =3) job是说将查询结果select * from job where id =3
作为表，别名为job，此时结果 左侧uesr 三条全部输出，右侧job结果都为null

 

 

2 排序：

排序指的是对查询结果的显示的排列顺序。
Hive支持ORDER BY子句和SORT BY子句。
当可能有多个reduce任务时，ORDER BY是在一reduce任务中进行排序；
SORT BY是在多个reduce任务内部进行排序，每个reduce任务自己排序，不管全局是否有序。

DISTRIBUTE BY会与SORT BY 一起使用，目的是在SORT BY 排序时把相同分类的数据放到一个reduce中进行排序。
CLUSTER BY 是Distribute by和Sort by 的缩写形式。