NVL、NVL2 与 COALESCE 函数详解

NVL、NVL2 与 COALESCE 函数详解在 SQL 中NULL表示“未知”或“缺失”的值。当对包含NULL的列进行运算或拼接时结果往往也会变成NULL。为了优雅地处理这些空值Oracle 提供了NVL和NVL2两个专用函数而COALESCE则是 SQL 标准函数在所有主流数据库Oracle、MySQL、PostgreSQL、SQL Server 等中均被支持。一、NVL 函数功能将NULL替换为指定的非空值。语法NVL(expr1,expr2)参数expr1需要检查是否为NULL的表达式。expr2如果expr1为NULL则返回的值。返回值若expr1不为NULL返回expr1。若expr1为NULL返回expr2。数据类型要求expr1和expr2的数据类型必须一致或可以隐式转换例如数值和字符串的转换需谨慎。示例-- 员工佣金为空时显示 0SELECTename,NVL(comm,0)AScommissionFROMemp;-- 字符串拼接电话号码为空时显示 无电话SELECTname,NVL(phone,无电话)AScontactFROMusers;-- 计算总和将空奖金视为 0 参与计算SELECTSUM(NVL(bonus,0))FROMsales;特点Oracle 专用非 SQL 标准。仅接受两个参数逻辑简单。会评估两个参数无短路效应。二、NVL2 函数功能根据表达式是否为NULL返回两个不同值中的一个。它是NVL的增强版提供了“IF-THEN-ELSE”风格的 NULL 处理。语法NVL2(expr1,expr2,expr3)参数expr1需要检查是否为NULL的表达式。expr2如果expr1不为 NULL时返回的值。expr3如果expr1为 NULL时返回的值。返回值若expr1不为NULL→ 返回expr2若expr1为NULL→ 返回expr3示例-- 有佣金的员工奖励 1000 元否则 0 元SELECTename,NVL2(comm,1000,0)ASrewardFROMemp;-- 根据是否有邮箱显示不同状态SELECTuser_name,NVL2(email,已绑定邮箱,未绑定邮箱)ASemail_statusFROMusers;-- 计算总收入有佣金比例时计算工资佣金否则只显示工资SELECTlast_name,salary,NVL2(commission_pct,salary*(1commission_pct),salary)AStotal_incomeFROMemployees;特点Oracle 专用。三个参数第一个是判断条件后两个是结果。仍然会评估所有参数。三、COALESCE 函数功能返回参数列表中第一个非 NULL的值。如果所有参数都是 NULL则返回 NULL。语法COALESCE(expr1,expr2,...,exprn)参数至少两个参数可以更多。返回值从左到右扫描返回第一个非NULL的表达式若全部为NULL则返回NULL。示例-- 从多个联系电话中取第一个有效的SELECTname,COALESCE(mobile,home_phone,work_phone,无电话)AScontactFROMcustomers;-- 商品价格优先级折后价 - 原价 - 默认价 5 元SELECTproduct_id,COALESCE(discount_price,price,5)ASfinal_priceFROMproducts;-- 求值顺序示例短路效应SELECTCOALESCE(NULL,2,3/0)FROMdual;-- 返回 2不会计算 3/0避免除零错误特点SQL 标准函数跨数据库通用。支持两个或更多参数非常灵活。短路求值一旦找到第一个非 NULL 值后续表达式不再求值这对避免错误或提高性能很有用。数据类型要求所有参数必须属于相同的数据类型族否则需要显式转换。四、三者对比总结特性NVLNVL2COALESCE参数个数固定 2 个固定 3 个2 个及以上返回值逻辑NULL→ 替换值NULL→ 值3非NULL→ 值2从左到右第一个非NULL值短路求值❌ 无❌ 无✅ 有标准兼容性Oracle 专有Oracle 专有SQL 标准所有主流 DBMS典型场景简单的 NULL 替换如空值转 0根据 NULL 与否返回不同结果多字段备选值如从多个电话号中选一个五、实战示例三者对比WITHsampleAS(SELECTNULLAScol1,10AScol2FROMdualUNIONALLSELECT5AScol1,NULLAScol2FROMdual)SELECTcol1,col2,NVL(col1,0)ASnvl_col1,-- NULL→0, 非NULL→原值NVL2(col1,col2,999)ASnvl2_result,-- col1非NULL→col2(可能NULL), col1为NULL→999COALESCE(col1,col2,888)AScoalesce_result-- 取第一个非NULLFROMsample;结果col1col2nvl_col1nvl2_resultcoalesce_resultNULL100999105NULL5NULL5六、性能与使用建议简单替换用 NVL当只需要把一列的 NULL 变成某个固定值时NVL最直观。条件分支用 NVL2需要根据列是否为 NULL 产生两个不同的结果时如“有佣金”/“无佣金”NVL2很方便。多字段备选用 COALESCE从多个候选字段中选取第一个有效值是COALESCE的强项。跨数据库迁移优先使用COALESCE因为它遵循 SQL 标准。注意数据类型NVL和NVL2对数据类型转换较宽容但可能导致意外结果COALESCE要求所有参数类型一致更严谨。示例避免除零错误-- 使用 COALESCE 短路特性避免分母为 0SELECTa/COALESCE(NULLIF(b,0),1)FROMdual;-- 若 b0则分母替换为 1