Like Share Discussion Bookmark Smile

J.J. Huang   2020-06-21   MySQL   瀏覽次數:

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

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

利用子查詢進行過濾

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

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

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

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

1
2
3
4
5
6
7
8
mysql> SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
mysql> SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
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語句難以閱讀和調試,特別是它們較為複雜時更是如此。如上所示把子查詢分解為多行並且適當地進行縮進,能極大地簡化子查詢的使用。

1
2
3
4
5
6
7
8
9
10
SELECT
cust_id
FROM
orders
WHERE
order_num IN(
SELECT
order_num FROM orderitems
WHERE
prod_id = 'TNT2');

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

1
2
3
4
5
6
7
8
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。

1
2
3
4
5
6
7
8
9
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表中。為了執行這個操作,遵循下面的步驟。

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
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) 涉及外部查詢的子查詢。

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

1
2
3
4
5
6
7
8
9
10
11
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語句時極有用,但必須注意限制有歧義性的列名。

結語

這邊的子查詢,給予初學者來說,是非常方便使用的,但是就像文中提到的,有性能上的問題。因為業務邏輯上的複雜,絕對不會是簡單一兩個查詢就可以解決。而文中也有特別提到資料庫的一個查詢由內而外,因為資料筆數所以要查詢幾次,這就是為什麼一直強調性能問題。

真的建議把上面的例子多看幾遍,就會對其更深入了解。

註:以上參考了
MySQL必知必会 MySQL Crash Course