过滤、NULL、CASE 与表达式

本章目录

本章学习目标

用三值逻辑解释 NULL 的过滤行为
正确组合 AND、OR 与日期范围
使用 CASE 和 COALESCE 并区分真实零与未知值

真假之外还有未知

SQL 中 NULL 表示缺失或未知，不是零，也不是空字符串。amount = NULL 不能找出未知金额，因为与未知值进行普通比较仍然未知。WHERE 只保留判断为真的行，假与未知都会被排除。判断空值必须写 IS NULL 或 IS NOT NULL。计算中十加未知仍然未知，因此未经思考把所有空值改成零可能制造错误的总额。

设用户键依次为一、二、NULL，user_id <> 1 只保留二，空键不会自动被视为“不等于一”。若业务目标明确包括未知用户，条件需写 user_id <> 1 OR user_id IS NULL。NOT 并不会把未知变成真。理解这个规则后，外连接补空行被 WHERE 删除、NOT IN 遇到空值失效等现象就不再神秘。

NOT IN 反例与可靠排除

查询没有订单的用户时，user_id NOT IN (SELECT user_id FROM orders) 看起来自然；但课程 orders 含空 user_id，列表因此含 NULL。对于一个既不等于任何已知键的用户，还需要与未知键比较，整体结果变成未知，筛选可能一行都不返回。NOT EXISTS 检查是否存在真正匹配行，没有匹配就为真，不受列表空值污染。

可以在子查询中明确排除空键后使用 NOT IN，但 NOT EXISTS 更直接表达反连接。对于常量列表 NOT IN (1,2) 没有这个列表空值问题，不过左侧自身为空仍返回未知。MySQL 的空值安全比较 <=> 是特定方言能力，日常等值连接不要无意使用它来让两侧未知键匹配；是否让未知值同组必须由业务定义。

组合条件、日期与文本

AND 优先于 OR。要筛选中国用户且渠道为自然流量或推荐，应写 country='CN' AND (channel='organic' OR channel='referral')；缺括号时推荐渠道的其他国家用户也可能进入结果。即使优先级已经明确，括号仍能向审阅者表达业务分组。LIKE 中百分号匹配任意长度，下划线匹配一个字符，不能把它与正则表达式混为一谈。

日期时间月份筛选优先使用左闭右开区间：order_date >= '2026-01-01' AND order_date < '2026-02-01'。BETWEEN 两端包含，若上界只写一月三十一日，隐含午夜，白天订单会遗漏。左闭右开便于相邻区间拼接且不会重叠。给索引列套 DATE 函数再比较通常不如原始范围便于利用普通索引，性能章会继续检查。

CASE 与 COALESCE 的业务解释

CASE WHEN 条件 THEN 值 ELSE 值 END 为每行赋分类。顺序很重要：先判断已退款，再判断已支付，可以避免宽条件把较具体的情况提前截走。没有 ELSE 时未命中会得到 NULL。示例把取消订单净额记为零，只用于这份分析结果；并没有将订单真实金额改成零。分类字段应命名清楚，避免把派生业务口径当成原始事实。

COALESCE 返回第一个非空值，适合外连接后“没有订单”的聚合显示为零，但未知退款不能无条件当成零。课程 refund_amount 使用明确的非空数值，因而 amount-refund_amount 有可解释含义。面试时说明用零填充的依据，比机械地在每个 SUM 外加 COALESCE 更重要。还要注意整除与百分比表达式的类型，必要时使用小数常量和 NULLIF 防止零分母。

从结果到验证

阅读一段查询时，先写下一行结果代表什么，再列出来源表的一行代表什么。把过滤、连接、聚合和排序分别检查，比仅凭输出看起来合理更可靠。对于金额应同时保留订单数量和用户数量，以便发现连接引起的重复累计；对于比率应同时展示分子与分母，以便发现筛选范围不同。结果少并不等于正确，结果多也不等于错误，判断依据是预先确定的业务口径。

先用纸笔构造三个用户、两三个订单的小表，再用数据库运行对应查询。至少包含没有订单的用户、同一用户多笔订单、一个未知值，以及筛选后没有记录的情形。用这些反例解释每个子句造成的变化，再扩大到课程数据。未写 ORDER BY 的结果没有稳定顺序；需要核对输出时总要明确排序。课程默认 MySQL 8.4，开启严格分组规则，不要通过关闭规则掩盖数据粒度问题。

