SQL中三值逻辑和NULL的具体使用

细雨如烟不染衣 · 发表于 2025-5-15 10:54:17

NULL的特性

NULL并不是一个具体值，而是一个特殊状态，其具有如下特性：

NULL不能相互比较：NULL = NULL结果不是TRUE，而是UNKNOWN。
NULL参与数值运算，结果为NULL：NULL + 10的结果仍然是NULL。
NULL参与逻辑运算，会影响逻辑结果：
- TRUE AND NULL，结果是UNKNOWN
- FALSE OR NULL，结果是UNKNOWN

如果WHERE条件结果为UNKNOWN，那么该记录将不会被查询结果包含。

NULL在SQL逻辑运算中的影响

1. 逻辑运算 (AND, OR, NOT)

AND运算

表达式	结果
TRUE AND TRUE	TRUE
TRUE AND FALSE	FALSE
TRUE AND UNKNOWN	UNKNOWN
FALSE AND UNKNOWN	FALSE
UNKNOWN AND UNKNOWN	UNKNOWN

OR运算

表达式	结果
TRUE OR UNKNOWN	TRUE
FALSE OR UNKNOWN	UNKNOWN
UNKNOWN OR UNKNOWN	UNKNOWN

NOT运算

表达式	结果
NOT TRUE	FALSE
NOT FALSE	TRUE
NOT UNKNOWN	UNKNOWN

2. NULL参与比较 (=, !=, >, <, etc.)

表达式	结果
NULL = NULL	UNKNOWN
NULL != NULL	UNKNOWN
NULL > 10	UNKNOWN
NULL < 10	UNKNOWN
NULL IS NULL	TRUE
NULL IS NOT NULL	FALSE

3. NULL在IN 和 NOT IN中的影响

如果 NULL 出现在 IN 或 NOT IN 语句中，会导致不可预期的结果：

SELECT * FROM users WHERE age IN (20, 30, NULL);

由于 NULL 是未知值，SQL 不知道 NULL 是否属于 age，导致 UNKNOWN，最终查询只会匹配 age=20 和 age=30，但不会匹配 NULL。

更严重的问题出现在 NOT IN 中：

SELECT * FROM users WHERE age NOT IN (20, 30, NULL);

由于 NULL 在 IN 语句中会返回 UNKNOWN，整个 NOT IN 变成 UNKNOWN，最终不会返回任何数据。

解决方法：

SELECT * FROM users WHERE age NOT IN (20, 30) OR age IS NULL;

4. NULL在DISTINCT、GROUP BY 和 ORDER BY 中

DISTINCT 视 NULL 为相同值：
```
SELECT DISTINCT category FROM products;
```
如果 category 列中有多个 NULL，DISTINCT 只会保留一个 NULL。
GROUP BY 视 NULL 为一个分组：
```
SELECT category, COUNT(*) FROM products GROUP BY category;
```
所有 NULL 值会被归为同一组。
ORDER BY 处理 NULL：
```
SELECT * FROM employees ORDER BY salary ASC;
```
NULL 默认排在最前或最后，具体行为取决于数据库：
- PostgreSQL: NULLS FIRST 或 NULLS LAST
- MySQL: NULL 默认排在最前
- SQL Server: NULL 默认排在最前

5. NULL在 COALESCE 和 IFNULL 处理

要避免 NULL 影响查询，可以使用 COALESCE 或 IFNULL 进行处理：

COALESCE(expr1, expr2, ..., exprN)：返回第一个非NULL值
```
SELECT name, COALESCE(email, '未知') AS email FROM users;
```

IFNULL(expr, default_value)（MySQL 专用）

SELECT name, IFNULL(email, '未知') AS email FROM users;

NULL 在 JOIN 中的影响

如果 NULL 存在于 JOIN 的关联列中，则该行不会被匹配：

SELECT * FROM orders
LEFT JOIN customers ON orders.customer_id = customers.id;

如果 orders.customer_id 是 NULL，= NULL 结果是 UNKNOWN，导致 INNER JOIN 失败。

LEFT JOIN 可以保留 orders 但 customers 数据为 NULL。

如何正确处理 NULL

查询时使用 IS NULL 和 IS NOT NULL
```
SELECT * FROM users WHERE email IS NULL;
```

避免 NULL 影响逻辑运算

SELECT * FROM orders WHERE discount IS NULL OR discount > 10;

在 JOIN 中考虑 NULL 可能带来的问题

SELECT * FROM orders
LEFT JOIN customers ON orders.customer_id = customers.id
WHERE customers.id IS NOT NULL;

使用 COALESCE() 处理 NULL

SELECT name, COALESCE(email, '未知') AS email FROM users;

正确使用 NOT IN

SELECT * FROM users WHERE age NOT IN (20, 30) OR age IS NULL;

总结（重点）

NULL 代表未知，不是空字符串或 0。
SQL 采用三值逻辑（TRUE, FALSE, UNKNOWN），导致 NULL 参与运算时可能返回 UNKNOWN。
NULL 不能用 = 直接比较，而要使用 IS NULL 和 IS NOT NULL。
NULL 可能影响 JOIN、GROUP BY、ORDER BY、IN/NOT IN 等查询，必须小心处理。
使用 COALESCE()、IFNULL() 等函数可以避免 NULL 带来的问题。

[MSSQL] SQL中三值逻辑和NULL的具体使用

目录