sql-null-values

SQL - 处理 NULL 值

1. 理解 NULL：‘未知’ 的概念

在 SQL 中，NULL 是一个特殊标记，用于表示数据库中不存在数据值。至关重要的是要理解，NULL 不同于零 (0)、空字符串 ('') 或布尔值 false。它代表一个缺失或未知的值。

类比：想象一个表格中有一个“中间名”字段。如果一个人没有中间名，该字段就会留空。这种值的缺失就是 NULL 所代表的。这并非意味着他们的中间名是“零”或一个空词；它仅仅是未知或不适用。

2. 比较的陷阱：NULL 与三值逻辑

SQL 中一个基本规则是，您不能使用标准比较运算符（=、!=、<、>）来测试 NULL。这是因为任何涉及 NULL 的算术或比较操作都会导致 NULL（未知）。

-- 这些比较不会按预期工作
SELECT * FROM customers WHERE salary = NULL; -- 错误
SELECT * FROM customers WHERE salary != NULL; -- 错误

NULL = NULL 的结果不是 TRUE，而是 UNKNOWN（未知）。要正确检查 NULL 值，您必须使用 IS NULL 和 IS NOT NULL 运算符。

3. 定义列：NOT NULL 约束

默认情况下，表中的列可以包含 NULL 值。为了强制列始终必须有一个值，您可以在创建表时使用 NOT NULL 约束。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL, -- 此列不能为 NULL
    department VARCHAR(50) NOT NULL,   -- 此列也不能为 NULL
    last_known_address VARCHAR(255)    -- 此列可以为 NULL
);

尝试插入一行时，如果 first_name 或 department 没有值（或明确将它们设置为 NULL），将导致数据库错误。

4. 过滤 NULL 值：IS NULL 和 IS NOT NULL

让我们使用一个示例 customers 表，其中一些薪资信息缺失。

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

INSERT INTO customers VALUES
    (1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
    (2, 'Khilan', 25, 'Delhi', 1500.00),
    (3, 'Kaushik', 23, 'Kota', 2000.00),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00),
    (5, 'Hardik', 27, 'Bhopal', 8500.00),
    (6, 'Komal', 22, 'Hyderabad', NULL), -- 薪资未知
    (7, 'Muffy', 24, 'Indore', NULL);      -- 薪资未知

要查找所有薪资已知的客户，请使用 IS NOT NULL：

SELECT ID, NAME, SALARY FROM customers WHERE SALARY IS NOT NULL;

要查找所有薪资缺失的客户，请使用 IS NULL：

SELECT ID, NAME FROM customers WHERE SALARY IS NULL;

5. 实践中处理 NULL 值：UPDATE 和 DELETE

您可以在 UPDATE 和 DELETE 语句的 WHERE 子句中使用 IS NULL 来定位缺失数据的行。例如，让我们为所有薪资为 NULL 的客户分配一个默认薪资 5000.00。

-- 更新所有当前薪资缺失的记录
UPDATE customers
SET SALARY = 5000.00
WHERE SALARY IS NULL;

同样，您可以删除缺少关键信息的记录：

-- 这将从表中删除 Komal 和 Muffy
-- 使用 DELETE 语句要小心！
DELETE FROM customers WHERE SALARY IS NULL;

6. 替换 NULL 值以进行计算：COALESCE() 及其他函数

在执行计算或显示数据时，NULL 值可能会带来问题。SQL 提供了用默认值替换 NULL 的函数。最标准的函数是 COALESCE()。

COALESCE(value1, value2, ...) 返回其参数列表中第一个非 NULL 的值。它非常适合提供一个默认值。

-- 显示所有客户，但如果薪资为 NULL 则显示 0.00
SELECT
    NAME,
    COALESCE(SALARY, 0.00) AS display_salary
FROM customers;

注意：某些数据库有自己的函数，例如 MySQL 中的 IFNULL(value, default) 和 SQL Server 中的 ISNULL(value, default)。COALESCE 是 ANSI SQL 标准，更具可移植性。

7. NULL 值与聚合函数：一个重要的区别

聚合函数对 NULL 值的处理方式不同，这通常是错误的常见来源：

• COUNT(column_name)： 统计 column_name 不为 NULL 的行数。
• COUNT(*)： 统计总行数，无论是否包含 NULL 值。
• SUM()、AVG()、MIN()、MAX()： 这些函数在计算时都会忽略 NULL 值。例如，AVG(SALARY) 将是已知薪资的总和除以已知薪资的客户数量。

SELECT
    COUNT(*) AS total_customers, -- 结果：7
    COUNT(SALARY) AS customers_with_salary, -- 结果：5
    AVG(SALARY) AS average_of_known_salaries, -- 基于 5 人计算，而非 7 人
    AVG(COALESCE(SALARY, 0)) AS average_with_zero_default -- 基于所有 7 人计算，NULL 值按 0 处理
FROM customers;