MySQL子查詢詳解 - tw511教學網

子查詢指一個查詢語句巢狀在另一個查詢語句內部的查詢，這個特性從 MySQL 4.1 開始引入，在 SELECT 子句中先計運算元查詢，子查詢結果作為外層另一個查詢的過濾條件，查詢可以基於一個表或者多個表。

子查詢中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。

子查詢可以新增到 SELECT、UPDATE 和 DELETE 語句中，而且可以進行多層巢狀。子查詢也可以使用比較運算子，如“<”、“<=”、“>”、“>=”、“！=”等。

子查詢中常用的運算子

1) IN子查詢

結合關鍵字 IN 所使用的子查詢主要用於判斷一個給定值是否存在於子查詢的結果集中。其語法格式為：

<表示式> [NOT] IN <子查詢>

語法說明如下。

<表示式>：用於指定表示式。當表示式與子查詢返回的結果集中的某個值相等時，返回 TRUE，否則返回 FALSE；若使用關鍵字 NOT，則返回的值正好相反。
<子查詢>：用於指定子查詢。這裡的子查詢只能返回一列資料。對於比較複雜的查詢要求，可以使用 SELECT 語句實現子查詢的多層巢狀。

2) 比較運算子子查詢

比較運算子所使用的子查詢主要用於對表示式的值和子查詢返回的值進行比較運算。其語法格式為：

<表示式> {= | < | > | >= | <= | <=> | < > | != }
{ ALL | SOME | ANY} <子查詢>

語法說明如下。

<子查詢>：用於指定子查詢。
<表示式>：用於指定要進行比較的表示式。
ALL、SOME 和 ANY：可選項。用於指定對比較運算的限制。其中，關鍵字 ALL 用於指定表示式需要與子查詢結果集中的每個值都進行比較，當表示式與每個值都滿足比較關係時，會返回 TRUE，否則返回 FALSE；關鍵字 SOME 和 ANY 是同義詞，表示表示式只要與子查詢結果集中的某個值滿足比較關係，就返回 TRUE，否則返回 FALSE。

3) EXIST子查詢

關鍵字 EXIST 所使用的子查詢主要用於判斷子查詢的結果集是否為空。其語法格式為：

EXIST <子查詢>

若子查詢的結果集不為空，則返回 TRUE；否則返回 FALSE。

子查詢的應用

【範例 1】在 tb_departments 表中查詢 dept_type 為 A 的學院 ID，並根據學院 ID 查詢該學院學生的名字，輸入的 SQL 語句和執行結果如下所示。

mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id IN
    -> (SELECT dept_id
    -> FROM tb_departments
    -> WHERE dept_type= 'A' );
+-------+
| name  |
+-------+
| Dany  |
| Henry |
| Jane  |
| Jim   |
| John  |
+-------+
5 rows in set (0.01 sec)

上述查詢過程可以分步執行，首先內層子查詢查出 tb_departments 表中符合條件的學院 ID，單獨執行內查詢，查詢結果如下所示。

mysql> SELECT dept_id
    -> FROM tb_departments
    -> WHERE dept_type='A';
+---------+
| dept_id |
+---------+
|       1 |
|       2 |
+---------+
2 rows in set (0.00 sec)

可以看到，符合條件的 dept_id 列的值有兩個：1 和 2。然後執行外層查詢，在 tb_students_info 表中查詢 dept_id 等於 1 或 2 的學生的名字。巢狀子查詢語句還可以寫為如下形式，可以實現相同的效果。

mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id IN(1,2);
+-------+
| name  |
+-------+
| Dany  |
| Henry |
| Jane  |
| Jim   |
| John  |
+-------+
5 rows in set (0.03 sec)

上例說明在處理 SELECT 語句時，MySQL 實際上執行了兩個操作過程，即先執行內層子查詢，再執行外層查詢，內層子查詢的結果作為外部查詢的比較條件。

【範例 2】與前一個例子類似，但是在 SELECT 語句中使用 NOT IN 關鍵字，輸入的 SQL 語句和執行結果如下所示。

mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id NOT IN
    -> (SELECT dept_id
    -> FROM tb_departments
    -> WHERE dept_type='A');
+--------+
| name   |
+--------+
| Green  |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
+--------+
5 rows in set (0.04 sec)

提示：子查詢的功能也可以通過連線查詢完成，但是子查詢使得 MySQL 程式碼更容易閱讀和編寫。

【範例 3】在 tb_departments 表中查詢 dept_name 等於“Computer”的學院 id，然後在 tb_students_info 表中查詢所有該學院的學生的姓名，輸入的 SQL 語句和執行過程如下所示。

 mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id =
    -> (SELECT dept_id
    -> FROM tb_departments
    -> WHERE dept_name='Computer');
+------+
| name |
+------+
| Dany |
| Jane |
| Jim  |
+------+
3 rows in set (0.00 sec)

【範例 4】在 tb_departments 表中查詢 dept_name 不等於“Computer”的學院 id，然後在 tb_students_info 表中查詢所有該學院的學生的姓名，輸入的 SQL 語句和執行過程如下所示。

mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id <>
    -> (SELECT dept_id
    -> FROM tb_departments
    -> WHERE dept_name='Computer');
+--------+
| name   |
+--------+
| Green  |
| Henry  |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
+--------+
7 rows in set (0.00 sec)

【範例 5】查詢 tb_departments 表中是否存在 dept_id=1 的供應商，如果存在，就查詢 tb_students_info 表中的記錄，輸入的 SQL 語句和執行結果如下所示。

mysql> SELECT * FROM tb_students_info
    -> WHERE EXISTS
    -> (SELECT dept_name
    -> FROM tb_departments
    -> WHERE dept_id=1);
+----+--------+---------+------+------+--------+------------+
| id | name   | dept_id | age  | sex  | height | login_date |
+----+--------+---------+------+------+--------+------------+
|  1 | Dany   |       1 |   25 | F    |    160 | 2015-09-10 |
|  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 |
|  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 |
|  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 |
|  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 |
|  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 |
|  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 |
|  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 |
|  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 |
| 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.00 sec)

由結果可以看到，內層查詢結果表明 tb_departments 表中存在 dept_id=1 的記錄，因此 EXSTS 表示式返回 TRUE，外層查詢語句接收 TRUE 之後對表 tb_students_info 進行查詢，返回所有的記錄。

EXISTS 關鍵字可以和條件表示式一起使用。

【範例 6】查詢 tb_departments 表中是否存在 dept_id=7 的供應商，如果存在，就查詢 tb_students_info 表中的記錄，輸入的 SQL 語句和執行結果如下所示。

mysql> SELECT * FROM tb_students_info
    -> WHERE EXISTS
    -> (SELECT dept_name
    -> FROM tb_departments
    -> WHERE dept_id=7);
Empty set (0.00 sec)