SQL Server 處理 NULL 值的三大陷阱與正確做法

陷阱一：COUNT(*) vs COUNT(column) 差異

CREATE TABLE #Test (Id INT IDENTITY, Val INT NULL);
INSERT INTO #Test (Val) VALUES (10), (20), (NULL), (30);

SELECT 
    COUNT(*) AS Cnt_Star,           -- 4：計算所有列
    COUNT(Val) AS Cnt_Val,          -- 3：忽略 NULL
    COUNT(ISNULL(Val,0)) AS Cnt_IsNull; -- 4：將 NULL 轉 0 後計算

規則：彙總函數 (SUM, AVG, MIN, MAX) 自動忽略 NULL；唯有 COUNT(*) 計入 NULL。需包含 NULL 時，使用 ISNULL/COALESCE 轉換。

陷阱二：外鍵關聯中的 NULL 允許性

即使父表主鍵為 NOT NULL，子表外鍵欄位預設允許 NULL。若業務要求「每筆子記錄必須對應父記錄」，必須額外加上 NOT NULL 約束。

CREATE TABLE Parent (Id INT PRIMARY KEY);
CREATE TABLE Child (
    Id INT PRIMARY KEY,
    ParentId INT NULL,  -- 預設允許 NULL！
    FOREIGN KEY (ParentId) REFERENCES Parent(Id)
);
-- 此時 INSERT INTO Child VALUES (1, NULL) 成功，但違反業務邏輯
-- 正確做法：
ALTER TABLE Child ALTER COLUMN ParentId INT NOT NULL;

陷阱三：NULL 參與比較/運算結果為 UNKNOWN

最佳實務清單

1. 欄位預設 NOT NULL，依業務給預設值（DEFAULT 0, DEFAULT '', DEFAULT GETDATE()）。
2. 必須允許 NULL 時，明確宣告 NULL 並加註解說明業務含義。
3. 查詢條件：WHERE Col = @Val 若 @Val 可能為 NULL，改用 WHERE (Col = @Val OR (@Val IS NULL AND Col IS NULL)) 或 WHERE ISNULL(Col, -1) = ISNULL(@Val, -1)（需注意索引）。
4. 彙總報表：AVG(ISNULL(Score,0)) 區別於 AVG(Score)（後者忽略 NULL），依業務決定。
5. 連接查詢：LEFT JOIN 產生的 NULL 擴展列，使用 ISNULL/COALESCE 處理顯示。
6. 檢查約束：CHECK (Col IS NOT NULL OR Col > 0) 允許 NULL 但若有值必須 > 0。

快速測試腳本

-- 建立測試表
CREATE TABLE #NullDemo (A INT, B INT NULL);
INSERT INTO #NullDemo VALUES (1,10), (2,NULL), (3,20);

-- 1. COUNT 差異
SELECT COUNT(*), COUNT(B), COUNT(ISNULL(B,-1)) FROM #NullDemo;

-- 2. NOT IN 陷阱
SELECT * FROM #NullDemo WHERE A NOT IN (SELECT B FROM #NullDemo); -- 回傳 0 列！
SELECT * FROM #NullDemo WHERE NOT EXISTS (SELECT 1 FROM #NullDemo WHERE B = A); -- 正確

-- 3. 字串串聯
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT 'Prefix_' + B FROM #NullDemo; -- NULL 列變 NULL
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'Prefix_' + B FROM #NullDemo; -- NULL 列變 'Prefix_'

DROP TABLE #NullDemo;