1.1.6　多表查询

以前讲述的查询语句都只涉及一个表的数据。在很多情况下，需要查询的数据往往涉及多个表，这时需要对多个表进行连接查询。例如，如果既要查询员工的信息，又要查询员工所在部门的信息，这就涉及emp和dept两个表。

表间的连接关系有相等连接、非相等连接、外连接和子连接等多种形式，其中最常用的连接形式是相等连接。相等连接体现在WHERE子句中指定的条件上，在条件中要指定两个表通过哪些列进行连接。一般情况下进行连接查询的两个表是通过主键和外键进行关联的，所以最简单的条件是一个表的外键与另一个表的主键相等。例如，下面的SELECT语句从EMP表中查询员工的姓名和工资，同时在dept表中查询员工所在部门的名称：

SQL＞SELECT ename, sal, dname FROM emp, dept WHERE emp.deptno=dept.deptno；

其中DEPTNO是DEPT表中的主键，同时它又是emp表中的外键，在这个查询语句中连接的条件是它们相等，条件emp.deptno=dept.deptno的意思是在emp表中查询每个员工所在的部门号，然后根据部门号在dept表中查询对应的部门名称，凡是不满足这个条件的部门名称都将被过滤掉。

如果一个列在两个表中同时存在，那么在SELECT语句中要用表名进行限定（表名.列名或者表的别名.列名），否则系统将无法确定是哪个表中的列。上面的SELECT语句可以改为下面的等价的语句：

SQL＞SELECT ename, sal, dname FROM emp a, dept b WHERE a.deptno=b.deptno；

在这个SELECT语句中，为了书写方便，为表emp和dept分别定义了别名a和b，这样在其他子句中就可以使用这样的别名了。在构造查询语句时，首先要仔细分析这个查询要涉及哪些表，以及这些表通过哪些列进行连接，然后在SELECT语句中指定所有涉及的表，在WHERE子句中指定连接条件。下面我们再考察一个涉及三个表的查询。除了emp和dept两个表以外，第三个表salgrade也是Oracle提供的模板表，这个表记录了工资级别的规定，它的结构为：

SQL＞desc salgrade

名称　是否为空?类型

GRADE NUMBER

LOSAL NUMBER

HISAL NUMBER

这三个列的意义分别是级别编号、工资下限和工资上限。

现在要查询部门10和20中每个员工的姓名、工资、工资级别以及所在部门的名称，相应的SELECT语句为：

SQL＞SELECT ename AS姓名，sal AS工资，grade AS工资级别，dname AS部门名称

FROM emp a, dept b, salgrade c

WHERE a.deptno=b.deptno

AND（a.deptno=10 or a.deptno=20）

AND（sal＞=c.losal and sal＜=c.hisal）

这条查询语句的执行结果为：

姓名　工资　工资级别　部门名称

SMITH 800 1 RESEARCH

MILLER 1300 2 ACCOUNTING

JONES 2975 4 RESEARCH

CLARK 2450 4 ACCOUNTING

KING 5000 5 ACCOUNTING

外连接是一种特殊的连接方式。假设有两个表A和B，用相等连接查询可以返回表A中的所有行，而表B中的部分行因为不满足相等条件，所以是不会被查询到的，但是利用外连接可以返回表B中的所有行。对于表A和B来说，外连接的条件表达式的格式为：

WHERE A.列名（+）=B.列名

如果要显示表B中所有行，包括使用相等连接无法显示的行，则在表A的列名之后指定外连接的标志“（+）”。例如，对于表emp和dept来说，利用相等连接可以查询所有员工的信息以及员工所在部门的信息。如果某个部门没有员工，那么该部门的信息是查询不到的，因为这样的部门不满足相等条件。但是如果使用外连接，可以保证它们同样被查询出来。完成这个查询功能的SELECT语句为：

SQL＞SELECT ename, dname FROM emp a, dept b WHERE a.deptno（+）=b.deptno；

这条SELECT语句的执行结果为：

ENAME DNAME

……

MARTIN SALES

WARD SALES

OPERATIONS

其中最后一个部门OPERATIONS为空，在表emp中没有与它的编号相等的员工，在相等连接查询中它是不会被显示出来的，但是利用外连接，可以保证这样的数据也被查询出来。

自连接是一种特殊的相等连接。相等连接一般涉及多个不同的表，自连接也涉及多个表，但是它们是同一个表。例如，在表emp中，每个员工都有一个顶头上司的编号，而这个上司同时也是该公司的员工。如果要查询每个员工的上司姓名，首先要确定上司的编号，然后根据这个编号再查询emp表，利用相等连接确定上司的姓名，这就相当于两个表的连接。能够完成这个查询的SELECT语句为：

SQL＞SELECT a.ename, b.ename AS manager

FROM emp a, emp b

WHERE a.mgr=b.empno；

这条语句的执行结果为：

ENAME MANAGER

SMITH FORD

ALLEN BLAKE

……

由于要把同一个表看成两个不同的表进行连接，所以在FROM子句中要为emp表定义两个不同的别名，而SELECT之后的两个ENAME列就分别是这两个表中的列，因此要用别名进行限定。在emp表中，员工KING是最高领导，他没有上司，所以在上述查询结果中并没有显示。为了在查询中将所有员工姓名都列出来，可以在上述查询的基础上再使用外连接。用于完成这个查询的SELECT语句为：

SQL＞SELECT a.ename, b.ename AS manager

FROM emp a, emp b

WHERE a.mgr=b.empno（+）；