在 SQL Server 中处理 NULL

Math08avan

4.79/5 (10投票s)

2015 年 10 月 3 日

CPOL

6分钟阅读

44727

148

理解 SQL Server 中缺失值是如何处理的重要事项

Download source - 1.1 KB

什么是 NULL？

在数据库中，一个常见的问题是如何表示缺失值。在 SQL Server 中，这个问题通过 NULL 来解决。它用于表示缺失或未知的值。关键字 NULL 用于指示这些值。当我们提到 NULL 值时，大多数时候我们称之为“NULL 值”。NULL 实际上并不是一个具体的值，它不是不存在的值，也不是零值或空字符串值。两个 NULL 值不相等。

NULL 是一种指示数据值不存在的状态。数据库引擎使用一个特殊的位图来跟踪行中哪些列是 NULL，哪些不是。该位图包含每一列的一个位，如果列是 NULL，则该位设置为 1。

例如，以下 SELECT 语句返回 BookDetails 表中的数据。

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails

如下面的结果所示

在上表中，有些列包含缺失或未知的值。SQL 保留 NULL 关键字来表示未知或缺失的值。在 SQL 中，NULL 的处理方式与值不同。在比较 null 值或在表达式中使用它们时，需要特别注意。

NULL 值比较

开发人员，特别是初学者 T-SQL 开发人员，如果不了解 NULL 的工作原理，有时会使用比较运算符将表达式与 NULL 值进行比较。

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate = NULL

他们的假设是，NULL 是一个有效值，可以与其他 NULL 值进行比较。因此，上述语句应该返回 ReleaseDate 值为 NULL 的所有行。根据假设的逻辑，BookId 1, 3 将会返回。但是数据库引擎无法比较空与空（NULL 与 NULL）。它将返回一个空结果集。

我们需要返回非 NULL 数据

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate <> NULL

上述语句试图将实际的非 NULL 值与 NULL 进行比较。这似乎是一个合理的逻辑。因此，根据假设的逻辑，BookId 2, 4 和 5 将会返回。但是我们不能将实际值与空进行比较。结果是，尽管我们确定数据存在，该语句仍然不返回任何数据。

正确的 NULL 比较方法

SQL 通过 IS NULL 和 IS NOT NULL 比较来处理这种情况，用于测试 NULL 值。

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate IS NULL

上面的 SELECT 语句使用 IS NULL 操作符来返回 ReleaseDate 为 NULL 的行。

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate IS NOT NULL

上面的 SELECT 语句使用 IS NOT NULL 操作符来返回 ReleaseDate 不为 NULL 的行。

设置 ANSI_NULL 以进行 NULL 值比较

SQL Server 支持一个名为 ANSI_NULLS 的功能，它决定数据库引擎如何处理 NULL 比较。ANSI_NULLS 选项控制我们是否可以使用等于运算符与 NULL 进行比较，使其含义等同于 IS NULL。ANSI SQL 标准规定 NULL 永远不等于任何东西。

默认情况下，SQL Server 安装时 ANSI_NULLS 选项是开启的，这会产生上述描述的结果。

检查当前的 ANSI_NULLS 设置

IF ((32 & @@OPTIONS) = 32)
  PRINT 'The ANSI_NULLS option turned on.'
ELSE
  PRINT 'The ANSI_NULLS option turned off.'

如果数据库引擎中 ANSI_NULLS 没有发生改变，它将返回“ANSI_NULLS 选项已开启。”

我们可以关闭 ANSI_NULLS 选项。SET 命令仅在执行命令的连接中改变行为。如果在存储过程中执行 SET 命令，该命令通常会控制该存储过程的行为。

SET ANSI_NULLS OFF;
SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate = NULL

SET ANSI_NULLS OFF;
SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ReleaseDate <> NULL

ISNULL & IS NULL

ISNULL 和 IS NULL 之间是另一个常见的混淆点。ISNULL 的含义与 IS NULL 操作符类似。让我们通过示例来理解。

返回作者值为 NULL 的图书详情。

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE Author IS NULL

如果我们不理解 ISNULL，可能会尝试使用 ISNULL 在 WHERE 子句中检索相同的结果

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ISNULL(Author)

幸运的是，我们可能会收到错误消息。

ISNULL 函数的目的是用一个实际值替换表达式的值。如果我们想正确地返回上面的示例，我们需要正确定义我们的布尔条件。

SELECT BookId, Title, ReleaseDate, Price, Author
FROM BookDetails
WHERE ISNULL(Author, 'nonexistent') IN ('nonexistent')

更常见的情况是，我们希望在 SELECT 语句中使用 ISNULL 函数来将 NULL 值替换为表达式的值。

SELECT BookId, Title, ReleaseDate, Price, ISNULL(Author, 'nonexistent') AS Author
FROM BookDetails

现在 Author 列对所有行都有值了。所有的 NULL 都被更改为不存在。

