MySQL子查询
对于简单查询，我们使用select语句都是从单个表中检索数据。
而子查询指一个查询语句嵌套在另一个查询语句内部的查询
在SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。
⚡ 子查询中常用的操作符有ANY（SOME）、ALL、IN、EXISTS。
子查询可以添加到SELECT、UPDATE和DELETE语句中，而且可以进行多层嵌套。
子查询中也可以使用比较运算符，如<,<=,>,>=,!=等。
利用子查询进行过滤
假如需要列出所有订购（订单表）物品TNT2（订单物品表）所有客户信息（客户表）？
应该怎样检索？下面列出具体的步骤。
(1) 检索包含物品TNT2的所有订单的编号。
(2) 检索具有前一步骤列出的订单编号的所有客户的ID。
(3) 检索前一步骤返回的所有客户ID的客户信息。
上述每个步骤都可以单独作为一个查询来执行，关键是步骤1和步骤2。
可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。
即：
-- （1）第一步
select order_num from orderitems
where prod_id = "TNT2"
-- output
order_num|
---------+
20005|
20007|
-- （2） 第二步
select cust_id from orders
where order_num in (20005,20007)
-- output
cust_id|
-------+
10001|
10004|
-- (3) 第三步，结果略
select * from customers where cust_id in (10001,10004)
对上述步骤1，步骤2而言，如果使用子查询，把第一个查询（返回订单号）结果作为子查询进行组合即可，如下：
-- 使用子查询
select cust_id,cust_name,cust_contact
from orders
where order_num in (
select order_num
from orderitems
where prod_id = "TNT2")
-- output
cust_id|cust_name |cust_contact|
-------+--------------+------------+
10001|Coyote Inc. |Y Lee |
10004|Yosemite Place|Y Sam |
如果把上面的步骤1-3全部使用子查询如下：
-- 使用子查询
select cust_id,cust_name,cust_contact
from customers
where cust_id in
(
select cust_id from orders
where order_num in (
select order_num from orderitems where prod_id = "TNT2"
)
)
-- output, 结果相同
cust_id|cust_name |cust_contact|
-------+--------------+------------+
10001|Coyote Inc. |Y Lee |
10004|Yosemite Place|Y Sam |
作为计算字段使用子查询
使用子查询的另一方法是创建计算字段。
假如需要显示customers表中每个客户的订单总数。
订单与相应的客户ID存储在orders表中。
步骤如下：
(1) 从customers表中检索客户列表。
(2) 对于检索出的每个客户，统计其在orders表中的订单数目
如果是单个客户，统计订单总数，我们可以使用 count(*), 例如 cust_id = 10001 的客户：
select count(*) from orders where cust_id = 10001
-- output
count(*)|
--------+
2|
现在是要查询每个客户的订单总数，那只需对每个客户只需count(*) 计算即可, 即将count(*) 作为一个子查询。
-- 作为计算字段使用子查询
select cust_name,cust_state, (
select count(*) from orders o where o.cust_id = c.cust_id
) as orders from customers c
-- output
cust_name |cust_state|orders|
--------------+----------+------+
Coyote Inc. |MI | 2|
Mouse House |OH | 0|
Wascals |IN | 1|
Yosemite Place|AZ | 1|
E Fudd |IL | 1|
这 条 SELECT 语句对 customers 表中每个客户返回 3 列 ：cust_name、cust_state和orders。
orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。
在此例子中，该子查询执行了5次，因为检索出了5个客户。
带ANY、IN、SOME关键字的子查询
ANY和SOME关键字是同义词，表示满足其中任一条件。
它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
语法
-- 语法如下
operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)
-- comparison_operator 为以下任意
= > < >= <= <> !=
操作演示
-- 1. 准备表和数据
CREATE TABLE `tb1` (
`num` int DEFAULT NULL
)
CREATE TABLE `tb2` (
`num` int DEFAULT NULL
)
INSERT INTO tb1 (num) VALUES(2);
INSERT INTO tb1 (num) VALUES(4);
INSERT INTO tb1 (num) VALUES(6);
INSERT INTO tb1 (num) VALUES(8);
INSERT INTO tb1 (num) VALUES(10);
INSERT INTO tb2 (num) VALUES(1);
INSERT INTO tb2 (num) VALUES(3);
INSERT INTO tb2 (num) VALUES(6);
INSERT INTO tb2 (num) VALUES(8);
-- 2. 测试1
SELECT num FROM tb1 WHERE num > any (SELECT num FROM tb2)
-- output， SELECT num FROM tb2 的值为（1,3,5），满足任何一个，即大于1即为true，所以输出所有
num|
---+
2|
4|
6|
8|
10|
-- 3. 测试2
select num FROM tb1 WHERE num >any (SELECT num FROM tb2 where num>2)
-- output，SELECT num FROM tb2 where num>4 的值为（3,5），即至少大于3或5的，结果即为
num|
---+
4|
6|
8|
10|
The word
SOMEis an alias forANY. Thus, these two statements are the same:⚡ SOME 是 ANY的别名
-- 以下两个sql等价
SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);
-- 即，以下等价
select num FROM tb1 WHERE num >any (SELECT num FROM tb2 where num>2)
select num FROM tb1 WHERE some >any (SELECT num FROM tb2 where num>2)
INis an alias for= ANY
即 IN 是 = ANY 或 = SOME 的别名
-- 以下等价
select num FROM tb1 where num in (SELECT num FROM tb2 )
SELECT num FROM tb1 WHERE num = ANY (SELECT num FROM tb2)
-- output
num|
---+
6|
8|
带ALL关键字的子查询
ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。
SELECT num FROM tb2
-- output
num|
---+
1|
3|
6|
8|
select num FROM tb1 where num >ALL (SELECT num FROM tb2 )
-- outp
num|
---+
10|
NOT IN is an alias for <> ALL.
-- 以下等价
select num FROM tb1 where num <> ALL (SELECT num FROM tb2 )
select num FROM tb1 where num not in (SELECT num FROM tb2 )
-- ouput, 即tb1中不包含 （1,3,6,8） 的全部值
num|
---+
2|
4|
10|
带EXISTS关键字的子查询
EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行。
如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；
如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。
-- 1.测试1
SELECT num FROM tb1 WHERE EXISTS (SELECT num FROM tb2);
-- output, 因为 SELECT num FROM tb2 返回4行，EXISTS的结果为true。因此进行外层查询
num|
---+
2|
4|
6|
8|
10|
-- 2.测试2
SELECT num FROM tb1 WHERE EXISTS (SELECT num FROM tb2 where num > 10);
-- output， 输出空，因为 SELECT num FROM tb2 where num > 10 没有任何返回行，EXISTS返回的结果是false，所以外层不进行查询
num|
---+
⚡ NOT EXISTS 即对 EXISTS 取反即可。
完整的子查询用法，请参看官方文档：
MySQL :: MySQL 8.0 Reference Manual :: 13.2.11 Subqueries
// 子查询包含如下内容
13.2.11.1 The Subquery as Scalar Operand
13.2.11.2 Comparisons Using Subqueries
13.2.11.3 Subqueries with ANY, IN, or SOME
13.2.11.4 Subqueries with ALL
13.2.11.5 Row Subqueries
13.2.11.6 Subqueries with EXISTS or NOT EXISTS
13.2.11.7 Correlated Subqueries
13.2.11.8 Derived Tables
13.2.11.9 Lateral Derived Tables
13.2.11.10 Subquery Errors
13.2.11.11 Optimizing Subqueries
13.2.11.12 Restrictions on Subqueries
