Hive基础知识（十四）：Hive的八种Join使用方式与优缺点

1. 等值 Join

Hive 支持通常的 SQL JOIN 语句。

1）案例实操

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

select e.ename,e.empno,d.dname from emp e join dept d on e.deptno = d.deptno;

2. 表的别名

1）好处

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

（2）使用表名前缀可以提高执行效率。

2）案例实操

合并员工表和部门表

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

3. 内连接

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

hive (hive3)> select e.ename,e.empno,d.dname from emp e join dept d on e.deptno = d.deptno;

4. 左外连接

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

hive (hive3)> select t.dname,e.ename,e.empno,e.deptno from emp e left join dept t on e.deptno = t.deptno;

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. 满外连接

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

select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

7. 多表连接

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

1700  Beijing
1800  London
1900  Tokyo

1）创建位置表

create table if not exists location(
loc int,
loc_name string
)row format delimited fields terminated by '\t';
hive (hive3)> load data local inpath '/home/zzdq/location.txt' into table location;

3）多表连接查询（查询员工姓名，部门名称，部门所在地）

select e.ename,d.dname,l.loc_name from emp e join dept d on e.deptno = d.deptno join location l on d.loc = l.loc;

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

注意：为什么不是表 d 和表 l 先进行连接操作呢？

这是因为 Hive 总是按照从左到右的顺序执行的。优化：当对3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的话，那么只会产生一个 MapReduce job。

8. 笛卡尔积

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

（1）省略连接条件

（2）连接条件无效

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

2）案例实操

hive (hive3)> select e.ename,d.dname from emp e join dept d;
Time taken: 26.495 seconds, Fetched: 56 row(s)