子查询：让一个问题为另一个问题提供答案
上一章，我们已经能把订单明细按品类分组，算出销量、销售额和平均价格。可聚合结果算出来以后，新的问题马上就来了：哪些商品高于全店平均价？哪些商品高于自己所属品类的平均价？哪些客户下过有效订单？哪些商品一笔销售也没有？
这些问题都有一个共同结构：要回答眼前的问题，必须先回答另一个问题。以“高于全店平均价”为例，数据库要先算出平均价，再拿每件商品的 unit_price 与它比较。这个写在另一条 SQL 里面、专门为外层提供答案的查询，就是子查询。
第一次看到查询套查询，很容易把注意力全放在括号上。其实真正决定写法的是两个问题：内层会返回一格值、一列值、一行多列，还是一张多行多列的表？内层是否需要读取外层当前行？前一个问题决定外层用 =、IN、行比较还是 FROM 接住答案，后一个问题决定它属于相关还是非相关子查询。
本章继续使用贯穿全课的小满商店。有效销售沿用上一章的统一口径：订单状态为 已支付、已发货、已完成；已取消 与 已退款 不计入有效销售。所有示例都使用共享的 customers、categories、products、orders、order_items、employees 与 inventory_movements，不会为了讲一个语法再换一套学生或银行数据。
从一个聚合结果开始
先看本章会频繁使用的商品。小满商店有 12 件商品，分属 5 个品类，其中既有在售商品，也有缺货和下架商品。
sql
SELECT
product_id,
product_name,
unit_price,
stock,
status
FROM products
ORDER BY product_id;shell
+------------+---------------------+------------+-------+--------+
| product_id | product_name | unit_price | stock | status |
+------------+---------------------+------------+-------+--------+
| 1 | 白瓷马克杯 | 39.90 | 120 | 在售 |
| 2 | 原木托盘 | 89.00 | 45 | 在售 |
| 3 | 四季手账本 | 49.00 | 86 | 在售 |
| 4 | 雾蓝中性笔套装 | 29.90 | 150 | 在售 |
| 5 | 65W 氮化镓充电器 | 169.00 | 32 | 在售 |
| 6 | 编织数据线 | 39.00 | 210 | 在售 |
| 7 | 亚麻抱枕 | 79.00 | 0 | 缺货 |
| 8 | 暖光阅读灯 | 129.00 | 28 | 在售 |
| 9 | 轻量折叠伞 | 99.00 | 64 | 在售 |
| 10 | 保温随行杯 | 139.00 | 39 | 在售 |
| 11 | 旧版周计划本 | 35.00 | 6 | 下架 |
| 12 | 香樟衣柜挂片 | 25.00 | 90 | 在售 |
+------------+---------------------+------------+-------+--------+上一章已经能计算全店商品平均价：
sql
SELECT ROUND(AVG(unit_price), 2) AS avg_price
FROM products;shell
+-----------+
| avg_price |
+-----------+
| 76.82 |
+-----------+如果目标是找出高于平均价的商品，可以先运行上面的 SQL，再把 76.82 抄进下一条查询。但商品一调价，手抄常量就过期了。让平均价查询直接给外层提供答案，才能让条件随数据一起变化：
sql
SELECT product_id, product_name, unit_price
FROM products
WHERE unit_price > (
SELECT AVG(unit_price)
FROM products
)
ORDER BY unit_price DESC;shell
+------------+---------------------+------------+
| product_id | product_name | unit_price |
+------------+---------------------+------------+
| 5 | 65W 氮化镓充电器 | 169.00 |
| 10 | 保温随行杯 | 139.00 |
| 8 | 暖光阅读灯 | 129.00 |
| 9 | 轻量折叠伞 | 99.00 |
| 2
外层查询负责找商品，内层查询负责给出比较基准。括号把内层划成一个独立查询块。调试复杂子查询时，可以先单独运行括号里的部分，确认它返回几行几列、有没有 NULL，再把它放回外层。
先看答案形状，再决定怎样接住它
很多子查询报错并不是条件写错，而是外层使用方式与内层答案形状不匹配。普通等号右边只能接一个值，IN 通常接一列值，行构造器要接列数相同的一行，FROM 需要一张结果表。我们依次拆开。
标量子查询：一行一列
标量就是一个值。下面的子查询通过 AVG() 返回一行一列，因此可以参与减法：
sql
SELECT
product_name,
unit_price,
ROUND(
unit_price - (SELECT AVG(unit_price) FROM products),
2
) AS above_avg
FROM products
WHERE product_id = 5;shell
+---------------------+------------+-----------+
| product_name | unit_price | above_avg |
+---------------------+------------+-----------+
| 65W 氮化镓充电器 | 169.00 | 92.18 |
+---------------------+------------+-----------+标量子查询最多返回一行。如果它返回两行或更多行，数据库不知道等号应该采用哪一个值，会报“子查询返回多于一行”一类错误。
sql
-- 错误：文具手账品类有三件商品，内层会返回三行
SELECT product_name
FROM products
WHERE unit_price = (
SELECT unit_price
FROM products
WHERE category_id = 3
);不要用随手加 LIMIT 1 的办法把错误压下去。除非题目明确允许任取一行，而且查询还给出了稳定的 ORDER BY，否则这只是把“为什么应该只有一个答案”藏起来。需要最高价就写 MAX()，需要最新一条就用日期与唯一键共同排序，应该唯一就用唯一条件保证。
标量子查询返回零行时，标量位置得到 NULL：
sql
SELECT (
SELECT unit_price
FROM products
WHERE product_id = 999
) AS no_row_value;shell
+--------------+
| no_row_value |
+--------------+
| NULL |
+--------------+聚合查询没有匹配明细时看起来也会得到 NULL，但过程不同。下面的 MAX() 仍返回一行，只是这一行的聚合值为空：
sql
SELECT (
SELECT MAX(unit_price)
FROM products
WHERE product_id = 999
) AS aggregate_value;shell
+-----------------+
| aggregate_value |
+-----------------+
| NULL |
+-----------------+这个区别在 ALL 的空集合语义里会直接影响结果，后面会专门比较。
单列子查询：多行一列
“所有有效订单的客户编号”不是一个值，而是一列值：
sql
SELECT customer_id
FROM orders
WHERE status IN ('已支付', '已发货', '已完成')
ORDER BY order_id;shell
+-------------+
| customer_id |
+-------------+
| 1 |
| 2 |
| 4 |
| 6 |
| 9 |
| 2 |
| 10 |
| 1 |
| 4 |
| 8 |
+-------------+顾言、苏小满和江行都重复出现，因为他们各有两笔有效订单。这个答案不能放到普通等号右边，却可以交给 IN、ANY 或 ALL。对于 IN，内层重复值不会让外层客户重复，因为它只判断成员关系，不把订单行拼到结果里。
行子查询：单行多列
有时一份答案要靠几列共同表达。下面找出“与商品 10 属于同一品类且价格相同”的商品，品类与价格必须成对比较：
sql
SELECT product_id, product_name, category_id, unit_price
FROM products
WHERE (category_id, unit_price) = (
SELECT category_id, unit_price
FROM products
WHERE product_id = 10
);shell
+------------+--------------+-------------+------------+
| product_id | product_name | category_id | unit_price |
+------------+--------------+-------------+------------+
| 10 | 保温随行杯 | 5 | 139.00 |
+------------+--------------+-------------+------------+左边 (category_id, unit_price) 是行构造器，右边必须返回一行两列。列数和顺序都要对应。若右边零行，整次行比较得到 NULL；若右边返回多行，则会报错。
多列答案也可以有多行，此时常与多列 IN 搭配。下面先按品类聚合出每类最高价，再用“品类编号 + 价格”联合匹配：
sql
SELECT p.category_id, p.product_name, p.unit_price
FROM products AS p
WHERE (p.category_id, p.unit_price) IN (
SELECT category_id, MAX(unit_price)
FROM products
GROUP BY category_id
)
ORDER BY p.category_id;shell
+-------------+---------------------+------------+
| category_id | product_name | unit_price |
+-------------+---------------------+------------+
| 1 | 暖光阅读灯 | 129.00 |
| 2 | 原木托盘 | 89.00 |
| 3 | 四季手账本 | 49.00 |
| 4 | 65W 氮化镓充电器 | 169.00 |
| 5
如果某个品类有两件并列最高价商品，它们都会保留。这正是联合匹配想表达的含义，不需要用 LIMIT 1 随机删掉并列项。
派生表：多行多列
当子查询写在 FROM 中，它提供一张只在本次语句里使用的结果表，常称为派生表。下面先按商品汇总有效销量和明细折后金额，再由外层补上商品名称：
sql
SELECT
p.product_name,
s.order_count,
s.sold_quantity,
ROUND(s.sales_amount, 2) AS sales_amount
FROM products AS p
JOIN (
SELECT
oi.product_id,
COUNT(DISTINCT oi.order_id) AS
shell
+---------------------+-------------+---------------+--------------+
| product_name | order_count | sold_quantity | sales_amount |
+---------------------+-------------+---------------+--------------+
| 65W 氮化镓充电器 | 3 | 3 | 463.69 |
| 轻量折叠伞 | 2 | 2 | 193.00 |
| 编织数据线 | 4 | 5 | 184.30 |
|
在 MySQL 中，FROM 里的派生表必须有别名，这里是 s。外层只能看到派生表公开的四列，不能再直接引用内层的 oi.quantity。这个边界能把复杂查询拆成两个清楚阶段：内层负责汇总，外层负责补充描述与排序。
“标量、单列、行、派生表”描述的是结果形状；“相关、非相关”描述的是内层是否引用外层。两套分类互不冲突。一个标量子查询既可能非相关，也可能相关；一个 EXISTS 子查询通常相关，但语法上也允许不引用外层。
非相关与相关：看内层是否读取外层当前行
判断一个子查询是否相关，不要靠嵌套层数，也不要看它是不是写在 EXISTS 里。只看一件事：内层查询有没有引用外层查询的列。
非相关子查询给所有外层行同一份答案
全店平均价与外层当前处理的是哪件商品无关：
sql
SELECT product_name, unit_price
FROM products
WHERE unit_price > (
SELECT AVG(unit_price)
FROM products
);内层只读取 products 自己的列，没有引用外层别名。从语义上说，12 件外层商品拿到的是同一份平均价答案。优化器可以先求一次再复用，也可以用其他等价计划实现。我们不应该根据 SQL 书写顺序断言物理执行一定“先内后外”，但可以确定内层不依赖外层当前行。
相关子查询把外层值带进内层
“高于全店平均价”只有一条基准线，“高于所属品类平均价”却需要每个品类自己的基准线。内层必须知道外层商品属于哪个品类：
sql
SELECT
p.product_name,
p.category_id,
p.unit_price,
ROUND((
SELECT AVG(p2.unit_price)
FROM products AS p2
WHERE p2.category_id = p.category_id
), 2) AS category_avg_price
FROM products
shell
+---------------------+-------------+------------+--------------------+
| product_name | category_id | unit_price | category_avg_price |
+---------------------+-------------+------------+--------------------+
| 亚麻抱枕 | 1 | 79.00 | 77.67 |
| 暖光阅读灯 | 1 | 129.00 | 77.67 |
| 原木托盘 | 2 | 89.00 | 64.45 |
| 四季手账本
关键条件是 p2.category_id = p.category_id。左边来自内层别名 p2，右边的 p.category_id 来自外层当前商品。外层来到白瓷马克杯时，右边是 2，内层问题变成“厨房用品平均价是多少”；外层来到暖光阅读灯时，右边换成 1，内层问题也随之变成“居家生活平均价是多少”。
第一次读相关子查询，可以按下面的方式代入：
先拿到外层一件商品。例如亚麻抱枕的 category_id 是 1，价格是 79 元。
把外层品类 1 带进内层条件，计算居家生活三件商品的平均价 77.67 元。
比较 79 是否大于 77.67。条件为真，所以亚麻抱枕进入结果。
外层换到香樟衣柜挂片，同一品类基准仍是 77.67；25 不大于基准，因此这一行被过滤。
这套逐行代入法适合理解逻辑语义，却不等于数据库必然照着它逐行物理执行。MySQL 8.4 的优化器可能把某些相关子查询去相关化，改成派生表与连接；IN 和 EXISTS 还可能转成半连接。反过来，如果计划仍保留依赖外层参数的子计划，它就可能重复求值。性能结论要看执行计划，不能只数括号。
别名决定列来自哪一层
相关子查询最隐蔽的错误是把两边都写成内层别名：
sql
-- 错误：两边都指向内层 p2，没有建立内外层关系
SELECT p.product_name
FROM products AS p
WHERE p.unit_price > (
SELECT AVG(p2.unit_price)
FROM products AS p2
WHERE p2.category_id = p2.category_id
);p2.category_id = p2.category_id 对所有非空品类都为真，内层算的是全店平均价，不是当前品类平均价。把外层写成 p、内层同表写成 p2 或 p_inner，并在相关条件两侧显式带别名，能避开这类结果看似合理、含义已经变掉的错误。
IN、EXISTS 与 NOT EXISTS 在问不同的问题
IN 和 EXISTS 经常能得到相同结果，但它们组织问题的方式不同。IN 关注“这个值是否属于内层集合”，EXISTS 关注“按当前外层行给出的条件，内层是否至少存在一行”。先选对语义，再让执行计划判断具体实现。
IN：把一个值放进动态集合里检查
找出有有效订单的客户，可以让内层先给出客户编号集合：
sql
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE status IN ('已支付', '已发货', '已完成')
)
ORDER BY customer_id;shell
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | 苏小满 |
| 2 | 顾言 |
| 4 | 江行 |
| 6 | 余晴 |
| 8 | 夏初 |
| 9 | 程一 |
| 10 |
内层的 1、2、4 各出现两次，外层仍只返回每位客户一次。IN 是真假判断，不会像普通一对多连接那样把客户行复制多份。给内层加 DISTINCT 可以让查询意图更直观，但是否有必要要看执行计划；半连接策略本来就只关心有没有匹配。
EXISTS：只问是否至少有一行
同一个问题也可以从客户出发，为当前客户检查有效订单是否存在：
sql
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status IN ('已支付', '已发货', '已完成')
)
结果仍是苏小满、顾言、江行、余晴、夏初、程一和安禾。EXISTS 不使用子查询选择列表里的具体值，它只关心有没有行，所以习惯写 SELECT 1。即使改成 SELECT NULL，只要条件返回一行，EXISTS 仍为真。
sql
SELECT EXISTS (
SELECT NULL
FROM orders
WHERE order_id = 101
) AS has_row;shell
+---------+
| has_row |
+---------+
| 1 |
+---------+从逻辑上说，发现第一条匹配行就足以确定 EXISTS 为真，不需要为了这个判断取完所有行。不过优化器可能把它整体改写成半连接，所以不要把“找到第一行即可”误解为永远可见的一条固定执行指令。
NOT EXISTS：找一行也没有匹配的对象
“从未下过任何订单的客户”是典型的反向存在问题：
sql
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;shell
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 7 | 陆野 |
+-------------+---------------+白露只有一笔已取消订单，乔木只有待支付订单，但题目问的是“任何订单”，所以他们都不在结果里。如果题目改成“没有有效订单”，就要把有效状态条件写进内层，白露、乔木和陆野都会被选中。NOT EXISTS 不会替你猜“下过单”究竟采用什么状态口径。
NOT IN 为什么会被一个 NULL 拖住
客户表的 referrer_id 记录推荐人，没人推荐时为 NULL。现在想找出“没有推荐过其他人的客户”，直觉上可能写：
sql
SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
SELECT referrer_id
FROM customers
);结果是空集。内层既有 1、2、3、4，也有多个 NULL。以客户 5 为例，SQL 不只是检查 5 <> 1、5 <> 2，还必须判断 5 <> NULL。后者不是 TRUE，而是 UNKNOWN。整组比较里没有相等造成的明确 FALSE，却含有 UNKNOWN，最后仍是 UNKNOWN；WHERE 只保留 TRUE，客户 5 也被过滤了。
一种修法是在内层排除空值：
sql
SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
SELECT referrer_id
FROM customers
WHERE referrer_id IS NOT NULL
)
ORDER BY customer_id;更贴近“没有推荐记录”这个业务句子的写法是 NOT EXISTS：
sql
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM customers AS referred
WHERE referred.referrer_id = c.customer_id
)
ORDER BY c.customer_id;shell
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 5 | 乔木 |
| 6 | 余晴 |
| 7 | 陆野 |
| 8 | 夏初 |
| 9 | 程一 |
| 10 | 安禾 |
+-------------+---------------+NOT EXISTS 不会因为内层别的行有 NULL 就污染整次判断。它只检查是否存在 referred.referrer_id = c.customer_id 的行。客户 5 没有被任何人引用，所以条件为真。
只要 NOT IN 的子查询列可能出现 NULL，就要停下来检查三值逻辑。能用 NOT EXISTS 清楚表达的反向存在问题，通常优先写 NOT EXISTS；如果保留 NOT IN，就在内层明确排除 NULL，同时确认外层参与比较的值是否也可能为空。
不要背 EXISTS 一定比 IN 快
可以先根据问题选写法：
MySQL 8.4 可以把符合条件的 IN、= ANY 与 EXISTS 改写成半连接，也可以选择物化；否定形式在条件允许时可能变成反连接。两种 SQL 看起来不同，最后却可能落到相近的执行计划。先写对含义，再用 EXPLAIN 检查这批表、索引与统计信息下的选择。
ANY 与 ALL：把一次比较扩展到一组答案
IN 处理等值成员关系。如果问题是“价格高于厨房用品中的至少一件商品”或“价格高于厨房用品中的所有商品”，就需要量化比较。
ANY：至少有一次比较为真
厨房用品有白瓷马克杯 39.90 元和原木托盘 89 元：
sql
SELECT product_name, unit_price
FROM products
WHERE unit_price > ANY (
SELECT unit_price
FROM products
WHERE category_id = 2
)
ORDER BY unit_price DESC;只要高于集合里的任意一个值就成立。这里相当于高于非空集合的最小值 39.90。
shell
+---------------------+------------+
| product_name | unit_price |
+---------------------+------------+
| 65W 氮化镓充电器 | 169.00 |
| 保温随行杯 | 139.00 |
| 暖光阅读灯 | 129.00 |
| 轻量折叠伞 | 99.00 |
| 原木托盘 | 89.00 |
| 亚麻抱枕 | 79.00 |
| 四季手账本
SOME 是 ANY 的同义词，项目中 ANY 更常见。= ANY (子查询) 与 IN (子查询) 等价。
ALL：每一次比较都必须为真
sql
SELECT product_name, unit_price
FROM products
WHERE unit_price > ALL (
SELECT unit_price
FROM products
WHERE category_id = 2
)
ORDER BY unit_price DESC;shell
+---------------------+------------+
| product_name | unit_price |
+---------------------+------------+
| 65W 氮化镓充电器 | 169.00 |
| 保温随行杯 | 139.00 |
| 暖光阅读灯 | 129.00 |
| 轻量折叠伞 | 99.00 |
+---------------------+------------+高于厨房用品的所有价格，就是高于最大值 89。类似地，< ALL 可理解为低于最小值，< ANY 可理解为低于最大值，但这些直观改写只有在认真处理 NULL 和空集合后才安全。
空集合会让 ANY 为假、ALL 为真
如果内层一行都没有，ANY 找不到任何一次成功比较，因此为假；ALL 也找不到任何一次失败比较，因此为真。
sql
SELECT
100 > ANY (
SELECT unit_price
FROM products
WHERE category_id = 999
) AS greater_than_any,
100 > ALL (
SELECT unit_price
FROM products
WHERE category_id = 999
) AS greater_than_all;shell
+------------------+------------------+
| greater_than_any | greater_than_all |
+------------------+------------------+
| 0 | 1 |
+------------------+------------------+这时不能把 > ALL (空集合) 随意改成 > (SELECT MAX(...))。聚合查询没有匹配明细时仍返回一行 NULL，而 100 > NULL 是 UNKNOWN：
sql
SELECT 100 > (
SELECT MAX(unit_price)
FROM products
WHERE category_id = 999
) AS greater_than_max;shell
+------------------+
| greater_than_max |
+------------------+
| NULL |
+------------------+集合里出现 NULL 时怎样传播
对 ANY 来说，只要有一次比较明确为真，整体就是真；如果没有真，但至少一次因为 NULL 成为 UNKNOWN，整体为 UNKNOWN。对 ALL 来说，只要有一次明确为假，整体就是假；如果没有假，但至少一次为 UNKNOWN，整体也是 UNKNOWN。
因此，把 ANY 或 ALL 改写成 MIN()、MAX() 前，要逐项确认内层会不会为空、比较列会不会有 NULL、原表达式在这些边界上应该是什么结果。
子查询可以放在 SELECT、WHERE、HAVING、FROM 和 DML 中
子查询不是 WHERE 的专属语法。只要当前位置允许对应形状的表达式或表，子查询就可以为它提供答案。
放在 SELECT：为每一行补一个值
下面为每位客户补上有效订单数与有效订单总额。这里使用 orders.total_amount，因为它是订单最终金额，可能包含没有拆进明细列的订单级优惠或运费。
sql
SELECT
c.customer_name,
(
SELECT COUNT(*)
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status IN ('已支付', '已发货', '已完成')
) AS effective_order_count,
COALESCE((
SELECT
shell
+---------------+-----------------------+------------------+
| customer_name | effective_order_count | effective_amount |
+---------------+-----------------------+------------------+
| 苏小满 | 2 | 218.80 |
| 顾言 | 2 | 338.00 |
| 白露 | 0 | 0.00 |
| 江行 | 2 | 410.90 |
| 乔木
两个子查询都引用 c.customer_id，属于相关标量子查询。COUNT(*) 没有匹配行仍返回 0；SUM() 没有可求和的行时返回 NULL，所以外层用 COALESCE(..., 0) 统一展示。
放在 WHERE：动态生成筛选基准
前面的“高于所属品类平均价”是相关子查询。非相关版本也很常见，例如找库存低于全店平均库存的商品：
sql
SELECT product_id, product_name, stock
FROM products
WHERE stock < (
SELECT AVG(stock)
FROM products
)
ORDER BY stock, product_id;全店平均库存为 72.5，结果如下：
shell
+------------+---------------------+-------+
| product_id | product_name | stock |
+------------+---------------------+-------+
| 7 | 亚麻抱枕 | 0 |
| 11 | 旧版周计划本 | 6 |
| 8 | 暖光阅读灯 | 28 |
| 5 | 65W 氮化镓充电器 | 32 |
| 10 |
放在 HAVING：让一个聚合结果约束另一个聚合结果
先算五个品类各自的商品数，再求“每个品类平均有多少件商品”，最后留下规模高于平均值的品类：
sql
SELECT
c.category_name,
COUNT(*) AS product_count
FROM categories AS c
JOIN products AS p ON p.category_id = c.category_id
GROUP BY c.category_id, c.category_name
HAVING COUNT(*) > (
SELECT
五个品类分别有 3、2、3、2、2 件商品，平均 2.4 件：
shell
+---------------+---------------+
| category_name | product_count |
+---------------+---------------+
| 居家生活 | 3 |
| 文具手账 | 3 |
+---------------+---------------+这里有两层子查询。最里面按品类计数，中间层求这些计数的平均值，外层 HAVING 筛选品类。每层只解决一个问题，反而比把所有逻辑挤在一个查询块里更容易验证。
放在 FROM：先汇总，再补充描述
下面同时构造品类均价与商品有效销售汇总，最后由外层形成商品经营表：
sql
SELECT
c.category_name,
p.product_name,
p.unit_price,
ROUND(ca.avg_price, 2) AS category_avg_price,
CASE
WHEN p.unit_price > ca.avg_price THEN '高于类别均价'
ELSE '不高于类别均价'
END AS price_position,
COALESCE(
shell
+---------------+---------------------+------------+--------------------+------------------+-----------------------+---------------+--------------+
| category_name | product_name | unit_price | category_avg_price | price_position | effective_order_count | sold_quantity | sales_amount |
+---------------+---------------------+------------+--------------------+------------------+-----------------------+---------------+--------------+
| 居家生活 | 暖光阅读灯 | 129.00 | 77.67 | 高于类别均价 | 1 | 1 | 120.00 |
| 居家生活
LEFT JOIN 很关键：销售汇总派生表里没有保温随行杯、旧版周计划本和香樟衣柜挂片，但经营表仍要保留所有商品，所以不能用内连接把它们丢掉。这已经在为下一章的外连接埋下问题。
放在 INSERT：让查询提供待写入数据
假设补货已经验收入库，需要为“在售、库存大于 0 且低于平均库存”的商品各记一笔 20 件采购入库。inventory_movements 的 quantity 不允许为 0，因此这里记录的是已经发生的入库数量，不是空任务：
sql
INSERT INTO inventory_movements (
movement_id,
product_id,
employee_id,
movement_type,
quantity,
moved_at,
note
)
SELECT
1000 + p.product_id,
p.product_id,
7,
'采购入库',
20,
'2026-08-01 09:00:00',
'低库存商品补货'
FROM products AS p
WHERE p
shell
Query OK, 5 rows affected五件商品是原木托盘、65W 氮化镓充电器、暖光阅读灯、轻量折叠伞和保温随行杯。真实业务还要在同一事务中更新 products.stock，不能只写流水不改库存；这里先聚焦“查询如何为插入提供多行数据”，事务会在后续章节完整处理。
放在 UPDATE：按存在性改变商品状态
找出当前仍在售、但从未进入任何订单明细的商品，把状态改为下架：
sql
START TRANSACTION;
UPDATE products AS p
SET status = '下架'
WHERE p.status = '在售'
AND NOT EXISTS (
SELECT 1
FROM order_items AS oi
WHERE oi.product_id = p.product_id
);
ROLLBACK;如果执行更新后再回滚，会看到本次匹配到保温随行杯与香樟衣柜挂片两件在售商品；旧版周计划本也从未进入订单明细，但它原本已经下架，不满足外层状态条件。用事务回滚，是为了让后续示例继续使用共享数据原貌。
放在 DELETE：先用同条件 SELECT 预览
删除动作不可凭感觉执行。假设要清理“已经下架且从未出现在订单明细中”的旧商品，先查看目标：
sql
SELECT p.product_id, p.product_name
FROM products AS p
WHERE p.status = '下架'
AND NOT EXISTS (
SELECT 1
FROM order_items AS oi
WHERE oi.product_id = p.product_id
);shell
+------------+----------------+
| product_id | product_name |
+------------+----------------+
| 11 | 旧版周计划本 |
+------------+----------------+确认范围后，再在事务里使用相同条件：
sql
START TRANSACTION;
DELETE FROM products
WHERE status = '下架'
AND NOT EXISTS (
SELECT 1
FROM order_items AS oi
WHERE oi.product_id = products.product_id
);
-- 确认影响行数与剩余数据；决定保留时使用 COMMIT
ROLLBACK;子查询只负责精确圈定目标，不能替代删除前的权限、备份和影响范围确认。更新与删除尤其要先写同条件 SELECT，再决定是否执行修改。
子查询不一定是最终写法：用 JOIN 或窗口函数改写
子查询很擅长表达“先回答一个问题，再回答另一个问题”。当数据量增大，或同一内层逻辑被反复计算时，我们还要识别可以等价改写的结构。改写不是为了消灭括号，而是为了给优化器更合适的选择，或让一份中间结果只计算一次。
把 SELECT 中的重复相关聚合改成一次分组连接
前面的客户订单统计在 SELECT 中写了两个相关子查询。可以先按客户聚合，再左连接客户表：
sql
SELECT
c.customer_name,
COALESCE(os.effective_order_count, 0) AS effective_order_count,
COALESCE(os.effective_amount, 0) AS effective_amount
FROM customers AS c
LEFT JOIN (
SELECT
customer_id,
COUNT(*) AS effective_order_count,
SUM(total_amount)
结果与相关标量子查询版本相同。派生表只按订单聚合一次；左连接保证白露、乔木和陆野仍出现在结果中。它不一定在所有规模上都更快，但如果还要增加最大订单额、最近下单时间等列，这种结构可以复用一次分组结果。
把 EXISTS 改成连接时小心行数膨胀
EXISTS 版本每位客户最多返回一次：
sql
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status IN ('已支付', '已发货', '已完成')
);如果直接改成普通内连接，苏小满、顾言和江行各有两笔有效订单，会出现两行。要保持“每位客户一行”的半连接语义，可以连接已经去重的键集合：
sql
SELECT c.customer_id, c.customer_name
FROM customers AS c
JOIN (
SELECT DISTINCT customer_id
FROM orders
WHERE status IN ('已支付', '已发货', '已完成')
) AS effective_customers
ON effective_customers.customer_id = c.customer_id
ORDER BY c.也可以在最外层 SELECT DISTINCT，但那会掩盖重复是在哪里产生的。EXISTS 天然说明“右边有多少条不重要”，只为了性能手工改成普通连接，很可能先把结果改错。
把 NOT EXISTS 改成左连接时检查右表主键
“从未下单的客户”也能写成左连接：
sql
SELECT c.customer_id, c.customer_name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;结果仍只有陆野。右表没有匹配时，连接补出的列都为 NULL，因此用右表主键 o.order_id IS NULL 判断最稳妥。不要随手检查右表本来就允许为空的业务列，否则“匹配到了但该列为空”与“完全没匹配”会混在一起。
每组最高价可以连接聚合结果
相关子查询版本：
sql
SELECT p.category_id, p.product_name, p.unit_price
FROM products AS p
WHERE p.unit_price = (
SELECT MAX(p2.unit_price)
FROM products AS p2
WHERE p2.category_id = p.category_id
);先聚合再连接的版本：
sql
SELECT p.category_id, p.product_name, p.unit_price
FROM products AS p
JOIN (
SELECT category_id, MAX(unit_price) AS max_price
FROM products
GROUP BY category_id
) AS m
ON m.category_id = p.category_id
AND m.
两种写法都会保留并列最高价。派生表版本明确告诉数据库“先得到每个品类一行的最高价，再匹配商品”。
用窗口函数保留每组最高价
sql
SELECT category_id, product_name, unit_price
FROM (
SELECT
p.category_id,
p.product_name,
p.unit_price,
DENSE_RANK() OVER (
PARTITION BY p.category_id
ORDER BY p.unit_price DESC
) AS price_rank
FROM products AS p
)
窗口函数不把原始商品行压缩掉，而是在每个品类内部给价格排名。DENSE_RANK() 让并列最高价都得到 1；如果使用 ROW_NUMBER()，每组只能留下一行。除非业务明确要求强行选一件，并提供稳定的并列排序，否则 DENSE_RANK() 更接近“所有最高价商品”。
改写前先检查三件事
- 改写前后是否保留相同的重复行、
NULL与空集合语义。 - 计划中的扫描量、循环次数和索引访问方式是否真的改善。
- 查询是否仍然清楚表达业务口径，例如有效订单状态有没有遗漏。
标量子查询只计算一次、结果很小，或 EXISTS 已被优化器转换成半连接时，手工改写未必有收益。能够准确表达问题且执行计划健康的子查询，不需要为了“看起来高级”硬拆成连接。
用 EXPLAIN 看优化器怎样处理子查询
SQL 描述想得到什么，不承诺按文字顺序执行。MySQL 8.4 可能把 IN 或 EXISTS 转成半连接，把可转换的否定存在条件变成反连接，把非相关子查询物化成内部结果，也可能保留依赖外层值的子计划。EXPLAIN 用来展示优化器最后选择的路径。
索引要落在相关键和存在性匹配键上
共享表已经有 idx_orders_customer_date(customer_id, order_date) 与 idx_order_items_product_order(product_id, order_id)。如果有效订单存在性检查非常频繁，还可以评估增加下面的复合索引：
sql
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);
CREATE INDEX idx_products_category_price
ON products(category_id, unit_price);第一个索引配合 o.customer_id = c.customer_id 与状态筛选，第二个索引配合相关品类条件与价格聚合。索引不是“给子查询加速”的抽象开关，它必须对应查询里真正使用的相关键、连接键和筛选列。
从传统 EXPLAIN 的 select_type 看依赖
sql
EXPLAIN
SELECT p.product_name
FROM products AS p
WHERE p.unit_price > (
SELECT AVG(p2.unit_price)
FROM products AS p2
WHERE p2.category_id = p.category_id
);具体计划会随数据量、统计信息、索引和优化器设置变化。传统表格中常见的子查询相关标记包括：
看到 DEPENDENT SUBQUERY 不等于立即判定“很慢”。继续问：外层有多少行？内层每次能否通过索引按品类定位？不同外层参数有多少种？是否可以先按品类聚合成 5 行再连接？
看 type、key、rows 与 Extra
传统 EXPLAIN 里可以先盯住这些列：
type表示访问方式。ALL通常是全表扫描，range、ref、eq_ref更有针对性，但不能脱离返回比例机械排名。possible_keys与key分别展示可选索引和实际采用的索引。相关列没有出现在可用索引里，值得回头检查索引设计。rows是优化器估计需要读取的行数，不是已经发生的读取量。filtered是预计经过本层条件后保留的比例。Extra可能展示额外过滤、覆盖索引、临时表或半连接策略等信息。
小满商店当前只有 12 件商品，即使扫描全表也很快；但课程要训练的是能迁移到百万行表的判断方式。外层百万行、内层依赖外层且每次扫描同品类大量行，风险很明显。若复合索引让内层每次只读少量索引项，成本会低很多；若优化器已去相关化，计划里可能看不到原样的相关子查询。
FORMAT=TREE 更容易看出计划结构
sql
EXPLAIN FORMAT=TREE
SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status IN ('已支付', '已发货', '已完成')
);树形计划能显示谁是谁的输入、条件在哪一层应用，以及优化器是否采用半连接、嵌套循环、索引查找或物化。不要背某一份固定文本，沿缩进从下往上读：底层怎样取得行，上层怎样过滤、聚合或连接。
EXPLAIN ANALYZE 用循环次数验证估计
sql
EXPLAIN ANALYZE
SELECT p.product_name
FROM products AS p
WHERE p.unit_price > (
SELECT AVG(p2.unit_price)
FROM products AS p2
WHERE p2.category_id = p.category_id
);EXPLAIN ANALYZE 会执行查询，并在计划节点上给出估计行数、返回行数、耗时与循环次数。对相关子查询，loops 很有用：内层节点循环次数接近外层行数，说明它确实在重复执行；如果只运行一次并被复用，则可能发生了物化或去相关化。
普通 EXPLAIN 只生成计划，EXPLAIN ANALYZE 会真正执行语句。对耗时查询要评估负载；对 UPDATE、DELETE、INSERT 更要谨慎，不能把分析命令当成没有副作用的预览。
一套够用的性能检查顺序
先确认语义。标量是否最多一行，NOT IN 是否处理 NULL，连接改写是否制造重复。结果不等价，速度比较没有意义。
再看依赖。计划中是否仍有依赖子查询、相关子计划或高循环次数节点；非相关结果是否只求值或物化一次。
检查扫描量。比较估计读取行数与输出行数，使用分析计划时再看实际行数、循环次数和累计耗时。
检查索引。相关键、存在性匹配键、连接键与筛选列是否按查询方式组成合适索引，索引是否真的被选择。
综合任务：从品类基准找到值得关注的商品
现在把几种角色放在同一条查询中：先算每个品类平均价，再找出高于平均价的商品；商品还必须至少出现在一笔有效订单中，最后展示有效销量和明细折后金额。
sql
SELECT
c.category_name,
p.product_name,
p.unit_price,
ROUND(category_stats.avg_price, 2) AS category_avg_price,
sales.sold_quantity,
ROUND(sales.sales_amount, 2) AS sales_amount
FROM products AS p
JOIN
shell
+---------------+---------------------+------------+--------------------+---------------+--------------+
| category_name | product_name | unit_price | category_avg_price | sold_quantity | sales_amount |
+---------------+---------------------+------------+--------------------+---------------+--------------+
| 数码配件 | 65W 氮化镓充电器 | 169.00 | 104.00 | 3 | 463.69 |
| 居家生活 | 亚麻抱枕 | 79.00 | 77.67 | 2
category_stats 是聚合派生表，给商品提供品类基准；EXISTS 只判断订单明细是否属于有效订单；sales 再按商品汇总数量和金额。保温随行杯虽然高于户外出行均价，却没有任何订单明细，因此不会进入销售派生表，也不会出现在结果中。
sales 内部的 EXISTS 也可以改成对 orders 的内连接：
sql
SELECT
oi.product_id,
SUM(oi.quantity) AS sold_quantity,
SUM(
oi.quantity * oi.unit_price * (1 - oi.discount_rate)
) AS sales_amount
FROM order_items AS oi
JOIN orders AS o ON o
这里连接不会制造额外明细，因为 orders.order_id 是主键，每条订单明细最多匹配一笔订单。确认这种唯一性，是安全改写连接的前提。
巩固练习
下面继续使用共享的小满商店数据。先把内层问题单独写出来，再决定外层应该用标量比较、IN、EXISTS、行比较还是派生表接住它。折叠答案同时给出结果，方便检查订单口径、重复行与 NULL 是否处理正确。
找出高于全店均价且仍在售的商品
要求使用标量子查询，展示商品名称、价格与全店均价，按价格降序。
找出每个品类价格最低的商品
要求使用多列 IN，并保留并列最低价。
找出有有效订单的杭州客户
要求使用 EXISTS，把城市条件放在外层。
找出从未进入任何订单明细的商品
要求使用 NOT EXISTS。
找出价格低于数码配件任意一件商品的商品
要求使用 < ANY，解释它与最大值的关系。
找出价格高于厨房用品全部商品的商品
要求使用 > ALL，并说明空集合时为什么不能直接改成 MAX()。
在 SELECT 中显示每件商品所属品类的商品数
要求使用相关标量子查询，按品类与商品编号排序。
统计每个品类的有效明细销售额
要求先在派生表中按商品汇总，再由外层按品类汇总。
把相关最高价查询改成窗口函数
要求保留并列最高价，并说明为什么选 DENSE_RANK()。
修复 NOT IN 的空结果
原查询想找没有推荐过其他人的客户，却因为内层含 NULL 返回空集。请分别用排除 NULL 与 NOT EXISTS 修复。
为存在性查询设计并检查索引
目标查询是“找出有有效订单的客户”。创建索引后，用树形计划检查是否发生半连接或索引查找。
从“是否匹配”走向“把两边都留下”
子查询的核心可以压缩成两次判断。第一，内层答案是什么形状：一个值、一列、一行，还是一张表。第二，内层是否引用外层当前行：不引用就是非相关，引用就是相关。形状决定外层用等号、IN、行比较还是 FROM；相关性提醒我们继续查看计划中是否存在重复求值。
IN、EXISTS 和 NOT EXISTS 解决成员与存在性，ANY 与 ALL 解决对整组值的量化比较。遇到 NULL 和空集合时，别靠自然语言猜结果，回到三值逻辑逐项判断。做性能优化时，也不要断言相关子查询必然逐行执行，更不要断言连接必然更快；让 EXPLAIN 和 EXPLAIN ANALYZE 告诉你优化器是否采用半连接、反连接、物化、索引查找或重复子计划。
不过，子查询常常只回答“有没有”“属于不属于”或“基准是多少”。下一步会遇到另一类需求：陆野即使没有订单也要出现在客户报表里，保温随行杯即使销量为零也要保留，左右两边的列还要同时展示。那时，仅做存在性判断已经不够，我们需要精确控制哪一边的行被保留、没有匹配时怎样补 NULL。这正是高级连接要解决的问题。