外观
06 - WHERE、HAVING 和 ON 放错位置,结果为什么会变
摘要与阅读定位
“保留所有商品,统计九月销量”包含两个要求:没有九月订单的商品也要显示,九月以外的明细不能计入销量。同一个时间条件放在 WHERE 或 ON 中,会作用于不同的对象。本文用两种错误查询和两种正确写法,说明怎样选择条件位置,并判断改写是否保持结果一致。
- 前置:05 连接粒度、10 三值逻辑;后续:07 聚合与窗口。
- 基线:MySQL 8.0.16+ / 8.4、InnoDB。第 6 节的分组合法性讨论以启用
ONLY_FULL_GROUP_BY为前提,这是 MySQL 检查分组查询是否合法的 SQL 模式。初始化、证据和清理遵循 A;下列输出均为根据示例数据推导的预期结果。
初次先读两表对照,再完成第 1—4 节的销量案例。第 5—6 节解释分组过滤,第 7 节下推与执行计划是可选进阶。无需先理解所有优化器术语才能判断结果。
先分清:匹配条件、行条件和组条件
JOIN 按条件组合不同表的记录,ON 定义本级连接的匹配条件。INNER JOIN(内连接)只输出匹配组合;LEFT JOIN(左外连接)还保留没有匹配的左侧记录,并用 NULL 填充右侧列。NULL 表示缺失或未知,不等于数值 0。
三个条件位置分别回答:
ON:这条左侧记录与哪些右侧记录匹配?WHERE:连接结果中的这一行是否留下,参与后续分组?HAVING:分组并计算汇总值之后,这一组是否留下?
GROUP BY 按指定字段把行分成组,SUM 等聚合函数再对组内的值计算。可以先按“连接匹配 → 行过滤 → 分组汇总 → 组过滤”分析结果,数据库实际怎样执行留到第 5 节讨论。
例如,要求保留所有商品,只关联单笔数量至少 3 的销售明细。条件放在 ON 中时,数量不足的明细不匹配;某商品若没有任何合格明细,LEFT JOIN 仍输出该商品,并将明细侧列补为 NULL。条件放在 WHERE 中时,筛选的是已经连接好的行,数量不足的行和补 NULL 的行都会被删除。
原因是 SQL 条件有真、假、未知三种结果,这称为三值逻辑。NULL >= 3 得到 UNKNOWN(未知),而 WHERE 只保留条件为真的行。
这解释了两表差别,却不是“右表条件都放 ON”的万能规则。第 3 节会进一步证明:三表关联中最后一级 ON 不能抹掉前一级已经匹配的明细数量。
1. 实验数据必须能击穿错误改写
以下初始化仅在隔离库执行,先提交或回滚其他实验事务。DROP 会删除已有教学表,只用于本篇的 predicate_* 表。商品表每行是一个商品,订单表每行是一笔订单,明细表每行记录某笔订单购买某商品的数量;order_id 和 product_id 分别用于关联订单与商品。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
DROP TABLE IF EXISTS predicate_item;
DROP TABLE IF EXISTS predicate_order;
DROP TABLE IF EXISTS predicate_product;
CREATE TABLE predicate_product (
id BIGINT PRIMARY KEY,
name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE predicate_order (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATETIME(6) NULL,
KEY idx_predicate_created (created_at)
) ENGINE=InnoDB;
CREATE TABLE predicate_item (
id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
KEY idx_predicate_product_order (product_id,order_id)
) ENGINE=InnoDB;
INSERT INTO predicate_product VALUES
(1,'Keyboard'),(2,'Mouse'),(3,'Monitor'),(4,'Cable');
INSERT INTO predicate_order VALUES
(101,10,'PAID','2026-09-01 00:00:00'),
(102,10,'PAID','2026-09-30 23:59:59.999999'),
(103,20,'CREATED','2026-08-31 23:59:59.999999'),
(104,20,'PAID','2026-10-01 00:00:00'),
(105,30,'CREATED',NULL);
INSERT INTO predicate_item VALUES
(1,101,1,2),(2,102,1,3),(3,103,1,7),
(4,104,2,11),(5,105,4,13);本文将“九月销量”定义为所有订单状态下,2026 年九月下单的商品数量,不额外要求状态为 PAID(已支付)。商品 1 的九月数量为 2 + 3 = 5;商品 2 的订单在十月,商品 3 没有明细,商品 4 的订单日期未知,因此后三者都显示 0。目标结果为商品 1→5、2→0、3→0、4→0。
时间条件包含 9 月 1 日,不包含 10 月 1 日,这称为半开区间。样本覆盖起点、终点前一微秒、月外订单、无明细商品和未知日期。月外明细尤其重要:如果只准备九月明细和完全没有明细的商品,就无法暴露第 3 节的错误求和。
初次先用两张表检验条件位置,逐条运行:
sql
SELECT product.id, item.id AS item_id, item.quantity
FROM predicate_product AS product
LEFT JOIN predicate_item AS item
ON item.product_id = product.id AND item.quantity >= 3
ORDER BY product.id, item.id;推导预期五行:(1,2,3)、(1,3,7)、(2,4,11)、(3,NULL,NULL)、(4,5,13)。再把数量条件移到 WHERE:
sql
SELECT product.id, item.id AS item_id, item.quantity
FROM predicate_product AS product
LEFT JOIN predicate_item AS item ON item.product_id = product.id
WHERE item.quantity >= 3
ORDER BY product.id, item.id;后者只剩四行,商品 3 消失。这只是数量匹配对照,还没有过滤九月订单,不能当作销量结果。接着进入完整三表需求。
2. 错法一:WHERE 丢掉未匹配商品
sql
SELECT product.id,COALESCE(SUM(item.quantity),0) AS qty
FROM predicate_product AS product
LEFT JOIN predicate_item AS item ON item.product_id=product.id
LEFT JOIN predicate_order AS orders ON orders.id=item.order_id
WHERE orders.created_at>='2026-09-01'
AND orders.created_at<'2026-10-01'
GROUP BY product.id
ORDER BY product.id;预期只返回 (1,5)。商品 2 的十月订单不满足时间条件,商品 3 的补空日期和商品 4 的未知日期则使条件得到 UNKNOWN。WHERE 将这些行全部删除,后续分组中便没有商品 2、3、4。
SUM(item.quantity) 对组内数量求和;COALESCE(x,0) 返回 x 与 0 中第一个非 NULL 的值,把空汇总显示为 0。但它只能处理已有结果行,不能恢复在 WHERE 阶段被删掉的商品。
3. 错法二:ON 留住商品,却仍累计月外明细
sql
SELECT product.id,COALESCE(SUM(item.quantity),0) AS qty
FROM predicate_product AS product
LEFT JOIN predicate_item AS item ON item.product_id=product.id
LEFT JOIN predicate_order AS orders
ON orders.id=item.order_id
AND orders.created_at>='2026-09-01'
AND orders.created_at<'2026-10-01'
GROUP BY product.id
ORDER BY product.id;预期 (1,12),(2,11),(3,0),(4,13),不是正确结果。第一次连接已经把商品与明细组合起来,第二次连接再寻找九月订单。时间条件只控制 orders 是否匹配;不满足条件时,仅 orders 侧补 NULL,item.quantity 仍保留。SUM(item.quantity) 因而继续累计月外和日期未知的明细,商品 1 得到 2 + 3 + 7 = 12。
sql
SELECT product.id AS product_id,item.id AS item_id,item.quantity,
orders.id AS matched_order_id
FROM predicate_product AS product
LEFT JOIN predicate_item AS item ON item.product_id=product.id
LEFT JOIN predicate_order AS orders
ON orders.id=item.order_id
AND orders.created_at>='2026-09-01'
AND orders.created_at<'2026-10-01'
ORDER BY product.id,item.id;预期商品 1 的 item 3 仍有 quantity=7,但 matched_order_id=NULL;商品 2、4 同样保留 11、13。排障必须看聚合输入来自哪一级连接,不能只看商品是否出现。
4. 正例:先限制有效明细,再外连接商品
sql
SELECT product.id,COALESCE(sales.qty,0) AS qty
FROM predicate_product AS product
LEFT JOIN (
SELECT item.product_id,SUM(item.quantity) AS qty
FROM predicate_item AS item
JOIN predicate_order AS orders ON orders.id=item.order_id
WHERE orders.created_at>='2026-09-01'
AND orders.created_at<'2026-10-01'
GROUP BY item.product_id
) AS sales ON sales.product_id=product.id
ORDER BY product.id;这里 FROM 中的子查询结果称为派生表,可以作为新的输入参与连接。子查询先用内连接找到明细对应的订单,再排除月外及未知日期,最后按商品汇总。这样 sales 中每个商品至多一行,且数量只来自九月订单。
外层再将所有商品与 sales 左连接:没有九月明细的商品仍被保留,sales.qty 补为 NULL,由 COALESCE 显示为 0。预期四行 (1,5),(2,0),(3,0),(4,0)。
另一个语义正确的方案是只累计匹配到订单的明细:
sql
SELECT product.id,
SUM(CASE WHEN orders.id IS NOT NULL THEN item.quantity ELSE 0 END) AS qty
FROM predicate_product AS product
LEFT JOIN predicate_item AS item ON item.product_id=product.id
LEFT JOIN predicate_order AS orders
ON orders.id=item.order_id
AND orders.created_at>='2026-09-01'
AND orders.created_at<'2026-10-01'
GROUP BY product.id
ORDER BY product.id;预期结果相同。CASE 根据条件选择值:匹配到九月订单时取明细数量,否则取 0,再交给 SUM 求和。这里用 orders.id IS NOT NULL 判断匹配,因为主键不会为 NULL;一般不应凭可空日期或金额判断记录是否存在。
这个方案仍让月外明细参与连接,只是在求和时将其贡献置为 0。两种方案哪个更快,需要结合真实数据分布和执行计划比较。示例结论以 orders.id 唯一、数量非 NULL 为前提;若再连支付流水,同一明细可能被多个匹配组合重复累计,需要重新审查 05 的乘法基数。
5. 原理:逻辑阶段不等于物理时间表
text
FROM / JOIN 匹配与每一级外连接补 NULL
→ WHERE 过滤行
→ GROUP BY / 聚合
→ HAVING 过滤组
→ 窗口计算
→ SELECT 输出 / DISTINCT
→ ORDER BY
→ LIMIT / OFFSET逻辑阶段用于解释哪些数据应当参与计算;物理计划则是数据库实际采用的扫描、连接和计算方式。上面的顺序是语义模型,并不要求数据库把每一步的完整结果都生成并保存。
窗口函数在已有行上计算附加结果,不像分组那样把多行合并为一组。它看到的是 WHERE、分组和 HAVING 处理后的行;要按窗口结果过滤,需要在外层查询中进行,见 07。MySQL 的 HAVING 可以引用部分 SELECT 别名,这是数据库判断名称指向哪个表达式的规则,不能据此推断 SELECT 在物理上先执行。
| 条件位置 | 过滤对象 | 不能随意移动的原因 |
|---|---|---|
| ON | 本级连接的候选匹配 | 外连接仍可能补行;不能清除早先已匹配的列 |
| WHERE | 分组前的结果行 | 拒绝 UNKNOWN,可能删除外连接补行 |
| HAVING | 分组后的组 | 聚合依赖哪些行已经进入组 |
谓词就是条件表达式,例如 item.quantity >= 3。对于同一查询中的内连接,普通行条件若具有确定性,即相同输入下结果相同,通常可以在 ON 与 WHERE 间等价表达。但涉及外连接、聚合、窗口、LIMIT、非确定函数或副作用时,需要逐项分析,不能机械搬移。
6. WHERE 与 HAVING:保持粒度再谈等价
sql
SELECT customer_id,COUNT(*) AS paid_orders
FROM predicate_order
WHERE status='PAID'
AND created_at>='2026-09-01' AND created_at<'2026-10-01'
GROUP BY customer_id
HAVING COUNT(*)>=2;预期 (10,2)。WHERE 先选择九月已支付订单,GROUP BY customer_id 再按客户分组,COUNT(*) 统计每组订单数,最后 HAVING COUNT(*) >= 2 只留下至少有两笔订单的客户。
不能把这个 COUNT(*) 条件直接写入同级 WHERE:行过滤时,组内订单数尚未形成。也不能把 status='PAID' 直接移到 HAVING,同时仍只按客户分组。在启用 ONLY_FULL_GROUP_BY 时,未聚合的 status 需要是分组项,或满足数据库可证明的函数依赖等合法性条件。函数依赖指分组键能唯一确定另一个字段的值;同一客户可能有不同状态的订单,不能假定客户号决定状态。
粒度指结果中一行代表什么。下面两条查询都按“客户与状态组合”分组,才有相同粒度可供比较:
sql
SELECT customer_id,status,COUNT(*) AS orders_count
FROM predicate_order
GROUP BY customer_id,status
HAVING status='PAID'
ORDER BY customer_id;
SELECT customer_id,status,COUNT(*) AS orders_count
FROM predicate_order
WHERE status='PAID'
GROUP BY customer_id,status
ORDER BY customer_id;预期都是 (10,'PAID',2),(20,'PAID',1)。同一组内的 status 相同:在分组后删除非 PAID 组,与分组前删除所有非 PAID 行,对保留组的计数没有影响,因此这里可以提前过滤。这两条查询没有月份条件。
如果为了使用 HAVING status='PAID' 而给原本按客户分组的查询增加 status 分组项,就改变了统计单位。判断等价时必须保持分组粒度和统计口径,不能只比较行数或总和。
7. 时间边界、索引和谓词下推
这一节是可选进阶。先确认第 4 节结果正确,再讨论数据库怎样更早减少输入,不需要通过移动条件来猜优化器行为。
半开区间包含本期起点、不包含下一期起点,不必猜测九月最后一刻应精确到秒、毫秒还是微秒。相邻月份也可以使用同一个边界,不会重复计入。
DATETIME 不自动代表 UTC(协调世界时):本例约定所有日期都按同一业务时区的本地日历时间存储。TIMESTAMP 的输入和显示受会话时区影响;如果数据存 UTC,而报表按某地区的日历月份统计,应先将该地区的起止边界转换为 UTC,再查询这个区间。涉及夏令时切换时,一个本地日历日不一定是 24 小时。
DATE(created_at)=某日 不等于“一定不用索引”;普通列索引通常更容易被直接范围条件利用,表达式/生成列索引及版本优化可能改变计划。使用 13的访问路径与成本模型判断,不背绝对结论。
谓词下推是在保持结果等价的前提下,把过滤放到更早的执行位置,以减少后续处理的输入。MySQL 8.0 的派生条件下推能力随小版本演进,是否能穿过派生表或聚合应看计划;不能从“优化器会下推”反推开发者可以任意搬移 ON、WHERE、HAVING。
在分布式场景,还要区分数据所在节点先过滤,还是将数据传给负责汇总结果的协调节点后再过滤;这会影响传输量。具体哪些字段和算子支持下推,应按目标产品版本确认。
sql
EXPLAIN FORMAT=TREE
SELECT product.id,COALESCE(sales.qty,0) AS qty
FROM predicate_product AS product
LEFT JOIN (
SELECT item.product_id,SUM(item.quantity) AS qty
FROM predicate_item AS item
JOIN predicate_order AS orders ON orders.id=item.order_id
WHERE orders.created_at>='2026-09-01' AND orders.created_at<'2026-10-01'
GROUP BY item.product_id
) AS sales ON sales.product_id=product.id;EXPLAIN FORMAT=TREE 用树形结构展示执行计划。阅读时记录过滤位于哪里、派生表怎样处理,以及估算基数(预计处理或输出的行数);再按 14的方法核对实际输入量。这里的数据很少,不能要求优化器一定选择时间索引。
8. 验收与清理
改写前后按商品 ID 对比整个结果,不只比较总和或行数;必须覆盖无匹配、多匹配、月外明细、NULL 日期、上下界。预期正确查询返回四行,总销量 5;错法二返回四行但总销量 36,正好说明“行数正确”不足以验收。
结果缺行时,先检查 WHERE 是否删除了补 NULL 的行;数量多算时,先输出每级连接的主键和数量,查清哪些明细仍在参与求和;结果正确但查询慢时,再检查实际扫描量和过滤位置。
清理前结束实验事务,只在隔离库删除 predicate_item、predicate_order、predicate_product,不要删除公共实验库。后续可用 07继续学习如何将正确的输入用于报表。
9. 名词解释
| 名词 | 含义 |
|---|---|
| ON / WHERE / HAVING | 匹配条件 / 分组前行条件 / 分组后组条件 |
| 派生表 | FROM 中由子查询产生的输入,可以先过滤和汇总再参与关联 |
| 谓词 | 一个条件表达式,例如数量大于 0 |
| 谓词下推 | 在结果等价的前提下,把过滤放到更早的执行位置 |
| 半开时间区间 | 包含开始时间、不包含下一期开始,避免猜最后一刻的精度 |
| 等价改写 | 在声明的约束和边界下结果一致,不只是总行数或总和一致 |
| 逻辑阶段 / 物理计划 | 解释 SQL 结果的语义顺序 / 数据库实际采用的执行任务 |
参考资料
- MySQL 8.4 Reference Manual:§14.20.2 Window Function Concepts and Syntax、§10.2.1.10 Outer Join Simplification、§10.2.2.5 Derived Condition Pushdown Optimization。
- 适用条件:SQL 语义决定结果,执行计划决定处理方式;具体派生条件下推能力和跨产品执行字段应以目标版本文档及计划为准。