HIve SQL中的七种join

原始数据:

dept表:

10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700

emp表:

7369	MITH	CLERK	7902	1980-12-17	800.0	NULL	20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.0	300.0	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.0	500.0	30
7566	JONES	MANAGER	7839	1981-4-2	2975.0	NULL	20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.0	1400.0	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.0	NULL	30
7782	CLARK	MANAGER	7839	1981-6-9	2450.0	NULL	10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.0	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.0	NULL	10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.0	NULL	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.0	NULL	20
7900	JAMES	CLERK	7698	1981-12-3	950.0	NULL	30
7902	FORD	ANALYST	7566	1981-12-3	3000.0	NULL	40
7934	MILLER	CLERK	7782	1982-1-23	1300.0	NULL	50

创建部门表:

create table if not exists dept( deptno int,
dname string, loc int
)
row format delimited fields terminated by '\t';

创建员工表:

create table if not exists dept( deptno int,
dname string, loc int
)
row format delimited fields terminated by '\t';

导入数据:

load data local inpath '/opt/module/datas/dept.txt' into table dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;

Join语句:

使用表的别名的好处:
(1)可以简化查询
(2)使用表名前缀可以提高执行效率

1.内连接

//内连接:只有进行连接的两个表中都存在与连接条件相匹配的数据才会保留下来
hive (default)> select e.empno, e.ename, d.deptno, d.dname 
from emp e
join dept d 
on e.deptno = d.deptno;	

注意:在这里面,如果select后面的字段是两张表都有的,则要加上别名,例如:别名.字段名(e.ename);如果字段只有一个表里面单独有的字段,则可以不用加别名,例如:ename字段是emp单独有的字段,可以不用加别名。但是在实际的开发过程中,每个字段都尽量加上别名,因为写上别名的查询效率要高,写了别名就到指定表里面查找,不写多张表都去查找。

2.左外连接

//左外连接：(以左表为主)JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回
hive (default)> select e.empno, e.ename, d.deptno 
from emp e 
left join dept d 
on e.deptno = d.deptno;

3.右外连接

//右外连接：(以右表为主)JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。
hive (default)> select e.empno, e.ename, d.deptno 
from emp e 
right join dept d 
on e.deptno = d.deptno;

4.满外连接

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

//所有的数据
hive (default)> select e.empno, e.ename, d.deptno 
from emp e 
full join dept d 
on e.deptno = d.deptno;

注意:满外连接的时候,是要把所有的字段都显示,但是在select的时候遇到公共字段,而我们取了别名,只能从取了别名的表中取数据,就导致另一个表里面的数据就缺少了.例如:A表和B表都有ID字段,A的id:10,20,30.B的id:10,20,40,这里面在select的时候选A.id或者B.id都不好,因为总会导致30或者40这个数据缺失.这里介绍了一个函数叫做,NVL<A,B>,如果A为null,则用B,如果B为Null,则用A.上面的情况可以写成:NVL(A.id,B.id).

5. 取左表独有部分

hive (default)>select  e.empno, e.ename, d.deptno 
from emp e
where e.deptno not in 
(select deptno
from dept
);

上面这种方法是通过子查询,还有另外一种写法:

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

6. 取右表独有部分

 hive (default)>select d.deptno,d.dname
 from  emp e
 right join dept d
 on e.deptno=d.deptno
 where e.deptno is null;

7.查询两张表独有的数据

 hive (default)>select e.empno,e.ename,nvl(e.deptno,d.deptno),d.dname
 from emp e
 full join  dept d 
 on e.deptno = d.deptno
 where e.deptno is nulll or d.deptno is null; 

---

笛卡尔积

hive中还有一个额外的连接方式,叫做笛卡尔积。
笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
举个例子来描述笛卡尔积,A表中有3条数据.B表中有4条数据,采用笛卡尔积来计算,则一共有3*4=12条数据。

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

---

穿插一个多表连接的知识点:

create table if not exists location( loc int,
loc_name string
)
row format delimited fields terminated by '\t';

表的数据:

1700	Beijing
1800	London
1900	Tokyo

//查询员工姓名,部门名称以及部门所在城市名称
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总是按照从左到右的顺序执行的。