COALESCE

在深入之前，ISNULL - 在 SQL Server 中实现，是该品牌的 T-SQL 特有的，而 COALESCE 是标准的 ANSI。这两个函数都允许我们用表达式的值替换 NULL，但它们之间存在差异。COALESCE 函数允许处理两个以上的表达式。

例如，以下 SELECT 语句返回 StudentDetails 表中的数据。

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III
FROM StudentDetails

让我们将 COALESCE 函数应用于上面的 SELECT 语句。

SELECT StudentId, StudentName, Department, 
Semester_I, Semester_II, Semester_III,
COALESCE(Semester_I, Semester_II, Semester_III, 0) AS COALESCE_Result
FROM StudentDetails

COALESCE 函数按指定的顺序评估表达式，并返回第一个非 NULL 值。COALESCE_Result 列显示 COALESCE 函数的结果。

实际上 COALESCE 函数是 CASE 表达式的简写，但是 CASE 表达式的构造很复杂。CASE 表达式的优点是它能非常清晰地展示逻辑。

SELECT StudentId, StudentName, Department, 
Semester_I, Semester_II, Semester_III,
COALESCE(Semester_I, Semester_II, Semester_III, 0) AS COALESCE_Result,
CASE
	WHEN Semester_I IS NOT NULL THEN Semester_I
	WHEN Semester_II IS NOT NULL THEN Semester_II
	WHEN Semester_III IS NOT NULL THEN Semester_III
	ELSE 0
END CASE_Result
FROM StudentDetails

COALESCE 与 ISNULL 的比较

ISNULL 函数是有限的。它只能指定两个表达式。

SELECT StudentId, StudentName, Department, 
Semester_I, ISNULL(Semester_II, 0) AS ISNULL_Result, Semester_III
FROM StudentDetails

在这种情况下，我们定义如果 Semester_II 是 NULL，则使用 0。

COALESCE 的数据类型处理方式不同。为了理解这一点，让我们从一个例子开始

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III
FROM StudentDetails
WHERE StudentId = 5

StudentId、Semester_I、Semester_II 和 Semester_III 列定义为 Int，StudentName 和 Department 列定义为 Varchar。上述语句返回以下结果。

现在让我们包含 COALESCE 函数。

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
COALESCE(Department, StudentName, Semester_I) AS COALESCE_Result
FROM StudentDetails
WHERE StudentId = 5

这次，我们遇到了一个错误消息。

COALESCE 使用具有最高优先级的类型，基于提交的值。Semester_I 列接受一个 Int，而 Int 的优先级高于 Varchar。因此，数据库引擎会尝试将 EEE 转换为 Int 值。数据库引擎不关心顺序。

现在让我们包含 ISNULL 函数。

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
ISNULL(Department, Semester_I) AS ISNULL_Result
FROM StudentDetails
WHERE StudentId = 5

在上面的 SELECT 语句中，ISNULL 函数的 Department 设定了标准，它是一个 Varchar 数据类型。所以数据库引擎没有问题。

现在，让我们改变 ISNULL 函数的顺序。

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
ISNULL(Semester_I, Department) AS ISNULL_Result
FROM StudentDetails
WHERE StudentId = 5

这次，我们也遇到了一个错误消息。因为 Semester_I 设定了标准，它是一个 Int 数据类型。所以数据库引擎无法将 'EEE' 转换为 Int 数据类型。

任何值 + NULL = 任何值？

当我们尝试进行计算时，例如将一个值加到空值、从空值减去、乘以空值、除以空值，或者对空值进行任何操作，我们最终得到的是空值，即 NULL。让我们看看这个 SELECT 语句

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
(Semester_I + Semester_II + Semester_III) AS TotalResult
FROM StudentDetails
WHERE StudentId = 5

当我们尝试通过 NULL 进行计算（这里使用了加法）时，我们会得到 NULL。我们可以通过为 NULL 定义一个默认值来使其合理。

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
(ISNULL(Semester_I, 0) + ISNULL(Semester_II,0) + ISNULL(Semester_III,0)) AS TotalResult
FROM StudentDetails
WHERE StudentId = 5

在这里，我们在加法函数内部的每个参数中使用 ISNULL 函数，并将其默认值设置为 0。所以现在 Semester_I 返回 0，因此 TotalResult 显示 90。

SELECT StudentId, StudentName, Department, Semester_I, Semester_II, Semester_III,
ISNULL((Semester_I + Semester_II + Semester_III),0) AS TotalResult
FROM StudentDetails
WHERE StudentId = 5

在这里，我们对加法的结果使用了 ISNULL 函数，其默认值为 0。

因此，如果 Semester_I 是 null，那么 Addition 结果返回 NULL。在 ISNULL 函数外部返回默认值 0。

摘要

希望这些解释能为您在 SQL Server 中处理 NULL 提供一些信息。