完成练习后，先自行解释结果再查看答案。记录的检查标准应是行数、键是否唯一、是否有空值、金额是否守恒，以及日期边界是否符合定义，而不只是查询没有语法错误。答案中的每个条件都应能追溯到题目口径；如果口径改变，先调整定义再改查询。这样形成的推理过程可以直接用于面试中解释思路和实际工作中复核分析报告。

完整示例与实际输出

SELECT order_id,user_id,
  CASE WHEN status='cancelled' THEN 0 ELSE amount-refund_amount END AS recognized_net,
  CASE WHEN status='refunded' THEN '退款' WHEN status='paid' THEN '支付' ELSE '取消' END AS status_label
FROM orders
WHERE order_date>='2026-01-01' AND order_date<'2026-02-01'
ORDER BY order_id;
SELECT u.user_id FROM users u WHERE NOT EXISTS
  (SELECT 1 FROM orders o WHERE o.user_id=u.user_id) ORDER BY u.user_id;
SELECT 3 NOT IN (1,2,NULL) AS unknown_result,3 NOT IN (1,2) AS true_result;

实际运行通过Actual MySQL 8.4.11 execution; strict grouping mode.

order_id	user_id	recognized_net	status_label
1	1	48.00	支付
2	2	240.00	支付
3	3	12.50	支付
4	4	48.00	支付
5	5	240.00	支付
6	6	12.50	支付
7	7	48.00	支付
8	8	240.00	支付
9	9	0	取消
10	10	48.00	支付
11	11	120.00	退款
12	12	12.50	支付
13	13	48.00	支付
14	14	240.00	支付
15	15	12.50	支付
16	16	48.00	支付
17	17	240.00	支付
18	18	0	取消
19	19	48.00	支付
20	20	240.00	支付
21	21	12.50	支付
22	22	24.00	退款
23	23	240.00	支付
24	24	12.50	支付
25	1	48.00	支付
26	2	240.00	支付
27	3	0	取消
28	4	48.00	支付
29	5	240.00	支付
30	6	12.50	支付
31	7	48.00	支付
32	8	240.00	支付
33	9	6.25	退款
34	10	48.00	支付
35	11	240.00	支付
36	12	0	取消
37	13	48.00	支付
38	14	240.00	支付
39	15	12.50	支付
40	16	48.00	支付
41	17	240.00	支付
42	18	12.50	支付
43	19	48.00	支付
44	20	120.00	退款
45	21	0	取消
46	22	48.00	支付
47	23	240.00	支付
48	24	12.50	支付
49	1	48.00	支付
50	2	240.00	支付
51	3	12.50	支付
52	4	48.00	支付
53	5	240.00	支付
54	6	0	取消
55	7	24.00	退款
56	8	240.00	支付
57	9	12.50	支付
58	10	48.00	支付
59	11	240.00	支付
60	NULL	12.50	支付

user_id
25
26
27
28
29
30

unknown_result	true_result
NULL	1

取第一个为真的分支；应先写具体条件，再写较宽条件。

两项动手任务

任务 1 · 列出 user_id 为空的订单，并将未知用户展示为字符串 unknown。

提示

显示替代与数据修改不同。

完整参考解答与检查方式

SELECT order_id,COALESCE(CAST(user_id AS CHAR),'unknown') AS user_label FROM orders WHERE user_id IS NULL ORDER BY order_id; CAST 使显示类型一致，原键仍为空。

任务 2 · 统计一月已支付订单中净额至少一百的明细行。

提示

状态先限定，再计算净额。

完整参考解答与检查方式

SELECT order_id,amount-refund_amount AS net_amount FROM orders WHERE status IN ('paid','refunded') AND order_date>='2026-01-01' AND order_date<'2026-02-01' AND amount-refund_amount>=100 ORDER BY order_id; 本题是一订单一行，不进行聚合。

官方资料与掌握状态

MySQL NULL

完成两项任务后，按独立完成程度记录。状态会尽量保存在当前浏览器，可导出备份。

动手任务记录

记录两项任务的方法、检查结果和卡点

掌握状态与复习

关掉资料独立完成后记 A；提示后完成记 B；需要重学记 C。