Hive-2.HiveQL查询中JOIN语句

本文主要是介绍Hive-2.HiveQL查询中JOIN语句，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

Hive支持常用到的SQL JOIN语句，但是只支持等值连接。

4.1 INNER JOIN

内连接（INNER JOIN）中，只有进行连接的两个表中都存在与连接标准相匹配的数据才会展示出来。例如：查询每个部门下的员工列表

查询语句：

select t.deptname,e.name

from employees e inner join dept t

on e.deptno=t.deptno

查询结果：

t.deptname e.name

bss wangwu1

bss wangwu2

pso wangwu3

pso wangwu4

4.2 JOIN优化

Hive同时假定查询中的最后一个表示最大的那个表。在对每行记录进行连接操作时候，他们会尝试将其他的表缓存起来，然后扫描最后的那个表进行计算。因此，用户需要保证连接的表的大小从左到有是依次增加的。

假定dept是小表，employees是大表，一般的解决方案：

select t.deptname,e.name

from dept t join employees e

on t.deptno=e.deptno

但是，用户并非总是要讲最大的放置在查询语句的最后面。这是因为Hive还提供了一个“标记”机制来显示地告知查询优化器哪张表是最大的表，使用方式如下：

select /*+STREAMTABLE(e1)*/ t.deptname,e1.name

from employees e1 join dept t on t.deptno=e1.deptno

where e1.country='China';

除上述join的优化外，也可以通过map-site join进行优化。

4.3 LEFT/RIGHT OUTER JOIN

left join 是left outer join的简写，left join默认是outer属性的。

通过两种方法分别统计查询每个部门的员工人员列表

l 使用left join：左边关联的表全部显示出来

select t.deptname,case when e.name is NULL then '该部门暂无人员' else e.name end as name

from dept t left join employees e

on t.deptno=e.deptno

输出结果：

t.deptname name

bss wangwu1

bss wangwu2

pso wangwu3

pso wangwu4

bdx 该部门暂无人员

l 使用right join：右边的表数据全部显示出来

select t.deptname,case when e.name is NULL then '该部门暂无人员' else e.name end as name

from employees e right join dept t

on e.deptno=t.deptno;

输出结果：

t.deptname name

bss wangwu1

bss wangwu2

pso wangwu3

pso wangwu4

bdx 该部门暂无人员

4.5 LEFT SEMI-JOIN

查询所有的员工信息，这些员工的信息所属部门必须在dept中

两种方案可以实现：

方案1:通过INNER JOIN

select e.*

from dept t inner join employees e

on t.deptno=e.deptno

方案2：通过LEFT SEMI JOIN

select e.*

from employees e

left semi join

dept t

on (

e.deptno=t.deptno

and t.deptno='d001')

注意： SELECT和WHERE语句中不能饮用到右边表中的字段

通过两种实现对比，一般情况下LEFT SEMI JOIN和INNER JOIN要更高效，原因如下：对于左表中的一条记录，在右边表中一旦找到匹配的记录，Hive就立即停止扫描。而不是匹配上要再次全表扫描右边的表。需要注意的：通过这两个方案还有一个区别，就是若右边的表dept存在重复的记录，查询的结果是不一样的，原因就在于在查找dept记录时候存在是否终止的问题。