SQL中NULL值的运算与三值逻辑

NULL值的运算与三值逻辑

问题提出：为什么有 = 还需要有 IS ，两者功能不重复么？**

首先，该问题一般的回答是：

• IS 只能判断NULL值，不能判断普通数值
• = 只能判断普通数值，不能判断NULL值

那问题又来了：为什么要写成“IS NULL”,而不是“= NULL”?

回答这个问题，要清楚SQL中的三种逻辑值:

• true：0 值
• false：非0 值
• unknown：用于对NULL值的比较运算时产生的结果值

以下式子都会判定为UNKNOWN：

• =NULL
• >NULL
• <NULL
• <>NULL
• NULL = NULL

这是因为：NULL既不是值也不是变量；只是一个表示“没有值”的标记，而比较谓词只适用于值。

因此，对并非值的NULL使用比较谓词本来就没有意义。所以，当值为NULL时：

1）算术运算：对其进行任何算术运算（加减乘除）——结果都为NULL

2）进行任何比较运算——均无法判定是真还是假，是除真假之外的第三种unknown，所以对应记录不会输出。

3）判定只能是 IS NULL 或 IS NOT NULL

4）聚合函数：只有count(*)会将NULL计入到内

1.count()：
	count(列名)：当将列名作为参数时，不会将NULL计入到内；
	count(\*)：而使用星号则会将NULL计入到内；
2.sum()：不受1）的影响，不会考虑NULL行；
3.avg()：不会将NULL行计算到内；
4.与distinct联用：count（distinct ...）

5）使用distinct时，NULL也被视为一行，而且只会保留一行；

6）group by()：会将NULL视为一组特定数据

某种意义上，distinct 与 group by 作用是一样的。

7)ABS(Null)：结果为Null

重点是，要分清楚逻辑真值unknown和作为NULL的UNKNOWN是不同的东西。

1)假设 a=2;b=5;c=Null，判断下面式子的真值：

1）a\<b AND b\>c : true and unknown ——> unknown

2）a>b or b<c：fasle or unknown ——> unknown

3）a<b or b<c：true or unknown ——> 真

4）Not (b \<\> c)：not unknown ——> unknown

2)case sex when null：只是sex = null 的缩写

需要写成：case when sex is null

3)NOT IN 中存在NULL时的问题：

age NOT IN (22,23,null) 
- 等价于 NOT age IN (22,23,null)
- 又等价于NOT ((age = 22) OR (age = 23) OR (age = null))
- 如果age取值19，则NOT unknown，值为假
- 与逻辑不符

4)ALL 中存在NULL时的问题：

age < ALL (22,23,null) 
- 等价于(age < 22) AND (age < 23) AND (age < null))
- 如果age取值19，则unknown，值为假
- 与逻辑不符

5)聚合函数根本没有满足条件的对应记录：

avg(age)，表中没有筛选出任何记录，此时结果为NULL

解决3) 4) 5)问题的方法：排除掉null项的干扰

1）直接筛选掉值为NULL对应的记录；
2）把NULL值进行转换：IFNULL()、coalesce（）函数

结论

• 1）NULL不是具体的值
• 2）所以不能对NULL使用谓词
• 3）对NULL使用谓词之后的结果是unknown
• 4）unknown参与到逻辑运算中，会产生预想不到的结果
• 5）按步骤追踪SQL的执行过程，能够有效应对4中的情况
• 6）最好的办法，就是用 NOT NULL直接排除掉NULL的影响