我以前整理SQL学习清单时把“没有下过订单的客户”记成了一个很直观的办法客户ID不在订单的客户ID集合里就选出来。这个入口没有问题但它省略了一个条件订单表里的客户ID能不能为NULL下面只用三名客户。客户1已经下单客户2、3没有下单。再放入一条没有关联客户的导入记录你会发现原本应该返回2、3的查询突然一行也不返回。我们不把这解释成“SQL坏了”而是跟着每个比较看清它到底回答了什么。本文是从旧学习清单补出的独立实验不是线上事故也不声称复现了某道平台题的全部约束。1. 三名客户两条订单用MySQL测试库里的同一个连接执行下面各段。两张表都是临时表只在当前连接中存在不创建或删除业务表。若当前连接已有同名临时表换一组名字不要直接删除未知表。CREATE TEMPORARY TABLE blog_null_customers ( id INT PRIMARY KEY ); CREATE TEMPORARY TABLE blog_null_orders ( id INT PRIMARY KEY, customer_id INT NULL, memo VARCHAR(32) NULL, INDEX (customer_id) );INSERT INTO blog_null_customers VALUES (1), (2), (3); INSERT INTO blog_null_orders VALUES (10, 1, NULL), (11, NULL, imported);客户ID是否存在客户ID等于它的订单应不应该输出1是订单10否2否是3否是订单11的customer_id未知不意味着它同时属于所有客户。在本实验的业务定义里只把ID相等的订单视作该客户的订单。2. 看起来正确的NOT IN为什么输出为空SELECT c.id FROM blog_null_customers AS c WHERE c.id NOT IN ( SELECT o.customer_id FROM blog_null_orders AS o ) ORDER BY c.id;子查询从订单10取出1从订单11取出NULL得到1、NULL。客户2要“不在里面”就要同时满足与第一项不同也与第二项不同。先把这两个比较分开2 1 AND 2 NULL TRUE AND UNKNOWN - UNKNOWN第一项可以确认2确实不是1。但第二项没有一个已知客户ID可比。问“这个未知ID是不是2”比较所需的信息不够普通不等号不能给出TRUESQL把这个结果记为UNKNOWNMySQL用NULL表示它。NULL不是另一个普通编号也不是与所有编号都不相等的特殊客户。再看AND它要求两项都为TRUE。一项确认不同另一项还未知不能说“两项都已确认不同”所以整体仍是UNKNOWN。WHERE只收下条件确定为TRUE的行客户2就没有被留下。客户1又不一样第一项1 1已经是FALSE不管另一项怎样都不可能“两项都为TRUE”。因此整体是FALSE不是UNKNOWN。把三个客户放在一起核对客户与1不相等与NULL不相等合起来WHERE保留1FALSE未知FALSE否2TRUE未知未知否3TRUE未知未知否这张表也解释了一个容易说过头的结论不是“遇到NULLNOT IN的结果全部变成NULL”。客户1已经确定匹配结果仍是FALSE。在本例的过滤查询中所有行都不满足TRUE才得到空结果。MySQL比较运算文档图只跟客户2这一行走不提前拿另一种写法的结果代替解释。这里不是没执行查询而是执行后没有任何行满足TRUE。3. 第一种修正先说明这个集合不包含NULLSELECT c.id FROM blog_null_customers AS c WHERE c.id NOT IN ( SELECT o.customer_id FROM blog_null_orders AS o WHERE o.customer_id IS NOT NULL ) ORDER BY c.id;现在参与比较的集合只有1输出2、3。这里有两个前提客户id是主键因此不为空业务允许忽略客户ID未知的订单。不是随手加一个IS NOT NULL就能把所有查询都修好。如果未知订单本身就是数据质量问题应另外统计或处理不能靠查询把异常藏起来。实际题目若明确保证订单客户ID非空原NOT IN写法也可以正确先读约束不需要为了展示技巧强行改写。4. 第二种写法直接问有没有匹配订单SELECT c.id FROM blog_null_customers AS c WHERE NOT EXISTS ( SELECT 1 FROM blog_null_orders AS o WHERE o.customer_id c.id ) ORDER BY c.id;对每个客户把相等的订单筛出来有匹配行就不输出没有匹配行才输出。比如检查客户2时订单11产生的比较是NULL 2结果仍是UNKNOWN不是FALSE。子查询的WHERE也只留下TRUE因此这条未知客户的订单不会构成匹配不是把NULL当成了一个确定不同的编号。EXISTS关注的是子查询有没有返回行不是SELECT列表里的值是不是NULL。SELECT 1表达“不需要订单的内容”不是魔法常量。也不要漏掉o.customer_id c.id若完全去掉相关条件只要订单表非空就会让所有客户都被排除。MySQL EXISTS文档5. LEFT JOIN也可以但检查哪一列很重要SELECT c.id FROM blog_null_customers AS c LEFT JOIN blog_null_orders AS o ON o.customer_id c.id WHERE o.id IS NULL ORDER BY c.id;左连接让没有匹配订单的客户也保留下来右侧列补成NULL。订单id是非空主键真实匹配行的id不可能为空因此o.id IS NULL能识别“右侧没有匹配”。不能把判断随便改成下面这样SELECT c.id FROM blog_null_customers AS c LEFT JOIN blog_null_orders AS o ON o.customer_id c.id WHERE o.memo IS NULL ORDER BY c.id;它会错误输出1、2、3。客户1有订单但订单10的备注恰好为空。“没有匹配行”和“匹配行里的某个字段为空”不是同一个事实。这里用o.id是因为它的约束明确。对这个特定等值连接检查o.customer_id IS NULL也能成立左侧id非空匹配成功的右侧customer_id必然等于它但这种推理依赖连接条件不能外推成任何可空列都能当匹配标记。6. 两个边界避免只记一句口诀可以在前面建好表的连接中执行SELECT 1 NOT IN (1, NULL) AS matched_value, 2 NOT IN (1, NULL) AS unmatched_value, NULL NOT IN (1) AS null_left_value, NULL NOT IN ( SELECT customer_id FROM blog_null_orders WHERE 1 0 ) AS null_left_empty_set;本次输出依次为0、NULL、NULL、1。最后一列提醒我们子查询为空与子查询含一行NULL不同不能都叫“里面没有值”。即使左侧是NULL对空子查询的NOT IN也能为TRUE。另外若外层键允许NULL过滤掉子查询NULL的NOT IN与普通等值相关的NOT EXISTS也不总等价右侧非空时前者可能得到未知后者因找不到相等行而为真。本文客户主键非空才避免了这个差异。是否应该输出未知客户必须先定业务语义不能先选语法再补解释。7. 回到原来的“没下单客户”原来的直觉可以保留把已经下单的客户排除。但实现前需要补三个问题被比较的两侧是否可能为NULL未知客户ID的订单在业务上应该如何处理如果用连接判断“没有匹配”被检查的列是否真的能标识匹配真正值得带走的不是“以后不用NOT IN”而是一个排除查询是否正确取决于空值约束和匹配语义不只取决于它看起来像不像集合运算。附录怎样验证这些结论本地验证从这篇正文提取SQL运行隔离MySQL实例只监听本地Unix socket不连业务数据库。客户固定为1、2、3订单客户ID从1、2、3、4、NULL中选枚举长度0到3的有序列表共1525125156组。这些样本包含空表、只有NULL、重复订单、部分客户匹配、全部客户匹配以及不存在的客户4。验证程序用独立的集合判断计算预期收集已知订单客户ID再列出不在集合里的客户。对每组数据过滤NULL的NOT IN、NOT EXISTS、正确LEFT JOIN都必须与预期一致。原NOT IN也被检查但它的预期来自三值语义已经匹配为假没有匹配且存在NULL为未知否则为真。我们不是把错误写法偶尔输出正确当成它已经安全。还做两个故意破坏的对照漏掉NOT EXISTS的相关条件以及把LEFT JOIN的非空主键检查改为可空备注检查。固定样例必须拒绝这两个错误。最后单独检查上一节四个表达式的结果。156组只是这个明确小范围的穷举不是所有类型转换、字符排序规则、多列比较和数据库版本的兼容性证明。没有测性能不给NOT EXISTS或JOIN贴“永远更快”的标签性能要在实际数据、索引和执行计划下另行核对。MySQL子查询优化