PostgreSQL 中的 NULL 陷阱：从一次排除过滤说起
SQL 中的 NULL 陷阱：从一次排除过滤说起
一、背景：看似简单的需求
在一次数据集成任务中，遇到了这样一个业务过滤需求：
当销售区域为"拉美（LA）"时，需要排除
region = 'BR'并且order_status = 'CANCELED'的订单数据。但有一个特别要求：如果这两个字段中任意一个为 NULL，该行数据必须保留。
需求很清晰，但实际写 SQL 时，才发现这里面藏着一个很经典的坑——SQL 的 NULL 三值逻辑。
二、最初的写法及其隐患
直觉上，"排除某个组合"会写成：
ANDNOT(region='BR'ANDorder_status='CANCELED')这段 SQL在大多数场景下跑起来是"正确"的，但它实际上依赖了 SQL NULL 三值逻辑的一个"副作用"来保留 NULL 行——这是隐式的，而非明确表达的意图，属于代码意图不清晰的隐患。
三、SQL 的三值逻辑（Three-Valued Logic）
这是理解 NULL 问题的基础。
和编程语言中的布尔两值逻辑（true/false）不同，SQL 采用的是三值逻辑：
| 值 | 含义 |
|---|---|
TRUE | 条件成立 |
FALSE | 条件不成立 |
UNKNOWN | 不确定（NULL 参与运算的结果） |
核心规则：WHERE 子句只保留结果为TRUE的行，UNKNOWN和FALSE都会被过滤。
NULL 参与任何比较运算，结果几乎都是UNKNOWN：
NULL='CANCELED'→ UNKNOWNNULL!='CANCELED'→ UNKNOWNNULLANDTRUE→ UNKNOWNNOTNULL→ UNKNOWN四、NOT (A AND B)遇到 NULL 时的完整分析
还原本文场景，逐行分析：
ANDNOT(region='BR'ANDorder_status='CANCELED')| region | order_status | A='BR' | B='CANCELED' | A AND B | NOT(A AND B) | WHERE 结果 |
|---|---|---|---|---|---|---|
'BR' | 'CANCELED' | TRUE | TRUE | TRUE | FALSE | ❌ 被排除 |
'BR' | 'OTHER' | TRUE | FALSE | FALSE | TRUE | ✅ 保留 |
'US' | 'CANCELED' | FALSE | TRUE | FALSE | TRUE | ✅ 保留 |
NULL | 'CANCELED' | UNKNOWN | TRUE | UNKNOWN | UNKNOWN | ⚠️ 被过滤！ |
'BR' | NULL | TRUE | UNKNOWN | UNKNOWN | UNKNOWN | ⚠️ 被过滤！ |
NULL | NULL | UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN | ⚠️ 被过滤！ |
⚠️结论：
NOT (A AND B)无法保留 NULL！NULL 行因结果是 UNKNOWN 而被悄悄过滤。
五、为什么"跑起来没报错"就以为是对的？
这正是最危险的地方。
如果在数据质量较好的表中，region字段实际上从不出现 NULL（比如它来自一个外键关联，能关联上的必然有值），那这段 SQL 跑起来结果看上去完全正确。
但一旦：
- 上游数据质量下降，出现 NULL
- 表结构调整，字段变为可空
- 换了一张数据较"脏"的表
原本"正确"的 SQL 就会悄无声息地少数据，排查起来极其困难。
六、正确写法：显式声明 NULL 保留
AND(regionISNULLORregion!='BR'ORorder_statusISNULLORorder_status!='CANCELED')逻辑含义：满足以下任意一个条件就保留这行数据：
region是 NULLregion不等于'BR'order_status是 NULLorder_status不等于'CANCELED'
唯一被排除的，是同时满足：region = 'BR'且order_status = 'CANCELED'（且两者都不为 NULL）。
七、三种写法对比
| 写法 | region=NULL时 | order_status=NULL时 | 意图清晰度 | 推荐 |
|---|---|---|---|---|
NOT (A AND B) | ⚠️ 隐式过滤 | ⚠️ 隐式过滤 | ❌ 差 | ❌ |
A IS NULL OR A!='BR' OR B IS NULL OR B!='CANCELED' | ✅ 明确保留 | ✅ 明确保留 | ✅ 好 | ✅ |
NOT (A='BR' AND B='CANCELED' AND A IS NOT NULL AND B IS NOT NULL) | ✅ 明确保留 | ✅ 明确保留 | 一般 | 可接受 |
八、延伸：其他高频 NULL 陷阱
陷阱 1：!=不等于不能过滤 NULL
-- ❌ 错误：order_status 是 NULL 的行也会被过滤掉WHEREorder_status!='CANCELED'-- ✅ 正确：明确保留 NULLWHEREorder_status!='CANCELED'ORorder_statusISNULL陷阱 2：NOT IN遇到子查询有 NULL，全部结果为空
-- ❌ 危险：子查询结果中有一个 NULL，整个查询返回空！WHEREorder_idNOTIN(SELECTorder_idFROMblacklist_orders)-- ✅ 安全写法：过滤子查询中的 NULLWHEREorder_idNOTIN(SELECTorder_idFROMblacklist_ordersWHEREorder_idISNOTNULL)-- ✅ 更推荐：用 NOT EXISTS，天然不受 NULL 影响WHERENOTEXISTS(SELECT1FROMblacklist_ordersWHEREblacklist_orders.order_id=t.order_id)陷阱 3：聚合函数中的 NULL
COUNT(*)-- 统计所有行，NULL 也计入COUNT(order_status)-- 忽略 NULL 行，两者结果可能不同！SUM(amount)-- NULL 行被忽略，不是当 0 处理AVG(amount)-- 分母只统计非 NULL 行，结果可能偏高九、快速验证字段是否有 NULL
在写过滤条件之前，先查一下字段的 NULL 情况，是一个好习惯：
-- 检查字段 NULL 数量SELECTCOUNT(*)AStotal,COUNT(region)ASregion_not_null,COUNT(*)-COUNT(region)ASregion_null_count,COUNT(order_status)ASstatus_not_null,COUNT(*)-COUNT(order_status)ASstatus_null_countFROMordersWHEREgeo='LA';十、总结：黄金法则
凡是业务上需要"保留 NULL"或"排除 NULL"的场景，必须用
IS NULL/IS NOT NULL显式处理，绝不能依赖三值逻辑的副作用。
记住这三句话：
✅ 显式优于隐式 —— 意图要写清楚，不要靠"副作用" ✅ 先查 NULL 分布 —— 动手写条件前，先确认字段是否可空 ✅ UNKNOWN ≠ FALSE —— NULL 参与运算结果是 UNKNOWN，WHERE 会过滤它附：本文最终落地的 SQL 写法（MyBatis XML）
<!-- 只在 geo = LA 时追加此过滤条件 --><iftest="geo == 'LA'">AND (region IS NULL OR region != 'BR' OR order_status IS NULL OR order_status != 'CANCELED')</if>读法：明确排除"region 确实等于 BR 且 order_status 确实等于 CANCELED"的行，其余所有行（包括任意字段为 NULL 的行）一律保留。意图清晰，无歧义，无副作用依赖。
番外：用 COALESCE 能解决吗？ 能，比如这样：
<iftest="geo == 'LA'">andnot(COALESCE(region_cd,'')='BR'ANDCOALESCE(order_status,'')='CREDIT NOTE')</if>为什么本文没有选择 COALESCE？
虽然COALESCE可行，但在本场景中有几个明显缺点：
❌ 缺点 1：占位符存在歧义风险
❌ 缺点 2：索引失效，影响查询性能
❌ 缺点 3：可读性可能不太好
反正合适就好吧！