Oracle多表关联

1.1.1笛卡尔积

select * from emp,dept;1.2等值连接

select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno1.3不等值连接

select e.ename,e.sal,sg.grade from emp e,salgrade sg where e.sal >= sg.losal and e.asl <=sg.hisal1.4外连接

左外连接：左边的表作为主表，右边表作为从表，主表数据都显⽰，从表数据没有，⽤null填充，⽤“+”号表⽰select * from dept d,emp e where d.deptno = e.deptno(+)

右外连接：右边的表作为主表，左边表作为从表，主表数据都显⽰，从表数据没有，⽤null填充，⽤“+”号表⽰select * from dept d,emp e where d.deptno(+) = e.deptno1.5⾃连接

查询每个雇员的上级领导

select e.ename “雇员”,m.ename \"领导\" from emp e, emp m where e.mgr = m.empno优化King

select e.ename \"雇员\" ,nvl(m.ename,\"boss\")\"领导\" from emp e,emp m where e.mgr = m.empno(+)1.6多余的两张表的连接

如果有多张表参与查询，先把t1*t2笛卡尔积得到⼀个⼤表T1，再把笛卡尔积得到⼀个另外的⼤表T2，以此类推查询SCO%T管理员最终都是两种表的查询

select e.ename,m.ename,sg.grade from emp e,emp m,salgrade sg

where e.mgr = m.empno and(m.sal between sg.losal and sg.hisal) and e.ename='SCO%T'

查询雇员Scott所在部门名称和薪资等级

select e.,d.,sg.* from emp e,dept d,salgrade sg where e.deptno = d.deptno and e.sal between sg.losal and sg.hisal and e.ename = 'SCO%T'

2.（1）表的过滤条件和表的连接条件混合在⼀起，维护⿇烦

（2）数据库的数据适合变化，根据where⼦句的执⾏规则，SQL语⾔也会发⽣相应变化，给维护造成⼀定成本2.1笛卡尔积

select * from dept d cross join emp e2.2⾃然连接

NATURAL JOIN⼦句基于两个表中列名完全相同的列产⽣连接【1】两个表中有相同字段名【2】数据类型相同

【3】从两个表中选出连接列的值相等的所有⾏select * from dept d natural join emp e

⾃然连接最优的使⽤场景是：主外键关系且主外键字段只有⼀个Using关键字，主要⽤于指定字段连接字段【1】按照指定的字段连接两张表【2】选指定字段值相同的数据⾏

⾃然连接的条件是基于表中所有同名列的等值连接，为了设置任意的连接条件或者指定连接的列，需要使⽤ON⼦句连个表的关联⽤关键字join，默认内连接语法select filed1，fild2，... from table1 join table2 on condition1

3.SQL查询是可以嵌套的，⼀个查询可以作为另外⼀个查询的条件，表

select select_list from table where expr operator(select select_list from table)3.1单⾏⼦查询：当⼦查询有单⾏时，可以取单⾏中⼀个字段形成单个值⽤于条件⽐较查询雇员其薪资在雇员平均薪资以上

【1】查询员⼯的平均薪资

select avg（e.sal）\"AVGSAL\" from emp e【2】查询满⾜条件的雇员

select * from emp e where e.sal > (select avg(e.sal) \"AVGSAL\" from emp e)3.2多⾏⼦查询：查在雇员中有哪些⼈是管理者【1】查询管理者

select distinct e.mgr from emp e where e.mgr is not null【2】查询指定列表的信息

select e.* from emp e where e.empto in(select distinct e.mgr from emp e where e.mgr is not null)多⾏⼦查询返回的结果可以作为表使⽤，通常结合in、some/any、all、exists3.3from后的⼦查询每个部门平均薪⽔的等级【1】部门平均薪资

select e.deptno,avg(e.sal)\"AVGSAL\" from emp e group by e.deptno【2】求等级

select vt0.deptno,vt0.avgsal,sg.grade from (select e.deptno,avg(e.sal)\"AVGSAL\" from emp e group by e.deptno)VT0,salgrade sg where vt0.avgsal between sg.losal and sg.hisal3.4TOP-N:把select得到的数据集提取前n条数rownum：表⽰对查询的数据集记录的编号，从1开始---查询前10名雇员

select e.*,rownum from emp e where rownum <=10

select vt0.*,rownum from (select e. * from emp e order by e.sal desc) VT0 where rownum <= 10【1】order by⼀定在整个结果集出现后执⾏【2】rownum在结果集出现后才有编号