09 | Hive Join语句

6.4 Join语句

6.4.1 等值Join

Hive支持通常的sql join语句，但是只支持等值连接，不支持非等值连接。

1）案例实操

（1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称。

hive (default)>

select

    e.empno,

    e.ename,

    d.dname

from emp e

join dept d

on e.deptno = d.deptno;

hive sql执行过程：

6.4.2 表的别名

1）好处

（1）使用别名可以简化查询。

（2）区分字段的来源。

2）案例实操

合并员工表和部门表。

hive (default)>

select

    e.*,

    d.*

from emp e

join dept d

on e.deptno = d.deptno;

6.4.3 内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

hive (default)>

select

    e.empno,

    e.ename,

    d.deptno

from emp e

join dept d

on e.deptno = d.deptno;

6.4.4 左外连接

左外连接：join操作符左边表中符合where子句的所有记录将会被返回。

hive (default)>

select

    e.empno,

    e.ename,

    d.deptno

from emp e

left join dept d

on e.deptno = d.deptno;

6.4.5 右外连接

右外连接：join操作符右边表中符合where子句的所有记录将会被返回。

hive (default)>

select

    e.empno,

    e.ename,

    d.deptno

from emp e

right join dept d

on e.deptno = d.deptno;

6.4.6 满外连接

满外连接：将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代。

hive (default)>

select

    e.empno,

    e.ename,

    d.deptno

from emp e

full join dept d

on e.deptno = d.deptno;

6.4.7 多表连接

注意：连接n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

数据准备，在/opt/module/hive/datas/下：vim location.txt

部门位置id  部门位置

[atguigu@hadoop102 datas]$ vim location.txt

1700   北京

1800   上海

1900   深圳

1）创建位置表

hive (default)>

create table if not exists location(

    loc int,           -- 部门位置id

    loc_name string   -- 部门位置

)

row format delimited fields terminated by '\t';

2）导入数据

hive (default)> load data local inpath '/opt/module/hive/datas/location.txt' into table location;

3）多表连接查询

hive (default)>

select

    e.ename,

    d.dname,

    l.loc_name

from emp e

join dept d

on d.deptno = e.deptno

join location l

on d.loc = l.loc;

大多数情况下，Hive会对每对join连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l进行连接操作。

注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。

6.4.8 笛卡尔集

1）笛卡尔集会在下面条件下产生

（1）省略连接条件

（2）连接条件无效

（3）所有表中的所有行互相连接

2）案例实操

hive (default)>

select

    empno,

    dname

from emp, dept;

hive sql执行过程：

6.4.9 联合（union & union all）

1）union&union all上下拼接

union和union all都是上下拼接sql的结果，这点是和join有区别的，join是左右关联，union和union all是上下拼接。union去重，union all不去重。

union和union all在上下拼接sql结果时有两个要求：

（1）两个sql的结果，列的个数必须相同

（2）两个sql的结果，上下所对应列的类型必须一致

2）案例实操

将员工表30部门的员工信息和40部门的员工信息，利用union进行拼接显示。

hive (default)>

select

    *

from emp

where deptno=30

union

select

    *

from emp

where deptno=40;