ANSI SQL怎样处理空值

在 ANSI SQL 中，**空值（NULL）**的处理有一套统一且严格的定义。理解 NULL 的核心在于：NULL 不是值，而是“未知 / 不存在”的标记。

下面按常见场景系统说明 ANSI SQL 如何处理空值。

一、NULL 的本质（ANSI SQL 定义）

• NULL 表示未知（Unknown）或缺失（Missing）值
• NULL 不等于任何值，包括它自己
• NULL 参与运算时，通常结果仍是 NULL

NULL = NULL        -- 结果为 Unknown（不是 TRUE）
NULL <> NULL       -- 结果也是 Unknown

二、NULL 与比较运算

1️⃣ 普通比较运算符（=、<>、<、> 等）

x = NULL        -- 永远不是 TRUE
x <> NULL       -- 永远不是 TRUE

✅ 正确判断 NULL 的方式：

x IS NULL
x IS NOT NULL

三、NULL 与逻辑运算（三值逻辑）

ANSI SQL 使用 三值逻辑（TRUE / FALSE / UNKNOWN）

✅ WHERE 子句中：

• 只返回 TRUE
• FALSE 和 UNKNOWN 都会被过滤

四、NULL 与算术运算

任何包含 NULL 的算术运算，结果都是 NULL：

1 + NULL     -- NULL
10 * NULL    -- NULL
NULL / 2     -- NULL

五、NULL 与聚合函数（重点）

1️⃣ COUNT

COUNT(*)           -- 统计所有行（包括 NULL）
COUNT(col)         -- 忽略 col 为 NULL 的行

2️⃣ SUM / AVG / MAX / MIN

SUM(col)     -- 忽略 NULL
AVG(col)     -- 忽略 NULL
MAX(col)     -- 忽略 NULL

✅ NULL 不参与聚合计算

六、NULL 与排序（ORDER BY）

ANSI SQL 规定：

• NULL 被视为 最小值
• 升序时 NULL 排在最前
• 降序时 NULL 排在最后

ORDER BY col ASC   -- NULL 在前
ORDER BY col DESC  -- NULL 在后

⚠️ 不同数据库实现可能不同（如 Oracle / PostgreSQL / SQL Server）

七、NULL 与唯一约束

• 允许多个 NULL
• 唯一约束只约束“非 NULL 值”

CREATE TABLE t (
  id INT UNIQUE
);

✅ 可插入多行 id IS NULL

八、NULL 处理函数（ANSI SQL 标准）

1️⃣ COALESCE（推荐）

COALESCE(col, 0)

• 返回第一个非 NULL 值
• ANSI SQL 标准函数

2️⃣ NULLIF

NULLIF(col, 0)

• 如果 col = 0，返回 NULL
• 否则返回 col

九、CASE 表达式与 NULL

CASE
  WHEN col IS NULL THEN '未知'
  ELSE '有值'
END

❌ 错误写法：

CASE
  WHEN col = NULL THEN ...
END

十、NULL 与 IN / NOT IN（易踩坑）

1️⃣ IN

x IN (1, 2, NULL)

• 如果 x = 1 → TRUE
• 如果 x 不在集合 → UNKNOWN

2️⃣ NOT IN + NULL（危险）

x NOT IN (1, 2, NULL)

• 只要列表中有 NULL，结果永远是 UNKNOWN
• WHERE 中会被过滤掉（查不到数据）

✅ 安全写法：

x NOT IN (1, 2)
AND x IS NOT NULL

十一、ANSI SQL 处理 NULL 的关键原则总结

✅ 记住这 6 条就够了：

1. NULL ≠ NULL
2. 用 IS NULL / IS NOT NULL
3. NULL 参与运算结果仍是 NULL
4. WHERE 只认 TRUE
5. 聚合函数忽略 NULL
6. 列表中有 NULL 时，NOT IN 要小心

如果你愿意，我也可以：

• 用 **具体数据库（MySQL / PostgreSQL / Oracle）**对比说明
• 给出 常见 NULL 陷阱 + 正确写法
• 用 示例表 + 查询结果演示