在使用數(shù)據(jù)庫時，我們經(jīng)常會遇到缺少數(shù)據(jù)的行。這些缺失數(shù)據(jù)可能是由于未知或不適用的值、數(shù)據(jù)導入或輸入過程中的錯誤或涉及不存在值的特定計算造成的。在這種情況下，有兩種表示缺失數(shù)據(jù)的方法：NULL 和空值（或空白值）。

雖然乍一看它們似乎相同，但它們是不同的，并且以不同的方式影響基本數(shù)據(jù)庫操作。本文探討 SQL Server 中 NULL 和空值之間的區(qū)別，并討論如何有效地處理它們。

dbForge Studio for SQL Server官方正版下載

SQL Server 中的 NULL 和空值

NULL 表示數(shù)據(jù)庫列中缺失或未知的數(shù)據(jù)。這可能發(fā)生在兩種情況下：數(shù)據(jù)不存在或數(shù)據(jù)存在但當前未知。NULL 可以分配給任何數(shù)據(jù)類型的字段，包括字符串、整數(shù)和日期。重要的是，該字段沒有分配內(nèi)存，因為 NULL 表示未知值。

相反，數(shù)據(jù)庫中的空白或空白區(qū)域是指空字符或空白字符。雖然其含義可能看起來與 NULL 相似，但它的存儲和檢索方式與文本字段中的任何其他字符一樣。空字符串特定于字符串列，不能應用于不同的數(shù)據(jù)類型。

例如，考慮一個包含產(chǎn)品信息的表，其中有一列存儲保修詳細信息。此列中的 NULL 值表示未指定保修期。相反，空值表示產(chǎn)品沒有保修。

在數(shù)據(jù)庫中，NULL 值和空白字符串在定義、語法和長度上有所不同，并且在查詢和數(shù)據(jù)操作中對它們的處理也不同。因此，分別檢測 NULL 和空值通常是必不可少的。大多數(shù)數(shù)據(jù)庫管理系統(tǒng)（包括 SQL Server）都提供了有效處理這種區(qū)別的工具和功能。

查找 NULL 或空值的標準方法

根據(jù)具體情況，如果 NULL 和空值代表相似的概念，則可將它們一起處理；如果它們在數(shù)據(jù)模型中具有不同的含義或條件，則可將它們分開處理。這種區(qū)別會顯著影響查詢性能和結果的準確性。

最常見的情況是，需要通過刪除 NULL 和空值或將 NULL 替換為其他值（如空）來避免 NULL 值錯誤。為了有效地管理這種情況，用戶需要可靠的方法來識別 NULL 和空列值。本指南探討了 SQL Server 中可用的內(nèi)置工具，包括專用查詢和函數(shù)。

使用 IS NULL 運算符

SQL Server 中的 IS NULL 運算符檢查列或表達式是否包含 NULL 值。基本查詢語法如下：

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

讓我們看一個簡單的例子。在此示例和后續(xù)示例中，我們將使用流行的 SQL Server AdventureWorks2022 測試數(shù)據(jù)庫和SQL Server dbForge Studio來演示測試用例。

假設我們需要檢索產(chǎn)品列表，包括其名稱和重量，其中重量小于 10 磅或顏色未知（即 NULL）。以下是實現(xiàn)此目的的查詢：

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列包含空值的產(chǎn)品列表。 在這種情況下，我們需要搜索空值：

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

用戶經(jīng)常需要同時獲取 NULL 和空值。然后，我們可以使用 OR 運算符將 IS NULL 運算符與空值搜索結合起來，如下所示：

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

我們要檢查表中是否所有產(chǎn)品都分配了ListPrice值。為此，我們要檢查是否有產(chǎn)品的ListPrice為 NULL 且ListPrice為空：

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

輸出包含空字符串和 NULL 值，從而給出更廣泛的結果。

使用 TRIM 函數(shù)來獲取僅包含空格的值

某些列可能包含完全由空格組成的值，這在從各種來源導入數(shù)據(jù)時很常見。這些值通常被視為空，因為它們?nèi)狈τ幸饬x的字符。要識別此類行，您可以使用 TRIM 函數(shù)。

默認情況下，TRIM 會刪除前導和尾隨空格，但也可以刪除字符串開頭和結尾的其他指定字符。在這種情況下，我們使用這個函數(shù)在以標準方式檢查空值之前刪除空格。

基本查詢語法是：

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) = '')

它可以幫助我們確保指定列中沒有空值或無意義的值。

內(nèi)置 SQL Server 函數(shù)

除了 SQL 查詢之外，Microsoft SQL Server 還提供了專門用于處理 NULL 值的內(nèi)置函數(shù)。在本節(jié)中，我們將探討它們的工作原理。

使用 COALESCE 函數(shù)

SQL COALESCE 允許我們用默認值替換 NULL，從而確保輸出中只有有意義的數(shù)據(jù)。當 NULL 值可能破壞計算或損害數(shù)據(jù)準確性時，此功能非常有用。

語法是：

COALESCE (expression [ ,...n ] )

我們使用的測試表包含一些 NULL 和一些空值，而不是有意義的數(shù)據(jù)。在我們的場景中，我們想要檢索缺少一些基本參數(shù)的產(chǎn)品名稱。包含顏色和尺寸 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 值的案例的表，并可以進一步處理數(shù)據(jù)。

SQL Server 中的 COALESCE 函數(shù)可以與 TRIM 函數(shù)一起使用，通過一個查詢檢索同時具有 NULL 和空值的行。

語法是：

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

這里，代碼是需要過濾數(shù)據(jù)的列的名稱。

在我們的測試用例中，我們想要識別Color列中具有 NULL 或空值的產(chǎn)品：

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

