专栏首页SQL实现SQL中的Null值处理

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  

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-08-23

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 的执行顺序

    了解 SQL 的执行顺序非常有价值,它可以让我们写出语法正确的 SQL,帮助我们简化编写新查询的过程。

    白日梦想家
  • 编写 SQL 的排除联接

    有两个表,就叫源表和目标表吧。它们有一个相同的字段,通过该字段可以把源表和目标表关联在一起,我们希望从源表中检索到的记录里的关联字段的值没有存在目标表中。举个例...

    白日梦想家
  • 不用 UNION 操作符实现 UNION 的效果

    当我们要合并两个表或者多个表的结果时,可使用 UNION ALL 或者 UNION 操作符, UNION 和 UNION ALL 的区别在于前者会对结果集去重...

    白日梦想家
  • EMR入门学习之Hue上创建工作流(十一)

    本文将通过一个简单,并且具有典型代表的例子,描述如何使用EMR产品中的Hue组件创建工作流,并使该工作流每天定时执行。

    披荆斩棘
  • 腾讯云EMR使用说明: 配置工作流

    本文将通过一个简单,并且具有典型代表的例子,描述如何使用EMR产品中的Hue组件创建工作流,并使该工作流每天定时执行。

    fastio
  • Python常用函数整理

    你好我是森林
  • 欧洲黑客组织通过已签名的垃圾邮件来实现多阶段恶意软件加载

    在过去的几个月时间里,研究人员观察到了多个新型的恶意垃圾邮件活动。在这些攻击活动中,攻击者使用了一种多阶段恶意软件加载器来传播GootKit银行木马,而这个新出...

    FB客服
  • 安全研究人员提醒AI助理需谨防人耳听不到的“海豚攻击”

    中美研究人员已经验证了一种可向 Siri 等 AI 助理发出“隐藏式攻击命令”的方法,因其采用了人耳察觉不到的音频,从而强迫智能助理执行非机主下达的恶意命令。《...

    周俊辉
  • 单页应用优化--懒加载

    最近查阅了一些帖子,发现了一个极其强大的方法,其兼容性有待提高~~(但已有相关的的Polyfill方式)

    奋飛
  • 禁止chrome浏览器http自动转成https

    1.地址栏中输入 chrome://net-internals/#hsts 2.在 Delete domain security policies 中输入项目...

    薛定喵君

扫码关注云+社区

领取腾讯云代金券