Oracle 多表连接 详细分类

一.多表连接 1992 旧标准

用单个select语句从多个表中查询相关的数据 被称为多表连接查询

oracle 11G对于1992旧标准和1999新标准 都支持

当我们对员工表(m条数据)部门表(n条数据)同时做全部查询时，会出现笛卡尔积，出现m×n条数据,这不是我们想要的，于是等值查询应运而生

等值查询

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

在父子表的关系上，用=来连接两个表的两个字段的多个字段或多个表的多个字段

在等值查询里，N个表连接需要N-1个等值条件

查询teacher表、student表、course表中的数据分别如下：

可以看到course表作为父表，其他两个表作为子表

select c.*,t.*,s.*
from course c,teacher t,score s
where c.tno=t.tno and c.cno=s.cno;

 可以看到使用了两个等值查询完成多表查询

非等值查询

在两张表没有父子关系时，无法再通过相同的字段进行连接查询，非等值查询用！=来进行连接两个表

查询员工的薪资等级：

select e.*,s.*
from emp e,salgrade s
where sal between losal and hisal

自连接

通过别名将一张表虚拟成两个表，在这两张表上做等值查询

查询员工姓名和上级名称

通过将一张emp表虚拟为两张表来进行查询

select m.ename 员工,e.empno,m.mgr,e.ename 对应上级
  from emp e,emp m
 where e.empno = m.mgr;

外连接

左外连接

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

左外连接可以把右表不满足等值条件的数据找出来

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

右外连接

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

右外连接可以把左表不满足等值条件的数据找出来

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

 不能同时让(+)出现在等号两边

二.1999新标准

交叉连接(笛卡尔积连接)

相当于92标准的没有条件的查询

select e.*,d.*
from emp e
cross join dept d;

自然连接

在父子表的基础上，自动连接两表中列名完整相同的字段，作为参照列，按照其进行多表查询

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

在进行自然连接natural  join 时 查询字段不能出现限定词（deptno）

此外natrual join会把所有参照列当作参照对象，当两个名称相同但数据类型不同的字段进行匹配会报错。

当natrual join 没有找到合适的参照列会进行笛卡尔积连接

由于自然连接缺陷明显，通过改进产生了join...using，来指定参照列来进行查询

select e.ename,e.empno,deptno,d.dname
from emp e
join dept d using(deptno);

同样的不能对参照列设置限定词

join on使用on里面的指定的条件作为查询条件(on里面的条件可以是任意的)

join on 做等值查询(inner join)

使用join on 做N个表的多表查询，需要N-1个join on 子句

select e.*,d.*
from emp e
join dept d on (e.deptno=d.deptno);

（有父子关系）

join on 做非等值查询(outer join)

查询员工号是7369的员工的薪资等级

select ename, grade
  from emp
  join salgrade
    on (sal between losal and hisal)
 where empno = 7369;

（无父子关系）

left outer join...on  左外连接：查询左表  特有的/不满足条件  的数据

select e.*,d.*
from dept d
left outer join emp e on d.deptno = e.deptno;  

注意这里已经不再通过on后面的条件进行左右表的判定，而是通过连接与被连接的表，即将条件对调不影响结果，只有更换表的位置，更换左右表才能影响结果

right outer join...on 右外连接：查询右表  特有的/不满足条件  的数据

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

full outer join...on 全外连接：查询所有表  特有的/不满足条件  的数据

select e.*,d.*
from dept d
full outer join emp e on d.deptno = e.deptno;