MySQL入門系列：查詢簡介（五）之子查詢

小孩子4919發表於2019-03-13

使用子查詢

多表查詢的需求

上邊介紹的查詢語句都是作用於單個表的，但是有時候會有從多個表中查詢資料的需求，比如我們想查一下名叫'杜琦燕'的學生的各科成績該怎麼辦呢？我們只能先從student_info表中根據名稱找到對應的學生學號，然後再通過學號到student_score表中找著對應的成績資訊，所以我們只能寫成這樣的兩個查詢：

mysql> SELECT number FROM student_info WHERE name = '杜琦燕';
+----------+
| number   |
+----------+
| 20180102 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM student_score WHERE number = 20180102;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180102 | 母豬的產後護理              |   100 |
| 20180102 | 論薩達姆的戰爭準備          |    98 |
+----------+-----------------------------+-------+
2 rows in set (0.00 sec)

mysql>
複製程式碼

標量子查詢

我們回過頭再仔細看看這兩條查詢語句，第二條查詢語句的搜尋條件其實是用到了第一條查詢語句的查詢結果，所以為了方便，我們可以把它們合併到一條語句中，就像這樣：

mysql> SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = '杜琦燕');
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180102 | 母豬的產後護理              |   100 |
| 20180102 | 論薩達姆的戰爭準備          |    98 |
+----------+-----------------------------+-------+
2 rows in set (0.01 sec)

mysql>
複製程式碼

我們把第二條查詢語句用小括號()擴起來直接放到了第一條的搜尋條件處，這樣就起到了合併兩條查詢語句的作用。小括號中的查詢語句也被稱為子查詢或者內層查詢，使用內層查詢的結果作為搜尋條件的查詢稱為外層查詢。如果你在一個查詢語句中需要用到更多的表的話，那麼在一個子查詢中可以繼續巢狀另一個子查詢，在執行查詢時，將按照從內到外的順序依次執行這些查詢。

在這個例子中的子查詢的結果只有一個值(也就是'杜琦燕'的學號)，這種子查詢稱之為標量子查詢。正因為標量子查詢單純的代表一個值，所以它可以作為表示式的運算元來參與運算，它除了用在外層查詢的搜尋條件以外，也可以作為查詢物件放在查詢列表處，比如這樣：

mysql> SELECT (SELECT number FROM student_info WHERE name = '杜琦燕') AS 學號;
+----------+
| 學號     |
+----------+
| 20180102 |
+----------+
1 row in set (0.00 sec)

mysql>
複製程式碼

因為標量子查詢單純的代表一個值，所以外層查詢的搜尋條件想怎麼寫就怎麼寫嘍，只要符合布林表示式的語法就可以，比方說我們來查詢學號大於'杜琦燕'的學號的學生成績，可以這麼寫：

mysql> SELECT * FROM student_score WHERE number > (SELECT number FROM student_info WHERE name = '杜琦燕');
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180103 | 母豬的產後護理              |    59 |
| 20180103 | 論薩達姆的戰爭準備          |    61 |
| 20180104 | 母豬的產後護理              |    55 |
| 20180104 | 論薩達姆的戰爭準備          |    46 |
+----------+-----------------------------+-------+
4 rows in set (0.00 sec)

mysql>
複製程式碼

這樣查出來的成績記錄中的學號都是大於'杜琦燕'的學號。

IN和NOT IN子查詢

有時候子查詢的結果並不是單純的一個值，而是多個值，比如我們想查詢'電腦科學與工程'專業的學生的成績，那我們可以拆成下邊兩個查詢：

mysql> SELECT number FROM student_info WHERE major = '電腦科學與工程';
+----------+
| number   |
+----------+
| 20180101 |
| 20180102 |
+----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM student_score WHERE number IN (20180101, 20180102);
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180101 | 母豬的產後護理              |    78 |
| 20180101 | 論薩達姆的戰爭準備          |    88 |
| 20180102 | 母豬的產後護理              |   100 |
| 20180102 | 論薩達姆的戰爭準備          |    98 |
+----------+-----------------------------+-------+
4 rows in set (0.00 sec)

