SQLSERVER 三值逻辑

三值逻辑 (3VL, Three-valued Logic) 

在SQL中逻辑表达式的可能值包括TRUE、FALSE、UNKNOWN。他们被称为三值逻辑。三值逻辑是SQL所特有的。大多数语言的逻辑表达式只有TRUE或FALSE两种值。

 

以下内容出自CSDN feilniu  http://topic.csdn.net/u/20100826/18/7b81012a-b5c4-48b1-b5d1-40a92f3e0388.html 
关于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相关的逻辑运算：
[code=sql] 
NOT Unknown --> Unknown 
Unknown AND/OR Unknown --> Unknown 
Unknown OR TRUE --> TRUE 
Unknown AND TRUE --> Unknown 
Unknown OR FALSE --> Unknown 
Unknown AND FALSE --> FALSE 
[/code]
具体可查三值逻辑的真值表。

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。