hive 内连接 左外连接 右外连接 满外连接 左半开连接 交叉连接 多表连接 隐式连接

半开连接 交叉连接 多表连接 隐式连接"/>

hive 内连接 左外连接 右外连接 满外连接 左半开连接 交叉连接 多表连接 隐式连接

目录

• • 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. 如果 表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 efull 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 einner join employee_address e_addron e.id = e_addr.id;

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 ainner join employee_address b
where a.id = b.id; -- 显示连接--B:
-- on：生成临时表时使用的条件 where：临时表生成后，对临时表进行过滤的条件
select *
from employee across join employee_address b on a.id = b.id;
select *
from employee across join employee_address b
where a.id = b.id;--C:
select *
from employee ainner join employee_address b on a.id = b.id;--一般不建议使用方法A和B，因为如果有where子句的话，往往会先进行笛卡尔积返回数据然后才根据where条件从中过滤，因此，如果两个表太大，将会非常非常慢，不建议使用。

hive 多表连接

求员工名字，所在部门编号，和公司所在位置

SELECT e.ename, d.deptno, l.loc_name
FROM emp eJOIN dept dON d.deptno = e.deptnoJOIN location lON 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 ainner join employee_address b
where a.id = b.id; -- 显示连接

例如有这样的一份数据

隐式连接

SELECT *
FROM (SELECT * FROM plant_carbon) t1,(SELECT * FROM plant_carbon) t2;