SQL COALESCE 与 NULLIF 使用案例

coalesce用于兜底NULL，但禁止在where中滥用导致索引失效；nullif用于条件转null防除零或清洗重复值；二者嵌套需注意null传播与类型兼容性，跨库迁移应显式cast。

COALESCE 用来兜底 NULL，但别在 WHERE 里滥用 COALESCE 的核心作用是按顺序取第一个非 NULL 值，常用于字段默认值兜底。比如查用户昵称，优先用 nike_name，没有就 fallback 到 user_id：

SELECT COALESCE(nike_name, CAST(user_id AS TEXT)) AS display_name FROM users;

常见错误是把它塞进 WHERE 条件里做“模糊匹配”：

❌ WHERE COALESCE(phone, '') LIKE '%123%' —— 这会强制全表扫描，索引失效

✅ 改成：WHERE phone IS NOT NULL AND phone LIKE '%123%'，再单独处理 NULL 分支

注意：所有参数类型必须兼容，否则 postgresql 会报 Error: COALESCE types text and Integer cannot be matched；mysql 和 SQL Server 会隐式转换，但行为不可靠。

NULLIF 防止除零或重复值，本质是“条件性转 NULL” NULLIF(a, b) 等价于 “如果 a = b，返回 NULL，否则返回 a”。最典型场景是避免除零：

SELECT amount / NULLIF(quantity, 0) AS unit_price FROM orders;

另一个实用场景是清洗重复的占位符值：

比如把空字符串、'N/A'、'unknown' 统一转为 NULL，再用 COALESCE 统一兜底：

COALESCE(NULLIF(trim(status), ''), 'active')

⚠️ 注意：比较是严格相等（包括类型），NULLIF('1', 1) 在 PostgreSQL 中不成立（字符串 vs 整数），不会返回 NULL；MySQL 可能隐式转，但别依赖。

COALESCE 和 NULLIF 嵌套时，执行顺序和 NULL 传播要盯紧 嵌套本身合法，但容易误判结果。例如：

SELECT COALESCE(NULLIF(x, 0), 999) FROM (VALUES (0), (1), (NULL)) t(x);

结果是：999、1、999 —— 因为 NULLIF(NULL, 0) 返回 NULL（NULL = 0 为 UNKNOWN，不触发替换）。 关键点：

• NULLIF 对 NULL 输入直接返回 NULL，不比较

• COALESCE 遇到 NULL 就继续往后找，直到非 NULL 或耗尽

• 所有中间表达式都可能产生 NULL，尤其涉及函数调用（如 trim()、upper()）后接 NULLIF

不同数据库对 NULLIF/COALESCE 的 NULL 处理逻辑一致，但类型推导有差异 标准 SQL 规定两者都遵循“first non-null type”规则，但实际中：

• PostgreSQL 严格按参数列表顺序推导类型，COALESCE('a', 1) 报错

• MySQL 宽松些，会尝试转成 double 或 VARCHAR，但结果可能意外截断或精度丢失

• SQL Server 允许混合类型，但以左侧参数类型为准，右侧强制转换 —— COALESCE(GETDATE(), '2020-01-01') 返回 DATETIME

跨库迁移时，别假设类型自动对齐；显式 CAST 最稳妥，比如：COALESCE(CAST(price AS NUMERIC), 0.0) NULL 处理看着简单，但嵌套 + 类型 + 索引影响三者叠加，最容易在上线后才发现慢查询或数据偏差。特别是 COALESCE 被用在索引字段上时，那一行的执行计划得亲手 EXPLAIN 一遍。