SQL使用Null等级测试

下面链接的这篇文章列出了在SQL标准规范里如何使用NULL的一些测试。

https://agentm.github.io/project-m36/posts/2024-07-16-are-you-qualified-to-use-null.html

本文整理了在PostgreSQL里的测试结果：

等级I‌

1.Boolean类型与NULL

SELECT NULL AND TRUE;
复制

• NULL
• FALSE

2.Boolean类型与NULL

SELECT NULL AND FALSE;
复制

• NULL
• FALSE

3.SUM函数与NULL

SELECT SUM(a) FROM (VALUES (3),(NULL)) as x(a);
复制

• 3
• NULL

4.COUNT函数与NULL

SELECT SUM(a) FROM (VALUES (3),(NULL)) as x(a);
复制

• 1
• NULL

5.等号操作符与NULL

SELECT * FROM (VALUES (3),(NULL)) as x(a) WHERE a = NULL;
复制

• (0 rows)
• (1 rows)

6.IS NULL

SELECT * FROM (VALUES (3),(NULL)) as x(a) WHERE a IS NULL;
复制

• (0 rows)
• (1 rows)

参考答案

1. NULL
2. FALSE
3. 3
4. 1
5. (0 rows)
6. (1 rows)

测试等级II‌

1.OUTER JOIN与NULL

SELECT * FROM (VALUES (3),(NULL)) as x(a) LEFT OUTER JOIN (VALUES (3),(NULL)) as y(a) ON x.a = y.a;
复制

• (2 rows)
• (1 rows)

2.SUM函数与NULL

SELECT SUM(a) FROM (VALUES (NULL::int)) AS x(a);
复制

• NULL
• 0

3.SUM函数与空结果集

SELECT SUM(a) FROM (VALUES (1),(2)) as x(a) WHERE false;
复制

• 0
• NULL

4.AVG函数与NULL

SELECT SUM(a) FROM (VALUES (1),(2)) as x(a) WHERE false;
复制

• NULL
• 3.0

5.NULL与IN

SELECT * FROM (VALUES (3),(NULL)) as x(a) WHERE a IN (NULL,3);
复制

• (1 rows)
• (2 rows)

6.NULL与NOT IN

SELECT * FROM (VALUES (3),(NULL)) as x(a) WHERE a NOT IN (1);
复制

• (1 rows)
• (2 rows)

7.NULL与CONCAT函数

SELECT CONCAT('uh oh', NULL);
复制

• uh oh
• NULL

8.NULL与 ||

SELECT 'uh oh' || NULL;
复制

• uh oh
• NULL

正确答案

1. (2 rows)
2. NULL
3. NULL
4. 3.0
5. (1 rows)
6. (1 rows)
7. uh oh
8. NULL

测试等级III‌

1.NULL与check约束

CREATE TABLE test(a INTEGER CHECK(a>0)); 
INSERT INTO test(a) VALUES (NULL);
复制

• 因违反约束条件而插入失败
• 插入成功

2.NULL与DISTINCT

SELECT DISTINCT(a) FROM (VALUES (3),(NULL),(NULL)) as x(a);
复制

• (2 rows)
• (1 rows)

3.NULL与GROUP BY

SELECT a FROM (VALUES (3),(NULL),(NULL)) as x(a) GROUP BY a;
复制

• (2 rows)
• (1 rows)

4.NULL与GROUP BY + CASE

SELECT CASE WHEN a IS NULL THEN 5 ELSE a END FROM (VALUES (3),(NULL),(NULL)) as x(a) GROUP BY a;
复制

• 3,5
• 3,NULL

5.NULL与GROUP BY +NULL equality + CASE

SELECT CASE WHEN a = NULL THEN 5 ELSE a END FROM (VALUES (3),(NULL),(NULL)) as x(a) GROUP BY a;
复制

• 3,NULL
• 3,5

6.NULL与唯一约束

CREATE TABLE y(a INTEGER, UNIQUE(a));
INSERT INTO y(a) VALUES (NULL),(NULL),(3); 
TABLE y;
复制

• NULL,NULL,3
• NULL,3

7.NULL与EXISTS

SELECT 5 WHERE EXISTS(SELECT NULL);
复制

• (1 rows)
• (0 rows)

8.NULL类型转换

SELECT CAST(NULL AS INTEGER) IS DISTINCT FROM CAST(NULL AS TEXT);
复制

• False
• 转型错误

正确答案

1. 插入成功
2. (2 rows)
3. (2 rows)
4. 3,5
5. 3,NULL
6. NULL,NULL,3
7. (1 rows)
8. 转型错误