查询基础（SELECT / JOIN / 聚合 / 排序） · SELECT 与 WHERE

NULL 在 WHERE 子句中的特殊行为？为什么 col = NULL 不能用，必须用 IS NULL？

The special behavior of NULL in a WHERE clause? Why doesn't `col = NULL`

答案语言

考察要点

这道题面试官想看你是否深刻理解 SQL 的三值逻辑（Three-Valued Logic, 3VL），以及 NULL 并非一个“值”而是一个“标记”的本质。这直接决定了你在 WHERE 子句、JOIN 条件和聚合函数中能否写出逻辑严谨、没有隐藏 bug 的 SQL。

核心概念

SQL 的逻辑判断和大多数编程语言的二值逻辑（True/False）不同，它引入了第三个状态：UNKNOWN。NULL 是导致 UNKNOWN 状态产生的根源。

NULL 的本质 NULL 代表“未知”、“不适用”或“缺失的信息”，它不是一个具体的值。因此，NULL 不等于任何东西，甚至不等于它自己。
- 5 = NULL 的结果是 UNKNOWN
- 'hello' <> NULL 的结果是 UNKNOWN
- NULL = NULL 的结果也是 UNKNOWN
WHERE 子句的工作原理 WHERE 子句只返回使其条件判断结果为 TRUE 的行。结果为 FALSE 或 UNKNOWN 的行都会被过滤掉。因为 col = NULL 的结果永远是 UNKNOWN，所以 WHERE col = NULL 这条语句永远不会返回任何数据。
专用判断谓词：IS NULL 和 IS NOT NULL 为了处理对 NULL 的判断，SQL 提供了专门的谓词：
- col IS NULL：如果 col 是 NULL，结果为 TRUE；否则为 FALSE。
- col IS NOT NULL：如果 col 不是 NULL，结果为 TRUE；否则为 FALSE。
这是在 WHERE 子句中正确筛选 NULL 记录的唯一标准方式。

数据示例 假设我们有一张 employees 表：

id	name	manager_id
1	Alice	10
2	Bob	NULL
3	Carol	10

错误查询：

sql

 1SELECT * FROM employees WHERE manager_id = NULL;
 2-- 结果：返回 0 行。因为 manager_id = NULL 的结果是 UNKNOWN。

正确查询：

sql

 1SELECT * FROM employees WHERE manager_id IS NULL;
 2-- 结果：返回 id=2 的 Bob 这一行。

另一个陷阱：查询所有有经理的员工

sql

 1-- 错误方式
 2SELECT * FROM employees WHERE manager_id <> NULL;
 3-- 结果：返回 0 行。因为 manager_id <> NULL 的结果也是 UNKNOWN。
 4
 5-- 正确方式
 6SELECT * FROM employees WHERE manager_id IS NOT NULL;
 7-- 结果：返回 id=1 和 id=3 的行。

MySQL 特有的 NULL-safe 等价运算符：<=> MySQL 提供了一个特殊的运算符 <=>（NULL-safe equal to operator），它在行为上类似 =，但视 NULL <=> NULL 为 TRUE，并且 col <=> NULL 在 col 不为 NULL 时为 FALSE。
sql
```
 1-- 使用 <=>
 2SELECT * FROM employees WHERE manager_id <=> NULL;
 3-- 结果：返回 id=2 的 Bob 这一行，等价于 IS NULL。
```
这个操作符在标准 SQL 中对应 IS NOT DISTINCT FROM。虽然方便，但 <=> 并非所有数据库都支持，可移植性较差。

常见低分回答

直接使用 col = NULL 或 col != NULL：这是最基础也是最致命的错误，直接表明候选人对 SQL 三值逻辑缺乏基本认识。
混淆 NULL 与空字符串 '' 或数字 0：回答“NULL 就是空字符串”，这是概念性错误。NULL 是未知，'' 是一个已知的、长度为零的字符串。col = '' 或 col = 0 是合法的、结果为 TRUE/FALSE 的判断。
认为 NOT (col = 'some_value') 可以匹配出 NULL 值：这是一个逻辑陷阱。
- 假设 col 为 NULL，则 col = 'some_value' 结果是 UNKNOWN。
- NOT (UNKNOWN) 的结果依然是 UNKNOWN。
- 因此 WHERE NOT (col = 'some_value') 同样会把 NULL 记录过滤掉。
- 正确的写法是 WHERE col <> 'some_value' OR col IS NULL。
在 COUNT 中误用 NULL：认为 COUNT(col) 和 COUNT(*) 结果总是一样。实际上 COUNT(col) 会忽略 col 为 NULL 的行，而 COUNT(*) 或 COUNT(1) 会统计所有行。
在 NOT IN 子查询中忽略 NULL：写出 SELECT ... FROM t1 WHERE id NOT IN (SELECT id FROM t2)，但没有意识到如果 t2 的 id 列中包含任何 NULL 值，整个查询将永远不返回任何结果。这是因为 NOT IN 内部展开为一系列 AND col <> value 的组合，只要其中一个 value 是 NULL，col <> NULL 的 UNKNOWN 结果就会毁掉整个逻辑链。