mysql>
複製程式碼

很顯然對於子查詢結果是多個值的情況，需要使用IN這種多值匹配操作符，所以我們可以把上邊兩個語句合併成這樣：

mysql> SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '電腦科學與工程');
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180101 | 母豬的產後護理              |    78 |
| 20180101 | 論薩達姆的戰爭準備          |    88 |
| 20180102 | 母豬的產後護理              |   100 |
| 20180102 | 論薩達姆的戰爭準備          |    98 |
+----------+-----------------------------+-------+
4 rows in set (0.00 sec)

mysql>
複製程式碼

NOT IN和IN的使用差不多，就不贅述了。

多列子查詢

上邊例子中出現的子查詢的結果集裡只有一個列，其實子查詢的結果集也可以有多個列的，只不過在子查詢的查詢列表和外層查詢WHERE子句的搜尋條件要匹配！比如我們可以這麼寫：

mysql> SELECT * FROM student_score WHERE (number, subject) IN (SELECT number, '母豬的產後護理' FROM student_info WHERE major = '電腦科學與工程');
+----------+-----------------------+-------+
| number   | subject               | score |
+----------+-----------------------+-------+
| 20180101 | 母豬的產後護理        |    78 |
| 20180102 | 母豬的產後護理        |   100 |
+----------+-----------------------+-------+
2 rows in set (0.00 sec)

mysql>
複製程式碼

