Oracle、MySQL、PostGreSQL、SQL Server-空值

Oracle、MySQL、PostGreSQL、SQL Server-null value

最近几年数据库市场百花齐放，在做跨数据库迁移的数据库选型时，除了性能、稳定、安全、运维、功能、可扩展外，像开发中对于值的处理往往容易被人忽视， 之前写过一篇关于PG区别Oracle在SQL解析缓存的笔记《PostgreSQL 12 : Prepare statement和plan_cache_mode 参数》，这里记录一下null 值在这几个数据库中的区别。

软件版本：Oracle 21c 、SQL Server 2019 、MySQL 8.0 、Mariadb 10.6 、PostGreSQL 13、OpenGauss 2.0

创建测试用例表

CREATE TABLE tab_null(id int, name char(10));
 
INSERT INTO tab_null VALUES(1,'anbob');
INSERT INTO tab_null VALUES(2,NULL);

测试数据过滤

# oracle
SQL> select * from tab_null where name is null;
        ID NAME
---------- ----------
         2

SQL> select * from tab_null where name is not null;
        ID NAME
---------- ----------
         1 anbob

SQL> select * from tab_null where name=null;
no rows selected

SQL> select * from tab_null where null=null;
no rows selected

# postgresql
postgres=# select * from tab_null where name is null;
 id | name
----+------
  2 |
(1 row)

postgres=# select * from tab_null where name is not null;
 id |    name
----+------------
  1 | anbob
(1 row)

postgres=# select * from tab_null where name=null;
 id | name
----+------
(0 rows)

postgres=# select * from tab_null where null=null;
 id | name
----+------
(0 rows)

# MySQL/MariaDB
mysql> select * from tab_null where name is null;
+------+------+
| id   | name |
+------+------+
|    2 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_null where name is not null;
+------+-------+
| id   | name  |
+------+-------+
|    1 | anbob |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from tab_null where name=null;
Empty set (0.00 sec)

mysql> select * from tab_null where null=null;
Empty set (0.00 sec)

# SQL Server

select * from tab_null where name is null;
        ID NAME
---------- ----------
         2

select * from tab_null where name is not null;
        ID NAME
---------- ----------</