4.3 使用子查詢

在執行數據操作的過程中，如果某個操作需要依賴于另外一個SELECT語句的結果，那么可以把SELECT語句嵌入該操縱語句中，這就形成了一個子查詢。實際上，在操作表中的數據時，數據并不是孤立的，而是互相關聯的。這樣就可以根據數據之間的關聯使用相應的子查詢，從而實現復雜的查詢。

4.3.1 子查詢的概念

在一個SELECT語句被嵌套在另外一個SELECT、UPDATE或DELETE等SQL語句中時，被嵌套的SELECT語句就是子查詢。使用子查詢的原因是，希望執行某個SQL語句，但是該SQL語句還需要依賴于另外一個SELECT語句的執行結果。

例如，當要檢索某一部門的員工信息時，可以連接查詢EMP和DEPT表，也可以在檢索EMP表時使用子查詢檢索DEPT表。如下的語句演示了一個子查詢。

從這里可以看出，相比連接多個表的查詢，子查詢的使用更加靈活，且功能更強大。在執行子查詢操作的語句中，子查詢也稱為“內查詢”，包含子查詢的查詢語句也稱為“外查詢語句”。例如在上面的示例中，如下的語句為內查詢：

      select deptno
      from dept
      where dname='SALES'

外查詢語句為：

      select empno,ename,job,sal
      from emp

在一般情況下，外查詢語句檢索一行，子查詢語句需要檢索一遍數據，然后判斷外查詢語句的條件是否滿足。如果條件滿足，則外查詢語句檢索到的數據行就是結果集中的行；如果條件不滿足，則外查詢語句繼續檢索下一行數據。

在多數情況下，子查詢可以使用連接查詢來代替。也就是說，使用子查詢完成的操作也可以使用連接查詢完成。實際上，連接查詢的效率也遠高于子查詢的效率，但是子查詢更好理解，使用更靈活、方便。

在使用子查詢執行操作時，應該遵循如下規則。

※ 子查詢必須使用括號括起來，否則無法判斷子查詢語句的開始和結束。

※ 子查詢中不能包括ORDER BY子句。

※ 子查詢允許嵌套多層，但是最多嵌套255層。

子查詢可以分為4種類型，即單行子查詢、多行子查詢、多列子查詢和關聯子查詢。各種子查詢的特點如下。

※ 單行子查詢：子查詢語句只返回單行單列的結果，即返回一個常量值。

※ 多行子查詢：子查詢語句返回多行單列的結果，即返回一系列值。

※ 多列子查詢：子查詢語句返回多列的結果。

※ 關聯子查詢：子查詢語句引用外查詢語句中的一個列或多個列，即外查詢和內查詢是相互關聯的。

各種子查詢之間還可以相互嵌套。在Oracle系統中，子查詢的嵌套層數可以達到255層，但是真正嵌套255層的子查詢是很少的。實際上，嵌套的層數越多，查詢語句的執行效率也就越差，因此應該盡量降低子查詢的嵌套層數。

4.3.2 單行子查詢

在單行子查詢中，該內查詢只返回單行單列值，因此可以把這種子查詢作為一個常量。在WHERE子句中，可以使用單行比較運算符來比較某個表達式與子查詢的結果。可以使用的單行比較運算符包括等于“=”、大于“>”、大于或等于“>=”、小于“<”、小于或等于“<=”和不等于“<>/!=”。

例如，下面的語句在子查詢中使用統計函數，從EMP表中得到薪金最低和薪金最高的員工信息。

在執行子查詢的過程中，如果內查詢的結果是空值，那么外查詢的條件始終不會滿足，該查詢的最終結果是空值。例如，在下面的查詢中，子查詢語句試圖查找部門名為MANAGER的信息。但是，由于在DEPT表中不存在該部門的信息，所以子查詢語句的結果是空值。這樣WHERE子句中的條件總為FALSE，因此該查詢的最終結果也是空值。

在單行子查詢中，常見的錯誤是在子查詢中返回了多行數據或者包含了ORDER BY子句。如果在單行子查詢中返回了多行數據，那么這個查詢就會發生錯誤，系統無法正確執行該操作。

在子查詢中也不能包含ORDER BY子句，如果希望對數據進行排序，那么只能在外查詢語句中使用ORDER BY子句。

4.3.3 多行子查詢

多行子查詢可以返回單列多行的數據。在這種多行子查詢中，必須使用多行運算符來判斷，而不能使用單行運算符。使用多行運算符可以執行與一個或多個數據的比較操作。在Oracle系統中，可以使用的多行比較運算符包括：IN（等于列表中的任何一值）、ANY（與子查詢返回的每個值進行比較）和ALL（與子查詢返回的所有值進行比較）。

ANY運算符表示與子查詢中的每個值進行比較。這時，需要將單行比較運算符與該運算符組合起來使用。與單行比較運算符組合之后，所使用的ANY運算符結果如下。

※ <any：表示小于最大值。

※ =any：與IN運算符等價。

※ >any：表示大于最小值。

對于ALL運算符而言，與單行比較運算符組合之后，所使用的ANY運算符結果如下。

※ <all：表示小于最小值。

※ >all：表示大于最大值。

在下面的示例，將練習使用ALL、ANY和IN運算符進行查詢。

① 以SCOTT身份連接數據庫。

② 在子查詢的比較條件中，使用>ANY運算符查詢大于MANAGER職位中最小薪金的員工信息。

③ 下面是使用IN運算符的多行子查詢，用于查詢屬于ACCOUNTING和RESEARCH部門的員工信息。

④ 下面是使用ALL運算符的多行子查詢，用于查詢薪金大于所有MANAGER職位的員工信息。

    SQL> select empno,ename,job,sal
      2  from emp where sal >all(select sal from emp where job='MANAGER');

另外，在使用IN、ALL和ANY等多行比較運算符時，還可以使用NOT運算符，表示取反。

4.3.4 關聯子查詢

在前面介紹的子查詢中，內查詢和外查詢是分開執行的，即內查詢的執行與外查詢的執行是沒有關系的，而外查詢僅使用內查詢的最終結果。在子查詢語句中，當內查詢的執行需要借助于外查詢，而外查詢的執行又離不開內查詢的執行時，內查詢和外查詢是相互關聯的，這種子查詢稱為“關聯子查詢”。例如，在內層被嵌套的SELECT語句中包含了外層SELECT語句中的員工代碼。這類子查詢在某些情況下可能會產生一定的問題，因為內層子查詢返回的記錄都是外層查詢所操作的候選對象，當數據量較大時，這會導致查詢效率低下。

現在使用關聯子查詢檢索某個職位的員工薪金是否超出了平均水平，所使用的查詢語句如下。

在上面的查詢語句中，外層查詢使用關聯子查詢計算每個職位的平均薪金。而關聯子查詢必須知道每個員工的職位，以便外層查詢尋找該員工的平均薪金是否高于所在部門的平均值。如果薪金高于平均薪金，則該員工的信息結果會顯示出來。在執行語句的過程中，必須遍歷EMP表中的每條員工記錄，因此如果EMP中有許多記錄，則該語句的執行速度將會異常緩慢。