hive-DML-查询-join语句

2024-01-23 18:19:37

hive支持以下连接表的语句

join_table:
    table_reference [INNER] JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_reference LEFT SEMI JOIN table_reference join_condition
  | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)
 
table_reference:
    table_factor
  | join_table
 
table_factor:
    tbl_name [alias]
  | table_subquery alias
  | ( table_references )
 
join_condition:
    ON expression

等值连接（内连接）

只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；

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

左外连接

JOIN操作符左边表中符合ON/WHERE子句的所有记录将会被返回。右边表没有的记录补NULL

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

右外连接

右外连接：JOIN操作符右边表中符合ON/WHERE子句的所有记录将会被返回。左边表没有的记录补NULL

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

满外连接

将会返回所有表中符合ON/WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

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

取左表独有数据(A-B)

select e.empno, e.ename, e.deptno ,d.dname
from emp e
left join dept d 
on e.deptno = d.deptno 
where d.deptno is null;

select e.empno, e.ename, e.deptno 
from emp e
where e.deptno not in
    (
    select deptno
    from dept
    );

取右表独有数据

select d.deptno,d.dname
from emp e 
right join dept d 
on e.deptno = d.deptno
where e.deptno is null;

取左右两表独有数据

select e.empno, e.ename, nvl(e.deptno,d.deptno),d.dname
from emp e 
full join dept d 
on e.deptno = d.deptno
where e.deptno is null or d.deptno is null;

select *
from
	(
	select e.empno, e.ename, e.deptno , d.deptno, d.dname
	from emp e
	left join dept d 
	on e.deptno = d.deptno 
	where d.deptno is null
	union
	select e.empno, e.ename, e.deptno , d.deptno, d.dname
	from emp e 
	right join dept d 
	on e.deptno = d.deptno
	where e.deptno is null
	) tmp;

多表连接

SELECT e.ename, d.dname, l.loc_name
FROM   emp e 
JOIN   dept d
ON     d.deptno = e.deptno 
JOIN   location l
ON     d.loc = l.loc;

连接 n个表，至少需要n-1个连接条件。

笛卡尔积

笛卡尔积会在下面条件下产生

1). 省略连接条件

2). 连接条件无效

3). 所有表中的所有行互相连接

码农公寓