來源 | Learning SQL Generate, Manipulate, and Retrieve Data， Third Edition
作者 | Alan Beaulieu
譯者 | Liangchu
校對 | gongyouliu
編輯 | auroral-L
全文共14790字，預計閱讀時間70分鐘。
第九章 SQL 子查詢
1. 什麼是子查詢
2. 子查詢類型
3. 非關聯子查詢
3.1 多行，單列子查詢
3.1.1 in 和 not in 操作符
3.1.2 all 操作符
3.1.2 any 操作符
3.2 多列子查詢
4. 關聯子查詢
4.1 exists 操作符
4.2 使用關聯子查詢操縱資料
5. 用到子查詢的情況
5.1 子查詢作為資料源
5.1.1 資料加工
5.1.2 面向任務的子查詢
5.1.3 公用表表達式
5.2 子查詢作為表達式生成器
6. 子查詢總結
子查詢是一個可以在總共四種SQL資料語句中使用的強大工具。在本章中，我将向你展示如何使用子查詢來過濾資料、生成值和構造臨時資料集。經過實驗之後，你應該就能認識到子查詢是SQL語言最強大的特性之一了。
1. 什麼是子查詢
子查詢（subquery）是包含在另一個SQL語句（後文中我用包含語句 containing statement代稱）中的查詢。子查詢總是用括号括起來，并且通常在包含語句之前執行。與其他查詢一樣，子查詢傳回的結果集類型包括：
• 單列單行；
• 單列多行；
• 多列多行。
子查詢傳回的結果集的類型決定了它是如何被使用以及包含語句可以使用哪些操作符來處理子查詢傳回的資料。當執行完包含語句後，所有子查詢傳回的資料都将被丢棄，這使得子查詢像一個具有語句作用域的臨時表（這意味着在SQL語句完成執行後，伺服器将釋放用于存儲子查詢結果的記憶體）。
其實你在前面的章節中已經看到過一些子查詢的示例了，不過現在還是以一個簡單的示例開始：
mysql> SELECT customer_id, first_name, last_name
-> FROM customer
-> WHERE customer_id = (SELECT MAX(customer_id) FROM customer);
+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
| 599 | AUSTIN | CINTRON |
+-------------+------------+-----------+
1 row in set (0.27 sec)
在本例中，子查詢傳回customer表中customer_id列的最大值，然後包含語句傳回關于該客戶的資料。如果你搞不清楚子查詢是如何工作的，那麼可以嘗試單獨運作子查詢（不帶括号）看看它傳回的内容。下面是上一個例子中子查詢的示例：
mysql> SELECT MAX(customer_id) FROM customer;
+------------------+
| MAX(customer_id) |
+------------------+
| 599 |
+------------------+
1 row in set (0.00 sec)
子查詢傳回單行單列結果，是以它可以被用作相等條件中的表達式之一（如果子查詢傳回兩行或更多行資料，則可以将其與某個行進行比較，但不能進行判等操作，我在後面會介紹這一點的）。在這種情況下，你可以用子查詢傳回的值替換包含查詢中過濾條件的右側表達式，如下所示：
mysql> SELECT customer_id, first_name, last_name
-> FROM customer
-> WHERE customer_id = 599;
+-------------+------------+-----------+
| customer_id | first_name | last_name |
+-------------+------------+-----------+
| 599 | AUSTIN | CINTRON |
+-------------+------------+-----------+
1 row in set (0.00 sec)
在這種情況下，子查詢很有用，因為它允許你在單個查詢中檢索有關最大customer_id的資訊，而不是先使用一個查詢檢索最大的ID，然後再編寫一個查詢從customer表中擷取所需的資料。你将看到，子查詢在許多其他情況下也很有用，并且可能成為SQL工具包中最強大的工具之一。
2. 子查詢類型
除了前面提到的關于子查詢傳回的結果集類型的差異（單行/單列、單行/多列或多行多列）之外，你也可以按照其他标準給子查詢分類：有些子查詢是完全獨立的（稱為非關聯子查詢），而另有些查詢則引用包含語句中的列（稱為關聯查詢）。接下來的幾節将探讨這兩個子查詢類型，并示範可以用來與它們互動的操作符示例。
3. 非關聯子查詢
本章前面的示例是一個非關聯子查詢，它可以單獨執行并且不需要引用包含語句中的任何内容。除非你編寫的是update語句或delete語句（通常使用關聯子查詢），否則你遇到的大多數子查詢都是這種類型的非關聯子查詢。前面的示例除了是非關聯子查詢，它也是一個傳回單行單列結果集的子查詢，像這樣的子查詢叫做标量子查詢（scalar subquery）并且可以出現在條件中常用操作符号（=，<>，<，>，<=，>=）的任意一側。下面的示例示範如何在不等條件中使用标量子查詢：
mysql> SELECT city_id, city
-> FROM city
-> WHERE country_id <>
-> (SELECT country_id FROM country WHERE country = 'India');
+---------+----------------------------+
| city_id | city |
+---------+----------------------------+
| 1 | A Corua (La Corua) |
| 2 | Abha |
| 3 | Abu Dhabi |
| 4 | Acua |
| 5 | Adana |
| 6 | Addis Abeba |
...
| 595 | Zapopan |
| 596 | Zaria |
| 597 | Zeleznogorsk |
| 598 | Zhezqazghan |
| 599 | Zhoushan |
| 600 | Ziguinchor |
+---------+----------------------------+
540 rows in set (0.02 sec)
此查詢傳回所有不在印度的城市。這個子查詢基于查詢語句的最後一行，這行傳回了印度城市的ID，然後包含語句傳回所有和印度城市ID不等的城市資訊。盡管這是個十分簡單的例子，但是實際上的子查詢可能也會很複雜，并且也可能和所有可用的查詢子句（select、from、where、group by、having和order by）結合使用，這些都是根據需求決定的。
如果在相等條件中使用子查詢，但子查詢傳回多行結果，則會報錯。例如，如果修改上一個查詢，使得子查詢傳回除印度以外的所有國家，則會産生以下錯誤：
mysql> SELECT city_id, city
-> FROM city
-> WHERE country_id <>
-> (SELECT country_id FROM country WHERE country <> 'India');
ERROR 1242 (21000): Subquery returns more than 1 row
如果單獨運作子查詢，将看到結果如下：
mysql> SELECT country_id FROM country WHERE country <> 'India';
+------------+
| country_id |
+------------+
| 1 |
| 2 |
| 3 |
| 4 |
...
| 106 |
| 107 |
| 108 |
| 109 |
+------------+
108 rows in set (0.00 sec)
包含查詢失敗，這是因為表達式（country_id）不能等同于一組表達式（包括country_id為1、2、3、…、109）。換句話說，單個事物不能等于一組事物的集合。在下一節中，我會介紹如何使用不同的操作符來解決問題。
3.1 多行，單列子查詢
正如上一個例子所示，如果子查詢傳回多行資料，則無法在相等條件的一側使用它，但是你也可以使用另外四個操作符來為這種類型的子查詢建構條件。
3.1.1 in和not in操作符
雖然不能對單個值與一個值集進行判等操作，但你可以檢查某個值集中是否存在某一個值。下一個示例雖然沒用子查詢，但示範了如何構造用in操作符在一組值中查找某個值的條件：
mysql> SELECT country_id
-> FROM country
-> WHERE country IN ('Canada','Mexico');
+------------+
| country_id |
+------------+
| 20 |
| 60 |
+------------+
2 rows in set (0.00 sec)
條件左側的表達式是country列，而條件右側是一組字元串。in操作符檢查是否可以在country列中找到字元串集合中的任意一個值，如果能則滿足條件，該行會被添加到結果集中。你也可以使用使用兩個相等條件獲得相同的結果，如：
mysql> SELECT country_id
-> FROM country
-> WHERE country = 'Canada' OR country = 'Mexico';
+------------+
| country_id |
+------------+
| 20 |
| 60 |
+------------+
2 rows in set (0.00 sec)
當集合隻包含兩個表達式時，這種方法似乎是合理的，但是當集合包括很多（成百上千）值，那麼就很好了解為什麼最好使用in操作符了。
你可能偶爾會建立一組字元串、日期或數字集合并将它用于條件的某一側，但是你更可能會通過執行子查詢生成這個集合。下面的查詢使用in操作符并在過濾條件的右側使用子查詢傳回加拿大或墨西哥的所有城市集合：
mysql> SELECT city_id, city
-> FROM city
-> WHERE country_id IN
-> (SELECT country_id
-> FROM country
-> WHERE country IN ('Canada','Mexico'));
+---------+----------------------------+
| city_id | city |
+---------+----------------------------+
| 179 | Gatineau |
| 196 | Halifax |
| 300 | Lethbridge |
| 313 | London |
| 383 | Oshawa |
| 430 | Richmond Hill |
| 565 | Vancouver |
...
| 452 | San Juan Bautista Tuxtepec |
| 541 | Torren |
| 556 | Uruapan |
| 563 | Valle de Santiago |
| 595 | Zapopan |
+---------+----------------------------+
37 rows in set (0.00 sec)
除了檢視某組值集中是否存在某個值外，還可以使用not in操作符檢查相反的情況。下面是使用not in代替in實作上一個查詢的例子：
mysql> SELECT city_id, city
-> FROM city
-> WHERE country_id NOT IN
-> (SELECT country_id
-> FROM country
-> WHERE country IN ('Canada','Mexico'));
+---------+----------------------------+
| city_id | city |
+---------+----------------------------+
| 1 | A Corua (La Corua) |
| 2 | Abha |
| 3 | Abu Dhabi |
| 5 | Adana |
| 6 | Addis Abeba |
...
| 596 | Zaria |
| 597 | Zeleznogorsk |
| 598 | Zhezqazghan |
| 599 | Zhoushan |
| 600 | Ziguinchor |
+---------+----------------------------+
563 rows in set (0.00 sec)
此查詢查找不在加拿大或墨西哥的所有城市。
3.1.2 all操作符
in操作符用于檢查是否可以在一組表達式集合中找到某個表達式，而all操作符則允許将某個值與集合中的每個值進行比較。要建構這樣的條件，通常需要将比較操作符（=，<>，<，>等）與all操作符結合使用。例如，下面的查詢查找從來沒享受過免費租賃電影服務的客戶：
mysql> SELECT first_name, last_name
-> FROM customer
-> WHERE customer_id <> ALL
-> (SELECT customer_id
-> FROM payment
-> WHERE amount = 0);
+-------------+--------------+
| first_name | last_name |
+-------------+--------------+
| MARY | SMITH |
| PATRICIA | JOHNSON |
| LINDA | WILLIAMS |
| BARBARA | JONES |
...
| EDUARDO | HIATT |
| TERRENCE | GUNDERSON |
| ENRIQUE | FORSYTHE |
| FREDDIE | DUGGAN |
| WADE | DELVALLE |
| AUSTIN | CINTRON |
+-------------+--------------+
576 rows in set (0.01 sec)
子查詢傳回免費租賃過電影的客戶的ID集，包含查詢傳回ID不在子查詢傳回的集合中所有客戶的名稱。你可能會覺得這種方法很笨拙，其實大多數人也是這麼想的，是以他們甯願使用不同的查詢短語以避免使用all操作符。為了說明這一點，下面的示例使用not in操作符實作上一個查詢的例子如下：
SELECT first_name, last_name
FROM customer
WHERE customer_id NOT IN
(SELECT customer_id
FROM payment
WHERE amount = 0)
其實采用哪種方法隻是一種偏好問題，但是我認為大多數人會覺得使用not in版本的查詢更好了解。
注意：使用not in或<> all将值與值集進行比較時，必須確定值集不包含null值，因為伺服器會将表達式左側的值與集合的每個成員進行判等操作，而任何試圖将值與null值進行比較的操作都會産生未知的結果。是以，以下查詢傳回一個空集：
mysql> SELECT first_name, last_name
-> FROM customer
-> WHERE customer_id NOT IN (122, 452, NULL);
Empty set (0.00 sec)
下面是另一個使用all操作符的示例，但這次子查詢位于having子句中：
mysql> SELECT customer_id, count(*)
-> FROM rental
-> GROUP BY customer_id
-> HAVING count(*) > ALL
-> (SELECT count(*)
-> FROM rental r
-> INNER JOIN customer c
-> ON r.customer_id = c.customer_id
-> INNER JOIN address a
-> ON c.address_id = a.address_id
-> INNER JOIN city ct
-> ON a.city_id = ct.city_id
-> INNER JOIN country co
-> ON ct.country_id = co.country_id
-> WHERE co.country IN ('United States','Mexico','Canada')
-> GROUP BY r.customer_id
-> );
+-------------+----------+
| customer_id | count(*) |
+-------------+----------+
| 148 | 46 |
+-------------+----------+
1 row in set (0.01 sec)
本例中的子查詢傳回北美所有客戶的電影租賃總數，包含查詢傳回電影租賃總數超過任何北美客戶的所有客戶。
3.1.3 any操作符
與all操作符一樣，any操作符允許将一個值與一組值集中的成員進行比較，但是與all不同的是，使用any操作符時，隻要有一個比較是成立的，則結果為true。下面的示例查詢電影租金總額超過玻利維亞、巴拉圭或智利所有客戶總付款的所有客戶：
mysql> SELECT customer_id, sum(amount)
-> FROM payment
-> GROUP BY customer_id
-> HAVING sum(amount) > ANY
-> (SELECT sum(p.amount)
-> FROM payment p
-> INNER JOIN customer c
-> ON p.customer_id = c.customer_id
-> INNER JOIN address a
-> ON c.address_id = a.address_id
-> INNER JOIN city ct
-> ON a.city_id = ct.city_id
-> INNER JOIN country co
-> ON ct.country_id = co.country_id
-> WHERE co.country IN ('Bolivia','Paraguay','Chile')
-> GROUP BY co.country
-> );
+-------------+-------------+
| customer_id | sum(amount) |
+-------------+-------------+
| 137 | 194.61 |
| 144 | 195.58 |
| 148 | 216.54 |
| 178 | 194.61 |
| 459 | 186.62 |
| 526 | 221.55 |
+-------------+-------------+
6 rows in set (0.03 sec)
子查詢傳回玻利維亞、巴拉圭和智利所有客戶的電影租金總額，包含查詢傳回至少超過這三個國家中一個國家的所有客戶（如果你發現自己的花費超過了整個國家的總花費，那麼你可以考慮取消訂閱Netflix并訂閱玻利維亞、巴拉圭或者智利了）。
注意：使用=any和in等效，不過大多數人還是喜歡使用in操作符。
3.2 多列子查詢
到目前為止，本章中的子查詢示例傳回單行單列或多行結果。但是某些情況下你也可能使用傳回不止一列資料的子查詢。為了展示多列子查詢的實用性，下面首先看一個使用多重單列子查詢的示例：
mysql> SELECT fa.actor_id, fa.film_id
-> FROM film_actor fa
-> WHERE fa.actor_id IN
-> (SELECT actor_id FROM actor WHERE last_name = 'MONROE')
-> AND fa.film_id IN
-> (SELECT film_id FROM film WHERE rating = 'PG');
+----------+---------+
| actor_id | film_id |
+----------+---------+
| 120 | 63 |
| 120 | 144 |
| 120 | 414 |
| 120 | 590 |
| 120 | 715 |
| 120 | 894 |
| 178 | 164 |
| 178 | 194 |
| 178 | 273 |
| 178 | 311 |
| 178 | 983 |
+----------+---------+
11 rows in set (0.00 sec)
此查詢使用兩個子查詢來檢索姓為Monroe的所有演員和分級為PG的所有電影，而包含查詢使用子查詢傳回的資訊檢索姓為Monroe的演員出現在分級為PG的電影中的所有情況。但你也可以将兩個單列子查詢合并到一個多列子查詢中，并将結果與film_actor表中的兩列進行比較。為此，你的過濾條件必須命名film_actor表中的兩列，并用括号括起來，使其順序與子查詢傳回的順序相同，如下所示：
mysql> SELECT actor_id, film_id
-> FROM film_actor
-> WHERE (actor_id, film_id) IN
-> (SELECT a.actor_id, f.film_id
-> FROM actor a
-> CROSS JOIN film f
-> WHERE a.last_name = 'MONROE'
-> AND f.rating = 'PG');
+----------+---------+
| actor_id | film_id |
+----------+---------+
| 120 | 63 |
| 120 | 144 |
| 120 | 414 |
| 120 | 590 |
| 120 | 715 |
| 120 | 894 |
| 178 | 164 |
| 178 | 194 |
| 178 | 273 |
| 178 | 311 |
| 178 | 983 |
+----------+---------+
11 rows in set (0.00 sec)
該查詢的功能與上一個執行個體的功能相同，但是它使用傳回兩列的單一查詢代替了兩個隻傳回單列的子查詢。此版本中的子查詢使用一種稱為交叉連接配接（cross join）的連接配接類型，我們會在下一章中讨論它。本查詢的基本思想是傳回所有叫Monroe的演員（2個）與所有分級為PG的電影（194）的組合，總共388行，其中11行可以在film_actor表中找到。
4. 關聯子查詢
到目前為止，所有的子查詢示例都是獨立于包含語句的，這意味着你可以單獨執行它們并檢視結果。此外關聯子查詢依賴于包含語句并且引用其一列或多個列。與非關聯子查詢不同，關聯子查詢在執行包含語句之前不會一次執行完畢，而是對每個候選行（可能包含在最終結果中的行）執行一次。例如，下面的查詢使用一個關聯子查詢來統計每個客戶的電影租賃數量，然後包含查詢将檢索正好租賃了20部電影的客戶：
mysql> SELECT c.first_name, c.last_name
-> FROM customer c
-> WHERE 20 =
-> (SELECT count(*) FROM rental r
-> WHERE r.customer_id = c.customer_id);
+------------+-------------+
| first_name | last_name |
+------------+-------------+
| LAUREN | HUDSON |
| JEANETTE | GREENE |
| TARA | RYAN |
| WILMA | RICHARDS |
| JO | FOWLER |
| KAY | CALDWELL |
| DANIEL | CABRAL |
| ANTHONY | SCHWAB |
| TERRY | GRISSOM |
| LUIS | YANEZ |
| HERBERT | KRUGER |
| OSCAR | AQUINO |
| RAUL | FORTIER |
| NELSON | CHRISTENSON |
| ALFREDO | MCADAMS |
+------------+-------------+
15 rows in set (0.01 sec)
子查詢末尾對c.customer_id的引用使子查詢具有相關性，也就是說，它的執行必須依賴于包含查詢提供的c.customer_id的值。在本例中，包含查詢從customer表中檢索所有599行資料，并為每個客戶執行一次子查詢，為每次執行傳遞相應的的客戶ID。如果子查詢傳回值20，則滿足過濾條件，并将該行添加到結果集中。
注意：由于關聯子查詢對包含查詢的每一行執行一次，是以如果包含查詢傳回的行數太多，那麼使用關聯子查詢可能會導緻性能問題。
除了相等條件外，你還可以在其他類型的條件中使用相關子查詢，如下是使用範圍條件的例子：
mysql> SELECT c.first_name, c.last_name
-> FROM customer c
-> WHERE
-> (SELECT sum(p.amount) FROM payment p
-> WHERE p.customer_id = c.customer_id)
-> BETWEEN 180 AND 240;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| RHONDA | KENNEDY |
| CLARA | SHAW |
| ELEANOR | HUNT |
| MARION | SNYDER |
| TOMMY | COLLAZO |
| KARL | SEAL |
+------------+-----------+
6 rows in set (0.03 sec)
在上一個查詢的基礎上，這個變體示例檢索所有電影租金總額在180美元到240美元之間的客戶。同樣地，關聯子查詢也執行599次（每個客戶行一次），每次執行子查詢都傳回給定客戶的總帳戶餘額。
注意
上面查詢與先前查詢的微妙差別是子查詢位于條件的左側，這看起來可能有點奇怪，但非常有效。
4.1 exists操作符
雖然你經常會看到應用于等式和範圍條件中的關聯子查詢，但其實用于建構包含關聯子查詢的條件的最常見操作符是exists操作符。如果隻考慮關系的存在性而不考慮數量問題，則可以使用exists操作符。例如，以下查詢将查找在2005年5月25日之前至少租用一部影片的所有客戶（不考慮租用了多少部影片）：
mysql> SELECT c.first_name, c.last_name
-> FROM customer c
-> WHERE EXISTS
-> (SELECT 1 FROM rental r
-> WHERE r.customer_id = c.customer_id
-> AND date(r.rental_date) < '2005-05-25');
+------------+-------------+
| first_name | last_name |
+------------+-------------+
| CHARLOTTE | HUNTER |
| DELORES | HANSEN |
| MINNIE | ROMERO |
| CASSANDRA | WALTERS |
| ANDREW | PURDY |
| MANUEL | MURRELL |
| TOMMY | COLLAZO |
| NELSON | CHRISTENSON |
+------------+-------------+
8 rows in set (0.03 sec)
使用exists操作符，子查詢可以傳回零行、一行或多行結果，條件隻是檢查子查詢是否傳回一行或多行。如果你檢視子查詢的select子句，你将看到它由單個文本（1）組成，因為包含查詢中的條件隻需要知道傳回了多少行，是以與子查詢傳回的實際資料是無關的。其實你的子查詢可以傳回你喜歡的任何内容，如下所示：
mysql> SELECT c.first_name, c.last_name
-> FROM customer c
-> WHERE EXISTS
-> (SELECT r.rental_date, r.customer_id, 'ABCD' str, 2 * 3 / 7 nmbr
-> FROM rental r
-> WHERE r.customer_id = c.customer_id
-> AND date(r.rental_date) < '2005-05-25');
+------------+-------------+
| first_name | last_name |
+------------+-------------+
| CHARLOTTE | HUNTER |
| DELORES | HANSEN |
| MINNIE | ROMERO |
| CASSANDRA | WALTERS |
| ANDREW | PURDY |
| MANUEL | MURRELL |
| TOMMY | COLLAZO |
| NELSON | CHRISTENSON |
+------------+-------------+
8 rows in set (0.03 sec)
但是，一般約定使用exists時指定select 1 or select *。
但你也可以使用not exists檢查子查詢傳回行數是否是0，如下所示：
mysql> SELECT a.first_name, a.last_name
-> FROM actor a
-> WHERE NOT EXISTS
-> (SELECT 1
-> FROM film_actor fa
-> INNER JOIN film f ON f.film_id = fa.film_id
-> WHERE fa.actor_id = a.actor_id
-> AND f.rating = 'R');
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| JANE | JACKMAN |
+------------+-----------+
1 row in set (0.00 sec)
這個查詢檢索所有從未在R級電影中出現過的演員。
4.2 使用關聯子查詢操縱資料
到目前為止，本章中所有的示例都是select語句，但這并不意味着子查詢在其他SQL語句中毫無用處。其實子查詢在update、delete和insert語句中也有大量應用，特别是在update和delete語句中有頻繁出現。下面的關聯子查詢示例修改customer表中的last_update列：
UPDATE customer c
SET c.last_update =
(SELECT max(r.rental_date) FROM rental r
WHERE r.customer_id = c.customer_id);
此語句通過查找rental表中每個客戶的最新租賃日期來修改customer表中的每一行（因為沒有指定where子句）。雖然認為每個客戶至少與一個電影租賃交易相關聯是合情合理的，但是最好還是在運作這個update語句之前檢查一下是否每個客戶都有租賃過電影，否則會因為子查詢不傳回任何行導緻last_update列的值為空值null。下面是update語句的另一個版本，但這次使用帶有第二個關聯子查詢的where子句：
UPDATE customer c
SET c.last_update =
(SELECT max(r.rental_date) FROM rental r
WHERE r.customer_id = c.customer_id)
WHERE EXISTS
(SELECT 1 FROM rental r
WHERE r.customer_id = c.customer_id);
除了select子句外，這兩個關聯子查詢是相同的。但是，set子句中的子查詢僅在update語句的where子句中的條件的計算結果為true時執行（這意味着至少為客戶找到了一個租賃記錄），進而保護last_update列中的資料不被空值覆寫。
關聯子查詢在delete語句中也很常見。例如，你可以在每個月末運作一個資料維護腳本以删除無用資料。腳本可能包含以下語句，該語句從customer表中删除過去一年中沒有租過電影的使用者行：
DELETE FROM customer
WHERE 365 < ALL
(SELECT datediff(now(), r.rental_date) days_since_last_rental
FROM rental r
WHERE r.customer_id = customer.customer_id);
請注意，在MySQL中使用帶有delete語句的關聯子查詢時，不論如何都不能使用表别名，這就是為什麼我在子查詢中使用了表全名的原因。但是在其他大多數資料庫伺服器中，是可以為customer表提供别名的，例如：
DELETE FROM customer c
WHERE 365 < ALL
(SELECT datediff(now(), r.rental_date) days_since_last_rental
FROM rental r
WHERE r.customer_id = c.customer_id);
5. 用到子查詢的情況
現在你已經學習過不同類型的子查詢，以及可以與子查詢傳回的資料進行互動的各種操作符，接下來就可以探讨使用子查詢建構強大SQL語句的多種方法了。接下來的三小節将示範如何使用子查詢來構造自定義表、構造條件以及在結果集中生成列值。
5.1 子查詢作為資料源
我在第三章中就說過select語句中from子句的作用是包含需要查詢的表。由于子查詢生成的結果集包含行資料和列資料，是以完全可以在from子句中包含子查詢以及表。乍一看，這似乎是一個有趣但是沒什麼實用價值的特性，但事實上這可能是編寫查詢時可用的最強大的工具之一。下面是一個簡單的例子：
mysql> SELECT c.first_name, c.last_name,
-> pymnt.num_rentals, pymnt.tot_payments
-> FROM customer c
-> INNER JOIN
-> (SELECT customer_id,
-> count(*) num_rentals, sum(amount) tot_payments
-> FROM payment
-> GROUP BY customer_id
-> ) pymnt
-> ON c.customer_id = pymnt.customer_id;
+-------------+--------------+-------------+--------------+
| first_name | last_name | num_rentals | tot_payments |
+-------------+--------------+-------------+--------------+
| MARY | SMITH | 32 | 118.68 |
| PATRICIA | JOHNSON | 27 | 128.73 |
| LINDA | WILLIAMS | 26 | 135.74 |
| BARBARA | JONES | 22 | 81.78 |
| ELIZABETH | BROWN | 38 | 144.62 |
...
| TERRENCE | GUNDERSON | 30 | 117.70 |
| ENRIQUE | FORSYTHE | 28 | 96.72 |
| FREDDIE | DUGGAN | 25 | 99.75 |
| WADE | DELVALLE | 22 | 83.78 |
| AUSTIN | CINTRON | 19 | 83.81 |
+-------------+--------------+-------------+--------------+
599 rows in set (0.03 sec)
在本例中，子查詢生成一個客戶ID清單，以及電影租賃數量和總付款額。下面是子查詢生成的結果集：
mysql> SELECT customer_id, count(*) num_rentals, sum(amount) tot_payments
-> FROM payment
-> GROUP BY customer_id;
+-------------+-------------+--------------+
| customer_id | num_rentals | tot_payments |
+-------------+-------------+--------------+
| 1 | 32 | 118.68 |
| 2 | 27 | 128.73 |
| 3 | 26 | 135.74 |
| 4 | 22 | 81.78 |
...
| 596 | 28 | 96.72 |
| 597 | 25 | 99.75 |
| 598 | 22 | 83.78 |
| 599 | 19 | 83.81 |
+-------------+-------------+--------------+
599 rows in set (0.03 sec)
子查詢的名稱為pymnt，并通過customer_id列連接配接到customer表。然後包含查詢從customer表中檢索客戶的名稱，以及pymnt子查詢中的租賃數量和總付款額。
from子句中使用的子查詢必須是非關聯的，它們首先執行，其資料會一直儲存在記憶體中直到包含查詢的執行完畢。子查詢為查詢的編寫提供了極大的靈活性，因為你可以遠遠超出可用表的範圍，幾乎可以用于建立所需資料的任何視圖，然後将結果連接配接到其他表或子查詢。如果你正在為外部系統編寫報告或生成資料源，則可以使用一個查詢來完成任務，而這個查詢通常需要多重查詢或使用過程語言來完成。
5.1.1 資料加工
除了使用子查詢彙總現有資料外，還可以使用子查詢生成資料庫中不存在的資料。例如，你可能希望按電影租金總額對客戶進行分組，但希望使用資料庫中未存儲的組定義。例如，假設你想将客戶分為下表（9-1）所示的組：
要在單個查詢中生成這些組，你首先需要定義一個用于生成組定義的查詢：
mysql> SELECT 'Small Fry' name, 0 low_limit, 74.99 high_limit
-> UNION ALL
-> SELECT 'Average Joes' name, 75 low_limit, 149.99 high_limit
-> UNION ALL
-> SELECT 'Heavy Hitters' name, 150 low_limit, 9999999.99 high_limit;
+---------------+-----------+------------+
| name | low_limit | high_limit |
+---------------+-----------+------------+
| Small Fry | 0 | 74.99 |
| Average Joes | 75 | 149.99 |
| Heavy Hitters | 150 | 9999999.99 |
+---------------+-----------+------------+
3 rows in set (0.00 sec)
上面的查詢中，我使用集合操作符union all将三個獨立查詢的結果合并到一個結果集中。每個查詢檢索三個文本，三個查詢的結果放在一起生成一個包含三行三列的結果集。現在你有了用于生成組的查詢，接下來将它添加到另一個查詢的from子句中即可：
mysql> SELECT pymnt_grps.name, count(*) num_customers
-> FROM
-> (SELECT customer_id,
-> count(*) num_rentals, sum(amount) tot_payments
-> FROM payment
-> GROUP BY customer_id
-> ) pymnt
-> INNER JOIN
-> (SELECT 'Small Fry' name, 0 low_limit, 74.99 high_limit
-> UNION ALL
-> SELECT 'Average Joes' name, 75 low_limit, 149.99 high_limit
-> UNION ALL
-> SELECT 'Heavy Hitters' name, 150 low_limit, 9999999.99 high_limit
-> ) pymnt_grps
-> ON pymnt.tot_payments
-> BETWEEN pymnt_grps.low_limit AND pymnt_grps.high_limit
-> GROUP BY pymnt_grps.name;
+---------------+---------------+
| name | num_customers |
+---------------+---------------+
| Average Joes | 515 |
| Heavy Hitters | 46 |
| Small Fry | 38 |
+---------------+---------------+
3 rows in set (0.03 sec)
from子句包含兩個子查詢：第一個子查詢pymnt傳回每個客戶的電影租賃總數和總付款額，而第二個子查詢pymnt_grps生成三個客戶分組。通過查找每個客戶所屬的三個組中的哪一個來連接配接這兩個子查詢，然後按組名稱對行進行分組，以便統計每個組中的客戶數。
當然，你也可以不使用子查詢，而是簡單地建構一個永久（或臨時）表來儲存組定義。使用這種方法，你可能會在一段時間後發現資料庫中到處都是這種小型專用表，并且你會忘了為什麼會建立這些表。然而通過使用子查詢，你就能遵守這樣的原則：隻有出于明确的業務目的，指明需要存儲新資料時，才将表添加到資料庫中。
5.1.2 面向任務的子查詢
假設你希望生成一個報告，顯示每個客戶的姓名、所在城市、租賃總次數和總付款金額。要實作這一點，你可以使用payment、customer、address和city表，然後根據客戶的名字和姓氏進行分組：
mysql> SELECT c.first_name, c.last_name, ct.city,
-> sum(p.amount) tot_payments, count(*) tot_rentals
-> FROM payment p
-> INNER JOIN customer c
-> ON p.customer_id = c.customer_id
-> INNER JOIN address a
-> ON c.address_id = a.address_id
-> INNER JOIN city ct
-> ON a.city_id = ct.city_id
-> GROUP BY c.first_name, c.last_name, ct.city;
+-------------+------------+-----------------+--------------+-------------+
| first_name | last_name | city | tot_payments | tot_rentals |
+-------------+------------+-----------------+--------------+-------------+
| MARY | SMITH | Sasebo | 118.68 | 32 |
| PATRICIA | JOHNSON | San Bernardino | 128.73 | 27 |
| LINDA | WILLIAMS | Athenai | 135.74 | 26 |
| BARBARA | JONES | Myingyan | 81.78 | 22 |
...
| TERRENCE | GUNDERSON | Jinzhou | 117.70 | 30 |
| ENRIQUE | FORSYTHE | Patras | 96.72 | 28 |
| FREDDIE | DUGGAN | Sullana | 99.75 | 25 |
| WADE | DELVALLE | Lausanne | 83.78 | 22 |
| AUSTIN | CINTRON | Tieli | 83.81 | 19 |
+-------------+------------+-----------------+--------------+-------------+
599 rows in set (0.06 sec)
該查詢傳回所需的資料，但如果仔細看一下該查詢，你會發現customer、address和city表僅起到描述的作用，而payment表包含生成分組（customer_id和amount）所需的所有内容。是以，你可以将生成分組的任務分離到子查詢中，然後将其他三個表連接配接到子查詢生成的表中，以獲得所需的最終結果。下面是分組子查詢：
mysql> SELECT customer_id,
-> count(*) tot_rentals, sum(amount) tot_payments
-> FROM payment
-> GROUP BY customer_id;
+-------------+-------------+--------------+
| customer_id | tot_rentals | tot_payments |
+-------------+-------------+--------------+
| 1 | 32 | 118.68 |
| 2 | 27 | 128.73 |
| 3 | 26 | 135.74 |
| 4 | 22 | 81.78 |
...
| 595 | 30 | 117.70 |
| 596 | 28 | 96.72 |
| 597 | 25 | 99.75 |
| 598 | 22 | 83.78 |
| 599 | 19 | 83.81 |
+-------------+-------------+--------------+
599 rows in set (0.03 sec)
上面正是查詢的核心。其他表（customer、address和city表）隻需要提供有意義的字元串來代替customer_id值。下一個查詢将上一個表連接配接到其他三個表：
mysql> SELECT c.first_name, c.last_name,
-> ct.city,
-> pymnt.tot_payments, pymnt.tot_rentals
-> FROM
-> (SELECT customer_id,
-> count(*) tot_rentals, sum(amount) tot_payments
-> FROM payment
-> GROUP BY customer_id
-> ) pymnt
-> INNER JOIN customer c
-> ON pymnt.customer_id = c.customer_id
-> INNER JOIN address a
-> ON c.address_id = a.address_id
-> INNER JOIN city ct
-> ON a.city_id = ct.city_id;
+-------------+------------+-----------------+--------------+-------------+
| first_name | last_name | city | tot_payments | tot_rentals |
+-------------+------------+-----------------+--------------+-------------+
| MARY | SMITH | Sasebo | 118.68 | 32 |
| PATRICIA | JOHNSON | San Bernardino | 128.73 | 27 |
| LINDA | WILLIAMS | Athenai | 135.74 | 26 |
| BARBARA | JONES | Myingyan | 81.78 | 22 |
...
| TERRENCE | GUNDERSON | Jinzhou | 117.70 | 30 |
| ENRIQUE | FORSYTHE | Patras | 96.72 | 28 |
| FREDDIE | DUGGAN | Sullana | 99.75 | 25 |
| WADE | DELVALLE | Lausanne | 83.78 | 22 |
| AUSTIN | CINTRON | Tieli | 83.81 | 19 |
+-------------+------------+-----------------+--------------+-------------+
599 rows in set (0.06 sec)
正所謂情人眼裡出西施，我認為這個版本的查詢比先前那個大而平的版本的查詢更令人滿意。該版本查詢的執行速度也更快，因為分組是在單個數字列（customer_id）而不是在多個冗長的字元串列（customer.first_name,customer.last_name,city.city）上進行的。
5.1.3 公用表表達式
公用表表達式（Common table expressions，又稱CTEs）是MySQL 8.0版的新特性，但其實在其他資料庫伺服器上已經有一段時間的曆史了。CTE是出現在with子句中查詢頂部的命名子查詢，互相之間由逗号分隔。除了使查詢更容易了解外，該特性還允許每個CTE引用在同一with子句中定義的任何其他CTE。以下示例包括三個CTE，其中第二個CTE引用第一個CTE，第三個CTE引用第二個CTE：
mysql> WITH actors_s AS
-> (SELECT actor_id, first_name, last_name
-> FROM actor
-> WHERE last_name LIKE 'S%'
-> ),
-> actors_s_pg AS
-> (SELECT s.actor_id, s.first_name, s.last_name,
-> f.film_id, f.title
-> FROM actors_s s
-> INNER JOIN film_actor fa
-> ON s.actor_id = fa.actor_id
-> INNER JOIN film f
-> ON f.film_id = fa.film_id
-> WHERE f.rating = 'PG'
-> ),
-> actors_s_pg_revenue AS
-> (SELECT spg.first_name, spg.last_name, p.amount
-> FROM actors_s_pg spg
-> INNER JOIN inventory i
-> ON i.film_id = spg.film_id
-> INNER JOIN rental r
-> ON i.inventory_id = r.inventory_id
-> INNER JOIN payment p
-> ON r.rental_id = p.rental_id
-> ) -- end of With clause
-> SELECT spg_rev.first_name, spg_rev.last_name,
-> sum(spg_rev.amount) tot_revenue
-> FROM actors_s_pg_revenue spg_rev
-> GROUP BY spg_rev.first_name, spg_rev.last_name
-> ORDER BY 3 desc;
+------------+-------------+-------------+
| first_name | last_name | tot_revenue |
+------------+-------------+-------------+
| NICK | STALLONE | 692.21 |
| JEFF | SILVERSTONE | 652.35 |
| DAN | STREEP | 509.02 |
| GROUCHO | SINATRA | 457.97 |
| SISSY | SOBIESKI | 379.03 |
| JAYNE | SILVERSTONE | 372.18 |
| CAMERON | STREEP | 361.00 |
| JOHN | SUVARI | 296.36 |
| JOE | SWANK | 177.52 |
+------------+-------------+-------------+
9 rows in set (0.18 sec)
此查詢計算級别為PG且演員包括姓氏以S開頭的演員的電影的租賃操作所産生的總收入。第一個子查詢（actors_S）查找姓氏以S開頭的所有演員，第二個子查詢（actors_s_pg）将該結果表連接配接到film表，并對分級為PG的電影進行過濾，第三個子查詢（actors_s_pg_revenue）将該結果表連接配接到payment表，以檢索為租用這些電影而支付的租金。最後一個查詢隻是将來自actors_s_pg_revenue的資料按名字/姓氏分組，并對收入進行求和操作。
注意
那些傾向于使用臨時表來存儲查詢結果以便在後續查詢中使用的人可能會發現CTE是一個有吸引力的替代方法。
5.2 子查詢作為表達式生成器
這是本章的最後一節，我會繼續講解本章剛開始提到的内容：單行單列的标量子查詢。除了可以用于過濾條件中，标量子查詢還可以用于表達式可以出現的任何位置，包括查詢中的select和order by子句以及insert語句中的values子句。
在179頁的“面向任務的子查詢”中，我展示了如何使用子查詢将分組機制從其他查詢中分離出來。下面是同一查詢的另一個版本，它使用子查詢實作相同的目的，但方法不同：
mysql> SELECT
-> (SELECT c.first_name FROM customer c
-> WHERE c.customer_id = p.customer_id
-> ) first_name,
-> (SELECT c.last_name FROM customer c
-> WHERE c.customer_id = p.customer_id
-> ) last_name,
-> (SELECT ct.city
-> FROM customer c
-> INNER JOIN address a
-> ON c.address_id = a.address_id
-> INNER JOIN city ct
-> ON a.city_id = ct.city_id
-> WHERE c.customer_id = p.customer_id
-> ) city,
-> sum(p.amount) tot_payments,
-> count(*) tot_rentals
-> FROM payment p
-> GROUP BY p.customer_id;
+-------------+------------+-----------------+--------------+-------------+
| first_name | last_name | city | tot_payments | tot_rentals |
+-------------+------------+-----------------+--------------+-------------+
| MARY | SMITH | Sasebo | 118.68 | 32 |
| PATRICIA | JOHNSON | San Bernardino | 128.73 | 27 |
| LINDA | WILLIAMS | Athenai | 135.74 | 26 |
| BARBARA | JONES | Myingyan | 81.78 | 22 |
...
| TERRENCE | GUNDERSON | Jinzhou | 117.70 | 30 |
| ENRIQUE | FORSYTHE | Patras | 96.72 | 28 |
| FREDDIE | DUGGAN | Sullana | 99.75 | 25 |
| WADE | DELVALLE | Lausanne | 83.78 | 22 |
| AUSTIN | CINTRON | Tieli | 83.81 | 19 |
+-------------+------------+-----------------+--------------+-------------+
599 rows in set (0.06 sec)
此查詢與前面那個在from子句中使用子查詢的早期查詢的兩個主要差別如下：
• 在select子句中使用關聯标量子查詢來查找客戶的名字/姓氏和城市，而不是将customer、address和city表連接配接到payment資料。
• 三次通路customer表（三個子查詢各一次），而不是一次。
customer表被通路了三次，因為标量子查詢隻能傳回單行單列，是以如果我們需要與customer表相關的三列資料，就必須使用三個不同的子查詢。
如前所述，标量子查詢也可以出現在order by子句中。以下查詢檢索演員的名字和姓氏，并按演員參演的電影數量多少進行排序：
mysql> SELECT a.actor_id, a.first_name, a.last_name
-> FROM actor a
-> ORDER BY
-> (SELECT count(*) FROM film_actor fa
-> WHERE fa.actor_id = a.actor_id) DESC;
+----------+-------------+--------------+
| actor_id | first_name | last_name |
+----------+-------------+--------------+
| 107 | GINA | DEGENERES |
| 102 | WALTER | TORN |
| 198 | MARY | KEITEL |
| 181 | MATTHEW | CARREY |
...
| 71 | ADAM | GRANT |
| 186 | JULIA | ZELLWEGER |
| 35 | JUDY | DEAN |
| 199 | JULIA | FAWCETT |
| 148 | EMILY | DEE |
+----------+-------------+--------------+
200 rows in set (0.01 sec)
該查詢在order by子句中使用一個關聯标量子查詢，隻傳回電影數量，該值僅用于排序目的。
除了在select語句中關聯相關标量子查詢外，還可以使用非關聯标量子查詢為insert語句生成值。例如，假設你将在film_actor表中插入新一行資料，資料如下已給出：
• 演員的名字和姓氏
• 影名
你有兩個選擇：一個是先執行兩個查詢從film和actor中檢索主鍵值，然後将這些值放入insert語句中，另一個就是使用子查詢從insert語句中直接檢索這兩個主鍵值。下面是後一種方法的示例：
INSERT INTO film_actor (actor_id, film_id, last_update)
VALUES (
(SELECT actor_id FROM actor
WHERE first_name = 'JENNIFER' AND last_name = 'DAVIS'),
(SELECT film_id FROM film
WHERE title = 'ACE GOLDFINGER'),
now()
);
使用一條SQL語句，就可以在film_actor表中建立一行資料，同時查詢兩個外鍵值了。
6. 子查詢總結
本章涉及了很多内容，是以最好回顧一下我們學習的知識。本章示例示範了子查詢的以下知識點：
• 可以傳回單行單列、單行多列以及多行多列
• 可以獨立于包含語句（非關聯子查詢）
• 可以引用包含語句中的一個或多個列（關聯子查詢）
• 可以用在條件中，這些條件使用比較操作符以及用于特殊目的的操作符in、not in、exists和not exists
• 可以出現在select、update、delete和insert語句中
• 可以生成結果集與其他表（或子查詢）連接配接
• 可以生成值以填充表或查詢結果集中的列
• 可以用于查詢的select、from、where、having和order by子句
顯然，子查詢是一個用途廣泛的工具，是以，如果你将本章讀完一遍之後還沒有了解全部的概念，也請别灰心。如果你堅持嘗試使用子查詢的各種用法，那麼你很快就能發現自己在每次編寫非平凡的SQL語句時都會思考如何使用子查詢來實作。
