1. COALESCE到底是什么先看一个最典型的痛点场景1.1 从“三值逻辑”说起为什么NULL这么麻烦做SQL开发的人几乎每天都要跟NULL打交道。NULL不是空字符串更不是数字0它表示“未知”。这个“未知”很阴险因为你跟它做任何算术运算或比较结果都会变成NULL。比如你写SELECT NULL 1结果不是1而是NULL写WHERE phone NULL它永远匹配不到任何行因为“未知等于未知”在SQL里是不成立的你必须写成WHERE phone IS NULL。我在实际项目里见过很多次这种问题报表页面某个字段突然空白业务人员以为数据丢了排查半天发现是源表里某个联系方式字段是NULL前端直接显示空白。这种问题不会报错看起来只是“少了点什么”但用户感知非常糟糕。微信、电商、后台管理这类系统里资料不全的用户太常见了电话没有、邮箱没有、备用联系方式也没有这时候你总不能把那一整行记录都藏起来吧。于是COALESCE就派上了用场。它是我在SQL Server里最常用的函数之一优先级甚至高于ISNULL。从SQL Server 2008一直用到2022这个函数的行为一直很稳定不管企业用的是老版本还是新版本都可以放心写。这个函数能做的事情很简单在一串表达式里从左往右找到第一个不是NULL的值并返回它。就这么一个朴素的功能能帮你解决掉一大票“字段可能为空”的显示、计算和拼接问题。1.2 COALESCE的语法三个词就能讲清楚COALESCE的语法非常直白COALESCE(expression1, expression2, ..., expressionN)它接收至少两个参数然后从左到右逐个判断哪个参数不是NULL就返回哪个如果所有参数都是NULL就返回NULL。举个例子SELECT COALESCE(NULL, a, b) -- 返回 a SELECT COALESCE(NULL, NULL, b) -- 返回 b SELECT COALESCE(NULL, NULL, NULL) -- 返回 NULL很多刚入门的朋友会把它和ISNULL搞混因为ISNULL也是取非NULL值SELECT ISNULL(NULL, a) -- 返回 a这两者表面上很像实际差别不小。最明显的一点是参数数量ISNULL只接受两个参数第一个是要检查的表达式第二个是兜底值而COALESCE可以接多个参数能在一个表达式中完成“多级兜底”。比如客户联系方式优先手机号没有手机号就取邮箱再没有就标记为“未知”写成COALESCE(phone, email, 未知)就够了如果用ISNULL就得嵌套写两层。另外COALESCE是SQL标准里的函数ISNULL是SQL Server特有的。虽然平时只在SQL Server里写代码感知不到这个差别但如果以后要迁移到PostgreSQL、MySQL、Oracle这些数据库COALESCE能直接平移过去ISNULL在别的数据库里就不是这个含义了。我在几个跨数据库项目里吃过这个亏所以现在能写COALESCE就尽量不用ISNULL。1.3 初看和ISNULL很像但实际差距很大既然提到了ISNULL就多说几句它们的关键差异避免你以后踩坑。我用一张表把这些区别列出来都是实际开发中真正会遇到的点对比项COALESCEISNULL参数个数至少2个可多个固定2个标准性SQL标准跨数据库通用SQL Server私有类型决定规则按参数列表中优先级最高的类型走按第一个参数的类型走内部实现可改写为CASE WHEN表达式可能被多次评估通常作为标量函数或简单表达式处理嵌套写法多级兜底一行搞定需要 ISNULL(ISNULL(a,b),c) 嵌套这里最坑的是类型决定规则。COALESCE在选择返回类型时会看所有参数里的数据类型优先级谁优先级高就转成谁。而ISNULL是老老实实按照第一个参数的类型来。我举个例子你就明白了SELECT COALESCE(NULL, 1, 2) -- 结果是什么 SELECT ISNULL(NULL, 1) -- 结果是什么COALESCE(NULL, 1, 2)的结果是整数1因为int的数据类型优先级比varchar高SQL Server会把整个表达式按int处理字符串2如果被选中也会转成数字2。而ISNULL(NULL, 1)的结果也是1逻辑上没什么争议。但如果你反过来写ISNULL(NULL, 2)它结果就是字符串2因为它尊重第一个参数的类型。这种差异平时没什么感觉一旦参数里混了日期、数字、字符串就很容易出现隐式转换错误后面第4章我会详细讲这个。2. 五个高频场景COALESCE的正确打开方式2.1 查询结果兜底让报表不再“一片空白”最基础的用法就是查询结果兜底。比如你要导出一份客户通讯录里面包含手机号、邮箱、微信号三列。但实际数据里总有用户什么都不留直接导出的话Excel里就是空单元格拿给业务部门看人家第一句话肯定是“数据怎么是缺的”。用COALESCE一行搞定SELECT CustomerName, COALESCE(Phone, Email, WeChat, 未填写) AS ContactInfo FROM Customers;这样哪个联系方式都没有的客户至少会显示“未填写”三个字业务人员一看就明白不是数据丢失而是用户确实没留。这里我特别想提醒一句COALESCE只处理NULL如果某个字段是空字符串它会认为这个值“存在”直接返回空字符串并不会走到下一个字段。这个坑非常常见后面第4章我会专门讲怎么配合NULLIF处理。2.2 多列数据合并从多个字段里挑“第一口奶”还有一种场景很常见数据表设计得比较松散同一个信息拆成了好几列比如客户有三个联系电话Phone1、Phone2、Phone3业务上只需要展示第一个能联系上的号码。这时候COALESCE就是天然的“优先取数器”SELECT CustomerName, COALESCE(NULLIF(Phone1, ), NULLIF(Phone2, ), NULLIF(Phone3, ), 无电话) AS ContactPhone FROM Customers;注意我这里顺手加了NULLIF因为空字符串在业务上也应该被当成“没填”。这个问题在真实数据里太常见了系统A导入的接口把空电话写成了NULL系统B写成了空字符串最后汇总到一起你光靠COALESCE根本兜不住。另一个典型场景是地址拼接。省市区分列存储但页面要展示完整地址。如果直接写SELECT Province City District Address只要其中任何一列是NULL整体结果就是NULL因为字符串和NULL拼接等于NULL。正确做法是给每个字段加默认空串SELECT COALESCE(Province, ) COALESCE(City, ) COALESCE(District, ) COALESCE(Address, ) AS FullAddress这样即便某个字段缺失也能拼出剩余部分不会整串地址消失。这个写法在报表开发里出现频率非常高我愿称之为“地址拼接标准解法”。2.3 聚合计算的“脏数据”清理SUM/AVG前的NULL处理聚合函数SUM、AVG遇到NULL时会忽略但结果可能不符合业务预期。比如你要统计一个月的订单总金额SELECT SUM(OrderAmount) FROM Orders WHERE OrderDate 2025-01-01;如果所有订单的OrderAmount都是NULLSUM返回的是NULL而不是0。在很多报表工具里NULL会被渲染成空或者“无数据”看起来就很奇怪。用COALESCE包一层就能改成0SELECT COALESCE(SUM(OrderAmount), 0) FROM Orders WHERE OrderDate 2025-01-01;注意我这里是包在SUM外面不是包在列上。有人喜欢写成SUM(COALESCE(OrderAmount, 0))这也能达到类似目的但从性能上讲前者让SQL Server少算一次非NULL判断而且逻辑更清晰先求和结果为空再归零。这里要强调一个业务意识NULL不是0把NULL转成0这件事必须确认业务上“没有金额”和“金额为0”是不是同一个意思。比如订单金额为NULL有可能是订单还没结算这时候如果显示0老板可能以为这是个免费单。碰上这种情况你应该在报表里显示“未结算”而不是拿COALESCE硬转0。工具是死的业务判断是活的。2.4 动态拼接SQL或字符串避免整个结果变成NULL写存储过程或动态SQL的时候COALESCE也特别好用。假设你要在存储过程里拼接一个筛选条件参数有可能为空DECLARE city VARCHAR(50) NULL; DECLARE sql VARCHAR(MAX); SET sql SELECT * FROM Customers WHERE 11 AND City city ; PRINT sql;如果city是NULL整个字符串就变成NULL了后面一执行就报错。以前不少人用ISNULL来解决SET sql SELECT * FROM Customers WHERE 11 AND City ISNULL(city, ) ;用COALESCE也可以而且如果你有多个可选参数需要拼接它写起来更从容。我在做报表系统的搜索接口时经常写这样的代码SELECT COALESCE(city, ) COALESCE(district, ) COALESCE(keyword, ) AS SearchText这种拼接场景里NULL就像多米诺骨牌只要有一张牌是NULL后面全盘变NULL。所以你的潜意识里应该形成一个反射看见拼接字符串第一反应就是给每个字段套COALESCE或者ISNULL。2.5 优雅替代复杂CASE WHEN做默认值映射有些开发者在做默认值映射时习惯写CASE WHEN其实逻辑简单的时候COALESCE更干净。比如状态字段1是“待审核”2是“已通过”其余显示“其他”SELECT CASE Status WHEN 1 THEN 待审核 WHEN 2 THEN 已通过 ELSE 其他 END AS StatusText FROM Orders;这种映射用COALESCE不太合适因为它是等值映射不是“取第一个非NULL”。但有一种情况适合字段本身可能是NULL也可能是空字符串你要给一个默认显示值。此时COALESCE组合NULLIF是最优雅的写法SELECT COALESCE(NULLIF(Status, ), 未知) AS StatusTextNULLIF的作用是如果两个参数相等返回NULL不等返回第一个参数。所以NULLIF(Status, )会把空字符串变成NULL然后COALESCE就会走到默认值 未知 上。这个组合拳比写一长串CASE WHEN简单多了下面第3章会有更完整的实操演示。3. 实操记录从建表到优化的完整流程3.1 建一张带NULL值的测试表并造数据光说不练假把式。我直接建一张测试表数据故意做得脏一点NULL和空字符串都有方便你对照结果。以下代码在SQL Server 2012及以上版本都可以直接跑。CREATE TABLE Customers ( CustomerID INT IDENTITY(1,1) PRIMARY KEY, CustomerName NVARCHAR(50), Phone NVARCHAR(20), Email NVARCHAR(100), Province NVARCHAR(20), City NVARCHAR(20), District NVARCHAR(20), Address NVARCHAR(200), OrderAmount DECIMAL(10,2), Status VARCHAR(10) ); INSERT INTO Customers (CustomerName, Phone, Email, Province, City, District, Address, OrderAmount, Status) VALUES (N张三, N13800000001, Nzhangsantest.com, N浙江省, N杭州市, N西湖区, N文一西路100号, 100.00, 1), (N李四, NULL, Nlisitest.com, N江苏省, N苏州市, N工业园区, N星湖街328号, NULL, 2), (N王五, N13900000002, NULL, N上海市, N上海市, NULL, NULL, 50.00, ), (N赵六, NULL, NULL, NULL, NULL, NULL, NULL, 200.00, NULL), (N孙七, N, N, N广东省, N深圳市, N南山区, N科技园路1号, NULL, 1);这张表覆盖了几种典型情况字段全空、部分为空、空字符串、NULL和有效值混在一起。后面所有查询例子都基于它。3.2 用COALESCE跑几个真实查询先看最基础的联系方式兜底查询SELECT CustomerName, COALESCE(Phone, Email, 无联系方式) AS ContactInfo FROM Customers;结果如下CustomerNameContactInfo张三13800000001李四lisitest.com王五13900000002赵六无联系方式孙七(空字符串因为Phone是不是NULL)注意孙七这一行Phone是空字符串COALESCE认为它不是NULL所以直接返回了空字符串。这就是前面反复强调的坑COALESCE不认识空字符串。要正确处理得加NULLIFSELECT CustomerName, COALESCE(NULLIF(Phone, ), NULLIF(Email, ), 无联系方式) AS ContactInfo FROM Customers;再看地址拼接。一共四个字段其中District和Address都有空值。直接用拼会得到一批NULL用COALESCE包一下SELECT CustomerName, COALESCE(Province, ) COALESCE(City, ) COALESCE(District, ) COALESCE(Address, ) AS FullAddress FROM Customers;结果里赵六那行因为省市区全部是NULL拼出来是空字符串。到这里你应该意识到COALESCE解决的是“NULL导致整体变NULL”的问题但解决不了“空字符串显示出来很难看”的问题。所以字段值真的是空串时可能需要再套一层NULLIF或者在前端显示层做处理。这属于业务层的取舍。然后是金额汇总SELECT COALESCE(SUM(OrderAmount), 0) AS TotalAmount, COUNT(*) AS OrderCount FROM Customers;这里把SUM结果包在COALESCE外层比逐行COALESCE列值再SUM要干净也少一些无谓的计算开销。最后看一个状态字段的默认值映射用NULLIFCOALESCE组合SELECT CustomerName, COALESCE(NULLIF(Status, ), 未知) AS StatusText FROM Customers;结果里王五的空字符串会变成“未知”赵六的NULL也会变成“未知”孙七的1不受影响。这个写法在处理外部系统导入的数据时特别省心。3.3 与CASE WHEN改写对比复杂逻辑中的可读性有人说这些逻辑我全都能用CASE WHEN写何必学COALESCE。这话没错但我建议你看一眼两种写法的代码量。比如“手机优先没有手机取邮箱再没有显示无联系方式”-- 写法ACOALESCE SELECT COALESCE(NULLIF(Phone,), NULLIF(Email,), 无联系方式) FROM Customers; -- 写法BCASE WHEN SELECT CASE WHEN NULLIF(Phone, ) IS NOT NULL THEN NULLIF(Phone, ) WHEN NULLIF(Email, ) IS NOT NULL THEN NULLIF(Email, ) ELSE 无联系方式 END FROM Customers;两种写法结果一样但CASE WHEN版本里NULLIF(Phone, )被写了两次。万一以后要改成判断其他字段你得分两处改漏一处就出bug。COALESCE版本里每个参数只写一次天然更利于维护。当然如果某个字段的判断不是简单的“非NULL”而是复杂的比较逻辑比如“当金额大于1000时取A大于100时取B”那就别硬套COALESCE了这时CASE WHEN才是正确工具。我的原则是能用简单函数解决的绝不堆复杂语句但复杂逻辑来了也别为了“函数风格统一”而强行简化。写SQL首先要让人看得懂其次才是炫技。4. 容易踩的坑与排查技巧4.1 参数个数与数据类型最隐蔽的坑COALESCE支持多个参数这是它的优势但也是出问题的地方。因为参数一多SQL Server就要决定整个表达式最终是什么数据类型。它的规则是从所有参数中选一个数据类型优先级最高的然后把其他参数隐式转换成这个类型。SQL Server的数据类型优先级大致是datetimeintdecimalvarchar等具体优先级表很长但你只需要记住一个原则数字和时间类型的优先级通常高于字符串。看一个经典的反面例子SELECT COALESCE(NULL, 1, abc);你以为它会返回1实际上它的确返回1因为int的优先级高于varchar整个表达式在编译阶段就被定为int类型了。如果第一个非NULL值是字符串ab那问题就大了因为ab转换不成数字直接抛转换错误。反过来SELECT COALESCE(NULL, abc, 1);这个又会怎样int依然优先所以 abc 还是会被尝试转成int结果同样是转换失败哪怕 abc 排在前面。这就是COALESCE最反直觉的地方不是谁在前面谁说了算而是谁的类型级别高谁说了算。这类问题在SQL Server里最常见的报错是“conversion failed when converting the varchar value abc to data type int”。不少初学者看到这个报错一脸懵觉得我明明没有做转换。实际上就是COALESCE在自动做类型统一时引发的。排查方法也很简单如果COALESCE里混用了数字和字符串优先考虑把所有数字参数显式转换成VARCHAR或者把所有非字符串参数用CAST转成统一类型。别指望隐式转换帮你干脏活。4.2 数据类型转换失败的典型报错日期和时间类型也经常出问题。我看到过有人在COALESCE里写SELECT COALESCE(OrderDate, 1900-01-01)当OrderDate是datetime类型时第一个非NULL的值会被转成datetime1900-01-01如果被选中也会被转成日期看起来没问题。但如果你用的是带小时分钟格式的字符串比如1900-01-01 00:00:00有些语言环境或排序规则下也可能出现转换失败。热搜词里有一条“sql server conversion failed when converting date and/or time fr”指的就是这类日期时间转换失败的报错。这类错误有一个通用的排查套路先确认COALESCE每个参数的数据类型是什么。再用SELECT CAST(参数 AS 目标类型)单独跑一遍定位是哪个参数转不过去。最后把该参数用CAST或CONVERT显式转成目标类型再放进COALESCE里。比如最稳妥的写法是SELECT COALESCE(OrderDate, CAST(1900-01-01 AS DATETIME))显式CAST之后SQL Server不会再用自己的隐式规则去猜报错概率会小很多。而且代码读起来也清楚别人一眼就知道默认值是个日期。4.3 NULL与空字符串的混淆业务语义大不同我见过不止一个同事在代码评审时被问到“为什么这个字段是空字符串你用COALESCE给它兜底却没用”因为COALESCE的触发条件只有一个NULL。空字符串是另一个东西它在SQL里是有值的长度为0的字符串不是“未知”。业务系统里出现空字符串通常有三个来源接口对接时上游系统传了空字符串而不是NULL。页面表单没做必填校验用户提交了空字符串。ETL同步时源系统用空字符串表示“无”目标表也照单全收。处理空字符串的通用方案就是NULLIF配合COALESCE。NULLIF(column, )会把空字符串转成NULL然后COALESCE继续往下找。这样你就把两种“空”统一成一种“无”再输出默认值。强烈建议你在数据清洗阶段就把空字符串统一成NULL免得每个查询都写一遍NULLIF。4.4 性能误区COALESCE不会拖慢查询但别在索引列上随意兜底很多人担心加了COALESCE会变慢。在绝大多数查询里COALESCE本身只是表达式执行计划里会被优化成条件判断或常量计算性能影响可以忽略不计。真正要担心的是你把COALESCE用在WHERE条件的索引列上。比如SELECT * FROM Orders WHERE COALESCE(Status, 0) 1;这条查询看起来能查出“没有状态也当0处理”的行但问题在于COALESCE(Status, 0)是一个计算表达式SQL Server很难对这段表达式做索引查找常常会退化成全表扫描。这就是所谓的“非SARGable”写法。数据量小无所谓表一上百万行性能差异就出来了。更好的写法是把这个条件拆开SELECT * FROM Orders WHERE Status 1 OR Status IS NULL;这条查询逻辑上更接近“状态是1或者状态没填也认”在Status上有索引的情况下执行计划可以用到索引。所以我的经验是COALESCE用在SELECT子句里做显示兜底没问题用在WHERE过滤条件里要格外小心。如果必须做NULL兜底过滤优先考虑改写OR条件或者用UNION ALL拆成两段查询。5. 一个藏在语法树里的冷知识COALESCE等价于CASE的展开5.1 SQL Server内部如何解析COALESCE有些资深的DBA会告诉你COALESCE就是语法糖SQL Server在编译时会把COALESCE(a, b, c)改写成等价的CASE表达式CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END这个改写背后的影响很实在表达式a、b、c有可能会被求值多次。如果参数只是普通列多次求值没什么成本但如果你传的是子查询、标量函数、或者复杂的计算表达式那就有隐患了。比如SELECT COALESCE( (SELECT TOP 1 Remark FROM OrderLog WHERE OrderID o.OrderID), 无备注 ) FROM Orders o;理论上第一个非NULL子查询就会被返回但CASE展开后如果第一段子查询返回NULL优化器可能还会去执行第二遍甚至更多遍。虽然SQL Server的优化器有时会做公共表达式提取但你不能百分百依赖它。碰到这种情况我更推荐先把子查询结果放到变量或派生表里再在外面套COALESCE避免重复执行。5.2 和ISNULL的执行计划差异性能层面ISNULL和COALESCE在绝大多数场景下生成的执行计划都差不多甚至看不出区别。但在某些特定写法下COALESCE被展开成CASE后可能让你的SQL看起来在执行计划里多了一个Compute Scalar操作而ISNULL则被优化器当成简单的标量函数处理。我见过有人在网上争论“COALESCE比ISNULL慢”实测下来通常差别可以忽略因为你大多数情况下优化器会把两边都简化成同样的常量表达式。真正拉开性能差距的往往是写法本身而不是函数选择。比如在索引列上套COALESCE无论你用哪个函数都会破坏索引寻址能力。所以结论很简单别把精力花在“哪个函数快1毫秒”上多关注索引列是否被包在表达式里收益大得多。这个冷知识虽然不影响你日常写代码但能帮你理解为什么某些复杂查询的执行计划里会出现重复的表达式评估。知道了原理排查问题时思路就会宽很多。6. 实用技巧让COALESCE更好用的三个小贴士6.1 与NULLIF配合处理空字符串这是我最推荐的组合没有之一。COALESCE只认NULLNULLIF可以把空字符串变成NULL两者配合等于同时处理了“真空”和“假空”两种状态。SELECT CustomerName, COALESCE(NULLIF(Phone, ), NULLIF(Email, ), 无联系方式) AS ContactInfo FROM Customers;既然业务系统里NULL和空字符串经常混着来我建议你在写报表代码时把它当成默认模板。甚至可以对多个字段都套一层NULLIF虽然写起来长一点但结果绝对是可靠的。这比我见过某些项目里用REPLACE(column, , 无)的野路子要安全得多。6.2 在UPDATE中使用COALESCE做增量填充COALESCE不是只能用在查询里写UPDATE时也能发挥大作用。比如你有一张客户表老数据里电话字段是空的现在从另一个系统同步了一批新电话过来但你只想填空不想覆盖已经有效的电话。这时候直接用UPDATE SET很危险因为会把已有数据覆盖掉。正确的做法是用COALESCE当“保护壳”UPDATE Customers SET Phone COALESCE(Phone, NewPhone) WHERE NewPhone IS NOT NULL;这条语句的意思是如果原来的电话已经是NULL就用新值填充如果原来有值就保留原值。这在ETL增量回填、数据修复、接口补偿更新等场景里非常实用。我曾经处理过一个会员系统数据修复任务几十万条记录里混着一批“只有新电话没有老电话”的数据就是靠这条语句干净利落地完成了回填全程没有误伤一条已有数据。6.3 在INSERT中配合默认值简化ETL写INSERT的时候也可以利用COALESCE来设置默认值。比如从临时表导入正式表临时表里有些字段允许为空但正式表需要展示层有一个可见值INSERT INTO Customers (CustomerName, Phone, Email, Status) SELECT CustomerName, COALESCE(Phone, 未知), COALESCE(Email, 未知), COALESCE(NULLIF(Status, ), 0);在导入阶段就把NULL和空字符串统一成业务默认值后面查询、报表、接口就都不用再到处加兜底逻辑了。我一直强调数据清洗要做在源头不要做在使用时。在写入端用COALESCE整理一次后续所有消费端都能省心。最后再分享一个个人习惯每次写完一段SQL我都会专门扫一遍“哪些列可能是NULL、哪些列可能是空字符串”然后决定要不要套COALESCE和NULLIF。这个习惯帮我避免了很多次报表数据异常。SQL Server里处理NULL的函数不少但真正稳定好用的组合就是COALESCE加NULLIF。这一章笔记先写到这下一章如果再写SQL Server笔记我大概率会接着聊CASE WHEN和NULLIF的进阶玩法毕竟它们三个经常要一起出场。
)
