查询（二）Join语句

一、等值Join

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。

案例实操：

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

hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

同样与

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

二、表的别名

1.好处

（1）使用别名可以简化查询。

（2）使用表名前缀可以提高执行效率。

2.案例实操

合并员工表和部门表

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno =

三、内连接

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

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on

四、左外连接

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

例如，下面以emp表为准，如果dept表中没有匹配的就附上null

hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on

五、右外连接

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

例如，下面以dept表为准，如果emp表中没有匹配的就附上null

hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

六、满外连接

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

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

七、多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

1.数据准备

2.创建位置表

create table if not exists location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';

3.导入数据

hive (default)> load data local inpath '/opt/module/hiveTest/location.txt' into table

4.多表连接查询distinct

hive (default)>SELECT e.ename, d.deptno, l. loc_name
FROM   emp e
JOIN
ON 
JOIN 
ON

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。

注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。

八、笛卡儿积

1.笛卡尔集会在下面条件下产生:

（1）省略连接条件

（2）连接条件无效

（3）所有表中的所有行互相连接

2.案例实操

hive (default)> select empno, deptno from emp, dept;

FAILED: SemanticException Column deptno Found in more than One Tables/Subqueries

九、连接谓词中不支持or

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno or