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;
1

hive 左外连接 left join

左外连接：以左表为基准

1. 如果 表1 中的某条记录在 表2 中刚好只有一条记录可以匹配，那么在返回的结果中会生成一个新的行
2. 如果 表1 中的某条记录在 表2 中有 N 条记录可以匹配，那么在返回结果中也会生成 N 个新的行，新的N行中会把 表1 的字段进行N次重复
3. 如果 表1 中的某条记录在 表2 中没有匹配的记录，那么在返回结果中仍然会生成一个新的行，只是该行所包含的 表2 的字段值都是 NULL

求员工编号，员工名字和所在部门编号

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

hive 右外连接 right join

求员工编号，员工名字和所在部门编号

右外连接：以右表为基准，原理同上

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

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;
1
2
3

hive left semi join

功能：类似于inner join，但只返回左表的结果
优点：比 inner join 效率高一些

select * from employee;
select * from employee_address;
1
2

select e.*,e_addr.*
from employee e
         inner join employee_address e_addr
                    on e.id = e_addr.id;
1
2
3
4

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条件从中过滤，因此，如果两个表太大，将会非常非常慢，不建议使用。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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;
1
2
3
4
5
6

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; -- 显示连接
1
2
3
4
5
6
7
8
9

例如有这样的一份数据

隐式连接

SELECT *
FROM (SELECT * FROM plant_carbon) t1,
     (SELECT * FROM plant_carbon) t2;
1
2
3