4.5 子查询的用法

视频讲解：光盘\TM\lx\4\子查询的用法．mp4

在执行数据操作（包括查询、添加、修改和删除等）的过程中，如果某个操作需要依赖于另外一个SELECT语句的查询结果，那么就可以把SELECT语句嵌入到该操作语句中，这样就形成了一个子查询。实际上，在关系型数据库中，各表之间的数据关系非常密切，它们相互关联，相互依存，这样就可以根据数据之间的关系使用相应的子查询，从而实现复杂的查询。

4.5.1 什么是子查询

子查询是在SQL语句内的另外一条SELECT语句，也被称为内查询或是内SELECT语句。在SELECT、INSERT、UPDATE或DELETE命令中允许是一个表达式的地方都可以包含子查询，子查询甚至可以包含在另外一个子查询中。

【例4.77】 在SCOTT模式下，在emp表中查询部门名称（dname）为“RESEARCH”的员工信息，具体代码如下（实例位置：光盘\TM\sl\4\13）。

        SQL>  select empno, ename, job from emp
          2   where deptno=(select deptno from dept
          3   where dname='RESEARCH');

本例运行结果如图4.69所示。

图4.69 子查询

对上面的代码进行分析，原本在emp表中是不存在dname字段（部门名称）的，但emp表中存在deptno字段（部门代码）; dname字段原本存在于dept表中，并且deptno字段也存在于dept表中，所以deptno为两个表之间的关联字段，这样本示例的需求完全可以通过多表关联查询来实现，即可以使用如下代码来替换上面的代码。

        SQL>select  empno, ename, job
          2  from emp join dept on emp.deptno=dept.deptno
          3  where dept.dname='RESEARCH';

从上面的两段代码中可以看出，相比多表关联查询，子查询的使用更加灵活、功能更强大，而且更容易理解。但是多表关联查询也有它自身的优点，比如，它的查询效率要高于子查询。

在执行子查询操作的语句中，子查询也称为内查询，包含子查询的查询语句也被称为外查询或主查询。在“例4.77”的代码中，下面的语句就是子查询：

        select deptno from dept
        where dname='RESEARCH'

那么，外查询语句就是：

        select empno, ename, job from emp
        where deptno=

在一般情况下，外查询语句检索一行，子查询语句需要检索一遍数据，然后判断外查询语句的条件是否满足。如果条件满足，则外查询语句将检索到的数据行添加到结果集中，如果条件不满足，则外查询语句继续检索下一行数据，所以子查询相对多表关联查询要慢一些。

另外，在使用子查询时，还应注意以下规则：

子查询必须用括号“（）”括起来。

子查询中不能包括ORDER BY子句。

子查询允许嵌套多层，但不能超过255层。

在Oracle 11g中，通常把子查询再细化为单行子查询、多行子查询和关联子查询3种，下面对这些子查询进行详细讲解。

4.5.2 单行子查询

单行子查询是指返回一行数据的子查询语句。当在WHERE子句中引用单行子查询时，可以使用单行比较运算符（=、>、<、>=、<=和< >）。

【例4.78】 在emp表中，查询出既不是最高工资，也不是最低工资的员工信息，具体代码如下：

        SQL> select empno, ename, sal from emp
          2  where sal>(select min(sal)from emp)
          3  and sal<(select max(sal)from emp);

本例运行结果如图4.70所示。

图4.70 单行子查询

在上面的语句中，如果内层子查询语句的执行结果为空值，那么外层的WHERE子句就始终不会满足条件，这样该查询的结果就必然为空值，因为空值无法参与比较运算。

在执行单行子查询时，要注意子查询的返回结果必须是一行数据，否则Oracle系统会提示无法执行。另外，子查询中也不能包含ORDER BY子句，如果非要对数据进行排序的话，那么只能在外查询语句中使用ORDER BY子句。

4.5.3 多行子查询

多行子查询是指返回多行数据的子查询语句。当在WHERE子句中使用多行子查询时，必须使用多行比较符（IN、ANY、ALL）。

1．使用IN运算符

当在多行子查询中使用IN运算符时，外查询会尝试与子查询结果中的任何一个结果进行匹配，只要有一个匹配成功，则外查询返回当前检索的记录。

【例4.79】 在emp表中，查询不是销售部门（SALES）的员工信息，具体代码如下：

        SQL> select empno, ename, job
          2  from emp where deptno in
          3  (select deptno from dept where dname<>'SALES');

本例运行结果如图4.71所示。

图4.71 多行子查询

2．使用ANY运算符

ANY运算符必须与单行操作符结合使用，并且返回行只要匹配子查询的任何一个结果即可。

【例4.80】 在emp表中，查询工资大于10号部门的任意一个员工工资的其他部门的员工信息，具体代码如下：

        SQL> select deptno, ename, sal from emp where sal > any
          2  (select sal from emp where deptno=10)and deptno<>10;

本例运行结果如图4.72所示。

图4.72 ANY运算符

3．使用ALL运算符

ALL运算符必须与单行运算符结合使用，并且返回行必须匹配所有子查询结果。

【例4.81】 在emp表中，查询工资大于部门编号为30的所有员工工资的员工信息，具体代码如下：

        SQL> select deptno, ename, sal from emp where sal > all
          2  (select sal from emp where deptno=30);

本例运行结果如图4.73所示。

图4.73 ALL运算符

4.5.4 关联子查询

在单行子查询和多行子查询中，内查询和外查询是分开执行的，也就是说，内查询的执行与外查询的执行是没有关系的，外查询仅仅是使用内查询的最终结果。在一些特殊需求的子查询中，内查询的执行需要借助于外查询，而外查询的执行又离不开内查询的执行，这时，内查询和外查询是相互关联的，这种子查询就被称为关联子查询。

【例4.82】 在emp表中，使用“关联子查询”检索工资大于同职位的平均工资的员工信息，具体代码如下（实例位置：光盘\TM\sl\4\14）。

        SQL> select empno, ename, sal
          2  from emp f
          3  where sal>(select avg(sal)from emp where job=f.job)
          4  order by job;

本例运行结果如图4.74所示。

图4.74 关联子查询

在上面的查询语句中，内层查询使用关联子查询计算每个职位的平均工资。而关联子查询必须知道职位的名称，为此外层查询就使用f.job字段值为内层查询提供职位名称，以便于计算出某个职位的平均工资。如果外层查询正在检索的数据行的工资高于平均工资，则该行的员工信息会显示出来，否则不显示。

需要补充一点的是，关联子查询不但可以作为SELECT语句的子查询，也可以作为INSERT、UPDATE或DELETE语句的关联子查询，关于在这3种语句中实现关联子查询的操作，将会在4.6节的“操作数据库”中详细讲解。

互动练习：EXISTS子查询实现两表交集。