oracle vs sqlserver vs MariaDB vs PostgreSQL 处理NULL值比较

NULL值在数据库中是一个特殊的存在，你会发现每个数据库对它的处理方式都有所不同。在这篇文章中，我将测试所有四个数据库：SQL Server、PostgreSQL、MariaDB和Oracle，并比较它们如何处理表列中的NULL值。

在标准SQL 1992中，NULL的定义如下：

null value (null): A special value, or mark, that is used to
indicate the absence of any data value.

为了检查列是否包含NULL，我们应该使用“IS NULL”或“IS NOT NULL”谓词，而不是任何其他谓词类型。所以让我们从检查开始。

1. 准备测试 table

创建一个简单的表在所有数据库上都是一样的，所以下面是我创建的表，并将在本文中使用：

CREATE TABLE test_tab
(id INTEGER,
f1 INTEGER UNIQUE);

INSERT INTO test_tab VALUES(1,1);
INSERT INTO test_tab VALUES(2,NULL);
IS [NOT] NULL

2. “IS NULL”和“IS NOT NULL”

我们将从“IS NULL”和“IS NOT NULL”这两个非常基本的测试开始。我执行了两个查询：

SELECT * FROM test_tab WHERE f1 IS NULL;
SELECT * FROM test_tab WHERE f1 IS NOT NULL;

所有数据库的结果都是相同的：

-- SELECT * FROM test_tab WHERE f1 IS NULL;
id f1
---------- ----------
2

-- SELECT * FROM test_tab WHERE f1 IS NOT NULL;
id f1
---------- ----------
1 1

在开始其他测试之前，我想确保它们对不同的运算符（如“>”、“<”和“=”）的行为相同，所以我执行了以下查询：

SELECT * FROM test_tab WHERE f1=NULL;
SELECT * FROM test_tab WHERE f1<NULL;
SELECT * FROM test_tab WHERE NULL=NULL;

我甚至尝试了NULL=NULL，以确保它不会被评估为TRUE。所有数据库都没有为所有查询返回任何行。现在我们有了一个基线，即所有数据库的基本NULL比较都是相同的。让我们继续前进。

3. Unique 列

我们知道Oracle和SQL Server之间有区别，但不知道其他两种，所以想检查一下。问题是：数据库如何处理具有NULL值的UNIQUE列。当我创建上面的表时，我将“f1”列定义为UNIQUE。这将在该列上创建一个唯一的索引，并防止其中的重复值。如果“f1”是唯一的，我将无法在“f1”为“1”的地方插入另一行（我在创建表后的第一个插入命令中已经有了这个值）。但是，如果我试图插入另一个NULL（我已经有一个了），会发生什么呢。

INSERT INTO test_tab VALUES(3,NULL);

结果如何？MariaDB、PostgreSQL和Oracle表现相同，都可以插入NULL值：

1 row inserted.

然而，SQL Server返回了完全不同的内容：

Error starting at line : 15 in command -
INSERT INTO test_tab VALUES(3,NULL)
Error at Command Line : 15 Column : 1
Error report -
SQL Error: Violation of UNIQUE KEY constraint 'UQ__test_tab__32139E59C76E5D9B'. Cannot insert duplicate key in object 'dbo.test_tab'. The duplicate key value is (<NULL>).

对于SQL Server，2个NULL是相同的，并且违反了列上的UNIQUE约束。然而，在其他数据库中，2个NULL不相同，并且数据库允许一个唯一列有多个NULL值。

4. Indexes

想检查索引的使用情况，就必须检查每个数据库的执行计划。我将准备表中的数据：

TRUNCATE TABLE test_tab;
INSERT INTO test_tab VALUES(1,1);
INSERT INTO test_tab VALUES(2,2);
INSERT INTO test_tab VALUES(3,3);
INSERT INTO test_tab VALUES(null,4);
CREATE INDEX tst_null_idx ON test_tab(id);

现在，让我们检查返回NULL的查询的SQL执行计划：

MariaDB

-- The Query
EXPLAIN SELECT * FROM test_tab Where id IS NULL;

-- The plan
id select_type table type possible_keys key key_len ref rows Extra
-------------------- ------------------- ----------------- ---------- ---------------- ------------------------ ----------- ---------- ---------- ----------------------------
1 SIMPLE test_tab ref tst_null_idx tst_null_idx 5 const 1 Using index condition

