sql server 设置 字段为null sql查询null值

SQL查询 — 三值逻辑和NULL

• 要点
• 应用样例

• 1. 排中律
• 2. CASE表达式和NULL
• 3. NOT IN 和 NOT EXISTS不是等价的
• 4. 限定谓词和NULL
• 5. 极值函数和限定谓词不是等价的
• 6. 聚合函数和NULL

---

要点

SQL语言里，有TRUE，FALSE，UNKNOWN三种值，这种逻辑体系被称为三值逻辑（three- valued logic）。因为关系数据库里引进了NULL，所以不得不同时引进第三个值。

1. IS NULL是判断NULL的正确写法。
2. 对NULL使用比较谓词后得到的结果总是Unknown。
3. 包含WHERE子句的查询结果只会包含判断结果为TRUE的行。
4. NULL 既不是值也不是变量。NULL只是一个表示"没有值"的标记。

以下式子都会被判为Unknown：
$~~~~~~~~~~$ 1 = NULL
$~~~~~~~~~~$ 2 > NULL
$~~~~~~~~~~$ 3 < NULL
$~~~~~~~~~~$ 4 <> NULL
$~~~~~~~~~~$

5. 真值unknown是明确的布尔型的真值。
   $~~~$Unknown = Unknown -> TRUE

三值逻辑真值表（NOT）

x	NOT x
t	f
f	t
u	u

三值逻辑真值表（AND）
* f > u > t

AND	t	u	f
t	t	u	f
u	u	u	f
f	f	f	f

三值逻辑真值表（OR）
* t > u > f

OR	t	u	f
t	t	t	t
u	t	u	u
f	t	u	f

---

应用样例

1. 排中律

Students表有列name，age，其中约翰的年龄为NULL。查询表中的所有行（年龄是20岁或不是20岁或年龄未知的人员记录）。
表Students

name	age
布朗	22
拉里	19
约翰
伯杰	21

SELECT *
FROM Students
WHERE age = 20 OR age <> 20 OR age IS NULL;

2. CASE表达式和NULL

错误写法

CASE col_1
WHEN 1 THEN '0'
WHEN NULL THEN 'X'

正确写法

CASE WHEN col_1 = 1 THEN '0'
 	 WHEN col_1 IS NULL THEN 'X'
END

3. NOT IN 和 NOT EXISTS不是等价的

有两张班级表Class_A，Class_B， 有列name，age，city，其中Class_B中住在东京的山田的age为NULL。查询"与B班住在东京的学生年龄不同的A班学生"。
表Class_A

name	age	city
布朗	22	东京
拉里	19	埼玉
伯杰	21	千叶

表Class_B

name	age	city
齐藤	22	东京
田尻	23	东京
山田		东京
和泉	18	千叶
武田	20	千叶
石川	19	神奈川

使用NOT IN将查询不到任何结果。如果NOT IN子查询中用到的表里被选择的列中存在NULL，则SQL语句整体查询结果永远是空。

SELECT *
FROM Class_A
WHERE age NOT IN (SELECT age
				  FROM Class_B
				  WHERE city = '东京');

* 以上条件相当于WHERE (age <>22) AND (age <>23) AND (age <> NULL)

使用NOT EXISTS可以返回结果。因为EXISTS谓词永远不会返回UNKNOWN，只会返回TRUE或FALSE。

SELECT *
FROM Class_A A
WHERE NOT EXISTS (SELECT *
				  FROM Class_B B
				  WHERE A.age = B.age
				  AND B.city ='东京');

* 子查询没有产生任何结果。因此相反地，NOT EXISTS为TRUE。

4. 限定谓词和NULL

SQL中有ALL和ANY两个限定谓词。因为ANY与IN是等价的，所以我们不经常使用ANY。
接上例，查询比B班住在东京的所有学生年龄都小的A班学生。

SELECT * 
FROM Class_A
WHERE age < ALL(SELECT age
 				FROM Class_B
     			WHERE city = '东京');

* 若B表中没有空年龄，以上查询能正常显示；若B表中有空年龄，结果不会为True，没有结果返回。
因为ALL谓词其实是多个以AND连接的逻辑表达式的省略写法。即以上条件相当于WHERE (age < 22) AND (age <23) AND (age < NULL)。

5. 极值函数和限定谓词不是等价的

接上例，查询比B班住在东京的的年龄最小的学生还要小的A班学生。
极值函数在统计时会把为NULL的数据排除掉，在输入为空表（空集）时会返回NULL。

SELECT *
FROM Class_A
WHERE age < (SELECT MIN(age)
       	     FROM Class _B
             WHERE city = '东京');

以下在两种情况下，这两种表达是不等价的：

1. 表里age存在NULL的时候
2. 谓词（或者函数）的输入为空集的情况，即Class_B表中没有东京学生的情况下。

在比较对象原本就不存在时，根据业务需求有时需要返回所有行，有时需要返回空集。需要返回所有行是，需要使用ALL谓词，或者使用COALESCE将极值函数返回的NULL处理成合适的值。

6. 聚合函数和NULL

当输入为空表时返回NULL的不只是极值函数，COUNT以外的聚合函数也是如此。
这种情况的的解决方法只有两种。要么把NULL改成具体值，要么接受NULL。
（如果某列有NOT NULL约束而我们有需要向其中插入聚合值，那么只能选择将NULL改成具体值。）

---