首页 > 编程笔记 > MySQL笔记 阅读：11

MySQL子查询详解（附带大量实例）

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入。

在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS 等。

子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如“<”“<=”“>”“>=”和“!=”等。

本节将介绍如何在 SELECT 语句中嵌套子查询。

带ANY、SOME关键字的子查询

ANY 和 SOME 关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

下面定义两个表 tbl1 和 tbl2： 
CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);

分别向两个表中插入数据： 
INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

ANY 关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为 TRUE，则返回 TRUE。

【实例】返回 tbl2 表的所有 num2 列，然后将 tbl1 中的 num1 的值与之进行比较，只要大于 num2 的任何 1 个值，即为符合查询条件的结果。 
mysql> SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);
+------+
| num1 |
+------+
|  13  |
|  27  |
+------+
在子查询中，返回的是 tbl2 表的所有 num2 列结果（6,14,11,20），然后将 tbl1 中的 num1 列的值与之进行比较，只要大于 num2 列的任意一个数即为符合条件的结果。

带ALL关键字的子查询

ALL 关键字与 ANY 和 SOME 不同，使用 ALL 时需要同时满足所有内层查询的条件。

ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE。

【实例】返回 tbl1 表中比 tbl2 表的 num2 列所有值都大的值，SQL 语句如下： 
mysql> SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);
+------+
| num1 |
+------+
|   27 |
+------+
在子查询中，返回的是 tbl2 的所有 num2 列结果（6,14,11,20），然后将 tbl1 中的 num1 列的值与之进行比较，大于所有 num2 列值的 num1 值只有 27，因此返回结果为 27。

带EXISTS关键字的子查询

EXISTS 关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么 EXISTS 的结果为 true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么 EXISTS 返回的结果是 false，此时外层语句将不进行查询。

【实例】查询 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 |
+------+-------+------------+---------+
| 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 |
| l2   |  104  | lemon      |    6.40 |
| 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  | xbababa    |    3.60 |
+------+-------+------------+---------+
由结果可以看到，内层查询结果表明 suppliers 表中存在 s_id=107 的记录，因此 EXISTS 表达式返回 true；外层查询语句接收 true 之后对表 fruits 进行查询，返回所有的记录。

EXISTS 关键字可以和条件表达式一起使用。

【实例】查询 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 |
+------+------+--------+---------+
由结果可以看到，内层查询结果表明 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，此时外层语句将进行查询。

【实例】查询 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)
查询语句SELECT s_name FROM suppliers WHERE s_id = 107，对 suppliers 表进行查询返回了一条记录，NOT EXISTS 表达式返回 false，外层表达式接收 false，将不再查询 fruits 表中的记录。

EXISTS 和 NOT EXISTS 的结果只取决于是否会返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。

带IN、NOT IN关键字的子查询

IN 关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

【实例】在 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 |
+-------+
查询结果的 c_id 有两个值，分别为 10001 和 10004。上述查询过程可以分步执行，首先内层子查询查出 orderitems 表中符合条件的订单号，单独执行内查询，查询结果如下： 
mysql> SELECT o_num  FROM orderitems WHERE f_id = 'c0';
+-------+
| o_num |
+-------+
| 30003 |
| 30005 |
+-------+
可以看到，符合条件的 o_num 列的值有两个：30003 和 30005，然后执行外层查询，在 orders 表中查询订单号等于 30003 或 30005 的客户 c_id。

嵌套子查询语句还可以写为如下形式，实现相同的效果： 
mysql> SELECT c_id FROM orders WHERE o_num IN (30003, 30005);
+-------+
| c_id  |
+-------+
| 10004 |
| 10001 |
+-------+
这个例子说明在处理 SELECT 语句的时候，MySQL 实际上执行了两个操作过程，即先执行内层子查询，再执行外层查询，内层子查询的结果作为外部查询的比较条件。

SELECT 语句中可以使用 NOT IN 关键字，其作用与 IN 正好相反。

【实例】与前一个例子类似，但是在 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 条记录，由前面例子可以看到，子查询返回的订单值有两个，即 30003 和 30005，但为什么这里还有值为 10001 的 c_id 呢？这是因为 c_id 等于 10001 的客户的订单不止一个，可以查看订单表 orders 中的记录。
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 |
+-------+---------------------+-------+
可以看到，虽然排除了订单号为 30003 和 30005 的客户 c_id，但是 o_num 为 30001 的订单与 30005 都是 10001 号客户的订单。所以结果中只是排除了订单号，但是仍然有可能选择同一个客户。

子查询的功能也可以通过连接查询完成，但是子查询使得 MySQL 代码更容易阅读和编写。

带比较运算符的子查询

在前面介绍的带 ANY、ALL 关键字的子查询时使用了“>”比较运算符，子查询时还可以使用其他的比较运算符，如“<”“<=”“=”“>=”和“!=”等。

【实例】在 suppliers 表中查询 s_city 等于“Tianjin”的供应商 s_id，然后在 fruits 表中查询所有该供应商提供的水果的种类，SQL 语句如下： 
SELECT s_id, f_name FROM fruits WHERE s_id =(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');
该嵌套查询首先在 suppliers 表中查找 s_city 等于“Tianjin”的供应商的 s_id，单独执行子查询查看 s_id 的值，执行下面的操作过程：
mysql> SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin';
+------+
| s_id |
+------+
|  101 |
+------+
然后在外层查询时，在 fruits 表中查找 s_id 等于 101 的供应商提供的水果的种类，查询结果如下： 
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     |
+------+------------+
结果表明，“Tianjin”地区的供应商提供的水果种类有 3 种，分别为“apple”、“blackberry” 和 “cherry”。

【实例】在 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  |
+------+---------+
|  103 | apricot |
|  104 | berry   |
|  107 | xxxx    |
|  102 | orange  |
|  105 | melon   |
|  104 | lemon   |
|  106 | mango   |
|  105 | xbabay  |
|  105 | xxtt    |
|  103 | coconut |
|  102 | banana  |
|  102 | grape   |
|  107 | xbababa |
+------+---------+
该嵌套查询执行过程与前面相同，在这里使用了不等于“<>”运算符，因此返回的结果和前面正好相反。

相关文章