目录
- hive outline
- hive 内连接 inner join
- hive 左外连接 left join
- hive 右外连接 right join
- hive 满外连接 full join
- hive left semi join
- hive cross join
- hive 多表连接
- hive 隐式联接
hive outline
链接
hive 内连接 inner join
内连接:
2个表的交集,表b中3条记录可以匹配上表a中的1条,left join后记录会有3条,inner join后记录只会有1条
求员工编号,员工名字和所在部门编号
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
hive 左外连接 left join
左外连接:
以左表为基准
- 如果 表1 中的某条记录在 表2 中刚好只有一条记录可以匹配,那么在返回的结果中会生成一个新的行
- 如果 表1 中的某条记录在 表2 中有 N 条记录可以匹配,那么在返回结果中也会生成 N 个新的行,新的N行中会把 表1 的字段进行N次重复
- 如果 表1 中的某条记录在 表2 中没有匹配的记录,那么在返回结果中仍然会生成一个新的行,只是该行所包含的 表2 的字段值都是 NULL
求员工编号,员工名字和所在部门编号
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
hive 右外连接 right join
求员工编号,员工名字和所在部门编号
右外连接:
以右表为基准,原理同上
hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
hive 满外连接 full join
求员工编号,员工名字和所在部门编号
满外连接:
类似于求并集(右表中有,左表没有,左表补为NULL)(左表中有,右表没有的,右表补为NULL)
select e.id, e.name, e_a.city, e_a.street
from employee e
full outer join employee_address e_a on e.id = e_a.id;
hive left semi join
功能:
类似于inner join,但只返回左表的结果
优点:
比 inner join 效率高一些
select * from employee;
select * from employee_address;
select e.*,e_addr.*
from employee e
inner join employee_address e_addr
on e.id = e_addr.id;
id | name | deg | salary | dept |
1204 | prasanth | dev | 30000 | AC |
1206 | kranthi | admin | 20000 | TP |
1201 | gopal | manager | 50000 | TP |
1202 | manisha | cto | 50000 | TP |
hive cross join
cross join:交叉连接 ,会返回两个表的笛卡尔积
注意:
cross join就是无条件的inner join
--下列A、B、C 执行结果相同,但是效率不一样:
--A:
select a.*, b.*
from employee a,
employee_address b
where a.id = b.id; -- 隐式连接
select a.*, b.*
from employee a
inner join employee_address b
where a.id = b.id; -- 显示连接
--B:
-- on:生成临时表时使用的条件 where:临时表生成后,对临时表进行过滤的条件
select *
from employee a
cross join employee_address b on a.id = b.id;
select *
from employee a
cross join employee_address b
where a.id = b.id;
--C:
select *
from employee a
inner join employee_address b on a.id = b.id;
--一般不建议使用方法A和B,因为如果有where子句的话,往往会先进行笛卡尔积返回数据然后才根据where条件从中过滤,因此,如果两个表太大,将会非常非常慢,不建议使用。
hive 多表连接
求员工名字,所在部门编号,和公司所在位置
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;
hive 隐式联接
从hive 0.13.0开始,支持隐式联接表示法(默认是inner join)。这允许FROM子句连接以逗号分隔表列表
,而省略JOIN关键字。例如
select a.*, b.*
from employee a,
employee_address b
where a.id = b.id; -- 隐式连接
select a.*, b.*
from employee a
inner join employee_address b
where a.id = b.id; -- 显示连接
例如有这样的一份数据
隐式连接
SELECT *
FROM (SELECT * FROM plant_carbon) t1,
(SELECT * FROM plant_carbon) t2;