深入理解 SQL 中的 NULL

在 SQL 中，NULL 表示一个缺失的值或未知的值。它不同于空字符串或数字 0，空字符串是一个确定的空文本值，数字 0 是一个确定的数值，而 NULL 代表值的不确定性。例如，在一个员工信息表中，如果某个员工的手机号码尚未录入，那么该员工记录中的手机号码字段值就可以为 NULL。

目录

  1. 基础概念
  2. 使用方法
  3. 常见实践
  4. 最佳实践
  5. 小结

基础概念

在 SQL 中，NULL 表示一个缺失的值或未知的值。它不同于空字符串或数字 0，空字符串是一个确定的空文本值，数字 0 是一个确定的数值，而 NULL 代表值的不确定性。

例如，在一个员工信息表中，如果某个员工的手机号码尚未录入，那么该员工记录中的手机号码字段值就可以为 NULL。

使用方法

插入 NULL 值

在创建表时定义了允许为 NULL 的列后，可以在插入数据时插入 NULL 值。 假设有一个简单的学生表 students：

CREATE TABLE students (
    student_id INT,
    student_name VARCHAR(50),
    age INT,
    address VARCHAR(100)
);

插入包含 NULL 值的数据：

INSERT INTO students (student_id, student_name, age, address)
VALUES (1, 'Alice', NULL, 'Beijing');

这里 age 字段的值为 NULL，表示该学生的年龄未知。

查询 NULL 值

不能使用常规的比较运算符（如 =）来查询 NULL 值，需要使用 IS NULL 或 IS NOT NULL 关键字。 查询 students 表中年龄为 NULL 的学生：

SELECT * 
FROM students 
WHERE age IS NULL;

查询年龄不为 NULL 的学生：

SELECT * 
FROM students 
WHERE age IS NOT NULL;

更新 NULL 值

可以将现有记录中的某个字段更新为 NULL，或者将 NULL 值更新为其他值。 将 student_id 为 1 的学生的地址更新为 NULL：

UPDATE students 
SET address = NULL 
WHERE student_id = 1;

将 student_id 为 1 的学生的年龄更新为 20（将 NULL 更新为具体值）：

UPDATE students 
SET age = 20 
WHERE student_id = 1;

删除 NULL 值相关记录

删除 students 表中年龄为 NULL 的记录：

DELETE FROM students 
WHERE age IS NULL;

常见实践

在 WHERE 子句中处理 NULL

在 WHERE 子句中使用逻辑运算符（如 AND、OR）时，NULL 的处理规则较为特殊。 例如，假设有一个产品表 products，包含 product_id、product_name 和 price 字段（price 可能为 NULL）。

-- 以下查询中，如果 price 为 NULL，整个条件将为 NULL，不会返回任何记录
SELECT * 
FROM products 
WHERE price > 10 AND price < 20; 

-- 使用 IS NOT NULL 确保排除 price 为 NULL 的记录
SELECT * 
FROM products 
WHERE price IS NOT NULL AND price > 10 AND price < 20;

NULL 与聚合函数

聚合函数（如 SUM、AVG、COUNT 等）对 NULL 的处理方式各不相同。

  • SUM 和 AVG：在计算时会忽略 NULL 值。 假设有一个销售记录表 sales，包含 sale_id、product_id 和 quantity 字段（quantity 可能为 NULL）。
-- 计算销售总量，忽略 quantity 为 NULL 的记录
SELECT SUM(quantity) 
FROM sales;
  • COUNT：
    • COUNT(*) 会统计所有记录，包括包含 NULL 值的记录。
    • COUNT(column_name) 会忽略指定列中值为 NULL 的记录。
-- 统计 sales 表中的总记录数
SELECT COUNT(*) 
FROM sales; 

-- 统计 quantity 不为 NULL 的记录数
SELECT COUNT(quantity) 
FROM sales;

ISNULL 函数和 COALESCE 函数的使用场景对比

  • ISNULL 函数：在 SQL Server 中使用，用于将 NULL 值替换为指定的值。
-- 如果 age 为 NULL，将其替换为 0
SELECT ISNULL(age, 0) 
FROM students;
  • COALESCE 函数：在多种数据库中都可用，它可以接受多个参数，返回第一个非 NULL 的值。
-- 如果 age 为 NULL，尝试使用默认值 18；如果默认值也为 NULL，返回 NULL
SELECT COALESCE(age, 18) 
FROM students; 

-- 可以有多个参数，返回第一个非 NULL 的值
SELECT COALESCE(NULL, NULL, 'default value')

最佳实践

设计表结构时对 NULL 的考虑

在设计表结构时，应谨慎决定哪些列允许为 NULL。尽量避免在经常用于连接、排序或条件判断的列上允许 NULL 值，因为这可能会增加查询的复杂性和性能开销。例如，在订单表和客户表的连接字段（如客户 ID）上，应确保不能为空，这样可以简化连接操作和提高查询效率。

避免 NULL 值对索引的影响

在某些数据库中，NULL 值可能会对索引产生不良影响。例如，在创建索引时，如果索引列包含大量 NULL 值，可能会导致索引的维护成本增加，并且查询性能下降。可以考虑通过设置默认值（如空字符串或适当的默认数字）来避免 NULL 值出现在索引列中。

代码可读性与 NULL 处理的平衡

在编写 SQL 代码时，要注意处理 NULL 值的方式，以保持代码的可读性。过多复杂的 NULL 处理逻辑可能会使代码难以理解和维护。例如，在使用 COALESCE 函数时，参数数量不宜过多，尽量将复杂的 NULL 处理逻辑封装成函数或视图，以提高代码的可维护性。

小结

NULL 在 SQL 中是一个重要且特殊的概念，正确理解和处理 NULL 值对于编写高效、准确的 SQL 查询和维护数据库的完整性至关重要。从基础概念到使用方法，再到常见实践和最佳实践，每个方面都需要我们仔细把握。通过合理设计表结构、谨慎处理 NULL 值在查询和聚合操作中的使用，以及注重代码的可读性和性能平衡，我们能够更好地利用 SQL 来管理和操作数据。希望本文能帮助读者更深入地理解和运用 SQL 中的 NULL。