子询查操作

WHERE 子句中的单行子查询

例8-3

SQL> SELECT empno, ename, sal, job

2         FROM emp

3         WHERE job=

4        (SELECT job

5        FROM emp

6        WHERE ename = 'SMITH');

例8-3的查询语句包含了一个子查询，它与例8-2显示的结果完全相同。

例8-3中括号内的查询叫子查询（Subquery）或内查询（Inner query），括号外的查询叫主查询（Main query）或外查询（Outer query）。

Oracle系统执行该查询语句的顺序如下：

Oracle首先执行括号中的子查询。

返回SMITH的职位（job）为文员（CLERK）。

主查询（Main query）把CLERK放在WHERE子句中之后执行主查询。单行子查询的比较关系符包括：

>（大于）

>=（大于等于）

<（小于）

<=（小于等于）

=（等于）

◇ 或 ！=（不等于）

单行子查询除了可以放在WHERE子句中，还可放在HAVING子句和FROM子句中。在书写单行子查询时要注意以下要求：

■ 单行子查询使用单行比较关系符。

■ 单行子查询放在单行比较关系符的右边。

■ 单行子查询放在括号中。

■ 单行子查询中不能使用ORDER BY 子句。

根据以上的要求，单行子查询必须返回单一的行。因此例8-3并不是一个好的子查询例子，因为在一个大型公司中员工的名字很难惟一。也许最稳妥的办法是在子查询的条件中使用主键（Primary Key）。

例8-4

SQL> SELECT empno,ename,sal,job

2 FROM emp

3 WHERE job=

4 (SELECT job

5 FROM emp

6 WHERE ename = 'SMITH')

7 AND sal<=

8(SELECT sal

9 FROM emp

10 WHERE ename = 'ADAMS');

例8-5

SQL> SELECT empno,ename,sal,job

2 FROM emp

3 WHERE sal<

4(SELECT AVG(sal)

5 FROM emp);

在例8-5中的子查询中一定不能含有GROUP BY子句，因为该子句将使子查询返回多值如果在子查询中加入了GROUP BY子句，Oracie会返回错误信息，如例8-6的查询语句。

在例8-7的查询语句中我们将ADAMS错误地写成了ADAM，而在emp表中没有ename为ADAM的记录，所以子查询返回零行。这相当于主查询中的WHERE子句变成了WHERE ename =NULL，我们知道空值（NULL）不等于任何值，所以主查询返回的结果为"未选定行"。因此写查询语句的人一定要保证子查询中的WHERE子句的条件成立并返回惟一的值。

我们已经介绍了WHERE子句中的单行子查询。单行子查询除了可以放在WHERE子句中外，还可以放在HAVING子句中。

HAVING子句中的单行子查询

SQL> SELECT job,MIN(sal),AVG(sal),MAX(sal)

2 FROM emp

3 WHERE job NOT LIKE 'PRESID%'

4 GROUP BY job

5 HAVING AVG(sal)>(

6                                 SELECT MIN(AVG(sal))

7                                 FROM emp

8                                GROUP BY job);

FROM子句中的单行子查询

例8-9

SQL> SELECT e.empno,e.ename,e.sal,e.job,a.avesal

2 FROM emp e,(SELECT job,AVG(sal)avesal

3                        FROM emp

4                        GROUP BY job) a

5 WHERE e.job=a.job

6 AND e.sal > a.avesal

7 AND e.job != 'CLERK';

多行子查询

在前面几节中我们介绍了单行子查询。现在我们介绍另一类子查询，即多行子查询。多行子查询使用多行比较操作符。它返回多行。

多行比较操作符包括：■ IN ■ ANY ■ ALL

我们在以后几节中通过例子来分别介绍由这几个比较操作符组成的多行子查询。

使用IN操作符的多行子查询

例8-10

SQL> SELECT empno,ename,job,sal

2 FROM emp

3 WHERE sal IN (SELECT MAX(sal)

4                        FROM emp

5                        GROUP BY job)

6 AND job <> 'CLERK'

7 AND job NOT LIKE 'PRES%';

比较操作符IN的含义为子查询返回列表中的任何一个。IN操作符比较子查询返回列表中的每一个值，并且显示任何相等的数据行。其实例8-10可以由例8-11和例8-12查询语句组成的。

例8-12

