06：Hive从0到1系列学习：查询之join连接

查询（重点）

查询的基本语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list| [DISTRIBUTE BY col_list] [SORT BY col_list]]
[LIMIT number]

Join语句

①等值join

根据dept和emp中的deptno相等，查询empno、deptno、ename和dname

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

②内连接：join

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

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

③左外连接：left join

将左边表的所有内容全部返回，如果右边表不存在，就返回数据null

比如：返回emp表中所有数据的empno、ename、deptno、dname

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

思考：如果想要得到只有A表独自有的该怎么办？

分析以下左外连接的结果

如果是A独有的，也就是有关B的赋值就是null，只要把B表为null的过滤出来，就是A所独有的

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

④右外连接：right join

返回右边表的所有数据，左边表中如若没有数据的用null代替

比如：返回所有部门的deptno、dname、empno、ename

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

思考：如果想要得到B表所独有的该怎么办？

同理，也是过滤出A表为空的就可以得到B表所独有的

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

⑤满外连接：full join

会返回两张表所有的数据，若没有就是null代替

比如：返回emp和dept两张表的所有数据

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

⑥多表连接

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

比如返回emp表和dept表和location表相同的数据

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

⑦笛卡尔积

会使得一个表中的数据逐次全部匹配另一个表中的数据

笛卡尔积产生的原因

（1）省略连接条件

（2）连接条件无效

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

比如不用连接条件，之间返回emp和dept的数据

select 
	empno, 
	dname 
from emp, dept;