详解数据库语言中的null值

2024-07-09 22:47:45

字体：大中小

来源：转载

供稿：网友

虽然熟练掌握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.