oracle多表查询

– 多表连接

– 1）内连接
– 2）外连接
– 3）自连接

select * from scott.emp; – 13
select * from scott.dept; – 7

– 笛卡尔集（了解）
– 主要忽略了一个连接条件或者说一个连接条件失效了
– 第一张表中的数据与第二张表中的所有数据（行）都有连接，造成数据的交集。

select * from scott.emp, scott.dept
where scott.emp.ename = ‘SMITH’;

– 开发中，需要避免笛卡尔集
– 如果要避免的话，我们需要包含一个有效连接条件的 where 子句。

– 内连接
– 两个表（连接）中某一数据项相等的连接叫内连接。也叫等值连接。
– where tb_stu.clazz_id = tb_clazz.id

– 内连接的运算顺序
– 1）参与的数据表（或连接）中的每列与其它数据表（或连接）的列匹配，会形成一个临时表。
– 2）将满足数据项相等的记录从临时数据表中选择出来。

select * from scott.dept, scott.emp
where scott.dept.deptno = scott.emp.deptno
and scott.emp.ename = ‘SMITH’; – 20

– 使用别名
select * from scott.dept d, scott.emp e
where d.deptno = e.deptno
and e.ename = ‘SMITH’;

– 查询指定列
select d.deptno 部门编号, d.dname 部门名称, e.sal 薪资
from scott.dept d, scott.emp e
where d.deptno = e.deptno
and e.ename = ‘SMITH’;

select d.deptno 部门编号, d.dname 部门名称, e.*
from scott.dept d, scott.emp e
where d.deptno = e.deptno
and e.ename = ‘SMITH’;

– 内连接的标准写法（了解）
– inner join 就是一个逗号 ，
select * from scott.dept d inner join scott.emp e
on d.deptno = e.deptno
and e.ename = ‘SMITH’; – 20

– 外连接（非等值连接）

– 用来查询一张表在另一张中没有关联数据的信息。
– 外连接的三种方式
– 1）左外连接 left outer join（重点）
– 2）右外连接 right outer join
– 2）全外连接 full outer join

select * from scott.emp; – 13
select * from scott.dept; – 7

– 等值连接：两张表中都有的数据
select * from scott.dept d, scott.emp e
where d.deptno = e.deptno;

– 左外连接
– 技巧：如果是左外，就在右边加 + 号。
– 左边的表会展示出所有的数据，右边表没有对应的数据则显示 null。
select * from scott.dept d, scott.emp e
where d.deptno = e.deptno(+);

– 左外的标准写法
– 书写的时候，需要注意：where 需要改为 on
select * from scott.dept d left outer join scott.emp e
on d.deptno = e.deptno;

– 右外连接
select * from scott.dept d, scott.emp e
where d.deptno(+) = e.deptno;

select * from scott.dept d right outer join scott.emp e
on d.deptno = e.deptno;

– 全外连接
select * from scott.dept d full outer join scott.emp e
on d.deptno = e.deptno;

– 自连接（重点）
– 在开发中使用比较广泛
– 使用自连接的时候，相当于复制了一个镜像对象出来，并可以当做另外一张表来处理。
– 《与神对话》1，2，3
– 使用了自连接可以把一张表当做多张表来使用，获取一些比较特殊的数据。
– 使用技巧：可以考虑把它当做外键来玩。

select * from scott.emp;

– 一个普通员工有自己的经理，经理也是一个员工，经理也有自己的经理

– 查询 SMITH 的员工编号，名称，上级经理的编号，上级经理的名称

– 创建一个临时表，数据来自 scott.emp
create table tb_temp as select * from scott.emp;

– 关键厘清对象之间的关系
select e1.empno, e1.ename, e1.mgr, e2.ename
from scott.emp e1, tb_temp e2
where e1.mgr = e2.empno
and e1.ename = ‘SMITH’;