sql-null-functions

SQL - 处理 NULL 值

在 SQL 中，NULL 是一种特殊的标记，用于表示数据库中数据值不存在。它不同于空字符串 '' 或数字 0。理解如何正确处理 NULL 对于编写准确无误的查询至关重要。

1. NULL 的概念和三值逻辑

可以将 NULL 理解为“未知”或“不适用”。由于它代表一个未知值，NULL 在比较时的行为有所不同：

• NULL 与任何其他值（包括另一个 NULL）的比较结果是 UNKNOWN（未知），而不是 TRUE（真）或 FALSE（假）。
• 这就是为什么 column = NULL 或 column != NULL 不会按预期工作的原因。WHERE 子句只包含评估结果为 TRUE 的行。
• 此系统被称为三值逻辑（TRUE、FALSE、UNKNOWN）。

示例数据

让我们使用一个 CUSTOMERS 表，其中一些 SALARY（薪资）和 ADDRESS（地址）值缺失（为 NULL）。

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(10, 2)
);

INSERT INTO CUSTOMERS VALUES
    (1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
    (2, 'Khilan', 25, 'Delhi', 1500.00),
    (3, 'Kaushik', 23, NULL, NULL),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00),
    (5, 'Komal', 22, 'Hyderabad', NULL);

2. 检查 NULL 值：IS NULL 和 IS NOT NULL

根据 NULL 过滤行的正确方法是使用 IS NULL 和 IS NOT NULL 运算符。

示例：查找没有薪资信息的客户。

SELECT NAME, SALARY
FROM CUSTOMERS
WHERE SALARY IS NULL;

输出：

姓名	薪资
Kaushik	NULL
Komal	NULL

3. 用默认值替换 NULL：COALESCE

COALESCE 函数是替代 NULL 值的标准且最通用的方法。它接受一个参数列表，并返回遇到的第一个非 NULL 值。

语法

COALESCE(value1, value2, ..., valueN)

示例：在报告中将 NULL 薪资显示为 ‘Not Provided’（未提供）。

请注意，我们必须将替换值强制转换为与列的数据类型匹配。

SELECT
    NAME,
    SALARY,
    COALESCE(CAST(SALARY AS VARCHAR(20)), 'Not Provided') AS DisplaySalary
FROM CUSTOMERS;

示例：使用备用列。

假设您有一个 CONTACT_NUMBER（联系电话）和一个 WORK_NUMBER（工作电话）。您想显示联系电话，但如果它是 NULL，则使用工作电话。

SELECT NAME, COALESCE(CONTACT_NUMBER, WORK_NUMBER, 'No number available') AS PrimaryContact FROM USERS;

4. 特定平台的替代方案

虽然 COALESCE 是标准函数，但许多关系型数据库管理系统（RDBMS）都有自己的函数。识别它们很有用，但通常更推荐使用 COALESCE 以确保可移植性。

• ISNULL(expression, replacement_value) (SQL Server)：类似于一个接受两个参数的 COALESCE。请注意数据类型优先级规则，它们可能与 COALESCE 不同。
• IFNULL(expression, replacement_value) (MySQL)：功能上与 SQL Server 的 ISNULL 相同。
• NVL(expression, replacement_value) (Oracle)：Oracle 的等效函数。

5. 使用 NULLIF 比较表达式

NULLIF 函数接受两个参数，如果它们相等则返回 NULL。否则，它返回第一个参数。这对于数据清洗很有用，例如将特定的占位符字符串转换为真正的 NULL。

语法

NULLIF(expression1, expression2)

示例：将 ‘N/A’ 视为 NULL。

如果某个列存储字符串 ‘N/A’ 来表示缺失数据，您可以将其转换为真正的 NULL 以进行计算。

-- 假设有一个名为 'product_weight' 的 VARCHAR 列，有时包含 'N/A'。
SELECT product_name, NULLIF(product_weight, 'N/A') AS CleanedWeight FROM Products;

6. NULL 常见陷阱

• 使用 = 或 != 与 NULL：最常见的错误。始终使用 IS NULL 或 IS NOT NULL。
• 聚合函数（Aggregate Functions）：SUM()、AVG()、MAX() 等函数会忽略 NULL 值。这通常是期望的行为，但您必须意识到这一点。例如，AVG(SALARY) 是非 NULL 薪资的平均值，而不是总薪资除以客户总数。
• 字符串连接（String Concatenation）：在许多 RDBMS（如 SQL Server）中，将字符串与 NULL 连接会得到 NULL。例如，FirstName + ' ' + NULL 结果为 NULL。使用 COALESCE 来处理这种情况：FirstName + ' ' + COALESCE(LastName, '')。