在使用数据库时，我们经常会遇到缺少数据的行。这些缺失数据可能是由于未知或不适用的值、数据导入或输入过程中的错误或涉及不存在值的特定计算造成的。在这种情况下，有两种表示缺失数据的方法：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）。以下是实现此目的的查询：
SELECTpt.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列包含空值的产品列表。 在这种情况下，我们需要搜索空值：
SELECTpt.Name ,pt.ProductNumber ,pt.Style FROM [Product.Test] pt WHERE pt.Style = ''
用户经常需要同时获取 NULL 和空值。然后，我们可以使用 OR 运算符将 IS NULL 运算符与空值搜索结合起来，如下所示：
SELECTcolumn_names FROM table_name WHERE column_name = '' OR column_name IS NULL
我们要检查表中是否所有产品都分配了ListPrice值。为此，我们要检查是否有产品的ListPrice为 NULL 且ListPrice为空：
SELECTProductID ,Name ,ProductNumber ,ListPrice FROM dbo.[Product.Test] WHERE ListPrice = '' OR ListPrice IS NULL
输出包含空字符串和 NULL 值，从而给出更广泛的结果。
使用 TRIM 函数来获取仅包含空格的值
某些列可能包含完全由空格组成的值，这在从各种来源导入数据时很常见。这些值通常被视为空，因为它们缺乏有意义的字符。要识别此类行，您可以使用 TRIM 函数。
默认情况下，TRIM 会删除前导和尾随空格，但也可以删除字符串开头和结尾的其他指定字符。在这种情况下，我们使用这个函数在以标准方式检查空值之前删除空格。
基本查询语法是：
SELECTcolumn_name FROM table_name WHERE column_name IS NULL OR TRIM(column_name) = ''
以下查询选择列Color、Size、ProductLine、Class和Style为 NULL 或在修剪任何前导和尾随空格后实际上为空的行。
SELECTColor ,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。
SELECTName ,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 和空值的行。
语法是：
SELECTcolumn_name FROM table_name WHERE TRIM(COALESCE(code, '')) = ''
这里，代码是需要过滤数据的列的名称。
在我们的测试用例中，我们想要识别Color列中具有 NULL 或空值的产品：
SELECTProductID ,Name ,Color FROM dbo.[Product.Test] WHERE TRIM(COALESCE(Color, '')) = ''
此查询识别具有 NULL 或空白颜色值的产品，并确保仅包含空格的字符串被视为空。
使用 NULLIF 函数
NULLIF 函数比较两个表达式，如果它们相等，则返回 NULL。当应用于包含空值的列时，它返回 NULL，允许我们使用 IS NULL 运算符检查 NULL：
SELECTcolumn_name FROM table_name WHERE NULLIF(TRIM(code), '') IS NULL
看看下面的例子：
SELECTName ,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 ：
SELECTName ,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列中的数据，以识别缺少颜色定义的记录：
SELECTProductID ,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分类：
SELECTProductID ,Name ,Class ,CASEWHEN Class IS NULL THEN 'Missing (NULL)'WHEN TRIM(Class) = '' THEN 'Missing (Empty or Spaces)'ELSE ClassEND 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 数据库，无论是在本地还是在云中。