NULL值处理

NULL是SQL中最特殊的值，本篇详细介绍NULL的处理方法和注意事项。

📊 支持情况

1. NULL检测

IS NULL / IS NOT NULL

-- 检测NULL
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- 检测NOT NULL（Oracle）
SELECT * FROM users WHERE email IS NOT NULL;

IS NULL优化

-- ✅ 正确写法
SELECT * FROM orders WHERE order_date IS NULL;

-- ❌ 错误写法
SELECT * FROM orders WHERE order_date = NULL;  -- 永远返回false

2. NULL替换

COALESCE

-- MySQL / Oracle / Hive
SELECT
    COALESCE(phone, 'N/A') AS phone,
    COALESCE(address, city, '未知') AS location
FROM users;

-- 多参数
SELECT COALESCE(col1, col2, col3, '默认值') FROM t;

IFNULL（MySQL）

SELECT IFNULL(phone, 'N/A') AS phone FROM users;

NVL（Oracle）

SELECT NVL(phone, 'N/A') AS phone FROM users;
SELECT NVL(commission_pct, 0) FROM employees;

NVL2（Oracle）

-- 如果为NULL返回值1，否则返回值2
SELECT
    NVL2(commission_pct, '有提成', '无提成') AS commission_status
FROM employees;

3. NULLIF

避免除零错误

-- MySQL / Oracle / Hive
SELECT
    amount / NULLIF(quantity, 0) AS unit_price
FROM products;

-- NULLIF返回NULL而不是报错
SELECT 10 / NULLIF(0, 0);  -- NULL（安全）

数据对比

-- 比较两个值，相同返回NULL
SELECT NULLIF('abc', 'abc');  -- NULL
SELECT NULLIF('abc', 'def');  -- 'abc'

4. 聚合函数中的NULL

SUM/AVG/COUNT

-- COUNT - 不计数NULL
SELECT COUNT(*) FROM t;         -- 所有行
SELECT COUNT(col) FROM t;       -- 非NULL的行

-- SUM - 忽略NULL
SELECT SUM(amount) FROM orders;  -- 自动忽略NULL

-- AVG - 分母不含NULL
SELECT AVG(amount) FROM orders;

处理空值

-- 用0填充后计算
SELECT
    SUM(COALESCE(bonus, 0)) AS total_bonus,
    AVG(COALESCE(bonus, 0)) AS avg_bonus
FROM employees;

5. 实战案例

场景1：字段默认值

-- MySQL - 显示默认值
SELECT
    user_id,
    COALESCE(user_name, '匿名用户') AS display_name,
    COALESCE(phone, '暂无') AS phone,
    COALESCE(email, CONCAT('user', user_id, '@default.com')) AS email
FROM users;

场景2：计算列处理

-- 计算毛利率
SELECT
    product_id,
    price,
    cost,
    CASE
        WHEN price IS NULL OR cost IS NULL THEN NULL
        WHEN cost = 0 THEN NULL  -- 避免除零
        ELSE ROUND((price - cost) / cost * 100, 2)
    END AS profit_margin
FROM products;

场景3：条件统计

-- 统计有销售和无销售的员工
SELECT
    COUNT(*) AS total,
    COUNT(sales_amount) AS with_sales,
    COUNT(*) - COUNT(sales_amount) AS without_sales
FROM employees;

场景4：JOIN时NULL处理

-- MySQL - 使用LEFT JOIN + NULL处理
SELECT
    u.user_id,
    COALESCE(o.order_count, 0) AS order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON u.user_id = o.user_id;

6. 特殊比较

NULL比较结果

-- NULL = NULL 返回 NULL（不是TRUE）
SELECT NULL = NULL;   -- NULL
SELECT NULL <> NULL;  -- NULL
SELECT NULL IS NULL;  -- TRUE

-- 需要使用IS NULL
SELECT * FROM t WHERE col = NULL;  -- 错误
SELECT * FROM t WHERE col IS NULL; -- 正确

三值逻辑

-- SQL的三值逻辑：TRUE, FALSE, UNKNOWN
SELECT * FROM t WHERE col = 'value';
-- 结果：TRUE的行被返回，FALSE和UNKNOWN的行被过滤

-- NOT处理
WHERE NOT (col IS NULL)
-- NOT TRUE = FALSE, NOT FALSE = TRUE, NOT UNKNOWN = UNKNOWN

⚠️ 注意事项

1. 索引失效

-- NULL值可能不走索引
-- Oracle可以创建基于函数的索引
CREATE INDEX idx_phone ON users (COALESCE(phone, 'N/A'));

2. 排序顺序

-- NULL通常排在最前或最后
-- MySQL
SELECT * FROM t ORDER BY col NULLS FIRST;
SELECT * FROM t ORDER BY col NULLS LAST;

-- Oracle
SELECT * FROM t ORDER BY col NULLS FIRST;

3. 字符串拼接

-- NULL会"吃掉"拼接
SELECT CONCAT('Hello', NULL, 'World');  -- NULL

-- 使用COALESCE
SELECT CONCAT('Hello', COALESCE(NULL, ''), 'World');

📝 练习题

-- 建表
CREATE TABLE employee_salary (
    emp_id INT,
    base_salary DECIMAL(10,2),
    bonus DECIMAL(10,2),
    commission DECIMAL(10,2)
);

INSERT INTO employee_salary VALUES
(1, 8000, 1000, 500),
(2, 7000, NULL, 300),
(3, 6000, 800, NULL),
(4, NULL, 500, 200);

要求：

1. 计算总薪资（base + bonus + commission，NULL视为0）
2. 找出没有bonus的员工
3. 计算平均总薪资（处理NULL）
4. 统计有commission的员工比例

-- 参考答案（MySQL）
-- 1. 总薪资计算
SELECT
    emp_id,
    COALESCE(base_salary, 0) + COALESCE(bonus, 0) + COALESCE(commission, 0) AS total_salary
FROM employee_salary;

-- 2. 没有bonus的员工
SELECT * FROM employee_salary WHERE bonus IS NULL;

-- 3. 平均总薪资
SELECT
    AVG(COALESCE(base_salary, 0) + COALESCE(bonus, 0) + COALESCE(commission, 0)) AS avg_total
FROM employee_salary;

-- 4. 有commission的员工比例
SELECT
    COUNT(CASE WHEN commission IS NOT NULL THEN 1 END) * 100.0 / COUNT(*) AS pct_with_comm
FROM employee_salary;