SQL入门：空值处理全攻略-从判断到计算

在 SQL 中，空值（NULL） 表示 “未知或不存在的数据”，并非 “空字符串” 或 “0”，其判断和处理逻辑与普通值不同，需遵循特定语法和规则，以下是标准 SQL 中空值的核心操作方法：

一、空值的判断：IS NULL 与 IS NOT NULL

由于 NULL 代表 “未知”，无法用普通比较运算符（=、!=）判断，必须使用专门的空值判断语法，这是标准 SQL 的强制规定。

1. 判断字段为空：IS NULL

用于筛选字段值为 NULL 的记录，常见于 “未填写”“未赋值” 场景（如用户未填邮箱、订单未关联物流单号）。
示例：查询 orders 表中 “未填写物流单号（logistics_no 为 NULL）” 的订单：

SELECT order_id, user_id 
FROM orders 
WHERE logistics_no IS NULL;

2. 判断字段不为空：IS NOT NULL

用于筛选字段值 “已赋值且非 NULL” 的记录，排除未填写的数据。
示例：查询 users 表中 “已填写邮箱（email 不为 NULL）” 的用户：

SELECT username, email 
FROM users 
WHERE email IS NOT NULL;

注意：避免用 = 或 != 判断 NULL

若错误使用 logistics_no = NULL 或 logistics_no != NULL，SQL 会返回 “未知（UNKNOWN）”，导致筛选结果为空（既不匹配 NULL，也不匹配非 NULL 值），这是新手常见误区。

二、空值的处理：常用函数与场景

当查询或计算中包含 NULL 时，结果可能不符合预期（如 NULL 与任何值运算结果仍为 NULL），需通过函数处理空值，标准 SQL 提供以下核心方法：

1. 用默认值替换 NULL：COALESCE()

COALESCE(表达式1, 表达式2, ..., 表达式n) 是标准 SQL 的通用函数，作用是 “从左到右依次判断，返回第一个非 NULL 的表达式值”，可用于查询结果显示、计算中替换 NULL。
适用场景：将 NULL 替换为用户易理解的默认值（如 “未填写”“0”）。
示例 1：查询订单时，将 NULL 的物流单号显示为 “待分配”：

SELECT 
  order_id,
  COALESCE(logistics_no, '待分配') AS logistics_no -- NULL→"待分配"
FROM orders;

示例 2：计算订单总金额（若折扣金额为 NULL，按 0 计算）：

SELECT 
  order_id,
  amount - COALESCE(discount, 0) AS actual_amount -- NULL折扣→0
FROM orders;

2. 条件替换 NULL：CASE WHEN

CASE WHEN 是 SQL 的条件判断语句，可通过逻辑判断灵活处理 NULL（如区分 “主动未填” 和 “系统未赋值” 场景），功能比 COALESCE() 更灵活。
示例：查询用户时，按邮箱是否为 NULL 显示不同状态：

SELECT 
  username,
  CASE 
    WHEN email IS NULL THEN '未绑定邮箱'
    ELSE '已绑定邮箱'
  END AS email_status
FROM users;

3. 聚合函数中的 NULL 处理

标准 SQL 的聚合函数（SUM、AVG、COUNT 等）对 NULL 有默认处理规则，无需额外操作：

• COUNT(字段名)：仅统计 “非 NULL 的字段值” 数量（若需统计所有记录，用 COUNT(*)）；
• SUM(字段名)、AVG(字段名)：自动忽略 NULL 值，仅计算非 NULL 值的总和或平均值；
  示例：统计用户表中 “已填写邮箱的用户数” 和 “平均年龄（忽略 NULL 的年龄）”：

SELECT 
  COUNT(email) AS email_count, -- 仅统计非NULL邮箱
  AVG(age) AS avg_age -- 忽略NULL年龄，计算有效年龄的平均值
FROM users;

4. 特殊场景：NULLIF()（判断并返回 NULL）

NULLIF(表达式1, 表达式2) 是标准 SQL 函数，作用是 “若表达式 1 等于表达式 2，返回 NULL；否则返回表达式 1”，常用于 “将特定值转为 NULL”（如将 “无” 转为 NULL，统一后续处理）。
示例：将订单状态中 “无” 转为 NULL，便于后续统一用 IS NULL 判断：

SELECT 
  order_id,
  NULLIF(status, '无') AS status -- "无"→NULL，其他值保留
FROM orders;

三、空值的常见陷阱与注意事项

1. NULL 参与运算的结果为 NULL
   若用 NULL 进行算术运算（如 10 + NULL）、字符串拼接（如 '用户' || NULL），结果均为 NULL，需提前用 COALESCE() 替换 NULL。
   反例（错误）：amount + discount（若 discount 为 NULL，结果为 NULL）；
   正例（正确）：amount + COALESCE(discount, 0)。

2. DISTINCT 与 GROUP BY 对 NULL 的处理
   标准 SQL 中，DISTINCT（去重）和 GROUP BY（分组）会将所有 NULL 视为 “同一组”（即多个 NULL 会被合并为一条记录）。   

   SELECT 
     logistics_no,
     COUNT(*) AS order_count
   FROM orders
   GROUP BY logistics_no; -- NULL会作为一个分组

   示例：按物流单号分组统计订单数（所有 NULL 的物流单号会归为一组）：

3. 建表时合理设置 NULL 允许性
   处理 NULL 的最佳时机是 “建表阶段”：对 “必填字段”（如订单金额、用户手机号）设置 NOT NULL 约束，从源头避免 NULL；对 “可选字段”（如备注、折扣）允许 NULL，减少冗余默认值。
   示例（建表时控制 NULL）：

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  amount DECIMAL(10,2) NOT NULL, -- 必填，不允许NULL
  discount DECIMAL(10,2) -- 可选，允许NULL
);