在上面的输出中，您可以看到“type”列包含值“ref”。这意味着在MariaDB中进行索引遍历，因此MariaDB扫描索引以找到“id”列为NULL的行。

PostgreSQL

-- The query
EXPLAIN SELECT * FROM test_tab Where id IS NULL;

-- The plan
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on test_tab (cost=4.24..14.88 rows=11 width=8)
Recheck Cond: (id IS NULL)
-> Bitmap Index Scan on tst_null_idx (cost=0.00..4.24 rows=11 width=0)
Index Cond: (id IS NULL)

我们在上面的计划中看到，PostgreSQL使用基于“id is NULL”条件的索引（最后一行）来查找相关行。

SQL Server

-- The query
SET showplan_all on;
SELECT * FROM test_tab with (index(tst_null_idx)) Where id IS NULL;

-- The plan
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------- ---------------------------------------- -------------- -------------- -------------- ----------- ---------------- ----------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
SELECT * FROM test_tab with (index(tst_null_idx)) Where id IS NULL 1 1 0 1 1.0 0.00657038 SELECT false
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([Bmk1000]) 1.0 0.0 4.18E-6 15 0.00657038 [test].[dbo].[test_tab].[id], [test].[dbo].[test_tab].[f1] PLAN_ROW false 1.0
|--Index Seek(OBJECT:([test].[dbo].[test_tab].[tst_null_idx]), SEEK:([test].[dbo].[test_tab].[id]=NULL) ORDERED FORWARD) 1 3 2 Index Seek Index Seek OBJECT:([test].[dbo].[test_tab].[tst_null_idx]), SEEK:([test].[dbo].[test_tab].[id]=NULL) ORDERED FORWARD, FORCEDINDEX [Bmk1000], [test].[dbo].[test_tab].[id] 1.0 0.003125 1.581E-4 19 0.0032831 [Bmk1000], [test].[dbo].[test_tab].[id] PLAN_ROW false 1.0
|--RID Lookup(OBJECT:([test].[dbo].[test_tab]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) 1 5 2 RID Lookup RID Lookup OBJECT:([test].[dbo].[test_tab]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD [test].[dbo].[test_tab].[f1] 1.0 0.003125 1.581E-4 11 0.0032831 [test].[dbo].[test_tab].[f1] PLAN_ROW false 1.0

这个计划有点长，但如果你看第二行到最后一行，你会看到“Index Seek”，这表明SQL Server使用索引来查找相关的行，就像PostgreSQL和MariaDB一样。如果你阅读了该查询，您可能还会注意到我使用了一个提示（“with（index（tst_null_idx））”）来要求SQL Server使用该索引。如果没有它，它就不会使用索引，而是简单地读取整个表。我的猜测是，这是因为统计数据和效率，这对小表来说是有意义的（Oracle的行为也是一样的）。因为我想在这种情况下强制它使用索引，所以我使用了提示。

Oracle

-- The queries
SELECT /*+ index(test_tab) */ * FROM test_tab WHERE id IS NULL;
SELECT * FROM TABLE(dbms_xplan.display_cursor());

-- The plan
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 1 | 26 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

oracle是个例外。当其他数据库使用索引来获取“id”为NULL的行时，Oracle扫描了整个表（计划中的最后一行）。如果您查看查询，我也尝试使用提示（/*+索引（test_tab）*/部分），但Oracle只是忽略了它。之所以会出现这种情况，是因为Oracle不在索引中保留NULL（显然这是四个索引中唯一不保留NULL的）。因此，当我有一个IS NULL谓词时，Oracle无法使用索引来查找行，因为信息根本不存在。在这些情况下，Oracle将始终执行完整的表扫描。请注意，“IS NOT NULL”可以使用索引，因为所有其他值都在索引中，只有NULL不在。

5. 结论

• 使用NULL的基本谓词（IS NULL、IS NOT NULL和其他具有NULL的运算符，如<、>、=等）在所有四个数据库上的行为完全相同。

• 当使用具有UNIQUE约束的列时，Oracle、MariaDB和PostgreSQL允许列中有多行NULL值，而SQL Server只允许一行NULL值。

• 当在索引列上使用“IS NULL”谓词进行查询时，SQL Server、PostgreSQL和MariaDB可以使用索引来满足谓词，而Oracle不能也将执行表扫描。