此查詢識別具有 NULL 或空白顏色值的產(chǎn)品，并確保僅包含空格的字符串被視為空。

使用 NULLIF 函數(shù)

NULLIF 函數(shù)比較兩個表達式，如果它們相等，則返回 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 函數(shù)有效地從表中過濾并返回Color或Size列為 NULL、空或僅包含空格的行。

使用 ISNULL 函數(shù)

ISNULL 函數(shù)用預定義的有意義的值替換 NULL。

該函數(shù)的語法是：

ISNULL(expression, replacement)

這里，expression是列名，而replacement是當列值為NULL時將替換該列的值。

在下面的例子中，我們檢索產(chǎn)品顏色、尺寸和類別的數(shù)據(jù)，并用預定義值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 和空值通常涉及高級技術，以實現(xiàn)更高效的數(shù)據(jù)處理和更精確的結果。

• 結合多種功能

您可能已經(jīng)注意到函數(shù)組合的使用，例如 TRIM 與 COALESCE 或 TRIM 與 ISNULL。多個函數(shù)的組合允許更高級的數(shù)據(jù)操作，從而提供精確且有針對性的結果。

以下查詢演示了如何通過刪除空格并用占位符替換 NULL 值來清理Color列中的數(shù)據(jù)，以識別缺少顏色定義的記錄：

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

COALESCE 函數(shù)將Color中的所有 NULL 值替換為空字符串，從而可以安全地應用 TRIM，進而從Color列中刪除所有前導或尾隨空格。NULLIF(TRIM(…),”) 將空字符串（最初為空或修剪為空）轉換回 NULL。ISNULL(…, 'Not provided') 將任何 NULL 值（無論是最初為 NULL 還是由 NULLIF 轉換為 NULL）替換為字符串Not provided。

• 使用 CASE

在 SQL Server 中，您可以使用條件表達式（例如 CASE）以及 ISNULL、COALESCE 和 TRIM 等函數(shù)來處理不同類型的缺失數(shù)據(jù)。在這種情況下，ISNULL() 或 COALESCE() 會用預定義的占位符替換 NULL，TRIM 會刪除前導和尾隨空格并檢查空字符串 (”)，而 CASE 與 TRIM 結合可確保將僅包含空格的字符串視為空。

下面是使用Product.Test表的示例查詢，旨在根據(jù)缺失數(shù)據(jù)的類型將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]

這種先進的技術有助于確保一致地處理缺失數(shù)據(jù)，并清理數(shù)據(jù)以進行分析、報告和驗證。

具有 NULL 和空值的大型數(shù)據(jù)集的性能注意事項

處理包含 NULL 和空值的大型數(shù)據(jù)集時，性能考慮至關重要，因為不同的因素會顯著影響查詢執(zhí)行和資源使用。考慮以下因素和策略來優(yōu)化性能：

• 索引使用

SQL Server 中 NULL 值的索引方式不同，查詢過濾可能無法有效利用索引。為避免出現(xiàn)問題，請使用過濾索引以僅包含非 NULL 或相關行（例如，WHERE Column IS NOT NULL）。

• 函數(shù)使用

直接在 WHERE 子句或索引列中應用 ISNULL、COALESCE 和 TRIM 等函數(shù)可能會阻止索引使用并導致全表掃描。解決方案是重組查詢以從 WHERE 子句中刪除這些函數(shù)。此外，先進的現(xiàn)代 ETL 解決方案提供內(nèi)置工具以立即清理數(shù)據(jù)。

• 存儲效率

如果管理不當，與內(nèi)存分配相關的 NULL 和空值的不同處理可能會導致存儲開銷。為了避免這種情況，請在數(shù)據(jù)輸入期間評估列默認值以盡量減少 NULL 和空值。

• 查詢設計和優(yōu)化

復雜的條件表達式可能會導致大型數(shù)據(jù)集的性能下降。解決方案可能是將 NULL 和空值分成不同的查詢過程。此外，在執(zhí)行查詢之前利用執(zhí)行計劃分析來識別查詢瓶頸，這將有助于相應地優(yōu)化它們。

• 統(tǒng)計數(shù)據(jù)和基數(shù)

在 SQL Server 中，包含許多 NULL 或空值的列的基數(shù)估計可能會受到影響。使用專用的 UPDATE STATISTICS 命令或自動更新功能定期更新統(tǒng)計信息至關重要。

這些策略可以幫助您確保有效處理具有 NULL 和空值的大型數(shù)據(jù)集，同時最大限度地減少資源消耗和查詢執(zhí)行時間。

結論

NULL 和空值在數(shù)據(jù)庫中很常見，因此了解它們的概念、區(qū)分它們并適當處理它們至關重要。本文探討了識別和解決由 NULL 或空值定義的缺失數(shù)據(jù)情況的可靠方法。它提供了實用的技術來幫助清理數(shù)據(jù)并確保計算準確。為了說明這些情況，我們使用了 dbForge Studio for SQL Server，這是一種擅長處理此類情況的工具。

dbForge Studio for SQL Server 提供了一個功能強大的 SQL 編輯器，其中包含基于上下文的建議、代碼分析、語法驗證、格式和代碼片段，使用戶能夠以兩倍的速度編寫高質(zhì)量的 SQL 代碼。此外，Studio 還提供了一套全面的工具集來管理 SQL Server 數(shù)據(jù)庫，無論是在本地還是在云中。

歡迎下載并體驗它如何將您的工作流程提升到一個新的水平！

如果您有產(chǎn)品試用下載、價格咨詢、優(yōu)惠獲取，或其他任何問題，請聯(lián)系。