NULL 值处理

导学

NULL 是数据库中最特殊的存在——它表示"未知"或"不存在"，而不是空字符串或 0。对 NULL 的误解是 SQL 新手写出 Bug 的首要原因。本节彻底讲透 NULL 的行为和正确处理方式。

定义

NULL：在 SQL 中表示"未知值"或"缺失值"的特殊标记。它与空字符串 ''、数字 0、布尔 FALSE 完全不同。

演示数据准备

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前 employees 表中的完整数据如下：

NULL 的核心特性

特性一：与任何值的比较结果都是 UNKNOWN

当前数据状态：见上文 employees 表完整数据。

执行语句：

SELECT NULL = NULL;   -- 结果：NULL（不是 TRUE）
SELECT NULL = 0;      -- 结果：NULL
SELECT NULL = '';     -- 结果：NULL
SELECT NULL <> NULL;  -- 结果：NULL

操作后结果：

结果解读：NULL 与任何值（包括 NULL 自身）的比较结果都是 UNKNOWN。这是 SQL 三值逻辑（TRUE、FALSE、UNKNOWN）的核心。

我们用表数据验证：

-- 错误写法：永远返回空结果
SELECT * FROM employees WHERE score = NULL;

操作后结果：空结果集（0 行）。

结果解读：score = NULL 对每一行都返回 UNKNOWN，而 WHERE 只保留 TRUE 的行，因此没有任何行被返回。

正确写法：

SELECT * FROM employees WHERE score IS NULL;

操作后结果：

结果解读：IS NULL 是专门判断 NULL 的运算符，正确返回了 score 为 NULL 的白歌。

特性二：算术运算结果为 NULL

当前数据状态：见上文 employees 表完整数据。

执行语句：

SELECT
    emp_name,
    score,
    score + 10 AS score_plus_10,
    score * 2 AS score_double,
    score / 2 AS score_half
FROM employees;

操作后结果：

结果解读：白歌的 score 为 NULL，因此任何包含 score 的算术运算结果都是 NULL。这不是报错，而是 SQL 的标准行为。

特性三：聚合函数忽略 NULL

当前数据状态：见上文 employees 表完整数据（2 行，其中 1 行 score 为 NULL）。

执行语句：

SELECT COUNT(*) FROM employees;           -- 统计所有行
SELECT COUNT(score) FROM employees;       -- 统计 score 非 NULL 的行
SELECT SUM(score) FROM employees;         -- 忽略 NULL 后求和
SELECT AVG(score) FROM employees;         -- 忽略 NULL 后求平均
SELECT MAX(score) FROM employees;         -- 忽略 NULL 后取最大值
SELECT MIN(score) FROM employees;         -- 忽略 NULL 后取最小值

操作后结果：

结果解读：

• COUNT(*) = 2：统计所有行，不忽略 NULL
• COUNT(score) = 1：只统计 score 非 NULL 的行（大翔 100）
• SUM(score) = 100：忽略 NULL
• AVG(score) = 100.0000：100 / 1 = 100，忽略 NULL 后除以非 NULL 行数
• MAX(score) = 100：忽略 NULL 后的最大值
• MIN(score) = 100：忽略 NULL 后的最小值（唯一非 NULL 值）

易错点：COUNT(列) 和 COUNT(*) 的区别是面试高频考点。COUNT(*) 统计行数，COUNT(列) 统计该列非 NULL 的行数。

SQL 示例

场景一：正确判断 NULL

当前数据状态：见上文 employees 表完整数据。

执行语句（错误）：

-- 错误：永远返回空结果
SELECT * FROM employees WHERE score = NULL;

操作后结果：空结果集（0 行）。

执行语句（正确）：

SELECT * FROM employees WHERE score IS NULL;

操作后结果：

执行语句（正确）：

SELECT * FROM employees WHERE score IS NOT NULL;

操作后结果：

结果解读：IS NULL 和 IS NOT NULL 是判断 NULL 的唯一正确方式。= 和 <> 对 NULL 无效。

场景二：将 NULL 替换为默认值

当前数据状态：见上文 employees 表完整数据。

执行语句（COALESCE）：

-- COALESCE：返回第一个非 NULL 的值
SELECT emp_name, COALESCE(score, 0) AS score_display FROM employees;

操作后结果：

执行语句（IFNULL）：

-- IFNULL：MySQL 特有，两个参数版 COALESCE
SELECT emp_name, IFNULL(score, 0) AS score_display FROM employees;

操作后结果：与 COALESCE 相同。

结果解读：COALESCE(score, 0) 逐个检查参数，返回第一个非 NULL 的值。如果 score 为 NULL，则返回 0。IFNULL 是 MySQL 特有的两参数版本，功能相同。

再看一个结合多列的替换例子：

SELECT
    emp_name,
    COALESCE(dept, '待定') AS dept_display,
    COALESCE(score, 0) AS score_safe
