8. NULL与三值逻辑

http://bbs.csdn.net/topics/350013767?page=1#post-394398547

8. NULL与三值逻辑

三值逻辑(3VL, Three-valued Logic)绝对是SQL修炼中的一个紧要关卡，值得特别注意，闭关静修。待冲破这一关卡之后，SQL中的NULL与NOT NULL将别无二致。

关于SQL是否应该允许NULL，在数据库领域已经近乎一个信仰式的争论。E.F.Codd认为NULL有存在的必要，但他的好友C.J.Date认为NULL完全可以取消。最终结果是，SQL标准支持NULL。

理论上的争论且不管。但在实践中，一定要知道NULL的三值逻辑会带来很多困扰的问题。
a. 不使用NULL的理由：
- NULL会引入复杂的三值逻辑。
- NULL在查询条件、外键和CHECK约束、唯一约束、GROUP BY、ORDER BY中的行为都是不一致的。
b. 使用NULL的理由：
- 当需要表示一个未知的、不确定的值时，用NULL更自然。比如一个现在职员工的离职时间、顶级员工（BOSS）的上级员工，等等。
- 外联接通常会引入NULL，即使所有表的字段都定义为NOT NULL。

首先，如果可能， 尽量让所有字段都声明为NOT NULL 。除非是更适合使用NULL的场合（从业务出发）。

其次， 在使用NULL时，一定要搞清楚三值逻辑和数据库引擎对NULL的处理 ：
（SQLServer有一个选项SET ANSI_DEFAULTS，默认为ON，即与SQL标准一致。设为OFF的效果详见联机丛书。）

1. NULL与别的值进行+-*/等计算操作（包括在大多数函数中使用NULL）后，结果是NULL（标量表达式）。NULL与别的值进行=、>、<等比较操作后，结果是Unknown（断言）。
Unknown相关的逻辑运算：

SQL code

?

1 2 3 4 5 6

NOT  Unknown  --> Unknown Unknown  AND / OR  Unknown  --> Unknown Unknown  OR  TRUE  --> TRUE Unknown  AND  TRUE  --> Unknown Unknown  OR  FALSE  --> Unknown Unknown  AND  FALSE  --> FALSE

具体可查三值逻辑的真值表。

2. 在where/on/having和if/case when中，只有True才使条件成立（即Unknown当作False来处理）。比如：
where column = value：表中column为NULL的行永远不会返回，即使value是NULL；
case value when NULL then XXX when ... end：XXX永远不会执行，即使value是NULL；
if <Unknown> XXX else YYY end或case when <Unknown> then XXX else YYY end：这两种情况下，YYY会执行。

3. 包含外键约束和Check约束的字段允许NULL（即约束只当条件为False时出错，Unknown是不管的）。
4. 包含唯一约束（unique index）的字段只允许一个NULL的行，再插入或更新该字段为NULL的行会报字段重复的错误。
5. GROUP BY时，所有NULL被视为一组。
6. ORDER BY时，所有NULL排在一起，但NULL排在非空值的前面（如SQL Server）还是后面（如Oracle），SQL标准未规定。
7. 聚集函数（SUM/AVG/MAX/MIN/COUNT）忽略NULL的行。
8. declare的变量，在未赋值之前为NULL。
9. 与NULL处理相关的运算符和函数：
- IS NULL/IS NOT NULL：用这两个运算符来判断一个值是否为NULL，而不是=或<>。
- ISNULL/COALESCE：取第一个非空值（注意两个函数的数据类型转换规则不同）。
- NULLIF(a,b)：等价于CASE WHEN a = b THEN NULL ELSE a END。