详解数据库语言中的null值

虽然熟练掌握SQL的人对于Null不会有什么疑问,但总结得很全的文章还是很难找,看到一篇英文版的, 感觉还不错。

Tony Hoare 在1965年发明了 null 引用, 并认为这是他犯下的“几十亿美元的错误”. 即便是50年后的今天, SQL中的 null 值还是导致许多常见错误的罪魁祸首.

我们一起来看那些最令人震惊的情况。

Null不支持大小/相等判断

下面的2个查询,不管表 users 中有多少条记录,返回的记录都是0行:

select * from users where deleted_at = null;

– result: 0 rows

select * from users where deleted_at != null;

– result: 0 rows

怎么会这样子? 一切只因为 null 是表示一种“未知”的类型。也就是说,用常规的比较操作符(normal conditional operators)来将 null 与其他值比较是没有意义的。 Null 也不等于 Null(近似理解: 未知的值不能等于未知的值,两者间的关系也是未知,否则数学和逻辑上就乱套了)。

– 注意: 下面的SQL适合于MySQL,如果是Oracle,你需要加上 … from dual;

select null > 0;

– result: null

select null < 0;

– result: null

select null = 0;

– result: null

select null = null;

– result: null

select null != null;

– result: null

将某个值与 null 进行比较的正确方法是使用 is 关键字, 以及 is not 操作符:

select * from users

where deleted_at is null;

– result: 所有被标记为删除的 users

如果想要判断两列的值是否不相同,则可以使用 is distinct from:

select * from users

where has_address is distinct from has_photo

– result: 地址(address)或照片(photo)两者只有其一的用户

not in 与 Null

子查询(subselect)是一种很方便的过滤数据的方法。例如,如果想要查询没有任何包的用户,可以编写下面这样一个查询:

select * from users 

where id not in (select user_id from packages)

但此时假若 packages 表中某一行的 user_id 是 null 的话,问题就来了: 返回结果是空的! 要理解为什么会发生这种古怪的事情, 我们需要理解SQL编译器究竟干了些什么. 下面是一个更简单的示例:

select * from users 

where id not in (1, 2, null)

这个SQL语句会被转换为:

select * from users 

where id != 1 and id != 2 and id != null

我们知道,id != null 结果是个未知值, null. 而任意值和 null 进行 and 运算的结果都是 null, 所以相当于没有其他条件. 那么出这种结果的原因就是 null 的逻辑值不为 true.

如果条件调换过来, 查询结果就没有问题。 现在我们查询有package的用户.

select * from users 

where id in (select user_id from packages)

同样我们可以使用简单的例子:

select * from users

where id in (1, 2, null)

这条SQL被转换为:

select * from users 

where id = 1 or id = 2 or id = null

因为 where 子句中是一串的 or 条件,所以其中某个的结果为 null 也是无关紧要的。非真(non-true)值并不影响子句中其他部分的计算结果,相当于被忽略了。

Null与排序

在排序时, null 值被认为是最大的. 在降序排序时(descending)这会让你非常头大,因为 null值排在了最前面。

下面这个查询是为了根据得分显示用户排名, 但它将没有得分的用户排到了最前面!

select name, points

from users

order by 2 desc;

– points 为 null 的记录排在所有记录之前!

解决这类问题有两种思路。最简单的一种是用 coalesce 消除 null的影响:

– 在输出时将 null 转换为 0 :

select name, coalesce(points, 0)

from users

order by 2 desc;

– 输出时保留 null, 但排序时转换为 0 :

select name, points

from users

order by coalesce(points, 0) desc;

还有一种方式需要数据库的支持,指定排序时将 null 值放在最前面还是最后面:

select name, coalesce(points, 0)

from users

order by 2 desc nulls last;

当然, null 也可以用来防止错误的发生,比如处理除数为0的数学运算错误。

被 0 除

除数为0是一个非常 egg-painfull 的错误。昨天还运行得好好的SQL,突然被0除一下子就出错了。一个常用的解决方法是先用 case 语句判断分母(denominator)是否为0,再进行除法运算。

