Oracle 表连接

概述

• 即查询的时候同时需要多张表（特别是存在外键关系的），此时需要多张表之间的值进行连接。
• 目前 SQL 标准提出过两种连接查询，第一种是较早的 SQL92 标准，第二种是目前使用广泛的较新的 SQL99 标准。
• 92 形式简单，但编写较为冗长， 99 不仅在底层得到优化，而且形式看上去更加一目了然，逻辑性更强，一般建议使用 99 标准。

92语法

• 多张表需要全部放在 from 之后，所有的连接条件都放在 where 当中，因此SQL92 中的等值连接，非等值链接，外连接等等其实只是 where 条件的筛选结构。
• 很多时候需要为表取别名（1，简化表名 2，可能存在自连接的情况）
• 连接的原理：按照 from 后面表的出现顺序，前面的表作为内存的 for 循环，后出现的表作为外层的 for 循环。

等值连接

• 不同表的键之间用=号进行连接。

实例

• 查询员工的信息以及所在部门的信息。
• 查询的数据：员工信息，部门信息。
• 数据来源：emp, dept
• 连接条件：emp.deptno=dept.deptno

select *
from emp e,dept d
where e.deptno=d.deptno;

非等值连接

• 不同表的键之间用!=, >, <, <>, between and进行连接。

实例

• 查询每一个员工的姓名，工资金额，入职时间，对应工资等级。
• 查询数据：ename, sal, hiredate, grade
• 数据来源：emp, salgrade
• 连接条件：emp, sal between losal and hisal

select ename, sal, hiredate, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

自连接

• 查询出每一个员工（有上级存在的员工）自己的信息，以及上级的信息。
• 查询信息：员工信息，上级信息。
• 数据来源：emp e, emp m
• 连接条件：e.mgr = m.empno

select *
from emp e,emp m
where e.mgr = m.empno;

外连接

• 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。
• 外联接可以是左向外联接，右向外联接或完整外部联接。
• 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
• 右向外联接是左向外联接的反向联接，将返回右表的所有行，如果右表的某行在左表中没有匹配行，则将为左表返回空值。
• +：带+的表为从表，对立面的表为主表。

实例

• 查询每一个部门的信息及对应人数。

select *
  from dept d, (select count(*), deptno from emp group by deptno) c
 where d.deptno = c.deptno(+);

99语法

cross join

• 交叉连接，可实现笛卡尔积。

实例

• 查询所有员工姓名及所在部门的名称。

select * from emp cross join dept;

natural join

• 自然连接（Natural join）是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。

实例

• 查询所有员工姓名，员工编号，所属的部门编号，部门名称。

select ename,empno,deptno,dname from emp natural join dept;

• 查询部门10的员工名称，部门编号，部门名称。

select ename, deptno, dname from emp natural join dept where deptno = 10;

join using()

• 有一种特殊情况，当两个要关联表的字段名是一样的，可以使用 USING，可减少 SQL 语句的长度。
• 自然连接也可以用join...using来实现。

select ename, deptno, dname
from emp
join dept
using (deptno)
where deptno = 10;

join on

• on连接，可做等值连接，非等值连接，自连接，可以解决一切连接，关系列必须要区分。

实例

• 使用on可以做等值连接。

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

• 使用on还可以做非等值连接。

select ename,sal,deptno,grade from emp e join salgrade s on
sal between losal and hisal;

• 查询30部门员工姓名，工资，部门编号，工资等级，部门名称。
• 数据来源：emp e, salgrade s, dept d
• 连接条件：sal between losal and hisal e.deptno=d.deptno
• 题意条件：e.deptno=30

select ename, sal, deptno, grade, dname
  from emp e
  join dept d
    on e.deptno = d.deptno
  join salgrade
    on e.sal between losal and hisal
 where e.deptno = 30;

外连接

• left [outer] join on
• left [outer] join using
• right [outer] join on
• right [outer] join using

实例

• 查询员工编号，员工名称，上级编号，上级名称。
• LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配，如果右表中没有匹配，则结果为 NULL

select e.empno 员工编号,
       e.ename 员工名称,
       e.mgr   上级编号,
       m.ename 上级名称。
from emp e
left outer join emp m
on e.mgr = m.empno;

集合操作

• Union，并集（去重）对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序。
• Union All，全集（不去重）对两个结果集进行并集操作，包括重复行，不进行排序。
• Intersect，交集（找出重复）对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序。
• Minus，差集（减去重复）对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。