-- dept表
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700

-- emp表
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10

Join语句

内连接

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

-- dept 和emp表 相同的deptno
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d
on e.deptno = d.deptno;

左外连接

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

-- 根据员工表和部门表中的部门编号相等，查询员工编号，员工名称，部门编号和部门名称，则没有部门名称则不中NULL
hive (default)>select
e.empno, e.ename, d.deptno,d.ename
from emp e
left join dept d
on e.deptno =d.deptno;
where e.deptno is null -- e.deptno是右表中没有的数据，也就是说如果过滤右表没右的数据，就可以得到 左右表连接后仅选择左表的数据且不包含右表的

右外连接

右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。
和左外连接一个意思，变的是把left换成right

全连接

-- 查询所以员工信息以及所有部门信息
hive (default)>select
e.empno, e.ename, d.deptno,d.ename
from
  emp e
full join
  dept d
on e.deptno =d.deptno;

-- nvl函数（x,y）如果x为null 则找y值
hive (default)>select
e.empno, e.ename, nvl(e.deptno,d.deptno),d.ename
from
  emp e
full join
  dept d
on e.deptno =d.deptno;

查询左表独有数据(左连接)

-- 查询员工信息，所在部门信息为NULL
hive (default)>
selecte.empno, e.ename, d.deptno,d.ename
from
  emp e
left join
  dept e
on e.deptno=d.deptno
where d.deptno is null；
-- 因为右表是不为NULL的，所以where会过滤掉当右表不为NULL的数据，从而会去掉两个表deptno共同的数据，保留左表独有数据

--第二种方法（第一种较为简单）
hive (default)>
selecte.empno, e.ename, d.deptno,d.ename
from
  emp e
left join
  dept d
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;
-- 第二种方法（union拼接）
select
*
from(
select e.empno, e.ename, d.deptno,d.ename
from
  emp e
left join
  dept d
on e.deptno=d.deptno
where d.deptno is null
union
selecte.empno, e.ename, d.deptno,d.ename
from
   emp e
right join
   dept d
on e.deptno=d.deptno
where e.deptno is null;
)tmp; --相当于给这个重命名为tmp

union,union all：使用的时候字段要一样）
union会去重，如果需求本身不存在重复数据，使用两者效果相同，使用union all 效率更高

原文地址：http://www.cnblogs.com/catch-autumn/p/16859091.html