nvl：NULL 值替换

速查结论

nvl(expr1, expr2) 是 Spark SQL 中用于将 NULL 替换为默认值的函数。当 expr1 为 NULL 时返回 expr2，否则返回 expr1。注意：nvl 只接受2个参数，如需多值回退请使用 coalesce（参数数量不限制），如需 ANSI SQL 标准语法请使用 ifnull（语义等价）。

语法

nvl(expr1, expr2)

参数说明

参数	说明
expr1	待检查的表达式
expr2	当 expr1 为 NULL 时的替代值

返回值：如果 expr1 为 NULL，则返回 expr2；否则返回 expr1。返回类型为 expr1 和 expr2 的公共类型（common type）。

Since: 2.0.0

示例

基础用法 — NULL 替换为默认值

-- expr1 为 NULL，返回 expr2
SELECT nvl(NULL, array('2'));
-- 结果: ["2"]

-- expr1 不为 NULL，返回原值
SELECT nvl('hello', 'world');
-- 结果: hello

数值 NULL 替换为默认值

-- 将 NULL 销售额替换为 0
SELECT
    product_id,
    nvl(sales_amount, 0) AS sales_amount
FROM sales;

字符串 NULL 替换为默认占位符

-- 将 NULL 用户名替换为 'Unknown'
SELECT nvl(username, 'Unknown') AS username
FROM users;

嵌套使用 — 多层 NULL 回退

-- nvl 只有2个参数，但可以通过嵌套实现3级回退
SELECT nvl(col1, nvl(col2, 'default_value'))
FROM t;

嵌套3层以上时，建议改用 coalesce(col1, col2, col3, 'default_value')，可读性更好。

nvl vs nvl2 vs coalesce vs ifnull 对比

这些函数都与 NULL 处理相关，但适用场景不同：

函数	参数个数	返回逻辑	适用场景
nvl(expr1, expr2)	固定 2 个	expr1 IS NULL → expr2	简单 NULL 替换（Spark SQL 特有语法）
nvl2(expr1, expr2, expr3)	固定 3 个	expr1 IS NOT NULL → expr2，否则 → expr3	NULL / 非 NULL 双分支取值
coalesce(expr1, ..., exprN)	不限	返回第一个非 NULL 值	多级回退，多列优选
ifnull(expr1, expr2)	固定 2 个	等同于 nvl	ANSI SQL 标准语法，跨引擎迁移首选

-- nvl: expr1 为 NULL 时返回默认值
SELECT nvl(col, 0) FROM t;

-- nvl2: expr1 不为 NULL 时返回 expr2，否则返回 expr3
SELECT nvl2(col, '有值', '无值') FROM t;

-- coalesce: 多列中取第一个非 NULL 值
SELECT coalesce(mobile, phone, email, '无联系方式') AS contact
FROM users;

-- ifnull: 语义与 nvl 一致，ANSI SQL 标准
SELECT ifnull(col, 0) FROM t;

nvl 与 nvl2 的核心区别

nvl 和 nvl2 虽然名字相似，但逻辑截然不同：

对比维度	nvl	nvl2
参数个数	2 个	3 个
判断逻辑	expr1 IS NULL → 返回 expr2	expr1 IS NOT NULL → 返回 expr2，否则 → 返回 expr3
使用场景	"如果是 NULL，用默认值替代"	"如果是 NULL 怎么办，不是 NULL 又怎么办"

-- nvl: 只处理 NULL 的情况
SELECT nvl(status, 0) FROM orders;
-- status 为 NULL → 0，status 有值 → 原值

-- nvl2: 同时处理 NULL 和 非 NULL 两种情况
SELECT nvl2(status, '已处理', '未处理') FROM orders;
-- status 不为 NULL → '已处理'，status 为 NULL → '未处理'

选型建议：

• 简单 NULL → 默认值：用 nvl，简洁直观
• NULL / 非 NULL 两种路径取不同值：用 nvl2
• 多个备选列取第一个非 NULL：用 coalesce
• 跨平台 SQL 兼容（如从 Hive / MySQL 迁移）：用 ifnull 或 coalesce

常见报错与避坑指南

参数类型不兼容导致报错

nvl(expr1, expr2) 要求两个参数的类型能够隐式转换为公共类型，否则会抛出 AnalysisException。

-- 错误写法：STRING 和 INT 没有公共类型
SELECT nvl(name, 0) FROM users;
-- AnalysisException: cannot resolve 'nvl(name, 0)' due to data type mismatch

-- 正确写法：统一为同类型或可隐式转换的类型
SELECT nvl(name, '0') FROM users;

-- 也可以使用 CAST 显式转换
SELECT nvl(name, CAST(0 AS STRING)) FROM users;

nvl 不会短路求值

这是一个常见的性能陷阱：即使 expr1 不为 NULL，expr2 中的表达式也会被计算。

-- 性能陷阱：expensive_udf() 总会执行
SELECT nvl(col, expensive_udf(col)) FROM t;
-- 即使 col 不为 NULL，expensive_udf 仍然会计算每一行

-- 避免非必要计算：用 CASE WHEN 实现短路
SELECT CASE WHEN col IS NULL THEN expensive_udf(col) ELSE col END
FROM t;

nvl 无法处理空字符串

nvl 只识别 NULL，不识别空字符串 ''。

-- nvl 对空字符串无效
SELECT nvl('', 'default');
-- 结果: ''（空字符串，不是 'default'）

-- 如需同时处理 NULL 和空字符串，用 CASE WHEN
SELECT CASE WHEN col IS NULL OR col = '' THEN 'default' ELSE col END
FROM t;

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题