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. 如果 表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;

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;