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

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;