SQL Null 函数

SQL NULL 值处理：COALESCE, ISNULL, NVL, IFNULL

NULL 值在计算中的挑战

在 SQL 中，NULL 表示缺失、未知或不适用的值。一个常见的挑战是，当 NULL 值参与算术运算或字符串连接时，它们通常会传播，导致整个表达式的结果为 NULL。本节将探讨标准和特定于 DBMS 的函数，以通过用默认值替换 NULL 来优雅地处理 NULL 值。

说明性场景：产品库存计算

考虑一个 Products 表，如果当前没有订购单位，其中的 UnitsOnOrder 可能为 NULL：

ProductID	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	Jarlsberg	10.45	16	15
2	Mascarpone	32.56	23	NULL
3	Gorgonzola	15.67	9	20
4	Mozzarella	12.00	30	NULL

如果我们尝试像这样计算总潜在库存价值：

SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder) AS PotentialValue FROM Products;

对于 UnitsOnOrder 为 NULL 的产品（例如 Mascarpone 和 Mozzarella），PotentialValue 也会是 NULL，因为 任意数字 + NULL 的结果是 NULL。

处理 NULL 的解决方案

为了解决这个问题，SQL 提供了几种函数。最标准和可移植的是 COALESCE()。

1. COALESCE(expression1, expression2, ..., default_value) (SQL 标准)

COALESCE() 返回其参数列表中第一个非 NULL 的表达式。由于其标准化和灵活性，它是首选方法。

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) AS PotentialValue
FROM Products;

在此查询中，如果 UnitsOnOrder 为 NULL，COALESCE(UnitsOnOrder, 0) 将返回 0，从而允许计算正确进行。

为什么通常首选 COALESCE：

• SQL 标准：确保在不同数据库系统之间具有更好的可移植性。
• 灵活性：可以接受多个参数，返回第一个非 NULL 的参数。例如，COALESCE(optional_discount, default_discount, 0)。

2. 特定于 DBMS 的函数（使用时需注意可移植性）

虽然 COALESCE 是标准函数，但各种 DBMS 提供自己的函数来替换 NULL，这些函数通常具有略微不同的行为或语法：

SQL Server: ISNULL(check_expression, replacement_value)

ISNULL() 用指定的 replacement_value 替换 NULL。它只接受两个参数。结果的数据类型由 check_expression 决定。

SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) AS PotentialValue
FROM Products; -- 适用于 SQL Server

Oracle: NVL(expression1, expression2)

NVL() 检查 expression1 是否为 NULL。如果是，它返回 expression2；否则，返回 expression1。数据类型必须兼容或可隐式转换。

SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0)) AS PotentialValue
FROM Products; -- 适用于 Oracle

Oracle 还有 NVL2(expression1, value_if_not_null, value_if_null) 以及 COALESCE 函数。

MySQL: IFNULL(expression1, expression2)

IFNULL() 在 expression1 为 NULL 时返回 expression2；否则，返回 expression1。

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) AS PotentialValue
FROM Products; -- 适用于 MySQL

MySQL 也有 ISNULL(expression) 函数，但它的工作方式不同：如果 expression 为 NULL，它返回 1 (true)；否则返回 0 (false)。它是一个测试函数，而不是替换函数。因此，在 MySQL 中使用 IFNULL 或 COALESCE 进行替换。

MS Access: Nz(variant, valueifnull)

Nz() (Null Zero) 函数在 Variant 为 NULL 时返回零、零长度字符串（""）或另一个指定的值。

SELECT ProductName, UnitPrice * (UnitsInStock + Nz(UnitsOnOrder, 0)) AS PotentialValue
FROM Products; -- 适用于 MS Access 查询

现代版本的 MS Access 通常也通过 ODBC/OLE DB 连接到功能更强大的数据库引擎来支持 COALESCE。

建议总结

• 优先使用 COALESCE()：对于新的开发项目，使用 COALESCE() 来替换 NULL 值，以确保更好的代码可移植性并遵循 SQL 标准。
• 理解 DBMS 特性：如果处理现有代码库或需要特定的 DBMS 功能，请了解 ISNULL()、NVL() 和 IFNULL() 等函数，但要理解它们在可移植性方面的局限性。
• 测试 NULL 条件：始终考虑 NULL 值可能如何影响您的查询，并在必要时包含明确的处理，以防止出现意外的 NULL 结果或错误。

在 WHERE 子句中处理 NULL：请记住，WHERE column_name = NULL 或 WHERE column_name != NULL 通常不会按预期工作。请改用 WHERE column_name IS NULL 或 WHERE column_name IS NOT NULL。