前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >算法工程师-SQL进阶:温柔的陷阱-NULL

算法工程师-SQL进阶:温柔的陷阱-NULL

作者头像
小萌哥
修改2020-07-21 14:08:20
8210
修改2020-07-21 14:08:20
举报
文章被收录于专栏:算法研习社

我们以往遇到的编程语言基本都是基于二值逻辑的,即逻辑真值只有truefalse两个。而 SQL 语言则采用一种特殊的逻辑体系——三值逻辑,逻辑真值除了truefalse,还有第三个值unknow,即 “不确定”。三值逻辑经常会给数据分析带来一些意想不到的难题,即使是资深的工程师,有时候也很难避免。

接下来,本节就介绍一下:SQL的温柔杀手-NULL

一、NULL怎么了

上面已经提到了,在 SQL 语言里,除truefalse外,还有第三个逻辑值unknown,这种逻辑体系被称为三值逻辑。原因就在于,SQL里引进了 NULL,所以不得不同时引进第三个值。

1、NULL的两种状态

两 种 NULL 分 别 指 的 是“ 未 知 ”(UNKNOWN) 和“ 不 适 用 ”。

举个例子:

(1)戴墨镜的这个人的眼球是什么颜色的?因为这个人没摘掉眼镜,所以不知道他的眼睛是什么颜色,但他的眼睛肯定是一种颜色。这种情况叫做:“ 未 知 ”

(2)这个男的生了几次孩子?因为男人不能生孩子,所以生孩子不能适用于男人,这个问题是无意义的,这种情况叫做:“ 不 适 用 ”

这两种情况,在现在的SQL版本中统称为NULL。

2、NULL不是一种值

你有没有想过,为什么以下的式子都会被判为 unknown

代码语言:javascript
复制
1 = NULL
2 > NULL
3 < NULL
4 <> NULL
NULL = NULL

这是因为,NULL 既不是值(value)也不是变量(variable)。NULL 只是一个表示“没有值”的标记,而比较谓词只适用于。因此,对并非值的 NULL 使用比较谓词本来就是没有意义的 。

因此,对 NULL 使用比较谓词后得到的结果总是 unknown。而查询结果只会包含 WHERE 子句里的判断结果为 true 的行,不会包含判断结果为 falseunknown 的行。判断是否为NULL,应该用谓词:IS NULL。

3、第三个真值:unknown

这里引入第三个真值,unknown,注意,unknown和前面说的NULL的一种情况:'UNKNOWN'(未知)是不一样的,unknown是与true和false概念一样的逻辑真值,而'UNKNOWN'的统称是NULL,它与比较谓词结合使用时的结果是:布尔值unknown

代码语言:javascript
复制
unknown = unknown →  true  -- 这个是明确的真值的比较
UNKNOWN = UNKNOWN → unknown -- 这个相当于 NULL = NULL

接下来看一下,三值逻辑的真值表

NOT

代码语言:javascript
复制
not true    →  false
not false   →  true
not unknown →  unknown

AND

代码语言:javascript
复制
true and true       →  true
true and false      →  false
true and unknown    →  unknown
false and unknown   →  false
false and false     →  false
unknown and unknown →  unknown

OR

代码语言:javascript
复制
true or true       →  true
true or false      →  true
true or unknown    →  true
false or unknown   →  unknown
false or false     →  false
unknown or unknown →  unknown

注意思考真值表里,unknown参与过的计算,为什么有的结果是确定的,有的还是unknown?

二、NULL有哪些陷阱

1、 比较谓词和 NULL(1) :排中律不成立

代码语言:javascript
复制
约翰是 20 岁,或者不是 20 岁,二者必居其一。——P

大家觉得正确吗?没错,在现实世界中毫无疑问这是个真命题。只要是人,就有年龄,只要有年龄,要么是

20 岁,要么不是 20 岁,不可能有别的情况。

像这样,“把命题和它的否命题通过‘或者’连接而成的命题全都是真命题”这个命题在二值逻辑中被称为:排中律。顾名思义,排中律就是指不认可中间状态,对命题真伪的判定黑白分明。

