MySQL資料查詢之子查詢

子查詢是指一個查詢語句嵌套在另一個查詢語句內部的查詢。在select子句中先計算子查詢，子查詢結果作為外層另一個查詢的篩選條件，查詢可以基於一個表格或多個表。

子查詢中常用的運算子有any(some)、all、in、exists。子查詢可以加入到select、update和delete語句中，而且可以進行多層巢狀。子查詢中也可以使用比較運算符，如"<"，"<="，">"，">=“和”!="。

---

(1)帶any、some關鍵字的子查詢
(2)帶all關鍵字的子查詢
(3)帶exists關鍵字的子查詢
(4)帶in關鍵字的子查詢
(5)帶有比較運算子的子查詢

（免費學習推薦：mysql影片教學）

---

(1)、有any、some關鍵字的子查詢

any、some關鍵字是同義詞，表示滿足其中任一條件，允許建立一個表達式對子查詢的傳回值清單進行比較，只要滿足內層子查詢中的任何一個比較條件，就傳回一個結果作為外層查詢的條件。

下面定義兩個表tbl1和tbl2，並在兩個表中插入資料：

mysql> create table tbl1( num1 int not null);Query OK, 0 rows affected (0.13 sec)mysql> create table tbl2(num2 int not null);Query OK, 0 rows affected (0.10 sec)mysql> insert into tbl1 values(1),(5),(13),(27);Query OK, 4 rows affected (0.05 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> insert into tbl2 values(6),(14),(11),(20);Query OK, 4 rows affected (0.06 sec)Records: 4  Duplicates: 0  Warnings: 0

any關鍵字接在一個比較運算元後面，表示若與子查詢傳回的任何職比較為true，則回傳true。

【範例】傳回tbl2表的所有num2列，然後將tbl1中的num1的值與之進行比較，只要大於num2的任何一個值，即為符合條件的結果。

mysql> select num1 from tbl1 where num1 > any(select num2 from tbl2);+------+| num1 |+------+|   13 ||   27 |+------+2 rows in set (0.00 sec)

(2)、帶有all關鍵字的子查詢

• all關鍵字需要同時滿足所有內層查詢的條件。
• all關鍵字接在一個比較運算子後面，表示與子查詢傳回的所有值比較為true，則傳回true。

【範例】傳回tbl1表中比tbl2表num2列所有值都大的值，SQL語句如下：

mysql> select num1 from tbl1 where num1 > all(select num2 from tbl2);+------+| num1 |+------+|   27 |+------+1 row in set (0.00 sec)

(3)、帶exists關鍵字的子查詢

• exists關鍵字後面的參數是一個任意的子查詢，系統對子查詢進行運算以判斷它是否回傳行，如果至少傳回一行，那麼exists的結果為true，此時外層查詢語句將進行查詢；如果子查詢沒有回傳任何行，那麼exists傳回的結果就是false，此時外層語句將不進行查詢。

【例1】查詢suppliers表中是否存在s_id=107的的供應商，如果存在，則查詢fruits表中的記錄，SQL語句如下：

mysql> select * from fruits    -> where exists
    -> (select s_name from suppliers where s_id = 107);+------+------+------------+---------+| f_id | s_id | f_name     | f_price |+------+------+------------+---------+| 12   |  104 | lemon      |    6.40 || a1   |  101 | apple      |    5.20 || a2   |  103 | apricot    |    2.20 || b1   |  101 | blackberry |   10.20 || b2   |  104 | berry      |    7.60 || b5   |  107 | xxxx       |    3.60 || bs1  |  102 | orange     |   11.20 || bs2  |  105 | melon      |    8.20 || c0   |  101 | cherry     |    3.20 || m1   |  106 | mango      |   15.70 || m2   |  105 | xbabay     |    2.60 || m3   |  105 | xxtt       |   11.60 || o2   |  103 | coconut    |    9.20 || t1   |  102 | banana     |   10.30 || t2   |  102 | grape      |    5.30 || t4   |  107 | xbabay     |    3.60 |+------+------+------------+---------+16 rows in set (0.00 sec)

由結果可知，內層查詢結果顯示suppliers表中存在s_id=107的記錄，因此exists表達式傳回true；外層查詢語句接收true之後對錶fruits進行查詢，傳回所有記錄。

【例2】查詢suppliers表中是否存在s_id=107的供應商，如果存在，則查詢fruits表中的f_price大於10.20的記錄,SQL語句如下:

mysql> select * from fruits    -> where f_price > 10.20 and exists
    -> (select s_name from suppliers where s_id = 107);+------+------+--------+---------+| f_id | s_id | f_name | f_price |+------+------+--------+---------+| bs1  |  102 | orange |   11.20 || m1   |  106 | mango  |   15.70 || m3   |  105 | xxtt   |   11.60 || t1   |  102 | banana |   10.30 |+------+------+--------+---------+4 rows in set (0.00 sec)

可以看到,內層查詢表名suppliers表中存在s_id=107的記錄，因此exists表達式返回true；外層查詢語句接收true之後根據查詢條件f_price>10.20對fruits表進行查詢，返回結果為4個f_price大於10.20的記錄。

• not exists與exists使用方法相同，傳回的結果相反。子查詢如果至少回傳一行，那麼not exists的結果為false，此時外層查詢語句將不進行查詢；如果子查詢沒有傳回任何行，那麼not exists傳回的結果就是true，此時外層語句將進行查詢。

【範例3】查詢suppliers表中是否存在s_id =107的供應商，如果不存在就查詢fruits表中的記錄，SQL語句如下：

mysql> select * from fruits    -> where not exists
    -> (select s_name from suppliers where s_id = 107);Empty set (0.00 sec)

可以看到，內層查詢傳回結果為false，外層表達式接收false將不再查詢fruits表中的記錄。

注意：exists 和 not exists的結果只取決於是否會回傳行，而不取決於這些行的內容，所以這個子查詢輸入清單通常是無關緊要的。

(4)、帶in關鍵字的子查詢

• in關鍵字進行子查詢時，內層查詢語句只會傳回一個資料列，這個資料列裡的值將提供給外層查詢語句進行比較操作。

【例1】在orderitems表中查詢f_id為c0的訂單號，並根據訂單號查詢具有訂單號的客戶c_id，SQL語句如下：

mysql> select c_id from orders where o_num in
    -> (select o_num from orderitems where f_id = 'c0');+-------+| c_id  |+-------+| 10004 || 10001 |+-------+2 rows in set (0.00 sec)

上面的語句是下面查詢方式的簡寫：

mysql> select o_num from orderitems where f_id = 'c0';+-------+| o_num |+-------+| 30003 || 30005 |+-------+2 rows in set (0.00 sec)mysql> select c_id from orders where o_num in (30003,30005);+-------+| c_id  |+-------+| 10004 || 10001 |+-------+2 rows in set (0.00 sec)

下面介紹與in相反的not in關鍵字：

【例2】與例1類似，但select語句中使用not in 關鍵字。 SQL語句如下:

mysql> select c_id from orders where o_num not in
    -> (select o_num from orderitems where f_id ='c0');+-------+| c_id  |+-------+| 10001 || 10003 || 10005 |+-------+3 rows in set (0.00 sec)

可以看到返回了三個表,查看orders中的記錄可知,c_id等於10001的客戶的訂單不止一個:

mysql> select * from orders;+-------+---------------------+-------+| o_num | o_date              | c_id  |+-------+---------------------+-------+| 30001 | 2008-09-01 00:00:00 | 10001 || 30002 | 2008-09-12 00:00:00 | 10003 || 30003 | 2008-09-30 00:00:00 | 10004 || 30004 | 2008-10-03 00:00:00 | 10005 || 30005 | 2008-10-08 00:00:00 | 10001 |+-------+---------------------+-------+5 rows in set (0.00 sec)

結果只是排除了訂單號，但仍有可能選擇同一個客戶。

子查詢的功能也可以透過連線查詢完成，但子查詢讓MySQL程式碼更容易閱讀和編寫。

(5)、带比较运算符的子查询

【例1】在suppliers表中查询s_city等于"Tianjin"的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类，SQL语句如下：

mysql> select s_id,f_name from fruits    -> where s_id =
    -> (select s1.s_id from suppliers as s1 where s1.s_city = 'Tianjin');+------+------------+| s_id | f_name     |+------+------------+|  101 | apple      ||  101 | blackberry ||  101 | cherry     |+------+------------+3 rows in set (0.00 sec)

【例2】在suppliers表中查询s_city等于"Tianjin"的供应商，s_id，然后在fruits表中查询所有非该供应商提供的水果的种类，SQL语句如下：

mysql> select s_id , f_name from fruits    -> where s_id <>
    -> (select s1.s_id from suppliers as s1 where s1.s_city ='Tianjin');+------+---------+| s_id | f_name  |+------+---------+|  104 | lemon   ||  103 | apricot ||  104 | berry   ||  107 | xxxx    ||  102 | orange  ||  105 | melon   ||  106 | mango   ||  105 | xbabay  ||  105 | xxtt    ||  103 | coconut ||  102 | banana  ||  102 | grape   ||  107 | xbabay  |+------+---------+13 rows in set (0.00 sec)

更多相关免费学习推荐：mysql教程(视频)

以上是MySQL資料查詢之子查詢的詳細內容。更多資訊請關注PHP中文網其他相關文章！