SQL NULL 值

SQL NULL 值表示字段没有值。NULL 不是空字符串，也不是零，而是表示"无值"或"未知值"的特殊标记。

NULL 值的特性

1. NULL 不等于任何值：包括它自己。NULL = NULL 的结果是 NULL，而不是 TRUE。

2. NULL 与任何值的运算结果都是 NULL：例如 1 + NULL 的结果是 NULL。

3. NULL 在聚合函数中被忽略：例如 SUM()、AVG()、COUNT() 等聚合函数会忽略 NULL 值。

4. NULL 可以存储在任何数据类型的列中：除非该列被定义为 NOT NULL。

语法

检查 NULL 值

SELECT column_name(s)
FROM table_name
WHERE column_name IS NULL;

检查非 NULL 值

SELECT column_name(s)
FROM table_name
WHERE column_name IS NOT NULL;

处理 NULL 值

-- 使用 ISNULL() 函数（SQL Server）
SELECT ISNULL(column_name, default_value)
FROM table_name;

-- 使用 COALESCE() 函数（通用）
SELECT COALESCE(column_name1, column_name2, default_value)
FROM table_name;

-- 使用 IFNULL() 函数（MySQL）
SELECT IFNULL(column_name, default_value)
FROM table_name;

-- 使用 NVL() 函数（Oracle）
SELECT NVL(column_name, default_value)
FROM table_name;

示例

示例 1：创建包含 NULL 值的表

-- 创建表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    grade VARCHAR(20),
    email VARCHAR(100)
);

-- 插入数据，包含 NULL 值
INSERT INTO students (name, age, grade, email)
VALUES ('张三', 18, '高三', 'zhangsan@example.com'),
       ('李四', 17, '高二', NULL),
       ('王五', NULL, '高一', 'wangwu@example.com'),
       ('赵六', 16, NULL, NULL);

-- 查询所有数据
SELECT * FROM students;

执行结果：

id	name	age	grade	email
1	张三	18	高三	zhangsan@example.com
2	李四	17	高二	NULL
3	王五	NULL	高一	wangwu@example.com
4	赵六	16	NULL	NULL

示例 2：查询 NULL 值

-- 查询 age 为 NULL 的学生
SELECT * FROM students
WHERE age IS NULL;

-- 查询 email 为 NULL 的学生
SELECT * FROM students
WHERE email IS NULL;

-- 查询 grade 不为 NULL 的学生
SELECT * FROM students
WHERE grade IS NOT NULL;

-- 查询 age 不为 NULL 且 email 不为 NULL 的学生
SELECT * FROM students
WHERE age IS NOT NULL AND email IS NOT NULL;

示例 3：处理 NULL 值

-- MySQL：使用 IFNULL() 函数
SELECT 
    id, 
    name, 
    IFNULL(age, 0) AS age, 
    IFNULL(grade, '未知') AS grade, 
    IFNULL(email, '无邮箱') AS email
FROM students;

-- SQL Server：使用 ISNULL() 函数
SELECT 
    id, 
    name, 
    ISNULL(age, 0) AS age, 
    ISNULL(grade, '未知') AS grade, 
    ISNULL(email, '无邮箱') AS email
FROM students;

-- Oracle：使用 NVL() 函数
SELECT 
    id, 
    name, 
    NVL(age, 0) AS age, 
    NVL(grade, '未知') AS grade, 
    NVL(email, '无邮箱') AS email
FROM students;

-- 通用：使用 COALESCE() 函数
SELECT 
    id, 
    name, 
    COALESCE(age, 0) AS age, 
    COALESCE(grade, '未知') AS grade, 
    COALESCE(email, '无邮箱') AS email
FROM students;

示例 4：NULL 值在聚合函数中的处理

-- 计算学生的平均年龄（NULL 值被忽略）
SELECT AVG(age) AS average_age
FROM students;

-- 计算学生的数量（COUNT(*) 包含 NULL 值）
SELECT COUNT(*) AS total_students
FROM students;

-- 计算 age 列的非 NULL 值数量
SELECT COUNT(age) AS non_null_age_count
FROM students;

-- 计算 email 列的非 NULL 值数量
SELECT COUNT(email) AS non_null_email_count
FROM students;

示例 5：NULL 值与条件表达式

-- NULL 与任何值的比较结果都是 NULL
SELECT 
    1 = NULL AS compare1, 
    1 <> NULL AS compare2, 
    NULL = NULL AS compare3, 
    1 > NULL AS compare4
FROM dual;

-- 使用 IS NULL 和 IS NOT NULL 检查 NULL 值
SELECT 
    id, 
    name, 
    CASE 
        WHEN age IS NULL THEN '年龄未知'
        WHEN age < 18 THEN '未成年'
        ELSE '成年'
    END AS age_category
FROM students;

注意事项

1. 使用 IS NULL 而不是 = NULL：在 WHERE 子句中，不能使用 = NULL 来检查 NULL 值，必须使用 IS NULL。

2. NULL 值的存储：NULL 值占用的存储空间通常比空字符串少，但具体取决于数据库系统。

3. 索引与 NULL 值：在大多数数据库系统中，NULL 值不会被包含在索引中，这可能会影响查询性能。

4. 外键与 NULL 值：外键列可以包含 NULL 值，此时不会进行引用完整性检查。

5. 默认值与 NULL 值：如果列没有设置默认值，且没有指定 NOT NULL 约束，则默认值为 NULL。

6. NULL 值的传递：在函数调用和表达式中，NULL 值会传递给结果，需要注意处理。

小结

SQL NULL 值是表示"无值"或"未知值"的特殊标记，它具有一些独特的特性。在使用 NULL 值时，需要注意其在比较、计算、聚合函数和索引中的行为。为了处理 NULL 值，可以使用各种函数如 ISNULL()、COALESCE()、IFNULL() 和 NVL() 等。合理使用 NULL 值可以使数据库设计更加灵活，但也需要注意其潜在的问题。