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子查詢實現兩表交集。