【实用技能】如何在 SQL Server 中处理 Null 或空值？

在使用数据库时，我们经常会遇到缺少数据的行。这些缺失数据可能是由于未知或不适用的值、数据导入或输入过程中的错误或涉及不存在值的特定计算造成的。在这种情况下，有两种表示缺失数据的方法：NULL 和空值（或空白值）。

虽然乍一看它们似乎相同，但它们是不同的，并且以不同的方式影响基本数据库操作。本文探讨 SQL Server 中 NULL 和空值之间的区别，并讨论如何有效地处理它们。

dbForge Studio for SQL Server官方正版下载

SQL Server 中的 NULL 和空值

NULL 表示数据库列中缺失或未知的数据。这可能发生在两种情况下：数据不存在或数据存在但当前未知。NULL 可以分配给任何数据类型的字段，包括字符串、整数和日期。重要的是，该字段没有分配内存，因为 NULL 表示未知值。

相反，数据库中的空白或空白区域是指空字符或空白字符。虽然其含义可能看起来与 NULL 相似，但它的存储和检索方式与文本字段中的任何其他字符一样。空字符串特定于字符串列，不能应用于不同的数据类型。

例如，考虑一个包含产品信息的表，其中有一列存储保修详细信息。此列中的 NULL 值表示未指定保修期。相反，空值表示产品没有保修。

在数据库中，NULL 值和空白字符串在定义、语法和长度上有所不同，并且在查询和数据操作中对它们的处理也不同。因此，分别检测 NULL 和空值通常是必不可少的。大多数数据库管理系统（包括 SQL Server）都提供了有效处理这种区别的工具和功能。

查找 NULL 或空值的标准方法

根据具体情况，如果 NULL 和空值代表相似的概念，则可将它们一起处理；如果它们在数据模型中具有不同的含义或条件，则可将它们分开处理。这种区别会显著影响查询性能和结果的准确性。

最常见的情况是，需要通过删除 NULL 和空值或将 NULL 替换为其他值（如空）来避免 NULL 值错误。为了有效地管理这种情况，用户需要可靠的方法来识别 NULL 和空列值。本指南探讨了 SQL Server 中可用的内置工具，包括专用查询和函数。

使用 IS NULL 运算符

SQL Server 中的 IS NULL 运算符检查列或表达式是否包含 NULL 值。基本查询语法如下：

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

让我们看一个简单的例子。在此示例和后续示例中，我们将使用流行的 SQL Server AdventureWorks2022 测试数据库和SQL Server dbForge Studio来演示测试用例。

假设我们需要检索产品列表，包括其名称和重量，其中重量小于 10 磅或颜色未知（即 NULL）。以下是实现此目的的查询：

SELECT
 pt.ProductID
,Name
,Weight
,Color
FROM [Product.Test] pt
WHERE Color IS NULL  

搜索空字符串

正如我们前面提到的，空值是长度为零的字符串，这会导致问题，因为空字符串不等于 NULL 值。SQL Server 对它们进行不同的处理，在具有 WHERE 条件的查询中使用 IS NULL 运算符不会返回空字符串。搜索空值的条件语法是：

WHERE column_name = ''

因此，基本查询语法是：

SELECT column_names
FROM table_name
WHERE column_name = ''

假设我们要检索Style列包含空值的产品列表。 在这种情况下，我们需要搜索空值：

SELECT
 pt.Name
,pt.ProductNumber
,pt.Style
FROM [Product.Test] pt
WHERE pt.Style = ''

用户经常需要同时获取 NULL 和空值。然后，我们可以使用 OR 运算符将 IS NULL 运算符与空值搜索结合起来，如下所示：

SELECT
 column_names
FROM table_name
WHERE column_name = ''
OR column_name IS NULL

我们要检查表中是否所有产品都分配了ListPrice值。为此，我们要检查是否有产品的ListPrice为 NULL 且ListPrice为空：

SELECT
 ProductID
,Name
,ProductNumber
,ListPrice
FROM dbo.[Product.Test]
WHERE ListPrice = ''
OR ListPrice IS NULL

输出包含空字符串和 NULL 值，从而给出更广泛的结果。

使用 TRIM 函数来获取仅包含空格的值

某些列可能包含完全由空格组成的值，这在从各种来源导入数据时很常见。这些值通常被视为空，因为它们缺乏有意义的字符。要识别此类行，您可以使用 TRIM 函数。

