sql查询时对null的处理

1. ​​NULL的基本概念​​

• ​​定义​​：NULL代表“无值”或“未知值”，与空字符串''或数字0不同。
• ​​三值逻辑​​：SQL的条件判断有三种结果：TRUE、FALSE、UNKNOWN。涉及NULL的运算通常返回UNKNOWN，在WHERE或HAVING子句中，只有TRUE的条件会被选中。

---

2. ​​在WHERE子句中的处理​​

• ​​错误方式​​：column = NULL永远返回UNKNOWN，不会匹配任何行。

• ​​正确方式​​：

  • IS NULL：筛选NULL值。
  • IS NOT NULL：筛选非NULL值。

  SELECT * FROM employees WHERE salary IS NULL; -- 查找未填写薪资的员工
  SELECT * FROM employees WHERE salary IS NOT NULL; -- 查找已填写薪资的员工

---

3. ​​聚合函数中的NULL处理​​

• ​​COUNT​​：
  • COUNT(*)：统计所有行数，包括NULL。
  • COUNT(column)：统计非NULL值的数量。
• ​​SUM/AVG/MAX/MIN​​：忽略NULL值。若所有值均为NULL，返回NULL。
• ​​示例​​：

  SELECT AVG(COALESCE(salary, 0)) FROM employees; -- 将NULL替换为0后计算平均值

---

4. ​​JOIN操作中的NULL​​

• ​​外连接​​：未匹配的列会填充NULL（如LEFT JOIN右表无匹配时）。
• ​​注意事项​​：在WHERE中对连接后的NULL过滤需谨慎，可能意外排除行。

  SELECT e.name, d.department_name
  FROM employees e
  LEFT JOIN departments d ON e.dept_id = d.dept_id
  WHERE d.dept_id IS NULL; -- 查找未分配部门的员工

---

5. ​​排序与分组中的NULL​​

• ​​排序​​：

  • 默认排序：不同数据库行为不同（如MySQL将NULL排在开头，Oracle排在末尾）。
  • 显式控制：使用NULLS FIRST或NULLS LAST。

  SELECT name, salary FROM employees ORDER BY salary NULLS LAST;

• ​​GROUP BY​​：所有NULL值会被分到同一组。

---

6. ​​使用函数处理NULL​​

• ​​COALESCE​​：将NULL替换为默认值。

  SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;

• ​​NULLIF​​：将特定值转换为NULL。

  SELECT NULLIF(salary, 0) AS non_zero_salary FROM employees; -- 将0转为NULL

---

7. ​​IN和NOT IN中的NULL​​

• ​​IN子句​​：包含NULL时不影响结果（如1 IN (1, NULL)返回TRUE）。
• ​​NOT IN子句​​：若子查询或列表包含NULL，结果可能为UNKNOWN导致无返回。

  SELECT * FROM employees WHERE id NOT IN (SELECT manager_id FROM departments); -- 若manager_id有NULL，可能无结果
  -- 更安全的替代方案：使用NOT EXISTS

---

8. ​​其他注意事项​​

• ​​算术运算​​：涉及NULL时结果为NULL（如5 + NULL返回NULL）。
• ​​唯一约束​​：多数数据库允许多个NULL值（因NULL不等于任何值，包括自身）。
• ​​CHECK约束​​：CHECK(column > 0)允许NULL（需显式处理 ​​索引​​：部分数据库（如Oracle）的B-tree索引不包含NULL，可能影响查询性能。

---

9. ​​不同数据库的差异​​

-约束​**​：SQL标准允许多个NULL，但某些数据库（如SQL Server）可能限制。

• ​​NULL排序​​：行为可能不同，需显式指定。
• ​​函数兼容性​​：如MySQL的CONCAT('a', NULL)返回NULL，而CONCAT_WS会忽略NULL。

---

10. ​​最佳实践​​

1. 显式处理NULL：使用IS NULL、COALESCE等确保逻辑正确。
2. 设计表结构时：明确是否需要NOT NULL约束，避免意外NULL。
3. 测试边界情况：特别是聚合和连接操作中的NULL影响。