但在SQL中,排中律不成立。

eg1: 有下面一张学生成绩单,记录了4个学生的语文成绩。如果想把及格和不及格的所有学生都查出来,下面SQL正确吗?

查询SQL:

代码语言:javascript
复制
SELECT name
  FROM Score2
 WHERE score < 60
    OR score >= 60

执行结果是:只有琪琪、倩倩和蓉蓉,涛涛会查不出来。

按理说及格和不及格加起来应该是所有同学啊,现在为什么少一个呢,原来是表里存在一个score为null的学生。

当SQL查到这一行时,score is null ,此时where条件:score<60 or score>=60 的判断结果会是什么呢?

代码语言:javascript
复制
NULL < 60 or NULL >= 60  →   unknown or unknown  →  unknown

也就是说,score is null的行的逻辑值会被判断为unknown,而where只返回结果为true的行,因此,最终结果会少一行。可以看出,当NULL存在时,SQL中的排中律就不成立了,切记~

2、 比较谓词和 NULL(2) :CASE 表达式和 NULL

eg2: 小明学习了case表达式后写了下面一段SQL,逻辑是:当col_1 为 1 时返回○、为 NULL 时返回 × ,你觉得能实现他想要的效果吗?

代码语言:javascript
复制
CASE col_1
     WHEN   1   THEN '○'
     WHEN  NULL THEN '×'
END

答案是:不能。

首先,这是一段简单的case表达式写法,会将col_1的值从上到下依次与when的值进行比较,一旦符合就break,不会再进行下面的分支判断。值得注意的是,这里是进行值的比较,看第二个条件分支,翻译过来就是:

when col_1 = NULL,前面我们已经说得很清楚了,NULL不是值,参与任何比较运算都是没有意义的,返回的逻辑真值都是unknown,因此,第二个分支永远不会触达。

怎么改呢?只要将NULL 改成 IS NULL,就可以了。

另外,case表达式最好有兜底逻辑,即,在END之前,把ELSE NULL 写上。

3、 NOT IN 陷阱

eg3: 有如下两个学生住址表,分别属于不同的班级。现在想找出:与clazz2中位于海淀区的学生年龄不同的所有clazz1班的学生。

你觉得这个SQL能实现吗?

代码语言:javascript
复制
SELECT name
  FROM clazz1
 WHERE age NOT IN
       ( SELECT age
           FROM clazz2
          WHERE city ='海淀区' );

答案是:NO!

原因是,上面的SQL中,子查询的结果中有有NULL存在,当使用谓词 not in 时,如果 in的对象中存在NULL,那其处理逻辑应该是这样的:

代码语言:javascript
复制
age not in (value1,null) → age != value1 and age != null  →  age != value1 and unknown  →  unknown 

因为null参与任何比较运算结果都unknown,unknown参与and运算的结果不可能为true,而where子句只会把逻辑值为true的筛选出来,因此,这个SQL的执行结果一定是空。

拯救方法是:用not exists 代替 not in 。

代码语言:javascript
复制
SELECT name
  FROM clazz1
 WHERE NOT EXISTS
      ( SELECT *
         FROM clazz2
        WHERE clazz1.age = clazz2.age
          AND clazz2.city ='海淀区' );

where子句处理NULL行的逻辑是:

代码语言:javascript
复制
(1)WHERE NOT EXISTS
    (SELECT *
     FROM clazz2
     WHERE clazz1.age = NULL
       AND clazz2.city ='海淀区');
(2)WHERE NOT EXISTS
    (SELECT *
     FROM clazz2
     WHERE unknown
       AND clazz2.city ='海淀区');