在這個例子中的子查詢的查詢列表是number, '母豬的產後護理'，number是列名，'母豬的產後護理'是一個常數，所以在外層查詢的WHERE子句的搜尋條件裡也需要兩個表示式，例子中使用的是``(number, subject)`。需要注意的是，WHERE子句的搜尋條件中多個表示式需要用小括號()括住。

EXISTS和NOT EXISTS子查詢

有時候外層查詢並不關心子查詢中的結果是什麼，而只關心子查詢的結果是不是為空，這時可以用到下邊這兩個操作符：

操作符	示例	描述
`EXISTS`	`EXISTS (SELECT ...)`	當子查詢有結果時表示式為真
`NOT EXISTS`	`NOT EXISTS (SELECT ...)`	當子查詢沒有結果時表示式為真

我們來舉個例子：

mysql> SELECT * FROM student_score WHERE EXISTS (SELECT * FROM student_info WHERE number = 20180108);
Empty set (0.00 sec)

mysql>
複製程式碼

這個例子的子查詢的意思是在student_info表中查詢學號為20180108的學生資訊，很顯然並沒有學號為20180108的學生，所以子查詢沒有結果，EXISTS表示式的結果為FALSE，所以外層查詢也就不查了，直接返回了一個Empty set，表示沒有結果。你可以自己試一下NOT EXISTS的使用。

相關子查詢

前邊介紹的子查詢和外層查詢都沒有依賴關係，也就是說可以獨立執行完子查詢得到結果之後，再拿結果作為外層查詢的搜尋條件去執行外層查詢，這種子查詢稱為不相關子查詢，比如下邊這個查詢：

mysql> SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = '杜琦燕');
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180102 | 母豬的產後護理              |   100 |
| 20180102 | 論薩達姆的戰爭準備          |    98 |
+----------+-----------------------------+-------+
2 rows in set (0.00 sec)

mysql>
複製程式碼

子查詢中只用到了student_info表而沒有使用到student_score表，這就是一種典型的不相關子查詢。

而有時候我們需要在子查詢的語句中引用到外層查詢的值，這樣的話子查詢就不能當作一個獨立的語句去執行，這種子查詢方式稱為相關子查詢。比方說我們想檢視一些學生的基本資訊，但是前提是這些學生有成績記錄，那可以這麼寫：

mysql> SELECT number, name, id_number, major FROM student_info WHERE EXISTS (SELECT * FROM student_score WHERE student_score.number = student_info.number);
+----------+-----------+--------------------+--------------------------+
| number   | name      | id_number          | major                    |
+----------+-----------+--------------------+--------------------------+
| 20180101 | 杜子騰    | 158177199901044792 | 電腦科學與工程         |
| 20180102 | 杜琦燕    | 151008199801178529 | 電腦科學與工程         |
| 20180103 | 範統      | 17156319980116959X | 軟體工程                 |
| 20180104 | 史珍香    | 141992199701078600 | 軟體工程                 |
+----------+-----------+--------------------+--------------------------+
4 rows in set (0.00 sec)

mysql>
複製程式碼

首先需要注意的是，student_info和student_score表裡都有number列，所以在子查詢的WHERE語句中書寫number = number會造成二義性，也就是讓伺服器懵逼，不知道這個number列到底是哪個表的，所以為了區分，在列名前邊加上了表名，並用點.連線起來，這種顯式的將列所屬的表名書寫出來的名稱稱為該列的全限定名。所以上邊子查詢的WHERE語句中用了列的全限定名：student_score.number = student_info.number。

這條查詢語句可以分成這麼兩部分來理解

我們要查詢學生的一些基本資訊。
這些學生必須符合這樣的條件：必須在student_score表中有記錄。

所以這個例子中的相關子查詢的查詢過程是這樣的：

先執行外層查詢獲得到student_info表的第一條記錄，發現它的number值是20180101。把20180101當作引數傳入到它的子查詢，此時子查詢的使意思判斷student_score表的number欄位是否有20180101這個值存在，子查詢的結果是該值存在，所以整個EXISTS表示式的值為TRUE，那麼student_info表的第一條記錄可以被加入到結果集。
再執行外層查詢獲得到student_info表的第二條記錄，發現它的number值是20180102，與上邊的步驟相同，student_info表的第二條記錄也可以被加入到結果集。
與上邊類似，student_info表的第三條記錄也可以被加入到結果集。
與上邊類似，student_info表的第四條記錄也可以被加入到結果集。
再執行外層查詢獲得到student_info表的第五條記錄，發現它的number值是20180105，把20180105當作引數傳入到它的子查詢，此時子查詢的使意思判斷student_score表的number欄位是否有20180105這個值存在，子查詢的結果是該值不存在，所以整個EXISTS表示式的值為FALSE，那麼student_info表的第五條記錄就不被加入結果集中。
與上邊類似，student_info表的第六條記錄也不被加入結果集中。
student_info表沒有更多的記錄了，結束查詢。

所以最後的查詢結果是上邊的4條記錄。如果你覺得相關子查詢還是有點兒繞的話，那就返回去再重新看幾遍這個查詢過程。需要注意的是，相關子查詢通常都用在EXISTS和NOT EXISTS子查詢裡，用於匹配在某個表裡查詢在另一個表裡有匹配行或者沒有匹配行的行。

對同一個表的子查詢

其實不只是在查詢多表的時候會用到子查詢，在單個表中有時也會用到子查詢。比方說我們想看看在student_score表的'母豬的產後護理'這門課的成績中，有哪些超過了平均分的記錄，腦子中第一印象是這麼寫：

mysql> SELECT * FROM student_score WHERE subject = '母豬的產後護理' AND score > AVG(score);
ERROR 1111 (HY000): Invalid use of group function
mysql>
複製程式碼

需要特別特別特別注意的是：聚集函式不能放到WHERE子句中！！！因為WHERE子句是針對每一條記錄來進行過濾的，而聚集函式只能用於過濾分組。所以如果我們想實現上邊的需求，就需要搞一個student_score表的副本，就相當於有了兩個student_score表，在一個表上使用聚集函式統計，統計完了之後拿著統計結果再到另一個表中進行過濾，這個過程可以這麼寫：

mysql>  SELECT * FROM student_score WHERE subject = '母豬的產後護理' AND score > (SELECT AVG(score) FROM student_score WHERE subject = '母豬的產後護理');
+----------+-----------------------+-------+
| number   | subject               | score |
+----------+-----------------------+-------+
| 20180101 | 母豬的產後護理        |    78 |
| 20180102 | 母豬的產後護理        |   100 |
+----------+-----------------------+-------+
2 rows in set (0.01 sec)

mysql>
複製程式碼

我們使用子查詢先統計出了'母豬的產後護理'這門課的平均分，然後再到外層查詢中使用這個平均分作為過濾條件來查詢大於平均分的記錄。這樣看上去student_score表就像是有了一個副本一樣～。

子查詢注意事項

標量子查詢的結果只有一個值，如果結果有多個值的話會報錯，可以在查詢語句末尾使用LIMIT 1來保證只有一條結果。
子查詢的查詢列表和外層查詢WHERE子句的搜尋條件要匹配！如果外層查詢的WHERE子句中的表示式個數大於1個，需要用小括號()擴起來。
在引用的列可能出現二義性時，必須使用列的全限定名(也就是用一個點.分隔的表名和列名)來註明該列所屬的表。
相關子查詢是涉及外部查詢的子查詢，一般用在EXISTS和NOT EXISTS子查詢裡。

小冊

本系列專欄都是MySQL入門知識，想看進階知識可以到小冊中檢視：《MySQL是怎樣執行的：從根兒上理解MySQL》的連結。小冊的內容主要是從小白的角度出發，用比較通俗的語言講解關於MySQL進階核的一些核心概念，比如記錄、索引、頁面、表空間、查詢優化、事務和鎖等，總共的字數大約是三四十萬字，配有上百幅原創插圖。主要是想降低普通程式設計師學習MySQL進階的難度，讓學習曲線更平滑一點～

MySQL入門系列：查詢簡介（二）
2019-03-09
MySql
MySQL入門系列：查詢簡介（七）之組合查詢
2019-03-14
MySql
MySQL入門系列：查詢簡介（三）之表示式和函式
2019-03-11
MySql函式
資料庫高階查詢之子查詢
2018-07-15
資料庫
MySQL - 資料查詢 - 簡單查詢
2020-12-27
MySql
《MySQL 入門教程》第 07 篇簡單查詢
2022-01-29
MySql
MongoDB入門系列（三）：查詢（SELECT）
2018-02-09
MongoDB
入門MySQL——查詢語法練習
2020-06-23
MySql
[MySQL光速入門]005 查詢資料
2019-04-18
MySql
MySQL入門--查詢資料庫資訊
2017-01-10
MySql資料庫
簡單的mysql查詢
2016-04-13
MySql
《MySQL 入門教程》第 19 篇子查詢
2022-02-16
MySql
TiDB 查詢優化及調優系列（二）TiDB 查詢計劃簡介
2022-04-27
TiDB優化
mysql-分組查詢-子查詢-連線查詢-組合查詢
2020-12-22
MySql
MySQL查詢
2024-04-14
MySql
《MySQL 入門教程》第 18 篇連線查詢
2022-02-13
MySql
Elasticsearch Query DSL查詢入門
2019-05-17
Elasticsearch
MySQL講義第 47 講——select 查詢之查詢練習（五）
2020-11-08
MySql
MySQL聯結查詢和子查詢
2018-02-24
MySql
[Mysql 查詢語句]——查詢欄位
2017-03-18
MySql
MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]
2024-03-10
MySql筆記
Oracle查詢轉換（五）子查詢展開
2017-06-19
Oracle
#查詢演算法#【1】簡單查詢：順序、折半查詢
2014-07-22
演算法
MySQL之連線查詢和子查詢
2021-09-04
MySql
mysql查詢結果多列拼接查詢
2018-08-17
MySql
mysql查詢語句5：連線查詢
2020-12-22
MySql
Access查詢實現Mysql的 limit 查詢
2016-08-23
MySqlMIT
[Mysql 查詢語句]——分組查詢group by
2017-03-18
MySql
[Mysql 查詢語句]——查詢指定記錄
2017-03-18
MySql
mysql查詢快取簡單使用
2021-03-06
MySql快取
MySQL的簡單查詢語句
2019-01-15
MySql
MySQL多表查詢
2020-07-19
MySql
MySQL子查詢
2019-11-29
MySql
mysql查詢模型
2019-03-12
MySql模型
MySQL 慢查詢
2021-01-27
MySql
MySQL慢查詢
2020-11-08
MySql
【MySQL】多表查詢
2018-06-18
MySql
MYsql 子查詢
2018-05-15
MySql