select case when num_users = 0 then 0 

else total_sales/num_users end;

ase 语句的方式其实很难看,而且分母被重复使用了。如果是简单的情况还好,如果分母是个很复杂的表达式,那么悲剧就来了: 很难读,很难维护和修改,一不小心就是一堆BUG.

这时候我们可以看看 null 的好处. 使用 nullif 使得分母为0时变成 null. 这样就不再报错, num_users = 0 时返回结果变为 null.

select total_sales/nullif(num_users, 0);

nullif 是将其他值转为 null, 而Oracle的 nvl 是将 null 转换为其他值。

如果不想要 null,而是希望转换为 0 或者其他数, 则可以在前一个SQL的基础上使用 coalesce函数:

select coalesce(total_sales/nullif(num_users, 0), 0);

null 再转换回0

Conclusion

Tony Hoare 也许会后悔自己的错误, 但至少 null 存在的问题很容易地就解决了. 那么快去练练新的大招吧,从此远离 null 挖出来的无效大坑(nullifying)!

(0)

相关推荐

  • sqlserver、Mysql、Oracle三种数据库的优缺点总结

    一.sqlserver优点:易用性.适合分布式组织的可伸缩性.用于决策支持的数据仓库功能.与许多其他服务器软件紧密关联的集成性.良好的性价比等:为数据管理与分析带来了灵活性,允许单位在快速变化的环境中从容响应,从而获得竞争优势.从数据管理和分析角度看,将原始数据转化为商业智能和充分利用Web带来的机会非常重要.作为一个完备的数据库和数据分析包,SQLServer为快速开发新一代企业级商业应用程序.为企业赢得核心竞争优势打开了胜利之门.作为重要的基准测试可伸缩性和速度奖的记录保持者,SQLServ

  • mysql仿oracle的decode效果查询

    以下代码在MYSQL中测试通过,MSSQL应该能跑通,未测试. 复制代码 代码如下: #创建表如下 create temporary table tmp (a int, b int ,c int); insert into tmp VALUES (1,10,1),(10,10,2),(10,100,2); #mysql执行 select sum(case when c = '1' then A else B end) from tmp #oracle执行 select sum(decode(c,

  • MySQL与Oracle的语法区别详细对比

    Oracle和mysql的一些简单命令对比 1) SQL> select to_char(sysdate,'yyyy-mm-dd') from dual; SQL> select to_char(sysdate,'hh24-mi-ss') from dual; mysql> select date_format(now(),'%Y-%m-%d'); mysql> select time_format(now(),'%H-%i-%S'); 日期函数 增加一个月: SQL> se

  • 详解数据库语言中的null值

    虽然熟练掌握SQL的人对于Null不会有什么疑问,但总结得很全的文章还是很难找,看到一篇英文版的, 感觉还不错. Tony Hoare 在1965年发明了 null 引用, 并认为这是他犯下的"几十亿美元的错误". 即便是50年后的今天, SQL中的 null 值还是导致许多常见错误的罪魁祸首. 我们一起来看那些最令人震惊的情况. Null不支持大小/相等判断 下面的2个查询,不管表 users 中有多少条记录,返回的记录都是0行: select * from users where

  • 详解C语言中return与exit的区别

    详解C语言中return与exit的区别 1,exit用于在程序运行的过程中随时结束程序,exit的参数是返回给OS的.main函数结束时也会隐式地调用exit函数.exit函数运行时首先会执行由atexit()函数登记的函数,然后会做一些自身的清理工作,同时刷新所有输出流.关闭所有打开的流并且关闭通过标准I/O函数tmpfile()创建的临时文件.exit是结束一个进程,它将删除进程使用的内存空间,同时把错误信息返回父进程,而return是返回函数值并退出函数 2,return是语言级别的,它

  • 详解 Go 语言中 Map 类型和 Slice 类型的传递

    Map 类型 先看例子 m1: func main() { m := make(map[int]int) mdMap(m) fmt.Println(m) } func mdMap(m map[int]int) { m[1] = 100 m[2] = 200 } 结果是 map[2:200 1:100] 我们再修改如下 m2: func main() { var m map[int]int mdMap(m) fmt.Println(m) } func mdMap(m map[int]int) {

  • 详解C语言中Char型指针数组与字符数组的区别

    详解C语言中Char型指针数组与字符数组的区别 1.char 类型的指针数组:每个元素都指向一个字符串,指向可以改变 char *name[3] = { "abc", "def", "gbk" }; for(int i = 0 ; i < strlen(name); i ++){ printf("%s\n", *(name+i)); //printf("%s\n", name[i]); } //指向改

  • 详解Go语言中for range的"坑"

    前言 Go 中的for range组合可以和方便的实现对一个数组或切片进行遍历,但是在某些情况下使用for range时很可能就会被"坑",下面用一段代码来模拟下: func main() { arr1 := []int{1, 2, 3} arr2 := make([]*int, len(arr1)) for i, v := range arr1 { arr2[i] = &v } for _, v := range arr2 { fmt.Println(*v) } } 代码解析

  • 详解Golang语言中的interface

    interface是一组method签名的组合,interface可以被任意对象实现,一个对象也可以实现多个interface.任意类型都实现了空interface(也就是包含0个method的interface),空interface可以存储任意类型的值.interface定义了一组方法,如果某个对象实现了某个接口的所有方法,则此对象就实现了此接口. go version go1.12 package main import ( "fmt" ) // 定义struct type Hu

  • 详解go语言中type关键词的几种使用

    type是go语法里的重要而且常用的关键字,type绝不只是对应于C/C++中的typedef.搞清楚type的使用,就容易理解go语言中的核心概念struct.interface.函数等的使用.以下我用例子代码总结描述,请特别留意代码中的注释. 1.定义结构体 //结构体定义 type person struct { name string //注意后面不能有逗号 age int } func main() { //结构体初始化 p := person{ name: "taozs",

  • 详解R语言中的PCA分析与可视化

    1. 常用术语 (1)标准化(Scale) 如果不对数据进行scale处理,本身数值大的基因对主成分的贡献会大.如果关注的是变量的相对大小对样品分类的贡献,则应SCALE,以防数值高的变量导入的大方差引入的偏见.但是定标(scale)可能会有一些负面效果,因为定标后变量之间的权重就是变得相同.如果我们的变量中有噪音的话,我们就在无形中把噪音和信息的权重变得相同,但PCA本身无法区分信号和噪音.在这样的情形下,我们就不必做定标. (2)特征值 (eigen value) 特征值与特征向量均为矩阵分

  • 详解C++语言中std::array的神奇用法

    概述 std::array是在C++11标准中增加的STL容器,它的设计目的是提供与原生数组类似的功能与性能.也正因此,使得std::array有很多与其他容器不同的特殊之处,比如:std::array的元素是直接存放在实例内部,而不是在堆上分配空间:std::array的大小必须在编译期确定:std::array的构造函数.析构函数和赋值操作符都是编译器隐式声明的--这让很多用惯了std::vector这类容器的程序员不习惯,觉得std::array不好用.但实际上,std::array的威力

  • 详解C语言中return返回函数局部变量的问题

    目录 return返回栈区局部变量的指针 return返回栈区局部的临时变量 return只读数据段和static数据 在计算机中,释放空间并不需要将空间中的内容全部置成0或者1,而是只要设置这一块空间的数据无效即可.比如在下载文件时需要花很长时间,但是删除文件却只要几秒钟,这是因为操作系统只是把文件标识(文件头链接)删掉了,文件原文还保留着,我们没了文件标识就找不到这个文件了.所以删除后的文件,还可以用特殊的办法被找回来. 这也就意味着,当函数结束调用的时候,函数中的局部变量实际上还是在的,只

随机推荐