NULL既不是值也不是变量。NULL只是一个表示“没有值”的标记。对NULL使用比较谓词后得到的结果总是unknown。
真值unknown是因关系数据库采用了NULL而被引入的的“第三个真值”,是明确的布尔型的真值。
要想解决NULL带来的各种问题,最佳方法应该是往表里添加NOT NULL约束来尽力排除NULL。
比较谓词和NULL(1):排中律不成立
假设约翰是一个人。那么,下面的语句是真是假?
约翰是20岁,或者不是20岁,二者必居其一。
如果排中律在SQL里也成立,那么下面的查询应该能选中表里的所有行。
1 | SELECT * |
遗憾的是,在SQL的世界里,排中律是不成立的。假设表Students里的数据如下所示。
那么这条SQL语句无法查询到约翰,因为约翰年龄不详。SQL语句的查询结果里只有判断结果为true的行。要想让约翰出现在结果里,需要添加下面这样的“第3个条件”。
1 | SELECT * |
比较谓词和NULL(2):CASE表达式和NULL
首先看下面的简单CASE表达式。
1 | CASE col_1 |
这个CASE表达式一定不会返回X。CASE表达式的判断方法与WHERE子句一样,只认可真值为true的条件。正确的写法是像下面这样使用搜索CASE表达式。
1 | CASE WHEN col_1 = 1 THEN 'O' |
NOT IN和NOT EXISTS不是等价的
在对SQL语句进行性能优化时,经常用到的一个技巧是将IN改写成EXISTS。这是等价改写,并没有什么问题。问题在于,将NOT IN改写成NOT EXISTS时,结果未必一样。
例如,看下面这两张班级学生表。
考虑一下如何根据这两张表查询“与B班住在东京的学生年龄不同的A班学生”。如果单纯地按照这个条件去实现,则SQL语句如下所示。
1 | SELECT * |
遗憾的是这条SQL语句的结果是空,查询不到任何数据。
如果NOT IN子查询中用到的表里被选择的列中存在NULL,则SQL语句整体的查询结果永远是空。
为了得到正确的结果,需要使用EXISTS谓词。
1 | SELECT * |
产生这样的结果,是因为EXISTS谓词永远不会返回unknown。EXISTS只会返回true或者false。
限定谓词和NULL
SQL里有ALL和ANY两个限定谓词。ALL可以和比较谓词一起使用,用来表达“与所有的XX都相等”,或“比所有的XX都大”的意思。
思考一下用于查询“比B班住在东京的所有学生年龄都小的A班学生”的SQL语句。
1 | SELECT * |
如果山田年龄不详,就会有问题了。这条SQL语句的执行结果还是空。这时因为,ALL谓词其实是多个以AND连接的逻辑表达式的省略写法。
限定谓词和极值函数不是等价的
使用极值函数可以代替ALL谓词。如果用极值函数重写刚才的SQL,应该是下面这样。
1 | SELECT * |
没有问题。即使山田的年龄无法确定,这段代码也能查询到拉里和伯杰两人。这是因为,极值函数在统计时会把NULL的数据排除掉。
ALL谓词和极值函数表达的命题含义分别如下所示。
ALL谓词:他的年龄比在东京住的所有学生都小
极值函数:他的年龄比在东京住的年龄最小的学生还要小
其实还有一种情况下,它们也是不等价的。谓词(或者函数)的输入为空集的情况。例如Class_B这张表为如下所示的情况。
这时,使用ALL谓词的SQL语句会查询到A班的所有学生。然而,用极值函数查询时一行数据都查询不到。这时因为,极值函数在输入为空表(空集)时会返回NULL。
聚合函数和NULL
实际上,当输入为空表时返回NULL的不只是极值函数,COUNT以外的聚合函数也是如此。所以下面这条看似普通的SQL语句也会带来意想不到的结果。
1 | SELECT * |
没有住在东京的学生时,AVG函数返回NULL,也就查询不到行。使用SUM也是一样。
聚合函数和极值函数的这个陷阱是由函数自身带来的,所以仅靠为具体列加上NOT NULL约束是无法从根本上消除的。