开头这一期的话题是Oracle里最不起眼、却又最能坑人的东西——NULL。做数据库开发这些年我见过太多人在NULL上翻车WHERE条件写了 NULL查不到数据、NOT IN子查询莫名其妙返回空集、排序时NULL跑到了最前面、Kettle同步数据时空字符串变成了NULL、报表汇总时金额算出来是空白的……每一种都让人抓狂。这一期就把NULL的用法系统地过一遍从三值逻辑、查询筛选、排序分页、函数处理到聚合去重、实战排查把常见的坑和标准解法都摊开讲清楚适合刚入门Oracle的开发者也适合写了好几年SQL、但遇到NULL语义依然心里没底的同行。1. 先搞明白NULL到底是什么它不是零也不是空字符串1.1 三值逻辑SQL里多出来的UNKNOWN我们平时写程序条件判断只有真和假两个结果但SQL不一样因为有了NULL比较逻辑变成了三个值TRUE、FALSE和UNKNOWN。NULL表示“未知”或者“缺失”它不是数值0也不是字符串里的空字符。这个“未知”的含义决定了它的行为方式任何值和NULL做比较结果都不是TRUE也不是FALSE而是UNKNOWN。举个例子你写WHERE salary NULLOracle不会报错但它永远匹配不到任何行因为salary NULL这个表达式的值不是TRUE而是UNKNOWNWHERE子句只认TRUE所以结果就是空。很多人刚开始学Oracle时都栽在这里明明表里有数据就是查不出来然后怀疑数据丢了、怀疑权限不够最后才发现是NULL比较的问题。这点和Java、Python这类编程语言很不一样。编程语言里x null是合法的判断SQL里你却必须写成x IS NULL或者x IS NOT NULL。所以第一课就是判断NULL不能用等号必须用IS NULL / IS NOT NULL。这一条看着简单却是整个NULL语义的根基。1.2 空字符串在Oracle里等于NULL这里要特别提醒从MySQL转过来的朋友。MySQL里空字符串和NULL是两个完全不同的东西空字符串有长度、能比较、能索引。但Oracle里不是这样和NULL是同一个东西你插入时Oracle自动把它当成NULL存储。提示在Oracle中处理字符串时必须避免的判断逻辑WHERE name 永远不会命中任何数据因为就是NULL等号比较再次失效。这个特性影响很广。最典型的是ETL场景Kettle、Informatica从外部文件抽数据时源文件里的空字符串到了Oracle就变成了NULL。很多做数据清洗的同行被这个坑折磨过明明源系统里该字段有一堆空白值同步到Oracle后查总数却发现少了很多一查才发现空串全被当成NULL了COUNT(字段)根本数不到它们。理解了Oracle的这个设计处理方案就清晰了要么在同步前用Kettle的“将空字符串替换为NULL”选项手动控制行为要么在SQL里统一用NVL等函数兜底。存储层面还有一个有意思的现象全NULL的行不会进入普通B树索引。因为索引需要键值而NULL没有确定的值所以单列索引里根本找不到全NULL的记录。这意味着WHERE 某列 IS NULL走不进正常索引这就是为什么IS NULL的查询往往很慢。解决办法后面会专门讲。2. 查询与筛选中NULL的三大经典坑2.1 NULL查不到数据IN和NOT IN也有隐藏陷阱除了前面说的判断失效IN和NOT IN这两个最常用的操作符遇到NULL时也有一堆幺蛾子。IN的语义其实是一组OR等值比较。WHERE col IN (1, 2, 3)等价于col 1 OR col 2 OR col 3。如果列表里有NULL比如col IN (1, NULL)因为col NULL是UNKNOWNOR运算符的结果是由TRUE决定的只要col等于1结果就是TRUE所以这个场景下NULL对IN的影响相对较小——只要其他条件命中就会返回行。真正的大坑在NOT IN。WHERE col NOT IN (1, 2, 3)的语义是col 1 AND col 2 AND col 3。如果列表里出现NULL比如col NOT IN (1, NULL)就变成了col 1 AND col NULL而col NULL的结果永远是UNKNOWN。AND逻辑里只要有一个分支是UNKNOWN在没有FALSE的情况下整个结果就是UNKNOWN于是所有行都会被过滤掉。我当年在一张订单表里执行过这样一个查询SELECT order_id, status FROM orders WHERE status NOT IN (CLOSED, PAID, NULL);结果返回了空集当时我还以为是业务数据出了问题。后来排查发现数据里明明有大量OPEN状态的记录问题就出在这个多余的NULL。正确写法是用NOT EXISTS配合子查询因为EXISTS做的只是存在性判断不受NULL影响SELECT o.order_id, o.status FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM blacklist_status b WHERE b.status o.status );注意只要NOT IN的子查询结果集可能包含NULL就该换用NOT EXISTS或者先排除NULL否则结果必然为空。2.2 IS NULL的查询优化为什么这么慢刚才提到普通的B树索引不会存储全NULL的键值所以WHERE 某列 IS NULL通常走全表扫描。这不是Oracle笨而是索引结构决定的。那实际业务里怎么处理几种常见方案如果业务上NULL有特殊含义建议给列设置默认值尽量避免NULL这是最彻底的解法。建函数索引比如需要频繁查“邮箱为空的用户”可以建CREATE INDEX idx_users_email_null ON users(NVL(email, 0))然后用WHERE NVL(email, 0) 0来查询这样就能命中索引。复合索引里如果其他列有值索引是可以包含这行的利用这个特性可以把IS NULL条件和其他过滤条件一起设计索引。实际工作中我还见过一种场景一张大表的某个日期字段频繁参与IS NULL判断DBA直接建了NVL(日期字段, DATE9999-12-31)的函数索引改写SQL之后执行计划从全表扫描变成了索引范围扫描性能提升了几个数量级。这个思路值得借鉴。2.3 过滤掉“不能转为数字”的字符串NULL的妙用最近在技术群里看到有人问Oracle里怎么把一个字段中不能转为数字的脏数据过滤掉比如一个VARCHAR2列里面既有数字也有字母想只保留能转成数字的记录。这个需求在数据迁移、接口对接时特别常见。单纯用TO_NUMBER(col)过滤不可转数字的字符串遇到字母直接报ORA-01722根本执行不下去。常规解法是结合正则表达式先判断SELECT col FROM your_table WHERE REGEXP_LIKE(col, ^[0-9]$) AND TO_NUMBER(col) IS NOT NULL;这里REGEXP_LIKE(col, ^[0-9]$)已经把格式不匹配的过滤掉剩下的理论上都能转数字。但严谨的写法还会加一个TO_NUMBER(col) IS NOT NULL条件做二次保护因为字符串里的前后空格、正负号、科学计数法这些边界情况单靠正则不容易全覆盖。这个场景反过来也说明NULL在Oracle里承担着“非法值标记”的角色只要你懂得用IS NULL来判断转换失败的结果就能稳健地处理脏数据。判断字符串是否包含某个子串时也常涉及NULL比如搜索名字里包含“张”的人如果name字段是NULLINSTR(name, 张)直接返回NULLWHERE条件里同样匹配不上。这类搜索需求要么给字段加默认值要么用NVL(name, )包一层否则搜索会漏数据。3. 排序和分页NULL到底排前面还是排后面3.1 默认行为升序时NULL最大降序时NULL最小NULL在排序里的行为是Oracle特有的设计之一。默认情况下使用ASC升序排列时NULL排在其他所有值之后使用DESC降序排列时NULL排在其他所有值之前。也就是说Oracle默认把NULL当成“比所有非NULL值都大”来处理。这个行为跟很多人的直觉相反——很多人觉得NULL应该排最前面才对。我当时带过一个新手他写报表排序时发现NULL全排在最后以为数据有问题把ORDER BY col DESC改成ORDER BY col结果NULL反而跑到了最前面他直接懵了。听完我解释默认规则他才明白。所以写排序SQL前务必先想清楚业务上希望NULL出现在哪里。3.2 用NULLS FIRST / NULLS LAST精确控制Oracle提供了两个关键字来控制NULL的位置NULLS FIRST表示NULL排最前NULLS LAST表示NULL排最后。这两个关键字弥补了默认行为不可控的问题也是我写排序SQL时必加的东西SELECT emp_name, salary FROM employees ORDER BY salary DESC NULLS LAST;上面这个查询把员工的薪资从高到低排同时保证薪资为空的记录放在最后。这种写法在生成报表时特别有用避免了NULL值插在数据中间干扰阅读。相信我只要你有过一次因为NULL乱序导致报表被业务方打回的经历就会养成每次ORDER BY都要考虑NULL位置的习惯。3.3 分页查询中NULL带来的大坑重复和丢数据Oracle分页最经典的是ROWNUM方式配合排序使用。但有一个细节很多人没注意如果ORDER BY的列里包含大量NULL且没有其他唯一性字段兜底分页就可能出现同一行在多个页码里反复出现同时另一些行永远查不到。为什么会这样因为数据库不保证排序的稳定性和全局唯一顺序。当排序列有大量重复值包括大量NULL时Oracle每次执行查询可能选择不同的执行计划行在排序结果里的相对位置就变了。ROWNUM分页是“先排序后取数”如果排序本身不稳定第1页和第2页就可能取到重复行。解决方案很直接在ORDER BY里加唯一字段作为二级排序SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT emp_id, emp_name, salary FROM employees ORDER BY salary DESC NULLS LAST, emp_id ) t WHERE ROWNUM 40 ) WHERE rn 20;我习惯在企业项目里统一采用这种“排序键 主键”的双层排序方式既能明确NULL位置又能保证分页稳定。尤其在用ROWNUM实现“每页20条取第21到40条”这种需求时这个细节能帮你避免大量线上事故。如果你用的是12c及以上的版本OFFSET/FETCH语法本身就要求ORDER BY后的排序键尽量唯一否则同样会有隐患。4. 运算与函数处理让NULL变成可用值4.1 NULL的传播性一个NULL毁掉整行计算NULL最让人头疼的是它的“传播性”。任何数值运算只要遇到NULL结果就是NULL。比如一个订单表里有商品单价和数量但数量字段忘了填那单价 * 数量的结果不是0而是NULL报表里显示为空求和时这一行也会被SUM直接忽略。字符串拼接更坑。Oracle里||拼接如果遇到NULL结果还是NULL——注意不是跳过NULL继续拼接而是整个结果变NULL。举例SELECT first_name || || last_name AS full_name FROM employees;如果last_name是NULL这一行的full_name就是NULL而不是只显示first_name。只有一个字段是NULL整行姓名就没了这是初学Oracle时最容易忽略的坑之一。处理思路就两条要么事前规范数据禁止关键字段为NULL要么事后兜底用NVL、COALESCE等函数把NULL替换成合理的默认值。我倾向于两条腿走路上线前靠约束保证数据完整查询时靠函数兜底保证展示正确。数据是死的业务却要一直跑兜底函数是必须的。4.2 NVL、NVL2、COALESCE、NULLIF的取舍Oracle里处理NULL的函数有四个用途各有不同函数语法说明典型场景NVLNVL(expr1, expr2)expr1为NULL时返回expr2否则返回expr1把NULL替换成默认值最常用NVL2NVL2(expr1, expr2, expr3)expr1非NULL返回expr2为NULL返回expr3根据NULL与否返回两种结果COALESCECOALESCE(expr1, expr2, ...)从左到右返回第一个非NULL值多列取第一个有效的值NULLIFNULLIF(expr1, expr2)两值相等时返回NULL否则返回expr1主动制造NULL比如除零保护用生活化类比解释NVL像保险条款里的“默认受益人”原值作废时自动顶上COALESCE像多个备胎按顺序排队谁先能用就用谁NULLIF则相反是刻意制造“空档”来触发保护逻辑。实际开发中我喜欢这样组合使用。比如工资计算绩效为NULL时不想让总额变成NULLSELECT emp_name, base_salary NVL(performance_bonus, 0) AS total_salary FROM employees;多字段取第一个非NULL值比如联系人电话手机没有就取座机座机没有就取备用号码SELECT COALESCE(mobile_phone, office_phone, backup_phone, 未登记) AS contact_phone FROM customers;除零保护是NULLIF的经典用法SELECT sales_amount / NULLIF(quantity, 0) AS unit_price FROM sales;当quantity为0时NULLIF返回NULL整个除法结果变成NULL而不是报ORA-01476除数为0错误。之后再包一层NVL就可以展示“无数据”这样的业务文案。4.3 DECODE和CASE里NULL的易错写法DECODE是Oracle特有的函数它在处理NULL时有一个细节DECODE(col, NULL, 空, 非空)是能正确判断NULL的因为DECODE做的是内部NULL相等比较而不是SQL的等号语义。这一点跟WHERE子句完全不同很多人不知道。而CASE表达式的写法要特别注意顺序和逻辑SELECT CASE WHEN col IS NULL THEN 空 WHEN col A THEN A类 ELSE 其他 END AS col_label FROM your_table;这里先判断IS NULL再判断具体值顺序反了就会出错。比如写成WHEN col A THEN ... WHEN col IS NULL THEN ...如果col是NULL是能走到第二个分支的但如果把WHEN col A放在前面NULL的行会跳过第一个分支继续判断逻辑上还能走通只是效率稍差。真正危险的是有人用WHEN col A来筛非A数据NULL的行根本不会进入这个分支——因为NULL A也是UNKNOWN。写条件判断时任何“非什么什么”的语义都要把NULL单独拎出来处理这已经是老生常谈的经验了。5. 聚合、去重、分组里的NULL特例5.1 COUNT(*)和COUNT(列)的差异一直被忽视的统计陷阱COUNT(*)统计的是行数不管这一行有多少NULLCOUNT(列)统计的是该列非NULL值的个数。这两个函数看着差不多统计结果却可能差很多。数据仓库里做质量巡检时我经常用这个差异来快速发现空值比例异常SELECT COUNT(*) AS total_rows, COUNT(customer_id) AS has_customer_id, COUNT(*) - COUNT(customer_id) AS missing_customer_id FROM orders;只要一跑哪个字段缺失严重一目了然。这种写法比写一堆WHERE 某列 IS NULL高效得多而且能在一行里看到全貌。SUM、AVG等聚合函数同样忽略NULL但需要注意一个细节如果某列全部为NULLSUM返回NULL而不是0AVG的除数是非NULL行的数量而不是总行数这两个行为经常导致报表数据异常。稳妥的做法是先NVL成0再聚合或者对聚合结果再用一次NVL。5.2 DISTINCT和GROUP BY多个NULL算一组使用DISTINCT去重时所有的NULL会被合并为一组也就是说结果里只会出现一个NULL。这个行为跟MySQL一致但很多人在做数据比对时依然会困惑明明原始表里有5行该字段为NULL去重后只剩1行了这其实是SQL标准默认行为。GROUP BY同理NULL值会单独形成一个分组。这在报表统计里很实用比如按状态字段分组统计订单量NULL状态的订单会单列一组不会丢失。但如果业务上想把NULL并到“未知”分组处理办法是分组前先NVLSELECT NVL(status, 未知) AS status_group, COUNT(*) AS cnt FROM orders GROUP BY NVL(status, 未知);这样分组结果里就没有NULL那一组了全部归入“未知”报表更友好。5.3 关联查询NULL永远匹配不上外键为NULL意味着什么JOIN操作里NULL的行为也需要注意。内连接INNER JOIN时任何一方的关联字段为NULL这行都不会出现在结果里因为NULL NULL不是TRUE。所以哪怕两个表里都有同一行数据只要关联键是NULL内连接就查不出来。这种情况在业务上经常导致“莫名其妙少数据”——比如关联客户表查订单详情客户ID为NULL的订单全部丢了。外连接LEFT/RIGHT JOIN里如果驱动表的关联键为NULL匹配不上的行仍然会保留但被驱动表的字段会是NULL。这个行为倒是符合直觉但设计报表时要注意LEFT JOIN后出现的NULL有两种含义一种是右表真的没有匹配数据另一种是右表匹配上了但该字段本来就为NULL分析数据时不能混为一谈。关联键含NULL时如果业务语义上需要“等价对待”常见的处理是两边都用NVL把NULL替换成相同的占位值再关联SELECT a.order_id, b.customer_name FROM orders a LEFT JOIN customers b ON NVL(a.customer_id, UNKNOWN) NVL(b.customer_id, UNKNOWN);但这招慎用因为NVL会让索引失效而且把多个真正的NULL客户强行聚到了一起可能产生笛卡尔积式的关联爆炸。通常我的建议是数据建模阶段就禁止关联键为NULL这是根治方案。6. 实战经验与常见问题排查实录6.1 Kettle同步时空字符串到底要不要转NULL关于Kettle和Oracle之间的数据同步有一个高频困惑表输入或表输出时要不要勾选“将空字符串替换为NULL”选项。我在多个项目里踩过这个坑说说我的结论。如果目标表是Oracle建议勾选。原因很简单Oracle本身就把空串当NULL你不勾选Kettle会尝试把空字符串 写入Oracle的VARCHAR2列Oracle自动把它转为NULL看起来没区别但有些驱动版本的转换不够干净可能出现“看起来有值但实际是NULL”的尴尬状态。与其让转换不可控不如在Kettle里明确处理。如果目标下游还有非Oracle系统或者做数据比对时需要区分“空串”和“NULL”那就要慎重。比如下游接了MySQL你对源系统字段定义要求严格空字符串表示“用户显式填了空”NULL表示“从未填写”这时最好在Oracle侧也用一个默认值字符串来代替空串而不是依赖Oracle的NULL化。简单说同步这件事宁可显式转换不要依赖隐式行为。6.2 PL/SQL变量和存储过程里的NULLPL/SQL里声明一个变量但没赋初值时变量自动为NULL这一点用存储过程的人常常忽视。比如你声明一个v_count NUMBER;判断时直接写IF v_count 0 THEN这段逻辑永远不成立因为NULL和0不相等。正确写法是IF NVL(v_count, 0) 0 THEN。这个案例我印象太深了。之前排查一个存储过程业务方反馈“某条件下应该执行更新却没有执行”查了很久发现就是变量初始值为NULL导致判断不通过。变量在声明时给一个明确的默认值是个好习惯比如数字给0、日期给SYSDATE、字符串给空串——虽然Oracle的空串还是NULL但至少逻辑上有意识地控制了行为。6.3 常见问题速查表我把这些年遇到的高频NULL问题整理成一张速查表适合贴在手边随时查阅现象原因解法WHERE col NULL 查不到数据等值比较不适用于NULL改写成 WHERE col IS NULLNOT IN 子查询包含NULL返回空集NULL参与AND逻辑传播改用NOT EXISTS或排除NULL字符串拼接结果为空任一拼接项为NULLNVL(col, )包一层算术运算结果为NULLNULL参与运算传播参与运算的列NVL成0排序结果NULL乱序升序NULL在尾、降序NULL在头ORDER BY ... NULLS FIRST/LAST分页出现重复或丢数据排序列含大量NULL且无唯一键排序键加主键兜底聚合SUM返回NULL全部行都是NULL外层包NVL(SUM(col), 0)COUNT(字段)结果偏少该字段大量为NULL确认统计口径后再选函数JOIN匹配不上数据关联键为NULL模型上禁止关联键为NULLKettle同步后空值变NULLOracle不区分空串和NULL同步选项或数据规范统一处理6.4 排除思路小结排查NULL相关问题时我的一般套路是三步走。第一步确认数据和字段的NULL占比用SELECT COUNT(*), COUNT(col), COUNT(DISTINCT col) FROM ...快速摸底别一上来就盯着SQL本身。第二步看SQL是否涉及比较、连接、聚合、排序这四类操作每一种都有对应的NULL陷阱对照上面的速查表逐个排除。第三步用一个小结果集做单元验证手工构造几行NULL数据执行同样的SQL看结果是否符合预期。有一个原则务必贯穿始终NULL的语义一定要跟产品经理确认。业务上“没有值”到底该显示成“0”“未知”还是“暂无”直接决定你的SQL怎么写。技术上读懂NULL的行为只是基本功把NULL的语义翻译成正确的业务口径才是真正的经验。这一期关于NULL的内容就到这儿了。回顾这些案例时我最大的感受是NULL相关的坑几乎都不是“语法不会”造成的而是对“未知值参与计算和比较会产生什么结果”缺乏预判。写SQL的时候多问自己一句这一列有没有可能是NULL如果有我的写法扛不扛得住把这个问题变成习惯能少踩一半的坑。最后再分享一个小技巧建表时所有字段都养成“非空优先确需可空则明确默认值”的习惯能从源头上给全团队省下大量排查NULL问题的时间。