SQL> SELECT empno,ename,job,sal

2 FROM emp

3 WHERE sal IN(3000,1300,2975,5000,1600)

4 AND job <> 'CLERK'

5 AND job NOT LIKE PRES%';

您还可以将例8-10的查询语句改写成例8-13的SOL语句。您会发现例8-13返回的结果与例8-10完全一样。

例8-13

SQL> SELECT empno,ename,job,sal

2 FROM emp

3 WHERE sal IN (SELECT MAX(sal)

4                         FROM emp

5                         WHERE job <> 'CLERK'

6                         AND job NOT LIKE 'PRES%'

7                        GROUP BY job);

使用 ALL 操作符的多行子查询

例8-14

SQL> SELECT empno,ename,sal,job

2 FROM emp

3 WHERE sal< ALL

4                        (SELECT AVG(sal)

5                        FROM emp

6                        GROUP BY job);

ALL操作符比较子查询返回列表中的每一个值。<ALL为小于最小的；>ALL为大于最大的：那么=ALL将是什么？请看例8-15的查询语句。

例8-15

SQL> SELECT empno, ename, sal, job

2 FROM emp

3 WHERE sal = ALL

4                                (SELECT AVG(sal) 

5                                 FROM emp

6                                  GROUP BY job):

例8-15结果未选定行

因为等于子查询返回列表中的所有值是不符合逻辑的，所以Oracle返回“未选定行”。

使用ANY操作符的多行子查询

例8-16

SQL> SELECT empno,ename,sal,job

2 FROM emp

3 WHERE sal > ANY

4                         (SELECT AVG(sal)

5                         FROM emp

6                         GROUP BY job);

ANY操作符比较子查询返回列表中的每一个值。<ANY为小于最大的；>ANY为大于最小的：那么=ANY将是什么？请看例8-17的查询语句。

例8-17

SQL> SELECT empno,ename,sal,job

2 FROM emp

3 WHERE sal = ANY

4                          (SELECT AVG(sal)

5                             FROM emp

6                            GROUP BY job);

=ANY等价于IN。我们可以使用例8-18的查询语句来验证这一点。

例8-18

SQL> SELECT empno,ename,sal,job

2 FROM emp

3 WHERE sal IN

4                        (SELECT AVG(sal)

5                        FROM emp

6                        GROUP BY job);

子查询中的空值（NULL）问题

例8-19

SQL> SELECT e.empno,e.ename,e.sal,e.job

2 FROM emp e

3 WHERE e.mgr IN

4                                (SELECT w.mgr

5                                FROM emp w

6                                WHERE w.mgr IS NULL);

未选定行

例8-19的结果真是令人失望。这是因为子查询返回列表里包含了空值（NULL），而任何值都不等于空值（NULL），所以例8-19的查询是得不到任何结果的。

成对比较（Pairwise Comparison）的多列子查询

例8-26

SQL> SELECT empno,ename,sal,job

2 FROM manager

3 WHERE(sal,job) IN

4                                (SELECT MAX(sal),job

5                                FROM manager

6                                 GROUP BY job);

在例8-26中子查询返回每一种职位（job）的最高工资和职位（job）的名称。之后主查询的每一行中的工资和职位（job）都要与子查询返回列表中的最高工资和职位（job）相比较，只有当两者同时完全匹配时才显示该数据行。这也就是所谓的成对比较的多列子查询。

在介绍完了成对比较的子查询之后，我们将介绍一下非成对比较的子查询。

非成对比较（Nonpairwise Comparison）的多列子查询

例8-27

SQL> SELECT empno,ename,sal,job

2 FROM manager

3 WHERE sal IN (SELECT MAX(sal)

4                          FROM manager

5                          GROUP BY job)

6 AND job IN (SELECT DISTINCT job

7                     FROM manager);

例8-27的结果多了两行。一行是工资为1600的经理（1600为推销员的最高工资），另一行是工资为1300的推销员（1300为文员的最高工资）。例8-27就是所谓的非成对比较的多列子查询。

从例8-26和例8-27的结果可以知道非成对比较的多列子查询的条件要宽松些，因为它并不要求在把主查询的工资和职位（job）与子查询返回列表中的最高工资和职位（job）进行比较和两者同时完全匹配，只要主查询工资和职位（job）在子查询返回列表中出现过就行