默认情况下，TRIM 会删除前导和尾随空格，但也可以删除字符串开头和结尾的其他指定字符。在这种情况下，我们使用这个函数在以标准方式检查空值之前删除空格。

基本查询语法是：

SELECT
 column_name
FROM table_name
WHERE column_name IS NULL
OR TRIM(column_name) = ''

以下查询选择列Color、Size、ProductLine、Class和Style为 NULL 或在修剪任何前导和尾随空格后实际上为空的行。

SELECT
 Color
,Size
,ProductLine
,Class
,Style
FROM dbo.[Product.Test]
WHERE (Color IS NULL
OR TRIM(Color) = '')
OR (Size IS NULL
OR TRIM(Size) = '')
OR (ProductLine IS NULL
OR TRIM(ProductLine) = '')
OR (Class IS NULL
OR TRIM(Class) = '')
OR (Style IS NULL
OR TRIM(Style) = '')

它可以帮助我们确保指定列中没有空值或无意义的值。

内置 SQL Server 函数

除了 SQL 查询之外，Microsoft SQL Server 还提供了专门用于处理 NULL 值的内置函数。在本节中，我们将探讨它们的工作原理。

使用 COALESCE 函数

SQL COALESCE 允许我们用默认值替换 NULL，从而确保输出中只有有意义的数据。当 NULL 值可能破坏计算或损害数据准确性时，此功能非常有用。

语法是：

COALESCE (expression [ ,...n ] )

我们使用的测试表包含一些 NULL 和一些空值，而不是有意义的数据。在我们的场景中，我们想要检索缺少一些基本参数的产品名称。包含颜色和尺寸 NULL 的行将返回为未知，而未提供ListPrice 的行将返回为 0。

SELECT
 Name
,Color
,Size
,ListPrice
,COALESCE(Color, 'No Color') AS MissingColor
,COALESCE(Size, 'No Size') AS MissingSize
,COALESCE(ListPrice, 0) AS MissingListPrice
FROM dbo.[Product.Test]

结果，我们得到一个定义所有具有 NULL 值的案例的表，并可以进一步处理数据。

SQL Server 中的 COALESCE 函数可以与 TRIM 函数一起使用，通过一个查询检索同时具有 NULL 和空值的行。

语法是：

SELECT
 column_name
FROM table_name
WHERE TRIM(COALESCE(code, '')) = ''

这里，代码是需要过滤数据的列的名称。

在我们的测试用例中，我们想要识别Color列中具有 NULL 或空值的产品：

SELECT
 ProductID
,Name
,Color
FROM dbo.[Product.Test]
WHERE TRIM(COALESCE(Color, '')) = ''

此查询识别具有 NULL 或空白颜色值的产品，并确保仅包含空格的字符串被视为空。

使用 NULLIF 函数

NULLIF 函数比较两个表达式，如果它们相等，则返回 NULL。当应用于包含空值的列时，它返回 NULL，允许我们使用 IS NULL 运算符检查 NULL：

SELECT
 column_name
FROM table_name
WHERE NULLIF(TRIM(code), '') IS NULL

看看下面的例子：

SELECT
 Name
,Color
,Size
FROM dbo.[Product.Test]
WHERE NULLIF(TRIM(COALESCE(Color, '')), '') IS NULL
OR NULLIF(TRIM(COALESCE(Size, '')), '') IS NULL

此查询使用 NULLIF 和 TRIM 函数有效地从表中过滤并返回Color或Size列为 NULL、空或仅包含空格的行。

使用 ISNULL 函数

ISNULL 函数用预定义的有意义的值替换 NULL。

该函数的语法是：

ISNULL(expression, replacement)

这里，expression是列名，而replacement是当列值为NULL时将替换该列的值。

在下面的例子中，我们检索产品颜色、尺寸和类别的数据，并用预定义值Unknown替换 NULL ：

SELECT
 Name
,ISNULL(NULLIF(LTRIM(RTRIM(Color)), ''), 'Unknown') AS Color
,ISNULL(NULLIF(LTRIM(RTRIM(Size)), ''), 'Unknown') AS Size
,ISNULL(NULLIF(LTRIM(RTRIM(Class)), ''), 'Unknown') AS Class
FROM dbo.[Product.Test]

管理 NULL 或空值的高级技术

