【大数据入门 | Hive】Join语句

1. 等值join

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

1）案例实操

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

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

2. 内连接

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

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

3. 左外连接

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

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

4. 右外连接

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

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

5. 满外连接

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

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

6. 多表连接

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

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

部门位置id  部门位置

[atguigu@hadoop102 datas]$ vim location.txt1700	北京
1800	上海
1900	深圳

1）创建位置表

hive (default)>
create table if not exists location(loc int,           -- 部门位置idloc_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进行连接操作。

7. 笛卡尔积

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

（1）省略连接条件

（2）连接条件无效

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

2）案例实操

hive (default)> 
select empno, dname 
from emp, dept;

8. 联合（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）案例实操

hive (default)> 
select *
from emp
where deptno=30
union
select *
from emp
where deptno=40;