(3)WHERE NOT EXISTS
    (SELECT *
     FROM clazz2
     WHERE unknown or false; -- 子查询的结果是空行
(4)WHERE true; -- 子查询没有返回结果,因此相反地,NOT EXISTS 为 true

因此,age为null的文生同学,与任何人的年龄比较后的结果都是true,即:"文生与任何人的年龄都不同"这句话总是对的。因此,这个null不会对整体结果产生任何影响。

可见,in虽然与exists可以互换,但是not in与not exists是不一样的哦~~

4、 ALL 与 NULL

ALL 谓词其实是多个以 AND 连接的逻辑表达式的省略写法,比如:

代码语言:javascript
复制
age < ALL(1,2,3) 就等价于:age < 1 AND age < 2 AND age < 3

想一想,当ALL的列表参数中,存在NULL,结果会怎么样?

eg4: 这里复用eg3中两个班的学生住址表,但是求的是:比clazz2中位于海淀区的学生年龄都小的所有clazz1班的学生。请看下面这个SQL能实现吗?

代码语言:javascript
复制
SELECT name
  FROM clazz1
 WHERE age < ALL
    ( SELECT age
       FROM clazz2
      WHERE city ='海淀区' );

答案是:不能!结果还是空。

相信聪明的读者已经能找到原因了,因为,子查询的结果中存在NULL,和前面的NOT IN 类似,ALL 运算实际执行时也是与每个列表的元素一一比较,然后进行AND的运算,最终结果不是false就是unknown。因此,where条件不会筛出任何一个结构,最终肯定返回空集。

补救方法是:用聚合函数代替ALL

代码语言:javascript
复制
SELECT name
  FROM clazz1
 WHERE age <
      ( SELECT MIN(age)
          FROM clazz2
          WHERE city ='海淀区' );

如果要比所有这些人的年龄都小,只需要比最小的年龄还小就行了啊。

这个为什么能正常呢?因为除count外的所有聚合函数都可以自动过滤掉NULL值。

5、聚合函数 与 NULL

eg5: 还是复用eg3中两个班的学生住址表,但是求的是:比clazz2中位于海淀区的学生的平均年龄小的所有clazz1班的学生。请看下面这个SQL能实现吗?

代码语言:javascript
复制
SELECT name
  FROM clazz1
 WHERE age <
      ( SELECT AVG(age)
          FROM clazz2
         WHERE city ='海淀区' );

答案是:可以。终于有一个能实现的了~.~

很显然,和eg4一样,AVG也是聚合函数,可以自动过滤null行,因此结果不受影响。

但是,你有没有想过,如果海淀区的学生的age全是null,结果会怎样呢?

如果聚合函数将null行过滤后,结果为空集合,或者传入聚合函数本身的集合本身就是空集,聚合函数会返回NULL。

那上面的SQL语句就会这样执行:

代码语言:javascript
复制
(1) WHERE age <
    (SELECT AVG(age)
     FROM clazz2
     WHERE city ='海淀区');
(2) WHERE age < NULL;
(3)  WHERE unknown;

因此,如果clazz2中一个海淀区的学生都没有,上面SQL的查询结果是空集。

三、总结

本节给大家讲解了,SQL中最最让人头疼的温柔陷阱:NULL。

简单总结一下:

  • NULL 不是值,而是一种标记;
  • 因为 NULL 不是值,所以不能对其使用谓词;
  • 对NULL 使用谓词后的结果是 unknown
  • unknown 参与到逻辑运算时,SQL 的运行会和预想的不一样;
  • 按步骤追踪 SQL 的执行过程能有效应对 4 中的情况。

另外,要想避免 NULL 带来的各种问题,方法如下:

  • 往表里添加 NOT NULL 约束来尽力排除 NULL;
  • 如果可以的话,在where条件中可以先把null行过滤掉;
  • 注意NOT IN 陷阱 和 ALL 陷阱,一定程度上,可以用NOT EXISTS 和 聚合函数解决。
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-07-02,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 算法研习社 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 一、NULL怎么了
    • 1、NULL的两种状态
      • 2、NULL不是一种值
        • 3、第三个真值:unknown
        • 二、NULL有哪些陷阱
          • 1、 比较谓词和 NULL(1) :排中律不成立
            • 2、 比较谓词和 NULL(2) :CASE 表达式和 NULL
              • 3、 NOT IN 陷阱
                • 4、 ALL 与 NULL
                  • 5、聚合函数 与 NULL
                  • 三、总结
                  领券
                  问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档