处理 NULL 和空值通常涉及高级技术，以实现更高效的数据处理和更精确的结果。

• 结合多种功能

您可能已经注意到函数组合的使用，例如 TRIM 与 COALESCE 或 TRIM 与 ISNULL。多个函数的组合允许更高级的数据操作，从而提供精确且有针对性的结果。

以下查询演示了如何通过删除空格并用占位符替换 NULL 值来清理Color列中的数据，以识别缺少颜色定义的记录：

SELECT
 ProductID
,Name
,ISNULL(NULLIF(TRIM(COALESCE(Color, '')), ''), 'Not provided') AS Color
FROM dbo.[Product.Test]

COALESCE 函数将Color中的所有 NULL 值替换为空字符串，从而可以安全地应用 TRIM，进而从Color列中删除所有前导或尾随空格。NULLIF(TRIM(…),”) 将空字符串（最初为空或修剪为空）转换回 NULL。ISNULL(…, 'Not provided') 将任何 NULL 值（无论是最初为 NULL 还是由 NULLIF 转换为 NULL）替换为字符串Not provided。

• 使用 CASE

在 SQL Server 中，您可以使用条件表达式（例如 CASE）以及 ISNULL、COALESCE 和 TRIM 等函数来处理不同类型的缺失数据。在这种情况下，ISNULL() 或 COALESCE() 会用预定义的占位符替换 NULL，TRIM 会删除前导和尾随空格并检查空字符串 (”)，而 CASE 与 TRIM 结合可确保将仅包含空格的字符串视为空。

下面是使用Product.Test表的示例查询，旨在根据缺失数据的类型将Class分类：

SELECT
 ProductID
,Name
,Class
,CASE
   WHEN Class IS NULL THEN 'Missing (NULL)'
   WHEN TRIM(Class) = '' THEN 'Missing (Empty or Spaces)'
   ELSE Class
 END AS ProductClassStatus
FROM dbo.[Product.Test]

这种先进的技术有助于确保一致地处理缺失数据，并清理数据以进行分析、报告和验证。

具有 NULL 和空值的大型数据集的性能注意事项

处理包含 NULL 和空值的大型数据集时，性能考虑至关重要，因为不同的因素会显著影响查询执行和资源使用。考虑以下因素和策略来优化性能：

• 索引使用

SQL Server 中 NULL 值的索引方式不同，查询过滤可能无法有效利用索引。为避免出现问题，请使用过滤索引以仅包含非 NULL 或相关行（例如，WHERE Column IS NOT NULL）。

• 函数使用

直接在 WHERE 子句或索引列中应用 ISNULL、COALESCE 和 TRIM 等函数可能会阻止索引使用并导致全表扫描。解决方案是重组查询以从 WHERE 子句中删除这些函数。此外，先进的现代 ETL 解决方案提供内置工具以立即清理数据。

• 存储效率

如果管理不当，与内存分配相关的 NULL 和空值的不同处理可能会导致存储开销。为了避免这种情况，请在数据输入期间评估列默认值以尽量减少 NULL 和空值。

• 查询设计和优化

复杂的条件表达式可能会导致大型数据集的性能下降。解决方案可能是将 NULL 和空值分成不同的查询过程。此外，在执行查询之前利用执行计划分析来识别查询瓶颈，这将有助于相应地优化它们。

• 统计数据和基数

在 SQL Server 中，包含许多 NULL 或空值的列的基数估计可能会受到影响。使用专用的 UPDATE STATISTICS 命令或自动更新功能定期更新统计信息至关重要。

这些策略可以帮助您确保有效处理具有 NULL 和空值的大型数据集，同时最大限度地减少资源消耗和查询执行时间。

结论

NULL 和空值在数据库中很常见，因此了解它们的概念、区分它们并适当处理它们至关重要。本文探讨了识别和解决由 NULL 或空值定义的缺失数据情况的可靠方法。它提供了实用的技术来帮助清理数据并确保计算准确。为了说明这些情况，我们使用了 dbForge Studio for SQL Server，这是一种擅长处理此类情况的工具。

dbForge Studio for SQL Server 提供了一个功能强大的 SQL 编辑器，其中包含基于上下文的建议、代码分析、语法验证、格式和代码片段，使用户能够以两倍的速度编写高质量的 SQL 代码。此外，Studio 还提供了一套全面的工具集来管理 SQL Server 数据库，无论是在本地还是在云中。