FROM employees;

操作后结果：

结果解读：COALESCE 可以处理多列的 NULL 替换，是处理 NULL 最灵活的方式之一。本例中 dept 列无 NULL，因此全部显示原部门。

场景三：聚合中的 NULL 陷阱

当前数据状态：见上文 employees 表完整数据（2 行，其中 1 行 score 为 NULL：白歌）。

执行语句：

-- employees 表有 2 行，其中 1 行 score 为 NULL
SELECT COUNT(*) FROM employees;      -- 结果：2
SELECT COUNT(score) FROM employees;  -- 结果：1（忽略 NULL）
SELECT AVG(score) FROM employees;    -- 结果：100（100/1，忽略 NULL）
SELECT SUM(score) FROM employees;    -- 结果：100（忽略 NULL）
SELECT AVG(IFNULL(score, 0)) FROM employees;  -- 结果：50.0000（(100+0)/2，NULL 当 0）

操作后结果：

结果解读：

• AVG(score) = 100.0000：忽略 NULL 后除以 1（非 NULL 行数）
• AVG(IFNULL(score, 0)) = 50.0000：(100+0)/2 = 50.0000

关键区别：AVG(score) 忽略 NULL 后除以非 NULL 行数；若希望 NULL 视为 0，需用 AVG(IFNULL(score, 0))。两者的语义完全不同，使用时要根据业务需求选择。

场景四：NOT IN 中的 NULL 陷阱（复习）

当前数据状态：见上文 employees 表完整数据。

执行语句（危险）：

-- 危险：永远返回空结果
SELECT emp_name FROM employees WHERE dept NOT IN ('技术部', NULL);

操作后结果：空结果集（0 行）。

结果解读：dept NOT IN ('技术部', NULL) 等价于 dept <> '技术部' AND dept <> NULL。dept <> NULL 的结果是 UNKNOWN，而 WHERE 只保留 TRUE，因此整个查询返回空结果。

正确写法：

SELECT emp_name FROM employees WHERE dept NOT IN ('技术部');

操作后结果：

结果解读：当前表中所有员工都在技术部，因此返回空结果。确保 NOT IN 列表中不包含 NULL，才能得到正确结果。

场景五：NULL 的排序行为

当前数据状态：见上文 employees 表完整数据，白歌的 score 为 NULL。

执行语句（升序）：

SELECT emp_name, score FROM employees ORDER BY score ASC;

操作后结果：

结果解读：MySQL 5.7 中，NULL 在升序时排在最前面。

执行语句（降序）：

SELECT emp_name, score FROM employees ORDER BY score DESC;

操作后结果：

结果解读：NULL 在降序时排在最后面。

如需自定义 NULL 的位置：

-- NULL 排在最后（升序）
SELECT emp_name, score FROM employees ORDER BY score IS NULL, score ASC;

操作后结果：

结果解读：score IS NULL 返回 1（NULL）或 0（非 NULL），先按此排序，非 NULL（0）在前，NULL（1）在后，再按实际 score 值排序。

常见误区

面试考点

Q：IS NULL 和 = NULL 为什么结果不同？

SQL 采用三值逻辑（TRUE/FALSE/UNKNOWN）。任何值与 NULL 的比较结果都是 UNKNOWN，而 WHERE 只保留 TRUE。IS NULL 是专门判断 NULL 的运算符。

Q：查询某列不为某值的所有行（包括 NULL），怎么写？

WHERE column <> '某值' OR column IS NULL。因为 column <> '某值' 对 NULL 返回 UNKNOWN，不会包含 NULL 行。

Q：COUNT(*)、COUNT(1)、COUNT(列) 的区别？

COUNT(*) 和 COUNT(1) 在 MySQL 5.7 中性能相同，都统计所有行数；COUNT(列) 统计该列非 NULL 的行数。

Q：如何计算"包含 NULL 的平均值"（NULL 视为 0）？

AVG(IFNULL(score, 0)) 或 AVG(COALESCE(score, 0))。注意这与默认 AVG(score) 的语义完全不同。

Q：NULL 和 '' 在排序时谁在前？

''（空字符串）是确定的值，按字符串规则排序；NULL 在升序时默认排在最前面。两者位置不同。

小结

• NULL 表示"未知"，不等于 0、空字符串或 FALSE
• 判断 NULL 只能用 IS NULL / IS NOT NULL
• 聚合函数忽略 NULL，COUNT(列) 与 COUNT(*) 结果可能不同
• 用 COALESCE 或 IFNULL 将 NULL 转为有意义的默认值
• NOT IN 列表中不可包含 NULL，否则结果永远为空
• NULL 参与的算术运算结果恒为 NULL

下一章引子：除了精确匹配，业务中经常需要模糊查找——比如按姓名前缀搜索员工。