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