SQL中的Null值处理

在日常的开发中，遇到需要处理 Null 值的场景还是蛮常见的。比如，查询某个字段包含 Null 值的记录、在展示的时候将 Null 值转为其它值、聚合包含 Null 值的列等。

今天就和大家聊聊在 MySQL 中处理 Null 值时需要注意的点，本文包含以下内容：

1. 查找 Null 值
2. 将 Null 值转为实际值
3. 在排序中对 Null 值的处理
4. 计算非 Null 值的数量
5. 聚合 Null 列

1 查找 Null 值

查找 Null 值不能使用 “=”，而应该使用“is Null”。同理，查找非 Null 值也不能使用 “<>” ，而要使用 “is not Null”。这是初学者容易犯的错误。

比如，查询 emp 表中字段 comm 为 Null 的记录，就这么写 SQL：

SELECT
  *
FROM
  emp
WHERE comm IS NULL

有时候根据业务需要，我们要找出在 emp 表中没有提成的员工的信息。没有提成可以理解为提成为 0 或者本身就不包含提成，因此查询的条件就应该这么写 comm IS NULL OR comm = 0。

类似的，在处理字符串类型的字段的时候，我们要找出某个字段没有值的记录。假设该字段叫作 xxx，xxx 允许设置 Null 值。初学者经常犯的错误就是给出的查询条件不完整，要么写成 xxx = ''，要么写成 xxx is null，正确的写法是 xxx = '' or xxx is null，或者是其它变体。

2 将 Null 值转为实际值

有时候做报表展示的时候，我们不希望将 Null 值直接展示出来，而是转为其它值。比如，是数值类型的字段就展示成 0，是字符串类型就展示成空白字符。

使用函数 COALESCE() 可将 Null 值转成其它值，将 emp 表中 comm 列的 Null 值转成 0 就可以这么写：COALESCE(comm,0)。在 MySQL 中还可以使用 IFNULL(comm,0)，或者 case when。

3 处理排序中的 Null 值

如果是使用默认的升序对包含有 Null 值的列做排序，有 Null 值的记录会排在前面，而使用了降序排序，包含了 Null 值的记录才会排在后面。

对于下面这条语句，它的排序结果在它下方。

SELECT
  empno,
  ename,
  comm
FROM
  emp
ORDER BY comm

排序结果：

 empno  ename   comm     
------  ------  ---------
  7369  SMITH   (NULL)   
  7566  JONES   (NULL)   
  7698  BLAKE   (NULL)   
  7782  CLARK   (NULL)   
  7788  SCOTT   (NULL)   
  7839  KING    (NULL)   
  7876  ADAMS   (NULL)   
  7900  JAMES   (NULL)   
  7902  FORD    (NULL)   
  7934  MILLER  (NULL)   
  7844  TURNER  0.00     
  7499  ALLEN   300.00   
  7521  WARD    500.00   
  7654  MARTIN  1400.00  

如果要想在升序排序中将有 Null 值的记录排在后面，可以将 Null 值转为一个最大值（比 comm 中的最大值还要大就行），或者增加一个排序列 is_null，记录有有 Null 值的时候 is_null = 1，其它时候为 0 。

SELECT
  empno,
  ename,
  comm
FROM
  emp
ORDER BY IF(comm IS NULL, 1, 0),
  comm

重新排序后的结果如下：

 empno  ename   comm     
------  ------  ---------
  7844  TURNER  0.00     
  7499  ALLEN   300.00   
  7521  WARD    500.00   
  7654  MARTIN  1400.00  
  7369  SMITH   (NULL)   
  7566  JONES   (NULL)   
  7698  BLAKE   (NULL)   
  7782  CLARK   (NULL)   
  7788  SCOTT   (NULL)   
  7839  KING    (NULL)   
  7876  ADAMS   (NULL)   
  7900  JAMES   (NULL)   
  7902  FORD    (NULL)   
  7934  MILLER  (NULL)   

4 计算非 Null 值的数量

要统计 emp 表中 comm 字段非 Null 值的数量，可以就这么写：

SELECT
  COUNT(comm)
FROM
  emp

结果是 4。

count(comm)  
-------------
            4

注意，如果要统计一张表有多少记录时，不要在允许设置为 Null 值的列上做统计，得出来的结果和实际数据有偏差。注意对比一下使用 count(*) 和 count(comm) 的区别。

count(*)  
----------
        14

5 聚合 Null 列

聚合函数 sum()、avg()、min() 等会忽略 Null 值，在使用 avg() 时要警惕 Null 值。通常的做法是先将列中的 Null 值转为 0，再做聚合操作。

比如，要求员工的平均提成，正确的 SQL 应该这么写：

SELECT
  AVG(COALESCE(comm, 0)) AS avg_comm
FROM
  emp

聚合的结果：

avg_comm    
------------
157.142857  

如果直接使用 AVG(comm) ，得出来的结果就千差万别了。

avg(comm)   
------------
550.000000