sql-is-null

SQL - 处理 NULL 值

在 SQL 中，NULL 是一个特殊标记，用于表示数据库中不存在数据值。它不同于空字符串（''）或数字零（0）。NULL 值代表缺失或未知信息。由于其特殊性质，你不能使用标准比较运算符（如 = 或 !=）来测试 NULL。

三值逻辑（3VL）

SQL 使用三值逻辑系统：TRUE、FALSE 和 UNKNOWN。任何涉及 NULL 值的比较（例如，5 = NULL 或 NULL = NULL）都评估为 UNKNOWN。查询的 WHERE 子句仅返回条件评估为 TRUE 的行。这就是为什么 WHERE column = NULL 从不返回任何行的原因。

IS NULL 和 IS NOT NULL 运算符

为了正确检查 NULL 值，SQL 提供了 IS NULL 和 IS NOT NULL 运算符。

语法

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

示例：查找信息缺失的客户

让我们在 Customers 表中查找未提供电话号码的客户。

-- 创建一个包含一些 NULL 值的示例表
CREATE TABLE Customers (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    PhoneNumber VARCHAR(20)
);

INSERT INTO Customers VALUES
(1, 'John Doe', 'john.d@example.com', '555-1234'),
(2, 'Jane Smith', 'jane.s@example.com', NULL),
(3, 'Peter Jones', 'peter.j@example.com', '555-5678'),
(4, 'Mary Johnson', 'mary.j@example.com', NULL);

-- 选择电话号码为 NULL 的客户
SELECT Name, Email
FROM Customers
WHERE PhoneNumber IS NULL;

高级 NULL 处理函数

除了简单地检查 NULL 之外，SQL 还提供了强大的函数来处理查询中的 NULL 值，这对于计算和显示至关重要。

COALESCE: 提供默认值

COALESCE 函数返回表达式列表中第一个非 NULL 的值。当列可能为 NULL 时，它非常适合用于替换默认值。

-- 如果 PhoneNumber 为 NULL，则显示 'Not Provided'
SELECT Name, COALESCE(PhoneNumber, 'Not Provided') AS ContactNumber
FROM Customers;

NULLIF: 比较两个表达式

NULLIF(expr1, expr2) 函数在两个表达式相等时返回 NULL；否则，它返回第一个表达式。这对于防止除以零等错误，或将特定值（如空字符串）视为 NULL 很有用。

-- 示例：避免除以零
-- 如果 'quantity' 为 0，NULLIF 会使其变为 NULL，并且除法返回 NULL 而不是错误。
SELECT total_price / NULLIF(quantity, 0) AS price_per_item
FROM sales_data;

NULL 值与聚合函数

聚合函数（如 SUM()、AVG()、MIN() 和 MAX()）在计算中会忽略 NULL 值。然而，COUNT() 的行为不同。

• COUNT(*): 计算组中的所有行，无论是否包含 NULL 值。
• COUNT(column_name): 仅计算 column_name 不为 NULL 的行。

示例：使用 NULL 值计数

-- 使用我们之前的 Customers 表：
SELECT
    COUNT(*) AS TotalCustomers,
    COUNT(PhoneNumber) AS CustomersWithPhone
FROM Customers;

此查询将为 TotalCustomers 返回 4，为 CustomersWithPhone 返回 2，这展示了 COUNT(column) 如何忽略 NULL 值。