MySQL子查询详解（附带实例）
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入。
在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一张表或者多张表。
子查询中常用的操作符有 ANY（SOME）、ALL、IN、EXISTS。子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如“<”、“<=”、“>”、“>=”和“!=”等。
本节将介绍如何在 SELECT 语句中嵌套子查询。
为了演示的需要，需要创建以下几张表：
1) 创建数据表 fruits，SQL 语句如下：
2) 创建数据表 suppliers，SQL 语句如下：
3) 创建表 orders，SQL 语句如下：
4) 创建数据表 orderitems，SQL 语句如下：
下面定义两张表 tbl1 和 tbl2：
分别向两张表中插入数据：
ANY 关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较的结果为 TRUE，则返回 TRUE。
【实例 1】返回表 tbl2 中的 num2 列的所有值，然后将表 tbl1 中的 num1 列的值与 num2 列的值进行比较，只要 num1 列中的值大于 num2 列中的任何一个值，即为符合查询条件的结果。
ALL 关键字接在一个比较操作符的后面，表示与子查询返回的所有值进行比较，若结果为 TRUE，则返回 TRUE。
下面修改【实例 1】，用 ALL 关键字替换 ANY。
【实例 2】返回表 tbl1 中比表 tbl2 的 num2 列所有值都大的值，SQL 语句如下：
【实例 3】查询表 suppliers 中是否存在 s_id=107 的水果供应商，如果存在，则查询表 fruits 中的记录，SQL 语句如下：
EXISTS 关键字可以和条件表达式一起使用。
【实例 4】查询表 suppliers 中是否存在 s_id=107 的水果供应商，如果存在，则查询表 fruits 中的 f_price>10.20 的记录，SQL 语句如下：
NOT EXISTS 与 EXISTS 的使用方法相同，返回的结果相反。子查询如果至少返回一行，那么 NOT EXISTS 返回的结果为 FALSE，此时外层查询语句将不进行查询；如果子查询没有返回任何行，那么 NOT EXISTS 返回的结果是 TRUE，此时外层查询语句将进行查询。
【实例 5】查询表 suppliers 中是否存在 s_id=107 的水果供应商，如果不存在，则查询表 fruits 中的记录，SQL 语句如下：
EXISTS 和 NOT EXISTS 的结果只取决于是否会返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。
【实例 6】在表 orderitems 中查询 f_id 为 c0 的订单号，并根据订单号查询客户 c_id，SQL 语句如下：
上述查询过程可以分步执行，首先内层子查询查出表 orderitems 中符合条件的订单号，单独执行内查询，查询结果如下：
SELECT 语句中可以使用 NOT IN 关键字，其作用与IN正好相反。
【实例 7】：与【实例 6】类似，但是在 SELECT 语句中使用 NOT IN 关键字，SQL 语句如下：
子查询的功能也可以通过连接查询完成，但是子查询使得 MySQL 代码更容易阅读和编写。
【实例 8】在表 suppliers 中查询 s_city 等于“Tianjin”的水果供应商 s_id，然后在表 fruits 中查询该供应商提供的所有的水果种类，SQL 语句如下：
然后在外层查询时，在表 fruits 中查找 s_id 等于 101 的水果供应商提供的水果的种类，查询结果如下：
【实例 9】在表 suppliers 中查询 s_city 等于“Tianjin”的水果供应商的 s_id，然后在表 fruits 中查询所有非该供应商提供的水果的种类，SQL 语句如下：
在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一张表或者多张表。
子查询中常用的操作符有 ANY（SOME）、ALL、IN、EXISTS。子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如“<”、“<=”、“>”、“>=”和“!=”等。
本节将介绍如何在 SELECT 语句中嵌套子查询。
为了演示的需要，需要创建以下几张表：
1) 创建数据表 fruits，SQL 语句如下：
CREATE TABLE fruits ( f_id char(10) NOT NULL, s_id INT NOT NULL, f_name char(255) NOT NULL, f_price decimal(8,2) NOT NULL, PRIMARY KEY(f_id) );插入如下数据：
mysql> INSERT INTO fruits (f_id, s_id, f_name, f_price)
VALUES('a1', 101,'apple',5.2),
('b1',101,'blackberry', 10.2),
('bs1',102,'orange', 11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana', 10.3),
('t2',102,'grape', 5.3),
('o2',103,'coconut', 9.2),
('c0',101,'cherry', 3.2),
('a2',103, 'apricot',2.2),
('l2',104,'lemon', 6.4),
('b2',104,'berry', 7.6),
('m1',106,'mango', 15.7),
('m2',105,'xbabay', 2.6),
('t4',107,'xbababa', 3.6),
('m3',105,'xxtt', 11.6),
('b5',107,'xxxx', 3.6);
2) 创建数据表 suppliers，SQL 语句如下：
CREATE TABLE suppliers ( s_id int NOT NULL AUTO_INCREMENT, s_name char(50) NOT NULL, s_city char(50) NULL, s_zip char(10) NULL, s_call CHAR(50) NOT NULL, PRIMARY KEY (s_id) ) ;插入需要演示的数据，SQL 语句如下：
INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call) VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'), (102,'LT Supplies','Chongqing','400000','44333'), (103,'ACME','Shanghai','200000','90046'), (104,'FNK Inc.','Zhongshan','528437','11111'), (105,'Good Set','Taiyuang','030000', '22222'), (106,'Just Eat Ours','Beijing','010', '45678'), (107,'DK Inc.','Zhengzhou','450000', '33332');
3) 创建表 orders，SQL 语句如下：
CREATE TABLE orders ( o_num int NOT NULL AUTO_INCREMENT, o_date datetime NOT NULL, c_id int NOT NULL, PRIMARY KEY (o_num) ) ;插入数据，SQL 语句如下：
INSERT INTO orders(o_num, o_date, c_id) VALUES(30001, '2008-09-01', 10001), (30002, '2008-09-12', 10003), (30003, '2008-09-30', 10004), (30004, '2008-10-03', 10005), (30005, '2008-10-08', 10001);
4) 创建数据表 orderitems，SQL 语句如下：
CREATE TABLE orderitems ( o_num int NOT NULL, o_item int NOT NULL, f_id char(10) NOT NULL, quantity int NOT NULL, item_price decimal(8,2) NOT NULL, PRIMARY KEY (o_num,o_item) ) ;然后插入演示数据，SQL 语句如下：
INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price) VALUES(30001, 1, 'a1', 10, 5.2), (30001, 2, 'b2', 3, 7.6), (30001, 3, 'bs1', 5, 11.2), (30001, 4, 'bs2', 15, 9.2), (30002, 1, 'b3', 2, 20.0), (30003, 1, 'c0', 100, 10), (30004, 1, 'o2', 50, 2.50), (30005, 1, 'c0', 5, 10), (30005, 2, 'b1', 10, 8.99), (30005, 3, 'a2', 10, 2.2), (30005, 4, 'm1', 5, 14.99);
带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。
【实例 1】返回表 tbl2 中的 num2 列的所有值，然后将表 tbl1 中的 num1 列的值与 num2 列的值进行比较，只要 num1 列中的值大于 num2 列中的任何一个值，即为符合查询条件的结果。
mysql> SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2); +------+ | num1 | +------+ | 13 | | 27 | +------+在子查询中，返回的是表 tbl2 的 num2 列的值，即（6,14,11,20），然后将表 tbl1 中的 num1 列的值与（6,14,11,20）进行比较，只要 num1 列中的值大于（6,14,11,20）中的任意一个，即为符合条件的结果，因此返回的结果为（13,27）。
带ALL关键字的子查询
ALL 关键字与 ANY 和 SOME 不同，使用 ALL 时需要同时满足所有内层查询的条件。ALL 关键字接在一个比较操作符的后面，表示与子查询返回的所有值进行比较，若结果为 TRUE，则返回 TRUE。
下面修改【实例 1】，用 ALL 关键字替换 ANY。
【实例 2】返回表 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，此时外层查询语句将不进行查询。【实例 3】查询表 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 关键字可以和条件表达式一起使用。
【实例 4】查询表 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，此时外层查询语句将进行查询。
【实例 5】查询表 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关键字的子查询
IN 关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。【实例 6】在表 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 | +-------+然后执行外层查询，在表 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正好相反。
【实例 7】：与【实例 6】类似，但是在 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 条记录。由【实例 6】可知，子查询返回的订单值有两个，即 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 的订单都是 c_id 为 10001 的客户的订单。因此，结果中只是排除了订单号，但是仍然有可能选择同一个客户。
子查询的功能也可以通过连接查询完成，但是子查询使得 MySQL 代码更容易阅读和编写。
带比较运算符的子查询
在前面介绍带 ANY、ALL 关键字的子查询时使用了“>”比较运算符，子查询时还可以使用其他的比较运算符，如“<”“<=”“=”“>=”和“!=”等。【实例 8】在表 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。
【实例 9】在表 suppliers 中查询 s_city 等于“Tianjin”的水果供应商的 s_id，然后在表 fruits 中查询所有非该供应商提供的水果的种类，SQL 语句如下：
mysql> SELECT s_id, f_name FROM fruits WHERE s_id <> (SELECT 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 | +--------+----------+该嵌套查询执行过程与【实例 8】相同，但这里使用了“<>”运算符，因此返回的结果和【实例 8】的正好相反。
ICP备案：
公安联网备案：