hive：join连接

一,Join连接方式介绍

SQL join 用于把来自两个或多个表的行结合起来。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

二.数据准备

1.模拟数据

emp表

7369	SMITH	CLERK	7902	1980-12-17	800.00		20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.00		30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00		10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20
7839	KING	PRESIDENT		1981-11-17	5000.00		10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00		20
7900	JAMES	CLERK	7698	1981-12-3	950.00		30
7902	FORD	ANALYST	7566	1981-12-3	3000.00		20
7934	MILLER	CLERK	7782	1982-1-23	1300.00		50

dept表

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

2.创表语句

（1）创建部门表

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

（2）创建创建员工表

create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';

三.七种join方式举例

备注：Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。

1.内连接

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

 

2.左外连接

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

 

 

 3.右外连接

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

 4.满外连接

备注：引入nvl函数，做空值判断

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

5.join取左独有的数据

方法一：差集 推荐用这个方法就差值

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

 

方法二：not in  效率低  不推荐

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

 

 6.join取右独有的

select 
    d.deptno,d.dname
from 
    emp e right 
join 
    dept d 
on 
    e.deptno=d.deptno 
where 
    e.deptno is null;

 7.join取左右两表独有的数据

方法一：

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 null or d.deptno is null;

 方法二：

如果需求需要去重，只能用union

如果需求不需要去重，用union all

如果需求本身不存在重复数据，使用union，union all效果相同，使用union all

select 
    * 
from
(
select 
    e.empno,e.ename,e.deptno,d.deptno,d.dname 
from 
    emp e 
left join 
    dept d 
on 
    e.deptno = d.deptno 
where 
    d.deptno is null
union
select 
    e.empno,e.ename,e.deptno,d.deptno,d.dname 
from 
    emp e 
right join 
    dept d 
on 
    e.deptno = d.deptno 
where 
    e.deptno is null
)tmp;

 8、多表连接

（1）准备数据

1700	Beijing
1800	London
1900	Tokyo

（2）创建表

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

（3）导入数据

load data local inpath '/opt/module/data/loc.txt' into table location;

（4）全表查询

SELECT 
    e.ename, d.deptno, l.loc_name
FROM   
    emp e 
JOIN   
    dept d
ON     
    d.deptno = e.deptno
JOIN   
    location l
ON     
    d.loc = l.loc;

 三.笛卡尔积

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

         a.省略连接条件

         b.连接条件无效

         c.所有表中的所有行互相连接

  2.例子

select empno, dname from emp, dept;