Oracle_plsql讲义：第4章 多表查询

Oracle SQL&PL/SQLOracle SQL&PL/SQL第第4 4章章 多表查询多表查询 了解表连接的基本原理，分清连接的类了解表连接的基本原理，分清连接的类型；型；掌握各种类型连接的使用方法；掌握各种类型连接的使用方法；掌握利用掌握利用SQL:1999SQL:1999的语法创建连接的的语法创建连接的方法；方法；掌握各种类型连接之间的区别与联系掌握各种类型连接之间的区别与联系本章要点本章要点 EMPNO DEPTNO LOC-7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO.14 rows selected.从多个表中获取数据从多个表中获取数据EMP EMP DEPT DEPT EMPNO ENAME.DEPTNO-.-7839 KING.10 7698 BLAKE.30 .7934 MILLER.10DEPTNO DNAME LOC -10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTON什么是连接什么是连接l使用连接从多个表中查询数据。

使用连接从多个表中查询数据在在 WHERE子句中书写连接条件子句中书写连接条件如果在多个表中出现相同的列名，则需要如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀使用表名作为来自该表的列名的前缀N个表相连时，至少需要个表相连时，至少需要N1个连接条件个连接条件SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;连接的类型连接的类型等值连接等值连接非等值连接非等值连接外部连接外部连接内部连接内部连接笛卡尔积笛卡尔积l笛卡尔积在下列情况产生：笛卡尔积在下列情况产生：连接条件被省略连接条件被省略连接条件是无效的连接条件是无效的第一个表中的所有行和第二个表中的所有第一个表中的所有行和第二个表中的所有行都发生连接行都发生连接l为了避免笛卡尔积，通常需要在为了避免笛卡尔积，通常需要在WHERE子句中包含一个有效的连接条子句中包含一个有效的连接条件笛卡尔积的产生笛卡尔积的产生ENAME DNAME-KINGACCOUNTINGBLAKE ACCOUNTING.KINGRESEARCHBLAKE RESEARCH.56 rows selected.EMP(14 EMP(14 行行行行)DEPT(4 DEPT(4 行行行行)EMPNO ENAME.DEPTNO-.-7839 KING.10 7698 BLAKE.30 .7934 MILLER.10DEPTNO DNAME LOC -10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALESCHICAGO 40 OPERATIONS BOSTON“笛卡尔积笛卡尔积笛卡尔积笛卡尔积:14*4=5614*4=56行行行行”等值连接等值连接EMPEMP DEPTDEPT EMPNO ENAME DEPTNO-7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20.14 rows selected.DEPTNO DNAME LOC -10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCHDALLAS 20 RESEARCHDALLAS.14 rows selected.外键外键外键外键主键主键主键主键 用等值连接检索数据用等值连接检索数据SQL SELECT emp.empno,emp.ename,emp.deptno,2dept.deptno,dept.loc 3 FROM emp,dept 4 WHERE emp.deptno=dept.deptno;EMPNO ENAME DEPTNO DEPTNO LOC-7839 KING 10 10 NEW YORK 7698 BLAKE 30 30 CHICAGO 7782 CLARK 10 10 NEW YORK 7566 JONES 20 20 DALLAS.14 rows selected.使用使用AND运算符增加其它查询条件运算符增加其它查询条件 EMP EMP DEPT DEPT EMPNO ENAME DEPTNO-7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20.14 rows selected.DEPTNO DNAME LOC -10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCHDALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCHDALLAS 20 RESEARCHDALLAS.14 rows selected.限制歧义列名限制歧义列名l在用到多个表时使用表名作前缀来限定在用到多个表时使用表名作前缀来限定列列l通过使用表前缀可以提高性能通过使用表前缀可以提高性能l通过使用列的别名可以区分来自不同表通过使用列的别名可以区分来自不同表但是名字相同的列但是名字相同的列使用表的别名使用表的别名l通过使用表的别名来简化查询语句通过使用表的别名来简化查询语句SQL SELECT emp.empno,emp.ename,emp.deptno,2 dept.deptno,dept.loc 3 FROM emp,dept 4 WHERE emp.deptno=dept.deptno;SQL SELECT e.empno,e.ename,e.deptno,2 d.deptno,d.loc 3 FROM emp e,dept d 4 WHERE e.deptno=d.deptno;多于两个表的连接多于两个表的连接NAMECUSTID-JOCKSPORTS 100TKB SPORT SHOP 101VOLLYRITE 102JUST TENNIS 103K+T SPORTS 105SHAPE UP 106WOMENS SPORTS 107.9 rows selected.CUSTOMER CUSTOMER CUSTID ORDID-101 610 102 611 104 612 106 601 102 602 106 604 106 605.21 rows selected.ORD ORD ORDID ITEMID-610 3 611 1 612 1 601 1 602 1.64 rows selected.ITEM ITEM 非等值连接非等值连接EMPEMPSALGRADESALGRADE“EMPEMP表中的薪水是在表中的薪水是在表中的薪水是在表中的薪水是在SALGRADE SALGRADE 表所规定的表所规定的表所规定的表所规定的最低和最高范围内。

最低和最高范围内最低和最高范围内最低和最高范围内EMPNO ENAME SAL-7839 KING 5000 7698 BLAKE 2850 7782 CLARK 2450 7566 JONES 2975 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950.14 rows selected.GRADE LOSAL HISAL-1 70012002 120114003 140120004200130005 30019999非等值连接的数据检索非等值连接的数据检索ENAME SAL GRADE-JAMES 950 1SMITH 800 1ADAMS 1100 1.14 rows selected.SQL SELECT e.ename,e.sal,s.grade 2FROMemp e,salgrade s 3WHERE e.sal 4BETWEEN s.losal AND s.hisal;外部连接外部连接EMPEMP DEPTDEPT 没有雇员工作在没有雇员工作在没有雇员工作在没有雇员工作在OPERATIONS OPERATIONS 部门部门部门部门ENAMEDEPTNO-KING10BLAKE30CLARK10JONES20.DEPTNO DNAME-10 ACCOUNTING30 SALES10 ACCOUNTING20RESEARCH.40OPERATIONS外部连接外部连接l您可以使用外部连接来查看哪些行没您可以使用外部连接来查看哪些行没有符合连接条件有符合连接条件l外部连接的符号是外部连接的符号是(+).SELECT table1.column,table2.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;SELECT table1.column,table2.columnFROMtable1,table2WHEREtable1.column=table2.column(+);外部连接外部连接外部连接就好象是为符号外部连接就好象是为符号(+)所在边的表增所在边的表增加一个加一个“万能万能”的行，这个行全部由空值的行，这个行全部由空值组成。

它可以和另一边的表中组成它可以和另一边的表中所有不满足所有不满足连接条件的元组进行连接由于这个连接条件的元组进行连接由于这个“万万能能”行的各列全部是空值，因此在连接结行的各列全部是空值，因此在连接结果中，来自果中，来自“万能万能”行属性值全部为空值行属性值全部为空值使用外部连接使用外部连接SQL SELECTe.ename,d.deptno,d.dname 2 FROMemp e,dept d 3 WHEREe.deptno(+)=d.deptno 4 ORDER BY e.deptno;ENAME DEPTNO DNAME-KING 10 ACCOUNTINGCLARK 10 ACCOUNTING.40 OPERATIONS15 rows selected.自身连接自身连接EMP(WORKER)EMP(WORKER)EMP(MANAGER)EMP(MANAGER)“WORKERWORKER表中的表中的表中的表中的 MGR MGR 等于等于等于等于 MANAGERMANAGER表中的表中的表中的表中的EMPNO”EMPNO”EMPNO ENAME MGR-7839 KING 7698 BLAKE7839 7782 CLARK7839 7566 JONES7839 7654 MARTIN 7698 7499 ALLEN7698EMPNO ENAME-7839 KING 7839 KING 7839 KING 7698 BLAKE 7698 BLAKE表的自身连接表的自身连接WORKER.ENAME|WORKSFOR|MANAG-BLAKE works for KINGCLARK works for KINGJONES works for KINGMARTIN works for BLAKE.13 rows selected.SQL SELECT worker.ename|works for|manager.ename 2 FROM emp worker,emp manager 3 WHERE worker.mgr=manager.empno;SQL:1999语法的连接语法的连接 Use a join to query data from more than one table.SELECTtable1.column,table2.columnFROMtable1CROSS JOIN table2|NATURAL JOIN table2|JOIN table2 USING(column_name)|JOIN table2 ON(table1.column_name=table2.column_name)|LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name);创建交叉连接创建交叉连接 lCROSS JOIN 子句会产生两个表的交叉乘子句会产生两个表的交叉乘积，和两个表之间的笛卡尔积是一样的。

积，和两个表之间的笛卡尔积是一样的SELECT emp.empno,emp.ename,emp.sal,emp.deptno,dept.loc FROM emp CROSS JOIN dept;EMPNO ENAME SAL DEPTNO LOC-7369 SMITH 800 20 NEW YORK 7499 ALLEN 1600 30 NEW YORK 7521 WARD 1250 30 NEW YORK 7566 JONES 2975 20 NEW YORK 7934 MILLER 1300 10 BOSTON已选择已选择56行自然连接自然连接 l使用自然连接时应注意以下事项：使用自然连接时应注意以下事项：自然连接子句是基于两个表存在相同名字的自然连接子句是基于两个表存在相同名字的列；列；返回两个表相匹配列中具有相同值的记录；返回两个表相匹配列中具有相同值的记录；如果相同名称的列的数据类型不同，则会产如果相同名称的列的数据类型不同，则会产生错误SELECT empno,ename,sal,deptno,loc FROM emp NATURAL JOIN dept;EMPNO ENAME SAL DEPTNO LOC-7369 SMITH 800 20 DALLAS 7499 ALLEN 1600 30 CHICAGO 7521 WARD 1250 30 CHICAGO 7566 JONES 2975 20 DALLAS 7934 MILLER 1300 10 NEW YORK已选择已选择14行。

行自然连接自然连接 使用使用USING子句创建连接子句创建连接 l自然连接使用所有名称和数据类型相自然连接使用所有名称和数据类型相匹配的列，匹配的列，USING子句可以用于指定子句可以用于指定产生连接的列产生连接的列SELECT e.ename,e.ename,e.sal,deptno,d.loc FROM emp e JOIN dept d USING(deptno)WHERE deptno=20;ENAME ENAME SAL DEPTNO LOC-SMITH SMITH 800 20 DALLASJONES JONES 2975 20 DALLASSCOTT SCOTT 3000 20 DALLASADAMS ADAMS 1100 20 DALLASFORD FORD 3000 20 DALLAS使用使用USING子句创建连接子句创建连接 使用使用USING子句创建连接子句创建连接 使用使用USING子句创建连接时，应注意以下子句创建连接时，应注意以下几点：几点：如果有若干个列名称相同但数据类型不同，自如果有若干个列名称相同但数据类型不同，自如果有若干个列名称相同但数据类型不同，自如果有若干个列名称相同但数据类型不同，自然连接子句可以用然连接子句可以用然连接子句可以用然连接子句可以用USINGUSING子句来替换，以指定产子句来替换，以指定产子句来替换，以指定产子句来替换，以指定产生等值连接的列。

生等值连接的列生等值连接的列生等值连接的列如果有多于一个列都匹配的情况，使用如果有多于一个列都匹配的情况，使用如果有多于一个列都匹配的情况，使用如果有多于一个列都匹配的情况，使用USINGUSING子子子子句只能指定其中的一列句只能指定其中的一列句只能指定其中的一列句只能指定其中的一列USINGUSING子句中的用到的列不能使用表名和别名子句中的用到的列不能使用表名和别名子句中的用到的列不能使用表名和别名子句中的用到的列不能使用表名和别名作为前缀作为前缀作为前缀作为前缀NATURAL JOINNATURAL JOIN子句和子句和子句和子句和USINGUSING子句是相互排斥子句是相互排斥子句是相互排斥子句是相互排斥的，不能同时使用的，不能同时使用的，不能同时使用的，不能同时使用使用使用ON子句创建连接子句创建连接 l自然连接条件基本上是具有相同列名自然连接条件基本上是具有相同列名的表之间的等值连接，要指定任意连的表之间的等值连接，要指定任意连接条件或特殊的列可以使用接条件或特殊的列可以使用ON子句，子句，用用ON将连接条件和其它检索条件分隔将连接条件和其它检索条件分隔开可以提高代码的可读性。

开可以提高代码的可读性SELECT e.empno,e.ename,e.deptno,d.deptno,d.loc FROM emp e JOIN dept d ON (e.deptno=d.deptno);使用使用ON子句创建连接子句创建连接 EMPNO ENAME DEPTNO DEPTNO LOC-7369 SMITH 20 20 DALLAS 7499 ALLEN 30 30 CHICAGO 7934 MILLER 10 10 NEW YORK已选择已选择14行用用ON 子句创建三向连接子句创建三向连接SELECT e.empno,e.ename,d.loc,m.ename FROM emp e JOIN dept d ON e.deptno=d.deptno JOIN manager m ON e.mgr=m.empno;EMPNO ENAME LOC ENAME-7788 SCOTT DALLAS JONES 7902 FORD DALLAS JONES 7499 ALLEN CHICAGO BLAKE 7521 WARD CHICAGO BLAKE 7369 SMITH DALLAS FORD已选择已选择13行。

行SELECT e.ename,e.deptno,d.loc FROM emp e LEFT OUTER JOIN dept d ON (e.deptno=d.deptno);左外连接左外连接 ENAME DEPTNO LOC-MILLER 10 NEW YORKKING 10 NEW YORKCLARK 10 NEW YORKFORD 20 DALLASWARD已选择已选择14行SELECT e.ename,e.deptno,d.loc FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno=d.deptno);右外连接右外连接 ENAME DEPTNO LOC-SMITH 20 DALLASALLEN 30 CHICAGOJONES 20 DALLASMARTIN 30 CHICAGO BOSTON已选择已选择14行SELECT e.ename,e.deptno,d.loc FROM emp e FULL OUTER JOIN dept d ON (e.deptno=d.deptno);全外连接全外连接 ENAME DEPTNO LOC-MILLER 10 NEW YORKKING 10 NEW YORKCLARK 10 NEW YORKWARD BOSTON已选择已选择15行。