下面链接的这篇文章列出了在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. 转型错误

再整理几个NULL相关的案例：

1.NULL与操作符

当使用"||"连接符和(+, -, *, /)算术运算符，以及(=, <, >, <=, >=, <>)比较操作符时，如果有NULL值会引起结果丢失。

为了更安全的使用NULL，在做连接时需要使用COALESCE函数，做比较运算时，(=, <>)可替换为 IS [NOT] DISTINCT FROM，动态语句传递变量时需要使用format函数，集合操作NOT IN可替换为NOT EXISTS。

2.NULL与索引

使用=或<>操作符时，运算结果不一定是布尔值，可能有NULL参与运算，确保返回布尔值需进行如下替换：

替换后的IS NOT DISTINCT FROM会绕过索引，如果既需要保证安全的布尔值，又需要走索引，可参考如下的SQL等价改写。

改写前的SQL

SELECT * FROM foo
WHERE bar IS NOT DISTINCT FROM baz;

改写后的SQL

SELECT * FROM foo
WHERE (bar IS NULL AND baz IS NULL)
OR bar = baz;

3.NULL与UNIQUE字段

由于NULL != NULL，UNIQUE字段允许有多个NULL值，从版本15开始能控制UNIQUE字段是否允许有多个NULL值，语法如下：

UNIQUE NULLS DISTINCT 
UNIQUE NULLS NOT DISTINCT 

例如下面表字段column1只允许插入一个NULL值，使用UNIQUE NULLS NOT DISTINCT

CREATE TABLE tab1(
  column1 VARCHAR, 
UNIQUE NULLS NOT DISTINCT(column1) 
);

15之前的版本可以使用带where条件的表达式索引来实现：

CREATE UNIQUE INDEX ON tab1 ((column1 is null)) 
WHERE column1 IS NULL;

4.NULL与外键引用

如果外键引用多个字段时，可以使用num_nulls/num_nonnulls函数更好的定义约束，例如只允许外键引用字段列表里任意一个字段非空:

CHECK (num_nonnulls(column1, column2, column3) = 1);

参考示例如下：

CREATE TABLE companies (
id serial primary key,
name text
);
CREATE TABLE orders (
id serial primary key
);
CREATE TABLE users  (
id serial primary key,
name text
);
CREATE TABLE notifications (
id serial,
    company_id INT REFERENCES companies (id),
    order_id INT REFERENCES orders (id),
    user_id INT REFERENCES users (id)
);

notifications表添加约束：要求外键引用的三个字段列表有一个字段不为NULL

ALTER TABLE notifications
ADD CONSTRAINT notifications_reference
CHECK (num_nonnulls(company_id, order_id, user_id) = 1);

版本15对外键引用多字段设置on delete级联删除时还允许对单个字段设置NULL值或者默认值，示例如下：

 CREATE TABLE tab_pk ( 
 tid int, 
id int, 
 primary key (tid, id)
 );

CREATE TABLE tab_fk ( 
 tid int, 
id int, 
 fk_id_del_set_null int, 
 fk_id_del_set_default int default 0, 
foreign key (tid, fk_id_del_set_null) references tab_pk  on delete set null (fk_id_del_set_null), 
foreign key (tid, fk_id_del_set_default) references tab_pk on delete set default (fk_id_del_set_default) 
);

5.NULL与默认值

MySQL迁移过来的表字段上有default NULL的设置，迁移工具做了如下转化处理：

CREATE TABLE tab(action varbinary default NULL::varbinary);

增加了对NULL按实际类型转换的处理，这个默认值的处理对后续修改字段类型造成了影响。

ALTER TABLE tab 
ALTER COLUMN action TYPE varchar(8) USING action::varchar(8);

上面的语句通常情况下是可以正常执行，不过由于默认值类型转换导致修改会失败：

ERROR:  default for column "action" cannot be cast automatically to type varchar

上面的默认值定义无实际用处，但造成了字段类型修改时报错，因此也需要先调整处理默认值。

可以将默认值的调整和字段类型的修改放在一个事务里进行操作：

ALTER TABLE tab
ALTER COLUMN action DROP DEFAULT,
ALTER COLUMN action TYPE varchar(8) USING action::varchar(8),
   ALTER COLUMN action SET DEFAULT NULL::varchar(8);

本文结束～