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

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

dbForge Studio for SQL Server官方正版下載

SQL Server 中的 NULL 和空值

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

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

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

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

查找 NULL 或空值的標(biāo)準(zhǔn)方法

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

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

使用 IS NULL 運(yùn)算符

SQL Server 中的 IS NULL 運(yùn)算符檢查列或表達(dá)式是否包含 NULL 值。基本查詢語(yǔ)法如下：

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

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

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

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

搜索空字符串

正如我們前面提到的，空值是長(zhǎng)度為零的字符串，這會(huì)導(dǎo)致問(wèn)題，因?yàn)榭兆址坏扔?NULL 值。SQL Server 對(duì)它們進(jìn)行不同的處理，在具有 WHERE 條件的查詢中使用 IS NULL 運(yùn)算符不會(huì)返回空字符串。搜索空值的條件語(yǔ)法是：

WHERE column_name = ''

因此，基本查詢語(yǔ)法是：

SELECT column_names
FROM table_name
WHERE column_name = ''

假設(shè)我們要檢索Style列包含空值的產(chǎn)品列表。 在這種情況下，我們需要搜索空值：

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

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

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 值，從而給出更廣泛的結(jié)果。

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

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

默認(rèn)情況下，TRIM 會(huì)刪除前導(dǎo)和尾隨空格，但也可以刪除字符串開(kāi)頭和結(jié)尾的其他指定字符。在這種情況下，我們使用這個(gè)函數(shù)在以標(biāo)準(zhǔn)方式檢查空值之前刪除空格。

基本查詢語(yǔ)法是：

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

以下查詢選擇列Color、Size、ProductLine、Class和Style為 NULL 或在修剪任何前導(dǎo)和尾隨空格后實(shí)際上為空的行。

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

它可以幫助我們確保指定列中沒(méi)有空值或無(wú)意義的值。

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

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

使用 COALESCE 函數(shù)

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

語(yǔ)法是：

COALESCE (expression [ ,...n ] )

我們使用的測(cè)試表包含一些 NULL 和一些空值，而不是有意義的數(shù)據(jù)。在我們的場(chǎng)景中，我們想要檢索缺少一些基本參數(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]

結(jié)果，我們得到一個(gè)定義所有具有 NULL 值的案例的表，并可以進(jìn)一步處理數(shù)據(jù)。

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

語(yǔ)法是：

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

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

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

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

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

使用 NULLIF 函數(shù)

NULLIF 函數(shù)比較兩個(gè)表達(dá)式，如果它們相等，則返回 NULL。當(dāng)應(yīng)用于包含空值的列時(shí)，它返回 NULL，允許我們使用 IS NULL 運(yùn)算符檢查 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ù)有效地從表中過(guò)濾并返回Color或Size列為 NULL、空或僅包含空格的行。

使用 ISNULL 函數(shù)

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

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

ISNULL(expression, replacement)

這里，expression是列名，而replacement是當(dāng)列值為NULL時(shí)將替換該列的值。

在下面的例子中，我們檢索產(chǎn)品顏色、尺寸和類別的數(shù)據(jù)，并用預(yù)定義值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 或空值的高級(jí)技術(shù)

處理 NULL 和空值通常涉及高級(jí)技術(shù)，以實(shí)現(xiàn)更高效的數(shù)據(jù)處理和更精確的結(jié)果。

• 結(jié)合多種功能

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

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

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

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

• 使用 CASE

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

下面是使用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]

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

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

處理包含 NULL 和空值的大型數(shù)據(jù)集時(shí)，性能考慮至關(guān)重要，因?yàn)椴煌囊蛩貢?huì)顯著影響查詢執(zhí)行和資源使用。考慮以下因素和策略來(lái)優(yōu)化性能：

• 索引使用

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

• 函數(shù)使用

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

• 存儲(chǔ)效率

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

• 查詢?cè)O(shè)計(jì)和優(yōu)化

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

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

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

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

結(jié)論

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

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

歡迎下載并體驗(yàn)它如何將您的工作流程提升到一個(gè)新的水平！

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