子查詢、集合查詢

子查詢是一個嵌套在 SELECT、INSERT、UPDATE 或 DELETE 語句或其他子查詢中的查詢。任何允許使用表達式的地方都可以使用子查詢，換句話說，子查詢幾乎可以出現在一條 SQL 語句的任意位置上，且必須用一對小括号來包裹子查詢的定義。本系列部落格的第 12 篇的 4.2 節已經示範了常見的三種子查詢寫法。子查詢也稱為内部查詢或内部選擇，而包含子查詢的語句也稱為外部查詢或外部選擇。

本人查閱了大量資料，似乎根本就找不到一種“正确的子查詢分類方法”。常見的分類名有：單行子查詢、多行子查詢、多列子查詢、相關子查詢、标量子查詢、内聯視圖等，前三個顧名思義，後三個分别簡要說明如下：

相關子查詢：子查詢引用了外部查詢中包含的一列或多列，也就是說内部查詢得依靠外部查詢獲得值，這樣的子查詢被稱為相關子查詢，也稱為重複子查詢。

标量子查詢：隻傳回單一值的子查詢稱為标量子查詢。

内聯視圖：一般是指寫在 FROM 子句後面的子查詢，它本質上是視圖，也稱内嵌視圖，與标準視圖的差別主要是無需事先編寫建立視圖的語句，便于執行查詢。

下面我再補充幾個 WHERE 子句帶子查詢的示例：

WITH 子查詢的作用類似于内聯視圖，包括内聯視圖在内的其它子查詢都隻能夠引用一次；而 WITH 子查詢需要在引用之前先定義，一旦定義了在整個查詢的後續部分就可以按名稱來反複引用，從這點來看又很像臨時表。Oracle 從 11g R2 開始支援遞歸的 WITH，即允許在 WITH 子查詢的定義中對自身引用，而其它資料庫，如 SQL Server、PostgreSQL、DB2 等都先于 Oracle 支援這一特性。文法示例：

案例一：查詢年齡在25歲及以下，固定工資在5000及以上的員工基本資訊。示例：

結果：

案例二：統計個個部門的人數、總工資、平均工資、最高工資、最低工資。示例：

案例二不使用 WITH 的示例：

在數學中可以對集合做交并差運算，在 SQL 中同樣可以對查詢結果集做交并差操作。這三種 SQL 集合查詢對應的操作符關鍵字分别是 INTERSECT、UNION/UNION ALL、MINUS。

最常見的 SQL 集合查詢就是并集查詢，并集查詢操作符有 UNION 和 UNION ALL 兩個，用法完全一緻。示例：

從 UNION 和 UNION ALL 的查詢結果來看，它們的差別之一就是：UNION 會去除重複行，而 UNION ALL 會保留重複行，如果把上例中的 ALL 關鍵字去掉，第 1、3 行中就會有 1 行被當作重複行去除；差別之二就是：結果集預設的排序不同，UNION ALL 隻是按關聯次序來組織資料，不再排序，而 UNION 将會按預設規則對整個資料集進行排序。另外，UNION ALL 的運算效率比 UNION 要高，故此，UNION ALL 也相對常用一些。

可通過 MINUS 操作符求兩個結果集的差集。差集結果集不包括重複行，且會按預設規則排序。示例：

可通過 INTERSECT 操作符求兩個結果集的交集。交集結果集不包括重複行，且會按預設規則排序。示例：

無論是交并差中的那種集合查詢，隻需要在最後一個查詢的後面加上 ORDER BY 子句，即可對整個結果集排序。示例：

有個需要注意的細節問題是：這個 ORDER BY 後的排序字段不能加任何别名限定，這個也好了解，畢竟排序操作是針對整個結果集的。也就是說上例中 ORDER BY 後的 <code>dept_code</code> 不屬于 t1、t2、t3 中任何一個表，而是整個結果集的。那萬一 t1、t2、t3 表中的字段名不相同的話，排序的字段名又改如何确定呢？本人的測試結果是：它一定是第 1 個查詢裡的字段名或列别名，但前提是後面所有查詢的該列都是真實字段名，或列别名與第 1 個查詢裡的字段名或列别名相同，NULL 除外。如果你嫌這個規則太複雜也不好記，那麼也可以給所有查詢的該列取一個相同的别名。另外還可以利用 ORDER BY 的另一個文法規則：<code>ORDER BY N</code>，即直接寫排序字段的列編号。如上例的查詢想要根據第 2 列來排序，這一這麼來寫：

DISTINCT 子句的作用就是把一組資料中的重複行去掉，留下唯一的資料。

示例：

COUNT、SUM、MAX、MIN、AVG 五個常見聚合函數内部都支援 DISTINCT。

文法：

示例一：

示例二：