工程实践与优化

索引对 NULL 的处理：
- 在现代存储引擎（如 InnoDB）中，B+Tree 索引会存储 NULL 值的条目。
- 因此，WHERE col IS NULL 是可以有效利用 col 上的索引的，执行计划中 type 可能是 ref，key 为对应索引，Extra 中显示 Using where。
- WHERE col IS NOT NULL 的查询，如果 col 列的 NULL 值比例很低（大部分值都非 NULL），优化器可能会认为走索引扫描大部分数据的成本高于全表扫描，从而放弃使用索引。
避免在 WHERE 子句中对列使用函数：
- 反例：WHERE IFNULL(col, 'default') = 'some_value'
- 这会导致索引失效，因为优化器无法直接利用 col 上的索引。
- 正解：改写为 SARGable (Search Argument Able) 的形式：WHERE (col = 'some_value' OR col IS NULL)。这样可以分别利用索引进行两次 range scan 或 ref 查询，然后合并结果，性能远高于全表扫描。
数据库 Schema 设计：
- 在设计表结构时，应尽可能为列设置 NOT NULL 约束，除非业务逻辑明确要求允许“未知”或“不适用”的状态。
- 这不仅能保证数据完整性，还能从根本上避免 NULL 带来的逻辑复杂性和潜在的查询错误。
执行计划分析：当遇到与 NULL 相关的性能问题时，务必使用 EXPLAIN 查看执行计划。关注 type, key, rows, Extra 等字段，确认是否如预期般使用了索引。对于 MySQL 8.0+，可以使用 EXPLAIN ANALYZE 获取更详细的执行信息。

常见追问与延伸题目

追问：NULL 在 JOIN 条件中是如何表现的？比如 ON t1.id = t2.id？
- 回答要点：JOIN 的 ON 子句行为与 WHERE 子句类似，遵循三值逻辑。如果 t1.id 或 t2.id 中有 NULL，那么 t1.id = t2.id 的判断结果是 UNKNOWN。因此，在 INNER JOIN 中，id 为 NULL 的行是永远不会互相匹配上的。这也是一个常见的数据丢失原因。
- 对于 LEFT JOIN，如果左表的 id 无法在右表中找到匹配（包括右表 id 为 NULL 的情况），则右表的所有列在结果集中都会显示为 NULL。
追问：WHERE col NOT IN (1, 2, NULL) 这个查询会返回什么？为什么？
- 回答要点：这个查询永远不会返回任何行。
- col NOT IN (1, 2, NULL) 在逻辑上等价于 col <> 1 AND col <> 2 AND col <> NULL。
- 对于任何 col 的值（包括 NULL 本身），col <> NULL 这个表达式的结果永远是 UNKNOWN。
- 在 AND 逻辑链中，TRUE AND TRUE AND UNKNOWN 的最终结果是 UNKNOWN。
- 由于 WHERE 子句只保留结果为 TRUE 的行，所以没有任何行会被返回。这是一个非常经典的 NOT IN 陷阱。
追问：AVG() 函数如何处理 NULL 值？这对计算结果有什么影响？
- 回答要点：所有标准的聚合函数（SUM, AVG, MAX, MIN, STDDEV 等），除了 COUNT(*) 和 GROUPING，在计算前都会忽略 NULL 值。
- AVG(col) 的计算公式是 SUM(col) / COUNT(col)，而不是 SUM(col) / COUNT(*)。
- 举例：对于数据集 {1, 2, 3, NULL}，AVG() 的结果是 (1+2+3) / 3 = 2，而不是 (1+2+3) / 4 = 1.5。这在统计平均值时非常重要，面试官想确认你是否清楚这个区别，因为它会直接影响业务报表的准确性。