MySQL - 第十四章 | 使用子查詢

SQL還允許創建子查詢(subquery)，即嵌套在其他查詢中的查詢。為什麼要這樣做呢?理解這個概念的最好方法是考察幾個例子。

利用子查詢進行過濾

資料庫表基本上都是關係表。訂單存儲在兩個表中。對於包含訂單號、客戶ID、訂單日期的每個訂單，orders表存儲一行。各訂單的物品存儲在相關的orderitems表中。orders表不存儲客戶訊息。它只存儲客戶的ID。實際的客戶訊息存儲在customers表中。

現在，假如需要列出訂購物品TNT2的所有客戶，應該怎樣檢索?

檢索包含物品TNT2的所有訂單的編號。
檢索具有前一步驟列出的訂單編號的所有客戶的ID。
檢索前一步驟返回的所有客戶ID的客戶訊息。

上述每個步驟都可以單獨作為一個查詢來執行。可以把一條SELECT語句返回的結果用於另一條SELECT語句的WHERE子句。
也可以使用子查詢來把3個查詢組合成一條語句。第一條SELECT語句的含義很明確，對於prod_id為TNT2的所有訂單物品，它檢索其order_num列。

mysql> SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+
2 rows in set (0.01 sec)

mysql> SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.01 sec)

mysql> SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.01 sec)

分析：在SELECT語句中，子查詢總是從內向外處理。在處理上面的SELECT語句時，MySQL實際上執行了兩個操作。

首先，它執行下面的查詢:SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
此查詢返回兩個訂單號:20005和20007。然後，這兩個值以IN操作符要求的逗號分隔的格式傳遞給外部查詢的WHERE子句。
外部查詢變成:SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
可以看到，輸出是正確的並且與前面硬編碼WHERE子句所返回的值相同。

提示：格式化SQL 包含子查詢的SELECT語句難以閱讀和調試，特別是它們較為複雜時更是如此。如上所示把子查詢分解為多行並且適當地進行縮進，能極大地簡化子查詢的使用。

SELECT
	cust_id
FROM
	orders
WHERE
	order_num IN(
		SELECT
			order_num FROM orderitems
		WHERE
			prod_id = 'TNT2');

得到了訂購物品TNT2的所有客戶的ID。下一步是檢索這些客戶ID的客戶訊息。

mysql> SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (10001,10004);
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

可以把其中的WHERE子句轉換為子查詢而不是硬編碼這些客戶ID。

mysql> SELECT cust_name, cust_contact FROM customers WHERE cust_id IN 
(SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

分析：為了執行上述SELECT語句，MySQL實際上必須執行3條SELECT語句。最裡邊的子查詢返回訂單號列表，此列表用於其外面的子查詢的WHERE子句。外面的子查詢返回客戶ID列表，此客戶ID列表用於最外層查詢的WHERE子句。最外層查詢確實返回所需的資料。

可見，在WHERE子句中使用子查詢能夠編寫出功能很強並且很靈活的SQL語句。對於能嵌套的子查詢的數目沒有限制，不過在實際使用時由於性能的限制，不能嵌套太多的子查詢。

注意：列必須匹配在WHERE子句中使用子查詢(如這裡所示)，應該保證SELECT語句具有與WHERE子句中相同數目的列。通常，子查詢將返回單個列並且與單個列匹配，但如果需要也可以使用多個列。

注意：子查詢和性能這裡給出的程式碼有效並獲得所需的結果。但是，使用子查詢並不總是執行這種類型的資料檢索的最有效的方法。

作為計算欄位使用子查詢

使用子查詢的另一方法是創建計算欄位。假如需要顯示customers表中每個客戶的訂單總數。訂單與相應的客戶ID存儲在orders表中。為了執行這個操作，遵循下面的步驟。

從customers表中檢索客戶列表。
對於檢索出的每個客戶，統計其在orders表中的訂單數目。

正如前兩章所述，可使用SELECT COUNT(*)對錶中的行進行計數，並且通過提供一條WHERE子句來過濾某個特定的客戶ID，可僅對該客戶的訂單進行計數。

mysql> SELECT COUNT(*) AS orders FROM orders WHERE cust_id = 10001;
+--------+
| orders |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

為了對每個客戶執行COUNT(*)計算，應該將COUNT(*)作為一個子查詢。

mysql> SELECT cust_name, cust_state, (SELECT COUNT(*) AS orders FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      2 |
| E Fudd         | IL         |      1 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
+----------------+------------+--------+
5 rows in set (0.01 sec)

分析：這條SELECT語句對customers表中每個客戶返回3列:cust_name、cust_state和orders。orders是一個計算欄位，它是由圓括號中的子查詢建立的。該子查詢對檢索出的每個客戶執行一次。在此例子中，該子查詢執行了5次，因為檢索出了5個客戶。

子查詢中的WHERE子句與前面使用的WHERE子句稍有不同，因為它使用了完全限定列名。下面的語句告訴SQL比較 orders表中的cust_id與當前正從customers表中檢索的cust_id:WHERE orders.cust_id = customers.cust_id

術語：相關子查詢(correlated subquery) 涉及外部查詢的子查詢。

這種類型的子查詢稱為相關子查詢。任何時候只要列名可能有多義性，就必須使用這種語法(表名和列名由一個句點分隔)。為什麼這樣?
我們來看看如果不使用完全限定的列名會發生什麼情況。

mysql> SELECT cust_name, cust_state, (SELECT COUNT(*) AS orders FROM orders WHERE cust_id = cust_id) AS orders FROM customers ORDER BY cust_name;
+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      5 |
| E Fudd         | IL         |      5 |
| Mouse House    | OH         |      5 |
| Wascals        | IN         |      5 |
| Yosemite Place | AZ         |      5 |
+----------------+------------+--------+
5 rows in set (0.00 sec)

分析：顯然，返回的結果不正確(請比較前面的結果)，那麼，為什麼會這樣呢?有兩個cust_id列，一個在customers中，另一個在 orders中，需要比較這兩個列以正確地把訂單與它們相應的顧客匹配。如果不完全限定列名，MySQL將假定你是對orders表中的cust_id進行自身比較。而SELECT COUNT(*) FROM orders WHERE cust_id = cust_id;總是返回orders表中的訂單總數(因為MySQL查看每個訂單的cust_id是否與本身匹配，當然，它們總是匹配的)。

雖然子查詢在構造這種SELECT語句時極有用，但必須注意限制有歧義性的列名。