ORACLE 多表连接与子查询.docx

ORACLE 多表连接与子查询 Oracle表连接SQL/Oracle使用表连接从多个表中查询数据语法格式:select 字段列表from table1,table2where table1.column1=table2.column2;说明:在where子句中指定连接条件当被连接的多个表中存在同名字段时,必须在该字段前加上表名作为前缀.连接的类型Oracle8i之前的表连接：等值连接(Equijoin)非等值连接(Non-Equijoin)外连接(Outer join):-左外连接-右外连接自连接(Self join)Oracle9之后新引入的连接形式(支持SQL99规范)交叉连接(Cross join)自然连接(Natural join)使用Using子句建立连接使用on子句建立连接外连接(Outer join):-左外连接-右外连接-全外连接等值连接(Equijoin)select empno,ename,sal,emp.deptno,dname from emp,deptwhere emp.deptno = dept.deptno; 多表连接中：可使用AND操作符增加查询条件 使用表别名可以简化查询 使用表名(表别名)前缀可提高查询效率 为了连接n个表,至少需要n-1个连接条件 非等值连接(Non-Equijoin)外连接(Outer join)使用外连接可以看到参与连接的某一方不满足连接条件的记录外连接运算符为(+)传统的外连接分为左外连接和右外连接两种语法格式:select 字段列表from table1,table2where table1.column1(+)=table2.column2;select 字段列表from table1,table2where table1.column1=table2.column2(+);自连接(Self join)select a.enpno,a.ename,a.ngr,b.enamefrom emp a,emp bwhere a.ngr = b.enpno; SQL99连接语法SQL1999规范中规定的连接查询语法select 字段列表from table1cross join table2 |natural join table2 |join table2 using(字段名) |join table2 on(table.column_name=table2.column_name) |(left | right | full out ) join table2on(table1.column_name=table2.column_name);交叉连接(Cross join)Cross join 产生了一个笛卡尔集，其效果等同于再两个表进行连接时未使用where子句限定连接条件;select empno,ename,sal,emp.deptno,dnamefrom emp cross join dept; 自然连接(Natural join)Natural join基于两个表中的全部同名列建立连接从两个表中选出同名列的值均对应相等的所有行 如果两个表中的同名列的所有数据类型不同,则出错 不允许在参照列上使用表名或者别名作为前缀 select empno,ename,sal,emp.deptno,dnamefrom emp natural join dept; Using子句 如果不希望参照被连接表的所有同名列进行等值连接,自然连接将无法满足要求，可以在连接时使用USING子句来设置用于等值连接的列(参照列)名.select empno,ename,sal,emp.deptno,dnamefrom emp join deptusing(deptno);不允许在参照列上使用表名或者别名作为前缀On子句如果要参照非同名的列进行等值连接,或想设置任意的连接条件,可以使用On子句select empno,ename,sal,emp.deptno,dnamefrom emp join depton(emp.deptno=dept.deptno);多表连接使用SQL99连接语法,两个以上的表进行连接时应依次/分别指定相临的两个表之间的连接条件.select 字段列表 from table1 cross join table2 | natural join table2 | join table2 using(字段名) | join table2 on(table.column_name=table2.column_name) | (left | right | full out ) join table2 on(table1.column_name=table2.column_name) cross join table3 | natural join table3 | join table3 using(字段名) | join table3 on(table.column_name=table3.column_name) | (left | right | full out ) join table3 on(table2.column_name=table3.column_name).; 内连接和外连接内连接(Inner join)在SQL99规范中,内连接只返回满足连接条件的数据.外连接(Outer join)左外连接(Left Outer Join)两个表在连接过程中除返回满足连接条件的行为外,还返回左表中不满足条件的行为，这种连接称为左外连接.右外连接(Right Outer Join)两个表在连接过程中除返回满足连接条件的行为外,还返回右表中不满足条件的行为,这种连接称为右外连接.满外连接(Full Outer Join)Oracle9开始新增功能,两个表在连接过程中除返回满足连接条件的行为外，还返回两个表中不满足条件的所有行为，这种连接称为满外连接.子查询(Sub Query)子查询子查询在主查询前执行一次主查询使用子查询的结果select 字段列表from tablewhere 表达式 operator (select 字段列表 from table);使用子查询注意事项在查询时基于未知时应考虑使用子查询子查询必须包含在括号内将子查询放在比较运算符的右侧,以增强可读性.除非进行Top-N分析,否则不要再子查询中使用Order by子句对单行子查询使用单行运算符对多行子查询使用多行运算符单行子查询单行子查询只返回一行记录对单行子查询可使用单行记录比较运算符=-等于-大于=-大于等于-小于=-小于等于-不等于select * from empwhere sal(select sal from emp where empno=7000);子查询空值/多值问题如果子查询未返回任何行,则主查询页不会返回任何结果如果子查询返回单行结果,则为单行子查询,可以在主查询中对其使用相应的单行记录比较运算符如果子查询返回多行结果,则为多行子查询,此时不允许对其使用单行记录比较运算符多行子查询多行子查询返回多行记录对多行子查询只能使用多行记录比较运算符in-等于列表中的任何一个any-和子查询返回的任意一个值比较all-和子查询返回的所有值比较select * from empwhere salany(select avg(sal) from emp group by deptno);select * from empwhere salall(select avg(sal) from emp group by deptno);select * from empwhere job in(select job from emp where ename=martin or ename=sss