oracle 内连接外连接查询笔记

elect ename,job,sal

from emp where deptno>10

order by sal desc;

联合查询，PK dept.deptno FK emp.deptno

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

也可以指定别名：

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

select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno where e.ename='SMITH';

dept : 主键被参照，是主表，父表

emp：外键参照dept的PK，从表

select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno where e.ename='SMITH';

emp 驱动表

dept 匹配表

1. 匹配不上， t1表的该结果不会出现

2. t1 在t2中只有一个匹配

3. t1在t2有多条记录，这时会形成多个组合，出现在结果集里面

不管驱动表还是匹配表，只有匹配之后才会有记录

select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno AND e.job='MANAGER';

select e.ename,e.sal,e.job,d.dname from emp e join dept d on e.deptno = d.deptno AND e.job='MANAGER';

非等值连接

select e.ename,e.sal, s.grade

from emp e join salgrade s

on e.sal between s.losal and s.hisal;

自参照自己的某一列参照自己的PK

select worker.ename as name, manager.ename as Mname

from emp worker join emp manager on worker.mgr = manager.empno;

内连接等值连接，非等值自连接

外连接

select e.ename,d.dname

from emp e join dept d

on e.deptno = d.deptno;

select e.ename,d.dname

from emp e left outer join dept d

on e.deptno = d.deptno;

left outer

t1度一条数据。T2中开始匹配，找得到就形成记录，匹配不上，那么，就放一个null

t1 left outer t2 t1 是驱动表

t1 right outer t2 t2是驱动表

t1 full outer t2 无论是否匹配，都将显示

select e.ename, d.dname

from emp e full outer join dept d

on e.deptno = d.deptno;