ifnull：NULL 值替换

速查结论

ifnull(expr1, expr2) 是 Spark SQL 中用于将 NULL 替换为默认值的函数，语义与 nvl 完全等价。当 expr1 为 NULL 时返回 expr2，否则返回 expr1。注意：ifnull 是 ANSI SQL 标准语法，如果需要在不同 SQL 引擎间迁移代码，优先使用 ifnull 而非 nvl。

语法

ifnull(expr1, expr2)

参数说明

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

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

Since: 2.0.0

示例

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

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

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

数值 NULL 替换为默认值

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

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

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

子查询中的 NULL 保护

-- 确保子查询结果为 NULL 时有兜底值
SELECT
    user_id,
    ifnull((SELECT max(amount) FROM orders WHERE orders.user_id = users.user_id), 0) AS max_amount
FROM users;

ifnull vs nvl vs coalesce 对比

函数	参数个数	返回逻辑	渊源	适用场景
ifnull(expr1, expr2)	固定 2 个	expr1 IS NULL → expr2	ANSI SQL 标准	跨引擎迁移（MySQL / Hive / Spark 通用）
nvl(expr1, expr2)	固定 2 个	与 ifnull 等价	Oracle / Spark SQL	Spark SQL 原生写法，可读性好
coalesce(expr1, ..., exprN)	不限	返回第一个非 NULL 值	ANSI SQL 标准	多级回退，多列优选

-- ifnull: ANSI SQL 标准，跨引擎兼容
SELECT ifnull(col, 0) FROM t;

-- nvl: 语义与 ifnull 等价，Spark SQL / Oracle 风格
SELECT nvl(col, 0) FROM t;

-- coalesce: 多值回退
SELECT coalesce(col1, col2, col3, '全部为空') FROM t;

选型建议：

• 简单 NULL → 默认值，且需要跨平台兼容：用 ifnull
• 简单 NULL → 默认值，仅 Spark SQL：用 nvl（更简洁）或 ifnull 均可
• 多个备选列取第一个非 NULL：用 coalesce

常见报错与避坑指南

参数类型不兼容导致报错

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

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

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

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

ifnull 不会短路求值

即使 expr1 不为 NULL，expr2 中的表达式也会被计算，这可能导致不必要的性能开销。

-- 性能陷阱：expensive_udf() 总会执行
SELECT ifnull(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;

ifnull 无法处理空字符串

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

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

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

📱关注公众号

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

💬加群交流

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