1. NULL 值到底是什么先把它从“数字 0”和“空字符串”里拎出来我做了这么多年数据库发现很多人对 NULL 的理解都停留在“NULL 就是没值嘛那不就是 0 或者空字符串吗”。这个理解如果只是日常聊天问题不大但只要你写 SQL 超过三个月早晚会在 NULL 上栽一次跟头。而且栽跟头的方式还特别统一要么是 WHERE 条件查不出数据要么是 COUNT 统计出来的数字跟对不上要么是字符串拼接出来一长串 NULL要么是 JOIN 的时候莫名其妙丢行。先说结论NULL 在 SQL 里不是“数字 0”不是“空字符串 ”也不是“空格 ”它表示的是“未知”UNKNOWN或者“没有值”NO VALUE。你可以把它理解成一个占位符意思是“这个地方什么数据都没有连空字符串都不算”。空字符串是一个确定的值它表示“这个字段的内容是空的”但 NULL 表示“这个字段压根没有被赋值”。这两者之间有本质区别理解了这个区别后面所有关于 NULL 的坑就都能想通了。举个例子。你现在维护一张用户表有一个字段叫“手机号”。如果某个用户的记录里手机号是 空字符串说明系统知道这个用户没有手机号至少“没有手机号”这个信息是确定的。但如果这个字段是 NULL说明系统压根不知道这个用户有没有手机号可能是没采集到可能是数据导入时漏了也可能是当时还没开放这个字段。一个是“确定没有”一个是“不知道有没有”这在业务上完全是两回事。所以你在做数据清洗的时候第一步绝对不是写 UPDATE 语句而是先搞清楚你的业务系统里NULL 和空字符串各自代表什么语义。否则你辛辛苦苦把 NULL 统一成 可能把“未知”这个信息直接抹掉了后续分析就会得出完全错误的结论。再往深处说一点NULL 在 SQL 的三值逻辑里承担着核心角色。普通逻辑判断只有 TRUE 和 FALSE但 SQL 里任何跟 NULL 比较的结果既不是 TRUE 也不是 FALSE而是 UNKNOWN。这一点非常反直觉因为你在写 WHERE 子句的时候会发现“WHERE 字段 NULL”永远查不到数据这其实不是你的 SQL 写错了而是 SQL 的语义设计就是如此。为了让你直观感受一下 NULL 和 0、 的区别我列了张对比表你可以存下来没事翻一翻场景结果说明NULL NULLUNKNOWN不会命中 WHERE两个未知值比不出相等因为两边都是“未知”NULL 0UNKNOWN未知值跟 0 没有可比性NULL UNKNOWN未知值跟空字符串也没有可比性 TRUE两个空字符串是确定的相等0 NULLUNKNOWN同上没有可比性COUNT(*)统计所有行数不关心字段是否为 NULLCOUNT(字段)只统计该字段非 NULL 的行数NULL 不会被计入SUM(字段)忽略 NULL 值但有个坑后面我会细讲这张表值得你仔细看几遍尤其注意NULL NULL这个结果。大多数人第一次写 SQL 时都会踩这个坑以为两个 NULL 应该相等结果用WHERE a b去匹配两张表的 NULL 字段发现怎么都匹配不上。这个问题在处理“关联两张表时匹配空值”的场景里尤其常见比如你拿 A 表的字段去 B 表里找记录两边都有 NULL 值你以为能关联上结果全丢了。2. 三值逻辑为什么 SQL 里会有 TRUE、FALSE、UNKNOWN 三种结果既然聊到 NULL就绕不开三值逻辑。这是理解 NULL 一切特性的一把钥匙。很多初学者总是记不住“为什么WHERE NULL NULL查不出数据”根本原因就是他们把 SQL 的逻辑跟编程语言里的逻辑混为一谈了。在 Java、Python、C 这些语言里条件表达式的求值结果只有两个真或假。但在标准 SQL 里由于 NULL 的存在比较运算的结果多了一个状态叫 UNKNOWN。这个 UNKNOWN 既不是 TRUE 也不是 FALSE它是逻辑上的“不确定”。你可以这么理解现在有一个盒子里面可能装着一个苹果也可能什么都没有反正你没法打开看。有人问你“这个盒子里是苹果吗”你没法回答“是”也没法回答“不是”你只能回答“不知道”。这个“不知道”在 SQL 里就是 UNKNOWN。那 UNKNOWN 在过滤条件里怎么表现呢当一个行的 WHERE 条件计算结果为 UNKNOWN 时这一行不会出现在结果集里。注意它和 FALSE 的结果一样都是“不出现”但语义上完全不同FALSE 是“确定不满足条件”UNKNOWN 是“无法确定是否满足条件”。虽然是同样的结果但如果你要排查数据问题了解这一点能帮你更快定位到根因。举个例子。你现在有两张表一张是“学生表”一张是“考试表”。你想查“所有参加考试的学生里得分大于 60 分的人”。如果某个学生没有参加考试考试表里根本没有他的记录那么这个学生就不会出现在结果里你很容易理解。但如果这个学生参加了考试但成绩字段是 NULL比如老师还没录入那成绩 60这个比较结果就是 UNKNOWN他也不会出现在结果里。这从业务上说得通成绩未知确实不能说他是“大于 60 分”。但麻烦在于很多人写 SQL 时如果不注意会把“成绩为 NULL”和“成绩小于等于 60”混为一谈。你如果写WHERE 成绩 60 OR 成绩 60你直觉上以为能覆盖所有情况但 NULL 的考试成绩既不满足大于 60也不满足小于等于 60UNKNOWN OR UNKNOWN 还是 UNKNOWN他照样被漏掉。这里要特别提醒你一个点如果你需要把 NULL 值单独抓出来处理不要用比较运算符要用IS NULL。这是 SQL 提供的一个专门用来判断 NULL 的语法也是唯一正确的判断方式。再说一个更微妙的场景NOT IN 子查询与 NULL 的配合问题。假设你要查“不在某个列表里的用户”你写的是WHERE id NOT IN (SELECT id FROM 黑名单)。如果黑名单子查询结果里出现了任意一个 NULL 值整个 NOT IN 的结果就会变成 UNKNOWN最终结果是“一行都查不出来”。这个坑非常隐蔽因为你的子查询大部分情况下返回的都是正常 id只有当某些记录恰好 id 为 NULL 时整个查询就废了。我自己在做数据清理的时候遇到过一次排查了很久才意识到是子查询结果里混进了 NULL。这类问题的解法是在子查询里显式加上WHERE id IS NOT NULL。你可以把三值逻辑归纳成下面几条记忆规则TRUE OR UNKNOWN TRUE因为 TRUE 已经能决定结果FALSE OR UNKNOWN UNKNOWN两边都没法确定TRUE AND UNKNOWN UNKNOWNTRUE 还需要 UNKNOWN 配合FALSE AND UNKNOWN FALSEFALSE 已经能决定结果NOT UNKNOWN UNKNOWN取反还是未知这几条规则不用死记你只要理解“UNKNOWN 就是一个不确定的值任何跟它组合的逻辑结果只要无法被另一个值确定最终就是 UNKNOWN”就够了。唯一例外的场景是如果另一个值已经能决定整个表达式的结果比如TRUE OR UNKNOWN里 TRUE 已经让整个结果为真那 UNKNOWN 就不起作用了。3. 聚合函数与 NULL 的纠缠COUNT、SUM、AVG、MAX、MIN 各有各的脾气聚合函数是日常写 SQL 最高频的场景之一但 NULL 在里面引发的坑我敢说十个写 SQL 的人里有八个都踩过。下面一个个说清楚。先讲COUNT。COUNT(*)统计的是“行数”不管这一行的字段是不是 NULL都会数进去。但COUNT(某个字段)统计的是“该字段非 NULL 的行数”NULL 值不会计入。这个区别直接导致你在做统计时会得到不同的结果。举个例子。有一张订单表里面有 10 条订单记录但其中 3 条记录没有填写“优惠券编号”这个字段值为 NULL。你执行SELECT COUNT(*) FROM 订单会得到 10执行SELECT COUNT(优惠券编号) FROM 订单会得到 7。如果你期望的是“有多少订单使用了优惠券”用 COUNT(优惠券编号) 是对的如果你期望的是“总共有多少订单”用 COUNT(*) 才是对的。但如果你不了解这个差异写了个 COUNT(优惠券编号) 就拿到结果自作聪明地当成总单量那报表就会少掉 3 单。再说SUM。SUM(字段)会自动忽略 NULL 值。这个行为的意外之处在于如果一个字段全部为 NULL那 SUM 的结果会是什么是 NULL而不是 0。这个很容易理解但很多人就挂在这一点上。你在一张新表上跑SELECT SUM(金额) FROM 订单 WHERE 状态 已完成如果一张已完成订单都没有你期望结果是 0结果控制台打出来一个大大的 NULL下游报表系统直接就显示成空白。你不得不用COALESCE(SUM(金额), 0)来兜底。很多人问我为什么不建议在数据库层面把 NULL 直接改成 0。一个核心原因是NULL 和 0 在 SUM 里能区分开“没有值”和“值为零”前者会影响平均值计算后者不会。如果你把所有 NULL 都改成 0AVG 计算结果会被拉低因为除数增加了 0 值但实际上这些记录本不该参与计算。说到AVG这个函数是 NULL 陷阱的重灾区。AVG(字段)在计算平均值时同样会忽略 NULL 值。这可能带来业务上的误导。比如某门课有 100 个学生选课其中 20 人缺考成绩为 NULL。你跑SELECT AVG(成绩) FROM 选课表 WHERE 课程数学得到的平均值是“参加考试的 80 人的平均分”而不是“所有选课学生的平均分”。如果你不知道这个逻辑一不小心就会得出一个虚高的平均分。这在很多场景里影响很大正确的做法是先想清楚业务需求是要“实考平均分”还是“全员平均分”如果是后者你就要用COALESCE(成绩, 0)显式地把缺考成绩转换为 0 参与计算。至于MAX和MIN它们同样会忽略 NULL。这通常问题不大因为最大最小值很少会因为存在 NULL 而改变语义。但有一个细节你可能用得上如果你要对某个字段取最大非 NULL 值可以直接用 MAX根本不用额外过滤。这个特点在一些数据校验场景里很好用。还有一个容易踩的坑是GROUP BY对 NULL 的处理。在 MySQL 和大多数数据库里NULL 值会被单独分成一组。也就是说如果你有一批订单部分订单的渠道字段是 NULL那么GROUP BY 渠道的结果里会出现一行“渠道 NULL”的汇总数据。这个不算错误但你要记得在结果呈现时处理一下。比如用COALESCE(渠道, 未知渠道)把它映射成业务可读的标签否则凭空多出一行 NULL 分组会让业务方摸不着头脑。我在实际项目里通常会在统计 SQL 的最后统一做一次 NULL 兜底处理比如SELECT COALESCE(SUM(amount), 0) AS total_amount, COUNT(*) AS order_count, COALESCE(AVG(amount), 0) AS avg_amount FROM orders WHERE status finished;这条 SQL 的意图是如果没有任何已完成订单SUM 返回 NULL用 COALESCE 兜底成 0AVG 同理。但注意这里用COALESCE(AVG(amount), 0)在某些语义下是有争议的。如果 pending 状态下的订单也会有 amount 记录那没问题但如果“没有已完成订单”和“平均金额为 0”是两种不同的业务含义你就得根据实际场景决定兜底值。4. 空字符串、NULL 和字符串拼接的混乱局面每次处理导入数据或者接手老系统的时候最头疼的就是 NULL、空字符串、空格这三个东西混在一起。它们在外表上可能都“看起来是空的”但在 SQL 里完全是三种情况处理方式也完全不同。空字符串 是一个确定的值表示这个字段的内容是空的。空格 是另一个确定的值表示这个字段里有一个空格字符。NULL 则是一个未知值上面已经反复强调过了。在数据清洗中最保险的策略是先用TRIM(字段)去掉两侧空格再用NULLIF(字段, )把空字符串转换为 NULL最后统一用IS NULL判断。NULLIF是很多 SQL 新手不太熟悉但非常好用的函数它的作用很简单如果两个参数相等返回 NULL如果不相等返回第一个参数。所以NULLIF(字段, )的意思就是如果字段为 返回 NULL否则返回原值。这个函数用来统一“空字符串”和“NULL”的差异非常顺手。举一个实际场景。你从外部系统导出一份名单里面的“备注”字段有的为空字符串有的为 NULL还有的是一串空格。你想查找所有没有备注的人如果直接写WHERE 备注 你会发现 NULL 和空格的人查不出来。正确写法是SELECT * FROM 名单 WHERE NULLIF(TRIM(备注), ) IS NULL;这条 SQL 的思路是先把空格去掉然后把空字符串转成 NULL最后统一判断是否为 NULL。这样无论是 NULL、 还是 都能被筛选出来。再来看字符串拼接。在 SQL Server 里直接用字段1 字段2 字段3拼接字符串时只要有一个字段是 NULL整个结果就是 NULL。这个坑很多人踩过。比如你拼一个“姓 名”的字段如果用户没有填写姓氏结果不是“张三”而是 NULL。解决方案是使用CONCAT函数它会把 NULL 当作空字符串处理然后拼接其他部分。不过要注意早期的 SQL Server2012 之前并不支持CONCAT你得用ISNULL(字段, )一个个处理或者用 CASE WHEN。现在用新版 SQL Server 的话直接写SELECT CONCAT(COALESCE(first_name, ), , COALESCE(last_name, )) AS full_name FROM users;这里我把COALESCE也加进去了原因是要兼容不同数据库的拼接规则并且如果你在 MySQL 里用双竖线拼接还需要先开启管线模式。经验法则就是任何涉及字符串拼接的场景只要存在 NULL 的可能要么显式COALESCE要么用安全拼接函数。另外还有一个容易被忽略的点ORDER BY里 NULL 的排序位置。不同数据库对 NULL 的排序规则不一致。在 MySQL 里ORDER BY 字段会把 NULL 排在最前面在 PostgreSQL 和 SQL Server 里默认把 NULL 排最后。如果你对排序顺序有明确要求推荐显式地写ORDER BY 字段 IS NULL, 字段 ASC这样在任何数据库上都能得到可预期的结果可读性也更好。5. JOIN 与 NULL 的暗坑为什么关联条件里 NULL 永远等不上 NULLJOIN 操作中的 NULL 问题非常常见而且最容易导致数据丢失。很多人以为两个表里的 NULL 值应该能“对上”比如 A 表的某个关联字段是 NULLB 表里也有 NULL理论上应该关联上。但实际结果是NULL 不等于 NULLJOIN 条件永远不会命中。前面提过NULL NULL的结果是 UNKNOWN而 JOIN 条件只接受 TRUE所以结果就是这些行直接被丢弃。实际业务中这种场景多半出现在“手工录入的数据”和“系统自动生成的数据”进行关联时。比如一张表是用户自己填写的资料一张表是系统日志自动生成的标签两边都有一个“来源”字段如果没填来源数据库里是 NULL你希望把两边的“未知来源”数据关联到一起结果一 JOIN 全没了。如果你确实需要让 NULL 和 NULL 匹配可以参考下面这个策略。在关联条件里要么统一用COALESCE把两边 NULL 转成同一个业务上不会出现的默认值要么直接写成两个 OR 条件。第一种方案的问题在于如果字段本身的值域正好包含你选的默认值比如 0、UNKNOWN 等就可能误匹配。第二种方案比较直观SELECT * FROM A LEFT JOIN B ON (A.key B.key) OR (A.key IS NULL AND B.key IS NULL);这样写的前提是你确实想匹配两边都为 NULL 的行。但很多业务场景下这不一定是你想要的。要想清楚NULL 代表“未知”把两个“未知”强行匹配到一起在语义上可能没有依据。所以在联表之前先把业务语义理清楚再决定要不要做这种特殊关联。另一个 JOIN 常见坑是LEFT JOIN之后你对右表的字段加了过滤条件结果把本来保留的左表行又过滤掉了。举个例子SELECT * FROM A LEFT JOIN B ON A.id B.a_id WHERE B.status active;如果 B 表里没有对应的行B.status 会是 NULL那么WHERE B.status active对这个 NULL 判断的结果是 UNKNOWN该行直接不出现在结果集里。很多人在这个环节里把自己绕晕了觉得明明用了 LEFT JOIN为什么还是丢数据。正确写法是把过滤条件放在 JOIN 的 ON 子句里SELECT * FROM A LEFT JOIN B ON A.id B.a_id AND B.status active;这个写法保证 A 表的所有行都会出现在结果里B 里有匹配就带出 B 的数据没有匹配就显示 NULL。区别非常微妙但结果截然不同。如果你对 LEFT JOIN 后右表字段做条件过滤建议自检一遍是不是把过滤条件放错位置了。还有一个跟 JOIN 相关的点是如果左右两表关联字段都包含 NULL那么COUNT(*)和COUNT(B.字段)统计出的连接结果可能不一样。如果你用LEFT JOIN算出 100 行这 100 行里可能有 30 行的右表字段为 NULL。如果你再用COUNT(B.id)它会只统计非 NULL 的那 70 行。这在统计连接后有效记录数时经常引起误会。最稳妥的办法是明确你要统计的是“连接后总行数”还是“右侧有匹配的行数”然后选择COUNT(*)还是COUNT(右表主键)。6. 函数与 NULL 的传播效应传入 NULL结果多半还是 NULL这一个点我觉得值得单独拿出来讲因为它会影响你对很多内置函数结果类型的预判。大部分 SQL 标量函数有一个共同特点如果传入的参数里有 NULL那么返回值通常也是 NULL。你可以把它理解为“NULL 的传染性”。比如ABS(NULL)的结果是 NULLUPPER(NULL)的结果是 NULLDATEADD(day, 1, NULL)的结果也是 NULL。背后的逻辑是函数无法在一个未知值上做计算所以结果也只能是未知。这个特性在写复杂表达式时很有用但也会造成误判。比如你写SELECT UPPER(NULL)你可能立刻预期它是 NULL但如果你在一个很长的计算链里比如ROUND(amount * rate, 2)而 amount 恰好是 NULL那么整个计算结果就是 NULL不会自动变成 0。如果你的报表系统对 NULL 不敏感可能就直接显示空白从而导致展示层出现问题。所以当你发现查询结果里某列出现了大片的空白而不是预期的“0”或空字符串时第一反应应该是去检查原始数据里是否有 NULL而不是怀疑 SQL 语法写错了。定位方法很简单直接查一下该字段有多少 NULL 值SELECT COUNT(*) AS total_rows, COUNT(字段) AS non_null_rows, COUNT(*) - COUNT(字段) AS null_rows FROM 表;如果在某个字段上这个差值非常大那么你后续的几乎所有计算、拼接、过滤都会受到 NULL 传播的影响。说到处理函数传播的标配就不能不提COALESCE和NULLIF这两个函数。COALESCE可以从多个参数里返回第一个非 NULL 值适合做默认值兜底。NULLIF则可以把一个特定值转换成 NULL。这两个函数组合使用可以完成很多复杂的数据清洗逻辑。我遇到过的一个真实场景有一张订单表金额字段存在三种情况正常数字、0、NULL。我在做财务统计时需要区分“金额为 0 的订单”和“金额未知的订单”。如果直接用WHERE 金额 0会把 NULL 漏掉如果直接用WHERE 金额 IS NULL会把 0 漏掉。为了把“金额为 0”和“金额未知”区分开我写了这样一段逻辑SELECT CASE WHEN amount IS NULL THEN 未知 WHEN amount 0 THEN 零元 ELSE 正常 END AS amount_category, COUNT(*) FROM orders GROUP BY CASE WHEN amount IS NULL THEN 未知 WHEN amount 0 THEN 零元 ELSE 正常 END;这段 SQL 的意义在于先让 NULL 和 0 分开归类然后再统计。如果你一上来就写COALESCE(amount, 0)那 NULL 和 0 就彻底混在一起了。所以是否使用 COALESCE 兜底取决于业务语义是否需要区分“缺失”和“零值”。另外一个和 NULL 传播相关的场景是窗口函数。像ROW_NUMBER()、RANK()这类的排序窗口函数在遇到 NULL 时排序规则默认是 NULL 最大MySQL 的 ASC 排序把 NULL 放最前但 ROW_NUMBER 的排序在数据库间有差异。如果你用ORDER BY 字段 DESCNULL 可能会被排到最后面。大多数情况下可以接受但如果你需要在窗口函数里精确定义 NULL 的位置建议显式地在 ORDER BY 里加NULLS FIRST或NULLS LAST语法不同数据库支持程度不同MySQL 现在还没有直接支持通常是用ORDER BY (字段 IS NULL) ASC, 字段 ASC来模拟。7. 唯一约束与 NULL 的共处为什么多行 NULL 不会触发主键冲突这个主题可能很多人在实际开发中会遇到在表的某列上加了唯一索引然后往里面插了两行 NULL理论上应该报“唯一性冲突”结果数据库居然允许了。原因在于唯一索引在判断重复时对 NULL 的处理方式和普通字段值不同。多个 NULL 值之间不被认为是重复值。也就是说唯一索引只约束“多个非 NULL 值不重复”但允许存在多个 NULL。这个特性有两个实际影响。第一个影响是如果你希望某个字段“除了 NULL 之外不重复”直接用唯一索引就能满足不需要额外写触发器。比如你有一张用户邮箱表允许用户暂未填写邮箱NULL但一旦填写了邮箱就不能重复。你只需在邮箱字段上建唯一索引NULL 行不会干扰。第二个影响是如果你反过来说“这个字段绝对不能重复连 NULL 也不行”那唯一索引就不够用了。例如你要身份证号唯一但有些历史脏数据把身份证号存成了 NULL想要强制让 NULL 也参与唯一性约束在不同数据库里有不同做法。在 PostgreSQL 里可以建唯一索引但无法直接让 NULL 也参与MySQL 里的 NULL 同样不参与唯一性约束。要达到严格约束通常需要额外加一个代理列比如给 NULL 映射成 UUID 或者其他绝对不可能重复的值CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(255), email_unique VARCHAR(255) GENERATED ALWAYS AS ( CASE WHEN email IS NULL THEN uuid() ELSE email END ) STORED, UNIQUE KEY uk_email (email_unique) );这里用了一个生成列当 email 为 NULL 时email_unique 生成一个 UUID这样每个人都会有一个唯一的 email_unique 值。这个方案在 MySQL 8.0 和部分数据库里可行但如果是其他数据库你可能要换用触发器或者在应用层做控制。总之理解了“唯一索引默认放过 NULL”你就不会在数据库设计阶段犯“以为 NULL 能撞索引”的错误。如果你目前的工作涉及建表和数据规范化我给你一个建议在设计阶段就要明确每个字段是否允许为 NULL。很多团队图省事把字段全部设为 NOT NULL DEFAULT 结果空字符串和 NULL 在语义上彻底混淆了。更好的做法是对确实可能缺失的字段允许 NULL对必须要有值的字段使用 NOT NULL 加默认值。这样逻辑语义清晰后续统计和数据校验也会简单很多。8. 聚合时 NULL 值引发的“空集问题”以及如何正确兜底前面已经说过SUM、AVG在没有任何匹配行时返回 NULL而不是 0。这个行为在报表场景里经常导致前端展示为空白甚至在导出 Excel 时出现错误提示。那么正确的兜底姿势是什么先说最常用的方案COALESCE包裹聚合函数。比如SELECT COALESCE(SUM(sales_amount), 0) AS total_sales, COALESCE(AVG(sales_amount), 0) AS avg_sales FROM sales WHERE sale_date 2025-01-01;这个写法的含义是如果没有这天的销售记录SUM 返回 NULLCOALESCE 把它替换成 0。同里AVG 返回 NULL替换成 0。但这里有一个很小的细节AVG 兜底成 0 是不是合理在“平均销售额”的语义里空集没有平均销售额0 其实是“无销售额”的表达跟我们想表达的“存在销售额但为 0”不同。比如某天没有销售记录平均销售额描述为 0可能让业务方误以为“每个商品都没有卖出去”而不是“当天根本没有数据”。所以更严谨的写法可能是返回 NULL让前端展示为“暂无数据”而不是“0”。从这个角度我给出一个更通用的准则不要盲目把所有聚合结果都兜底成 0。写 SQL 之前先问一句下游业务方能不能理解“没有数据”和“数据为 0”的区别如果能就保留 NULL如果不能就用 COALESCE 兜底。另一个空集相关的常见场景是你在统计报表里撞了GROUP BY某些组没有任何记录不会在结果集里生成一行“值为 0”的汇总行。比如你想统计每个产品类别下的订单数但某个类别没卖出去那结果里就没有该类别对应的分组行。这个时候你需要在应用层拿到所有类别列表后做填充或者用 LEFT JOIN 一张类别表 COUNT(订单表主键)但订单表主键会为 NULL所以 COUNT(主键) 会返回 0这算是 LEFT JOIN 空集的常规解法SELECT c.category_name, COUNT(o.order_id) AS order_count FROM categories c LEFT JOIN orders o ON o.category_id c.id AND o.status paid GROUP BY c.category_name;这里的关键点是把过滤条件放到了 JOIN 的 ON 子句里。如果你把o.status paid放在 WHERE 里LEFT JOIN 就变成了内连接没有成交记录的类别会被过滤掉。这个点跟前面 LEFT JOIN 的坑一模一样但因为加了 COUNT 和 GROUP BY很多人更容易忽略。9. 数据迁移、ETL 与 NULL 的实战难题在实际业务里NULL 问题最棘手的不是查询而是数据迁移和 ETL 过程。迁移时要面对的不只是数据库层面的语法坑还有源系统和目标系统对“空”的定义不一致。最常见的矛盾是源系统里空字符串表示“没有”目标系统里 NULL 表示“没有”或者反过来源系统里 NULL 表示“未知”目标系统里用 0 代替所有空值。ETL 开发人员如果一开始没确认好映射规则迁移完成后光对齐数据就得花掉大量精力。拿 Kettle 举例这是一款常用的 ETL 工具网上有人搜索过“kettle 局部修改空字符串不转换为 null”说明很多人遇到导入时字符串全变成了 NULL 或者本来就该是空字符串的地方被 NULL 替换的问题。在处理 Kettle 数据流时你有两个选择在输入步骤里通过“空操作”或“字符串剪切”把空字符串转换成 NULL在 SQL 写入目标表时使用NULLIF(字段, )或COALESCE(字段, 默认值)做统一规范化。我个人的建议是尽量在 ETL 的转换层做规则化处理让进入目标表的数据已经符合目标表的设计约定而不是把“脏数据”先塞进去再在 SQL 里补救。这样后续查询、报表、下游数据应用都能省不少事。再补充一个数据库之间行为差异的点。SQL Server 和 Oracle 对空字符串的处理并不同。SQL Server 把空字符串当作单独的确定值而 Oracle 会把空字符串当作 NULL 处理。在迁移这些数据时如果两端都做“空字符串”的转换很容易造成预期外的结果。你在做跨库迁移前最好先建立一张“字段级别 NULL / 空串映射表”逐字段确认源和目标的口径。举一个实际例子。某系统底层是 SQL Server字段为varchar(50)未填写的值可能是 NULL 或者是 而 Oracle 数据库里拼接字符串、判断为空时往往会受 NULL 影响。迁移时如果想把 Oracle 端所有空字符串视为 NULL那么 SQL 处理里就要写NULLIF(column, )如果想把 NULL 也保留在 Oracle 端那么直接插入即可。但如果你在转换时漏了等到目标库里混入 NULL 和 之后下游执行COUNT(字段)统计就会少算一行。现在 C 端报表一旦不齐业务立刻就会炸所以迁移前明确口径真的非常关键。10. 多数据库方言下的 NULL 排序、去重、比较差异我前面提过不同数据库对 NULL 的默认处理方式存在差异这个点在进行跨数据库开发时尤其值得关注。虽然标题是 NULL 值详解但如果你只在一类数据库里写过 SQL可能很难意识到“同一个 SQL 在另一个数据库里结果完全不同”的可能性。先看排序。MySQL 里ORDER BY 字段 ASC时 NULL 默认在最前面ORDER BY 字段 DESC时 NULL 默认在最后面。PostgreSQL 默认把 NULL 放在最大值一端也就是ASC时 NULL 在最后DESC时 NULL 在最前。SQL Server 默认 NULL 在排序结果中间具体位置取决于查询计划。Oracle 默认 NULL 在最大值一端所以ASC时 NULL 在最后DESC时 NULL 在最前。如果你要跨数据库保持一致要么用显式的NULLS FIRST/LAST语法Oracle、PostgreSQL 支持MySQL 不支持要么用ORDER BY (字段 IS NULL), 字段方向这种写法MySQL、SQL Server 也都能用。再来看去重。SELECT DISTINCT会把多个 NULL 合并成一个 NULL 记录这在所有主流数据库里都一样。但是在GROUP BY时多个 NULL 同样会归为一组。这里有一个很隐蔽的坑如果你用GROUP BY对包含 NULL 的字段去重统计结果里会有“NULL”组你需要在展示层或者查询结果里做标签映射比如COALESCE(字段, 未知)。再看比较。除了前面说过的“NULL 不能参与等值比较”外还有一个常见问题是NOT IN子查询遇到 NULL。前面已经举过例子这里重点强调一下解决思路如果你一定要用NOT IN对子查询的结果先过滤掉 NULL比如SELECT * FROM users WHERE id NOT IN ( SELECT user_id FROM blacklist WHERE user_id IS NOT NULL );如果你用NOT EXISTS则没有这个烦恼因为NOT EXISTS是子查询里有没有匹配行而不是比较 NULL 值。所以更推荐写NOT EXISTSSELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM blacklist b WHERE b.user_id u.id );如果你的 SQL 能改成 NOT EXISTS那就尽量改能少踩很多隐含的 NULL 坑。11. 常见问题速查表把这些组合都记下来最后我按多年实战经验把最常见的 NULL 问题整理成了一个速查表。你可以直接收藏遇到问题对着查场景错误写法正确写法或处理思路判断字段是否为 NULLWHERE 字段 NULLWHERE 字段 IS NULL判断字段是否非 NULLWHERE 字段 NULLWHERE 字段 IS NOT NULL把空串和 NULL 统一处理WHERE 字段 WHERE NULLIF(TRIM(字段), ) IS NULL取 SUM 时避免返回 NULLSELECT SUM(字段) FROM 表SELECT COALESCE(SUM(字段), 0) FROM 表NOT IN 子查询结果含 NULLWHERE id NOT IN (子查询)子查询里加WHERE 字段 IS NOT NULL或改用 NOT EXISTSLEFT JOIN 后右表加过滤导致丢行WHERE B.字段 xxx把过滤条件放进ON子句字符串拼接时避免 NULL 传染字段1 字段2CONCAT(字段1, 字段2)或COALESCE(字段, )两个 NULL 匹配关联ON A.字段 B.字段根据业务语义使用ON A.字段 B.字段 OR (A.字段 IS NULL AND B.字段 IS NULL)空字符串转换为 NULL直接 UPDATE 为 NULL 太粗暴用NULLIF(字段, )统计非 NULL 行数COUNT(*)误用了明确语义用COUNT(字段)分组结果里 NULL 组显示直接展示 NULLCOALESCE(字段, 未知)AVG 计算忽略 NULL 导致虚高直接AVG(字段)分清“实考平均”和“全员平均”后者用AVG(COALESCE(字段, 0))窗口函数排序 NULL 位置不统一默认排序ORDER BY (字段 IS NULL) ASC, 字段 ASC数据迁移时空串与 NULL 混用直接复制数据先做好字段级映射统一口径这张表里每一条都是实打实踩过的坑因为 NULL 在逻辑上太特殊稍有疏忽就会跟业务预期不一致。12. 我个人最后的一点实操建议如果你让我用最简短的话总结 NULL 的学习心得我会说SQL 里的 NULL 不仅是一个“数据值”问题更是一个“语义问题”你要跟产品、数据分析师、后端同事把“NULL 代表什么”对齐才有可能写出真正正确的 SQL。我自己的习惯是在项目初期就建立一份“字段空值规范”明确规定哪些字段允许为 NULL哪些字段必须为 NOT NULL空字符串和 NULL 各自表示什么含义。这份规范不需要多复杂但能省掉后面无数的扯皮。你可以从一张简单的表开始维护字段名类型是否允许 NULL空值业务含义默认值备注user_idINT否无无主键mobileVARCHAR是未填写手机号NULL统计时注意 COUNTremarkVARCHAR是暂无备注NULL展示用 COALESCE(remark, -)amountDECIMAL否金额为 00汇总时直接 SUM这种规范文档不需要写得多花哨但能让开发、测试、运维在处理数据时有一个统一口径。对一个长期演进的项目来说能在最早期就把 NULL 语义定清楚比之后写十个补丁都管用。再补充一个很实用的小技巧当你拿到一张陌生表不确定某些字段是否含有 NULL 时先不要急着写复杂 SQL。你可以在本地跑一句探查语句SELECT COUNT(*) AS total, COUNT(字段A) AS field_a_non_null, COUNT(字段B) AS field_b_non_null FROM 表;如果某个字段的非 NULL 数量明显小于总行数就意味着该字段存在大量 NULL你后续任何跟这个字段相关的计算都要格外小心。很多时候你写出来的 SQL 结果不对并不是语法问题而是你把 NULL 当成了普通值去比较和运算。踩过几次坑之后我现在写任何一句 SQL 都会下意识扫一眼哪些字段可能会是 NULL这些字段参与了等值判断、聚合、字符串拼接还是 JOIN 条件。如果是就提前用COALESCE、NULLIF、IS NULL等手段规避。这套习惯养成之后你写的 SQL 在数据质量上会明显比旁人更稳排查问题的速度也会快很多。希望这篇内容能帮你把 NULL 这个老朋友彻底摸透。
SQL NULL 值详解：三值逻辑、空字符串区别与聚合函数避坑指南
1. NULL 值到底是什么先把它从“数字 0”和“空字符串”里拎出来我做了这么多年数据库发现很多人对 NULL 的理解都停留在“NULL 就是没值嘛那不就是 0 或者空字符串吗”。这个理解如果只是日常聊天问题不大但只要你写 SQL 超过三个月早晚会在 NULL 上栽一次跟头。而且栽跟头的方式还特别统一要么是 WHERE 条件查不出数据要么是 COUNT 统计出来的数字跟对不上要么是字符串拼接出来一长串 NULL要么是 JOIN 的时候莫名其妙丢行。先说结论NULL 在 SQL 里不是“数字 0”不是“空字符串 ”也不是“空格 ”它表示的是“未知”UNKNOWN或者“没有值”NO VALUE。你可以把它理解成一个占位符意思是“这个地方什么数据都没有连空字符串都不算”。空字符串是一个确定的值它表示“这个字段的内容是空的”但 NULL 表示“这个字段压根没有被赋值”。这两者之间有本质区别理解了这个区别后面所有关于 NULL 的坑就都能想通了。举个例子。你现在维护一张用户表有一个字段叫“手机号”。如果某个用户的记录里手机号是 空字符串说明系统知道这个用户没有手机号至少“没有手机号”这个信息是确定的。但如果这个字段是 NULL说明系统压根不知道这个用户有没有手机号可能是没采集到可能是数据导入时漏了也可能是当时还没开放这个字段。一个是“确定没有”一个是“不知道有没有”这在业务上完全是两回事。所以你在做数据清洗的时候第一步绝对不是写 UPDATE 语句而是先搞清楚你的业务系统里NULL 和空字符串各自代表什么语义。否则你辛辛苦苦把 NULL 统一成 可能把“未知”这个信息直接抹掉了后续分析就会得出完全错误的结论。再往深处说一点NULL 在 SQL 的三值逻辑里承担着核心角色。普通逻辑判断只有 TRUE 和 FALSE但 SQL 里任何跟 NULL 比较的结果既不是 TRUE 也不是 FALSE而是 UNKNOWN。这一点非常反直觉因为你在写 WHERE 子句的时候会发现“WHERE 字段 NULL”永远查不到数据这其实不是你的 SQL 写错了而是 SQL 的语义设计就是如此。为了让你直观感受一下 NULL 和 0、 的区别我列了张对比表你可以存下来没事翻一翻场景结果说明NULL NULLUNKNOWN不会命中 WHERE两个未知值比不出相等因为两边都是“未知”NULL 0UNKNOWN未知值跟 0 没有可比性NULL UNKNOWN未知值跟空字符串也没有可比性 TRUE两个空字符串是确定的相等0 NULLUNKNOWN同上没有可比性COUNT(*)统计所有行数不关心字段是否为 NULLCOUNT(字段)只统计该字段非 NULL 的行数NULL 不会被计入SUM(字段)忽略 NULL 值但有个坑后面我会细讲这张表值得你仔细看几遍尤其注意NULL NULL这个结果。大多数人第一次写 SQL 时都会踩这个坑以为两个 NULL 应该相等结果用WHERE a b去匹配两张表的 NULL 字段发现怎么都匹配不上。这个问题在处理“关联两张表时匹配空值”的场景里尤其常见比如你拿 A 表的字段去 B 表里找记录两边都有 NULL 值你以为能关联上结果全丢了。2. 三值逻辑为什么 SQL 里会有 TRUE、FALSE、UNKNOWN 三种结果既然聊到 NULL就绕不开三值逻辑。这是理解 NULL 一切特性的一把钥匙。很多初学者总是记不住“为什么WHERE NULL NULL查不出数据”根本原因就是他们把 SQL 的逻辑跟编程语言里的逻辑混为一谈了。在 Java、Python、C 这些语言里条件表达式的求值结果只有两个真或假。但在标准 SQL 里由于 NULL 的存在比较运算的结果多了一个状态叫 UNKNOWN。这个 UNKNOWN 既不是 TRUE 也不是 FALSE它是逻辑上的“不确定”。你可以这么理解现在有一个盒子里面可能装着一个苹果也可能什么都没有反正你没法打开看。有人问你“这个盒子里是苹果吗”你没法回答“是”也没法回答“不是”你只能回答“不知道”。这个“不知道”在 SQL 里就是 UNKNOWN。那 UNKNOWN 在过滤条件里怎么表现呢当一个行的 WHERE 条件计算结果为 UNKNOWN 时这一行不会出现在结果集里。注意它和 FALSE 的结果一样都是“不出现”但语义上完全不同FALSE 是“确定不满足条件”UNKNOWN 是“无法确定是否满足条件”。虽然是同样的结果但如果你要排查数据问题了解这一点能帮你更快定位到根因。举个例子。你现在有两张表一张是“学生表”一张是“考试表”。你想查“所有参加考试的学生里得分大于 60 分的人”。如果某个学生没有参加考试考试表里根本没有他的记录那么这个学生就不会出现在结果里你很容易理解。但如果这个学生参加了考试但成绩字段是 NULL比如老师还没录入那成绩 60这个比较结果就是 UNKNOWN他也不会出现在结果里。这从业务上说得通成绩未知确实不能说他是“大于 60 分”。但麻烦在于很多人写 SQL 时如果不注意会把“成绩为 NULL”和“成绩小于等于 60”混为一谈。你如果写WHERE 成绩 60 OR 成绩 60你直觉上以为能覆盖所有情况但 NULL 的考试成绩既不满足大于 60也不满足小于等于 60UNKNOWN OR UNKNOWN 还是 UNKNOWN他照样被漏掉。这里要特别提醒你一个点如果你需要把 NULL 值单独抓出来处理不要用比较运算符要用IS NULL。这是 SQL 提供的一个专门用来判断 NULL 的语法也是唯一正确的判断方式。再说一个更微妙的场景NOT IN 子查询与 NULL 的配合问题。假设你要查“不在某个列表里的用户”你写的是WHERE id NOT IN (SELECT id FROM 黑名单)。如果黑名单子查询结果里出现了任意一个 NULL 值整个 NOT IN 的结果就会变成 UNKNOWN最终结果是“一行都查不出来”。这个坑非常隐蔽因为你的子查询大部分情况下返回的都是正常 id只有当某些记录恰好 id 为 NULL 时整个查询就废了。我自己在做数据清理的时候遇到过一次排查了很久才意识到是子查询结果里混进了 NULL。这类问题的解法是在子查询里显式加上WHERE id IS NOT NULL。你可以把三值逻辑归纳成下面几条记忆规则TRUE OR UNKNOWN TRUE因为 TRUE 已经能决定结果FALSE OR UNKNOWN UNKNOWN两边都没法确定TRUE AND UNKNOWN UNKNOWNTRUE 还需要 UNKNOWN 配合FALSE AND UNKNOWN FALSEFALSE 已经能决定结果NOT UNKNOWN UNKNOWN取反还是未知这几条规则不用死记你只要理解“UNKNOWN 就是一个不确定的值任何跟它组合的逻辑结果只要无法被另一个值确定最终就是 UNKNOWN”就够了。唯一例外的场景是如果另一个值已经能决定整个表达式的结果比如TRUE OR UNKNOWN里 TRUE 已经让整个结果为真那 UNKNOWN 就不起作用了。3. 聚合函数与 NULL 的纠缠COUNT、SUM、AVG、MAX、MIN 各有各的脾气聚合函数是日常写 SQL 最高频的场景之一但 NULL 在里面引发的坑我敢说十个写 SQL 的人里有八个都踩过。下面一个个说清楚。先讲COUNT。COUNT(*)统计的是“行数”不管这一行的字段是不是 NULL都会数进去。但COUNT(某个字段)统计的是“该字段非 NULL 的行数”NULL 值不会计入。这个区别直接导致你在做统计时会得到不同的结果。举个例子。有一张订单表里面有 10 条订单记录但其中 3 条记录没有填写“优惠券编号”这个字段值为 NULL。你执行SELECT COUNT(*) FROM 订单会得到 10执行SELECT COUNT(优惠券编号) FROM 订单会得到 7。如果你期望的是“有多少订单使用了优惠券”用 COUNT(优惠券编号) 是对的如果你期望的是“总共有多少订单”用 COUNT(*) 才是对的。但如果你不了解这个差异写了个 COUNT(优惠券编号) 就拿到结果自作聪明地当成总单量那报表就会少掉 3 单。再说SUM。SUM(字段)会自动忽略 NULL 值。这个行为的意外之处在于如果一个字段全部为 NULL那 SUM 的结果会是什么是 NULL而不是 0。这个很容易理解但很多人就挂在这一点上。你在一张新表上跑SELECT SUM(金额) FROM 订单 WHERE 状态 已完成如果一张已完成订单都没有你期望结果是 0结果控制台打出来一个大大的 NULL下游报表系统直接就显示成空白。你不得不用COALESCE(SUM(金额), 0)来兜底。很多人问我为什么不建议在数据库层面把 NULL 直接改成 0。一个核心原因是NULL 和 0 在 SUM 里能区分开“没有值”和“值为零”前者会影响平均值计算后者不会。如果你把所有 NULL 都改成 0AVG 计算结果会被拉低因为除数增加了 0 值但实际上这些记录本不该参与计算。说到AVG这个函数是 NULL 陷阱的重灾区。AVG(字段)在计算平均值时同样会忽略 NULL 值。这可能带来业务上的误导。比如某门课有 100 个学生选课其中 20 人缺考成绩为 NULL。你跑SELECT AVG(成绩) FROM 选课表 WHERE 课程数学得到的平均值是“参加考试的 80 人的平均分”而不是“所有选课学生的平均分”。如果你不知道这个逻辑一不小心就会得出一个虚高的平均分。这在很多场景里影响很大正确的做法是先想清楚业务需求是要“实考平均分”还是“全员平均分”如果是后者你就要用COALESCE(成绩, 0)显式地把缺考成绩转换为 0 参与计算。至于MAX和MIN它们同样会忽略 NULL。这通常问题不大因为最大最小值很少会因为存在 NULL 而改变语义。但有一个细节你可能用得上如果你要对某个字段取最大非 NULL 值可以直接用 MAX根本不用额外过滤。这个特点在一些数据校验场景里很好用。还有一个容易踩的坑是GROUP BY对 NULL 的处理。在 MySQL 和大多数数据库里NULL 值会被单独分成一组。也就是说如果你有一批订单部分订单的渠道字段是 NULL那么GROUP BY 渠道的结果里会出现一行“渠道 NULL”的汇总数据。这个不算错误但你要记得在结果呈现时处理一下。比如用COALESCE(渠道, 未知渠道)把它映射成业务可读的标签否则凭空多出一行 NULL 分组会让业务方摸不着头脑。我在实际项目里通常会在统计 SQL 的最后统一做一次 NULL 兜底处理比如SELECT COALESCE(SUM(amount), 0) AS total_amount, COUNT(*) AS order_count, COALESCE(AVG(amount), 0) AS avg_amount FROM orders WHERE status finished;这条 SQL 的意图是如果没有任何已完成订单SUM 返回 NULL用 COALESCE 兜底成 0AVG 同理。但注意这里用COALESCE(AVG(amount), 0)在某些语义下是有争议的。如果 pending 状态下的订单也会有 amount 记录那没问题但如果“没有已完成订单”和“平均金额为 0”是两种不同的业务含义你就得根据实际场景决定兜底值。4. 空字符串、NULL 和字符串拼接的混乱局面每次处理导入数据或者接手老系统的时候最头疼的就是 NULL、空字符串、空格这三个东西混在一起。它们在外表上可能都“看起来是空的”但在 SQL 里完全是三种情况处理方式也完全不同。空字符串 是一个确定的值表示这个字段的内容是空的。空格 是另一个确定的值表示这个字段里有一个空格字符。NULL 则是一个未知值上面已经反复强调过了。在数据清洗中最保险的策略是先用TRIM(字段)去掉两侧空格再用NULLIF(字段, )把空字符串转换为 NULL最后统一用IS NULL判断。NULLIF是很多 SQL 新手不太熟悉但非常好用的函数它的作用很简单如果两个参数相等返回 NULL如果不相等返回第一个参数。所以NULLIF(字段, )的意思就是如果字段为 返回 NULL否则返回原值。这个函数用来统一“空字符串”和“NULL”的差异非常顺手。举一个实际场景。你从外部系统导出一份名单里面的“备注”字段有的为空字符串有的为 NULL还有的是一串空格。你想查找所有没有备注的人如果直接写WHERE 备注 你会发现 NULL 和空格的人查不出来。正确写法是SELECT * FROM 名单 WHERE NULLIF(TRIM(备注), ) IS NULL;这条 SQL 的思路是先把空格去掉然后把空字符串转成 NULL最后统一判断是否为 NULL。这样无论是 NULL、 还是 都能被筛选出来。再来看字符串拼接。在 SQL Server 里直接用字段1 字段2 字段3拼接字符串时只要有一个字段是 NULL整个结果就是 NULL。这个坑很多人踩过。比如你拼一个“姓 名”的字段如果用户没有填写姓氏结果不是“张三”而是 NULL。解决方案是使用CONCAT函数它会把 NULL 当作空字符串处理然后拼接其他部分。不过要注意早期的 SQL Server2012 之前并不支持CONCAT你得用ISNULL(字段, )一个个处理或者用 CASE WHEN。现在用新版 SQL Server 的话直接写SELECT CONCAT(COALESCE(first_name, ), , COALESCE(last_name, )) AS full_name FROM users;这里我把COALESCE也加进去了原因是要兼容不同数据库的拼接规则并且如果你在 MySQL 里用双竖线拼接还需要先开启管线模式。经验法则就是任何涉及字符串拼接的场景只要存在 NULL 的可能要么显式COALESCE要么用安全拼接函数。另外还有一个容易被忽略的点ORDER BY里 NULL 的排序位置。不同数据库对 NULL 的排序规则不一致。在 MySQL 里ORDER BY 字段会把 NULL 排在最前面在 PostgreSQL 和 SQL Server 里默认把 NULL 排最后。如果你对排序顺序有明确要求推荐显式地写ORDER BY 字段 IS NULL, 字段 ASC这样在任何数据库上都能得到可预期的结果可读性也更好。5. JOIN 与 NULL 的暗坑为什么关联条件里 NULL 永远等不上 NULLJOIN 操作中的 NULL 问题非常常见而且最容易导致数据丢失。很多人以为两个表里的 NULL 值应该能“对上”比如 A 表的某个关联字段是 NULLB 表里也有 NULL理论上应该关联上。但实际结果是NULL 不等于 NULLJOIN 条件永远不会命中。前面提过NULL NULL的结果是 UNKNOWN而 JOIN 条件只接受 TRUE所以结果就是这些行直接被丢弃。实际业务中这种场景多半出现在“手工录入的数据”和“系统自动生成的数据”进行关联时。比如一张表是用户自己填写的资料一张表是系统日志自动生成的标签两边都有一个“来源”字段如果没填来源数据库里是 NULL你希望把两边的“未知来源”数据关联到一起结果一 JOIN 全没了。如果你确实需要让 NULL 和 NULL 匹配可以参考下面这个策略。在关联条件里要么统一用COALESCE把两边 NULL 转成同一个业务上不会出现的默认值要么直接写成两个 OR 条件。第一种方案的问题在于如果字段本身的值域正好包含你选的默认值比如 0、UNKNOWN 等就可能误匹配。第二种方案比较直观SELECT * FROM A LEFT JOIN B ON (A.key B.key) OR (A.key IS NULL AND B.key IS NULL);这样写的前提是你确实想匹配两边都为 NULL 的行。但很多业务场景下这不一定是你想要的。要想清楚NULL 代表“未知”把两个“未知”强行匹配到一起在语义上可能没有依据。所以在联表之前先把业务语义理清楚再决定要不要做这种特殊关联。另一个 JOIN 常见坑是LEFT JOIN之后你对右表的字段加了过滤条件结果把本来保留的左表行又过滤掉了。举个例子SELECT * FROM A LEFT JOIN B ON A.id B.a_id WHERE B.status active;如果 B 表里没有对应的行B.status 会是 NULL那么WHERE B.status active对这个 NULL 判断的结果是 UNKNOWN该行直接不出现在结果集里。很多人在这个环节里把自己绕晕了觉得明明用了 LEFT JOIN为什么还是丢数据。正确写法是把过滤条件放在 JOIN 的 ON 子句里SELECT * FROM A LEFT JOIN B ON A.id B.a_id AND B.status active;这个写法保证 A 表的所有行都会出现在结果里B 里有匹配就带出 B 的数据没有匹配就显示 NULL。区别非常微妙但结果截然不同。如果你对 LEFT JOIN 后右表字段做条件过滤建议自检一遍是不是把过滤条件放错位置了。还有一个跟 JOIN 相关的点是如果左右两表关联字段都包含 NULL那么COUNT(*)和COUNT(B.字段)统计出的连接结果可能不一样。如果你用LEFT JOIN算出 100 行这 100 行里可能有 30 行的右表字段为 NULL。如果你再用COUNT(B.id)它会只统计非 NULL 的那 70 行。这在统计连接后有效记录数时经常引起误会。最稳妥的办法是明确你要统计的是“连接后总行数”还是“右侧有匹配的行数”然后选择COUNT(*)还是COUNT(右表主键)。6. 函数与 NULL 的传播效应传入 NULL结果多半还是 NULL这一个点我觉得值得单独拿出来讲因为它会影响你对很多内置函数结果类型的预判。大部分 SQL 标量函数有一个共同特点如果传入的参数里有 NULL那么返回值通常也是 NULL。你可以把它理解为“NULL 的传染性”。比如ABS(NULL)的结果是 NULLUPPER(NULL)的结果是 NULLDATEADD(day, 1, NULL)的结果也是 NULL。背后的逻辑是函数无法在一个未知值上做计算所以结果也只能是未知。这个特性在写复杂表达式时很有用但也会造成误判。比如你写SELECT UPPER(NULL)你可能立刻预期它是 NULL但如果你在一个很长的计算链里比如ROUND(amount * rate, 2)而 amount 恰好是 NULL那么整个计算结果就是 NULL不会自动变成 0。如果你的报表系统对 NULL 不敏感可能就直接显示空白从而导致展示层出现问题。所以当你发现查询结果里某列出现了大片的空白而不是预期的“0”或空字符串时第一反应应该是去检查原始数据里是否有 NULL而不是怀疑 SQL 语法写错了。定位方法很简单直接查一下该字段有多少 NULL 值SELECT COUNT(*) AS total_rows, COUNT(字段) AS non_null_rows, COUNT(*) - COUNT(字段) AS null_rows FROM 表;如果在某个字段上这个差值非常大那么你后续的几乎所有计算、拼接、过滤都会受到 NULL 传播的影响。说到处理函数传播的标配就不能不提COALESCE和NULLIF这两个函数。COALESCE可以从多个参数里返回第一个非 NULL 值适合做默认值兜底。NULLIF则可以把一个特定值转换成 NULL。这两个函数组合使用可以完成很多复杂的数据清洗逻辑。我遇到过的一个真实场景有一张订单表金额字段存在三种情况正常数字、0、NULL。我在做财务统计时需要区分“金额为 0 的订单”和“金额未知的订单”。如果直接用WHERE 金额 0会把 NULL 漏掉如果直接用WHERE 金额 IS NULL会把 0 漏掉。为了把“金额为 0”和“金额未知”区分开我写了这样一段逻辑SELECT CASE WHEN amount IS NULL THEN 未知 WHEN amount 0 THEN 零元 ELSE 正常 END AS amount_category, COUNT(*) FROM orders GROUP BY CASE WHEN amount IS NULL THEN 未知 WHEN amount 0 THEN 零元 ELSE 正常 END;这段 SQL 的意义在于先让 NULL 和 0 分开归类然后再统计。如果你一上来就写COALESCE(amount, 0)那 NULL 和 0 就彻底混在一起了。所以是否使用 COALESCE 兜底取决于业务语义是否需要区分“缺失”和“零值”。另外一个和 NULL 传播相关的场景是窗口函数。像ROW_NUMBER()、RANK()这类的排序窗口函数在遇到 NULL 时排序规则默认是 NULL 最大MySQL 的 ASC 排序把 NULL 放最前但 ROW_NUMBER 的排序在数据库间有差异。如果你用ORDER BY 字段 DESCNULL 可能会被排到最后面。大多数情况下可以接受但如果你需要在窗口函数里精确定义 NULL 的位置建议显式地在 ORDER BY 里加NULLS FIRST或NULLS LAST语法不同数据库支持程度不同MySQL 现在还没有直接支持通常是用ORDER BY (字段 IS NULL) ASC, 字段 ASC来模拟。7. 唯一约束与 NULL 的共处为什么多行 NULL 不会触发主键冲突这个主题可能很多人在实际开发中会遇到在表的某列上加了唯一索引然后往里面插了两行 NULL理论上应该报“唯一性冲突”结果数据库居然允许了。原因在于唯一索引在判断重复时对 NULL 的处理方式和普通字段值不同。多个 NULL 值之间不被认为是重复值。也就是说唯一索引只约束“多个非 NULL 值不重复”但允许存在多个 NULL。这个特性有两个实际影响。第一个影响是如果你希望某个字段“除了 NULL 之外不重复”直接用唯一索引就能满足不需要额外写触发器。比如你有一张用户邮箱表允许用户暂未填写邮箱NULL但一旦填写了邮箱就不能重复。你只需在邮箱字段上建唯一索引NULL 行不会干扰。第二个影响是如果你反过来说“这个字段绝对不能重复连 NULL 也不行”那唯一索引就不够用了。例如你要身份证号唯一但有些历史脏数据把身份证号存成了 NULL想要强制让 NULL 也参与唯一性约束在不同数据库里有不同做法。在 PostgreSQL 里可以建唯一索引但无法直接让 NULL 也参与MySQL 里的 NULL 同样不参与唯一性约束。要达到严格约束通常需要额外加一个代理列比如给 NULL 映射成 UUID 或者其他绝对不可能重复的值CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(255), email_unique VARCHAR(255) GENERATED ALWAYS AS ( CASE WHEN email IS NULL THEN uuid() ELSE email END ) STORED, UNIQUE KEY uk_email (email_unique) );这里用了一个生成列当 email 为 NULL 时email_unique 生成一个 UUID这样每个人都会有一个唯一的 email_unique 值。这个方案在 MySQL 8.0 和部分数据库里可行但如果是其他数据库你可能要换用触发器或者在应用层做控制。总之理解了“唯一索引默认放过 NULL”你就不会在数据库设计阶段犯“以为 NULL 能撞索引”的错误。如果你目前的工作涉及建表和数据规范化我给你一个建议在设计阶段就要明确每个字段是否允许为 NULL。很多团队图省事把字段全部设为 NOT NULL DEFAULT 结果空字符串和 NULL 在语义上彻底混淆了。更好的做法是对确实可能缺失的字段允许 NULL对必须要有值的字段使用 NOT NULL 加默认值。这样逻辑语义清晰后续统计和数据校验也会简单很多。8. 聚合时 NULL 值引发的“空集问题”以及如何正确兜底前面已经说过SUM、AVG在没有任何匹配行时返回 NULL而不是 0。这个行为在报表场景里经常导致前端展示为空白甚至在导出 Excel 时出现错误提示。那么正确的兜底姿势是什么先说最常用的方案COALESCE包裹聚合函数。比如SELECT COALESCE(SUM(sales_amount), 0) AS total_sales, COALESCE(AVG(sales_amount), 0) AS avg_sales FROM sales WHERE sale_date 2025-01-01;这个写法的含义是如果没有这天的销售记录SUM 返回 NULLCOALESCE 把它替换成 0。同里AVG 返回 NULL替换成 0。但这里有一个很小的细节AVG 兜底成 0 是不是合理在“平均销售额”的语义里空集没有平均销售额0 其实是“无销售额”的表达跟我们想表达的“存在销售额但为 0”不同。比如某天没有销售记录平均销售额描述为 0可能让业务方误以为“每个商品都没有卖出去”而不是“当天根本没有数据”。所以更严谨的写法可能是返回 NULL让前端展示为“暂无数据”而不是“0”。从这个角度我给出一个更通用的准则不要盲目把所有聚合结果都兜底成 0。写 SQL 之前先问一句下游业务方能不能理解“没有数据”和“数据为 0”的区别如果能就保留 NULL如果不能就用 COALESCE 兜底。另一个空集相关的常见场景是你在统计报表里撞了GROUP BY某些组没有任何记录不会在结果集里生成一行“值为 0”的汇总行。比如你想统计每个产品类别下的订单数但某个类别没卖出去那结果里就没有该类别对应的分组行。这个时候你需要在应用层拿到所有类别列表后做填充或者用 LEFT JOIN 一张类别表 COUNT(订单表主键)但订单表主键会为 NULL所以 COUNT(主键) 会返回 0这算是 LEFT JOIN 空集的常规解法SELECT c.category_name, COUNT(o.order_id) AS order_count FROM categories c LEFT JOIN orders o ON o.category_id c.id AND o.status paid GROUP BY c.category_name;这里的关键点是把过滤条件放到了 JOIN 的 ON 子句里。如果你把o.status paid放在 WHERE 里LEFT JOIN 就变成了内连接没有成交记录的类别会被过滤掉。这个点跟前面 LEFT JOIN 的坑一模一样但因为加了 COUNT 和 GROUP BY很多人更容易忽略。9. 数据迁移、ETL 与 NULL 的实战难题在实际业务里NULL 问题最棘手的不是查询而是数据迁移和 ETL 过程。迁移时要面对的不只是数据库层面的语法坑还有源系统和目标系统对“空”的定义不一致。最常见的矛盾是源系统里空字符串表示“没有”目标系统里 NULL 表示“没有”或者反过来源系统里 NULL 表示“未知”目标系统里用 0 代替所有空值。ETL 开发人员如果一开始没确认好映射规则迁移完成后光对齐数据就得花掉大量精力。拿 Kettle 举例这是一款常用的 ETL 工具网上有人搜索过“kettle 局部修改空字符串不转换为 null”说明很多人遇到导入时字符串全变成了 NULL 或者本来就该是空字符串的地方被 NULL 替换的问题。在处理 Kettle 数据流时你有两个选择在输入步骤里通过“空操作”或“字符串剪切”把空字符串转换成 NULL在 SQL 写入目标表时使用NULLIF(字段, )或COALESCE(字段, 默认值)做统一规范化。我个人的建议是尽量在 ETL 的转换层做规则化处理让进入目标表的数据已经符合目标表的设计约定而不是把“脏数据”先塞进去再在 SQL 里补救。这样后续查询、报表、下游数据应用都能省不少事。再补充一个数据库之间行为差异的点。SQL Server 和 Oracle 对空字符串的处理并不同。SQL Server 把空字符串当作单独的确定值而 Oracle 会把空字符串当作 NULL 处理。在迁移这些数据时如果两端都做“空字符串”的转换很容易造成预期外的结果。你在做跨库迁移前最好先建立一张“字段级别 NULL / 空串映射表”逐字段确认源和目标的口径。举一个实际例子。某系统底层是 SQL Server字段为varchar(50)未填写的值可能是 NULL 或者是 而 Oracle 数据库里拼接字符串、判断为空时往往会受 NULL 影响。迁移时如果想把 Oracle 端所有空字符串视为 NULL那么 SQL 处理里就要写NULLIF(column, )如果想把 NULL 也保留在 Oracle 端那么直接插入即可。但如果你在转换时漏了等到目标库里混入 NULL 和 之后下游执行COUNT(字段)统计就会少算一行。现在 C 端报表一旦不齐业务立刻就会炸所以迁移前明确口径真的非常关键。10. 多数据库方言下的 NULL 排序、去重、比较差异我前面提过不同数据库对 NULL 的默认处理方式存在差异这个点在进行跨数据库开发时尤其值得关注。虽然标题是 NULL 值详解但如果你只在一类数据库里写过 SQL可能很难意识到“同一个 SQL 在另一个数据库里结果完全不同”的可能性。先看排序。MySQL 里ORDER BY 字段 ASC时 NULL 默认在最前面ORDER BY 字段 DESC时 NULL 默认在最后面。PostgreSQL 默认把 NULL 放在最大值一端也就是ASC时 NULL 在最后DESC时 NULL 在最前。SQL Server 默认 NULL 在排序结果中间具体位置取决于查询计划。Oracle 默认 NULL 在最大值一端所以ASC时 NULL 在最后DESC时 NULL 在最前。如果你要跨数据库保持一致要么用显式的NULLS FIRST/LAST语法Oracle、PostgreSQL 支持MySQL 不支持要么用ORDER BY (字段 IS NULL), 字段方向这种写法MySQL、SQL Server 也都能用。再来看去重。SELECT DISTINCT会把多个 NULL 合并成一个 NULL 记录这在所有主流数据库里都一样。但是在GROUP BY时多个 NULL 同样会归为一组。这里有一个很隐蔽的坑如果你用GROUP BY对包含 NULL 的字段去重统计结果里会有“NULL”组你需要在展示层或者查询结果里做标签映射比如COALESCE(字段, 未知)。再看比较。除了前面说过的“NULL 不能参与等值比较”外还有一个常见问题是NOT IN子查询遇到 NULL。前面已经举过例子这里重点强调一下解决思路如果你一定要用NOT IN对子查询的结果先过滤掉 NULL比如SELECT * FROM users WHERE id NOT IN ( SELECT user_id FROM blacklist WHERE user_id IS NOT NULL );如果你用NOT EXISTS则没有这个烦恼因为NOT EXISTS是子查询里有没有匹配行而不是比较 NULL 值。所以更推荐写NOT EXISTSSELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM blacklist b WHERE b.user_id u.id );如果你的 SQL 能改成 NOT EXISTS那就尽量改能少踩很多隐含的 NULL 坑。11. 常见问题速查表把这些组合都记下来最后我按多年实战经验把最常见的 NULL 问题整理成了一个速查表。你可以直接收藏遇到问题对着查场景错误写法正确写法或处理思路判断字段是否为 NULLWHERE 字段 NULLWHERE 字段 IS NULL判断字段是否非 NULLWHERE 字段 NULLWHERE 字段 IS NOT NULL把空串和 NULL 统一处理WHERE 字段 WHERE NULLIF(TRIM(字段), ) IS NULL取 SUM 时避免返回 NULLSELECT SUM(字段) FROM 表SELECT COALESCE(SUM(字段), 0) FROM 表NOT IN 子查询结果含 NULLWHERE id NOT IN (子查询)子查询里加WHERE 字段 IS NOT NULL或改用 NOT EXISTSLEFT JOIN 后右表加过滤导致丢行WHERE B.字段 xxx把过滤条件放进ON子句字符串拼接时避免 NULL 传染字段1 字段2CONCAT(字段1, 字段2)或COALESCE(字段, )两个 NULL 匹配关联ON A.字段 B.字段根据业务语义使用ON A.字段 B.字段 OR (A.字段 IS NULL AND B.字段 IS NULL)空字符串转换为 NULL直接 UPDATE 为 NULL 太粗暴用NULLIF(字段, )统计非 NULL 行数COUNT(*)误用了明确语义用COUNT(字段)分组结果里 NULL 组显示直接展示 NULLCOALESCE(字段, 未知)AVG 计算忽略 NULL 导致虚高直接AVG(字段)分清“实考平均”和“全员平均”后者用AVG(COALESCE(字段, 0))窗口函数排序 NULL 位置不统一默认排序ORDER BY (字段 IS NULL) ASC, 字段 ASC数据迁移时空串与 NULL 混用直接复制数据先做好字段级映射统一口径这张表里每一条都是实打实踩过的坑因为 NULL 在逻辑上太特殊稍有疏忽就会跟业务预期不一致。12. 我个人最后的一点实操建议如果你让我用最简短的话总结 NULL 的学习心得我会说SQL 里的 NULL 不仅是一个“数据值”问题更是一个“语义问题”你要跟产品、数据分析师、后端同事把“NULL 代表什么”对齐才有可能写出真正正确的 SQL。我自己的习惯是在项目初期就建立一份“字段空值规范”明确规定哪些字段允许为 NULL哪些字段必须为 NOT NULL空字符串和 NULL 各自表示什么含义。这份规范不需要多复杂但能省掉后面无数的扯皮。你可以从一张简单的表开始维护字段名类型是否允许 NULL空值业务含义默认值备注user_idINT否无无主键mobileVARCHAR是未填写手机号NULL统计时注意 COUNTremarkVARCHAR是暂无备注NULL展示用 COALESCE(remark, -)amountDECIMAL否金额为 00汇总时直接 SUM这种规范文档不需要写得多花哨但能让开发、测试、运维在处理数据时有一个统一口径。对一个长期演进的项目来说能在最早期就把 NULL 语义定清楚比之后写十个补丁都管用。再补充一个很实用的小技巧当你拿到一张陌生表不确定某些字段是否含有 NULL 时先不要急着写复杂 SQL。你可以在本地跑一句探查语句SELECT COUNT(*) AS total, COUNT(字段A) AS field_a_non_null, COUNT(字段B) AS field_b_non_null FROM 表;如果某个字段的非 NULL 数量明显小于总行数就意味着该字段存在大量 NULL你后续任何跟这个字段相关的计算都要格外小心。很多时候你写出来的 SQL 结果不对并不是语法问题而是你把 NULL 当成了普通值去比较和运算。踩过几次坑之后我现在写任何一句 SQL 都会下意识扫一眼哪些字段可能会是 NULL这些字段参与了等值判断、聚合、字符串拼接还是 JOIN 条件。如果是就提前用COALESCE、NULLIF、IS NULL等手段规避。这套习惯养成之后你写的 SQL 在数据质量上会明显比旁人更稳排查问题的速度也会快很多。希望这篇内容能帮你把 NULL 这个老朋友彻底摸透。
