Where 子句中的空值檢查條件

DECLARE @SubDesc1 nvarchar(100)   
SET @SubDesc1 = null

我想做這樣的事情。

查詢 1

SELECT * 
From Table1   
where Category = 'Category1'   
 AND [Description] ='ABC'   
 AND SubDescription1 IS NULL   
  OR SubDescription1 = @SubDesc1

查詢 2

SELECT * 
FROM Table1   
WHERE Category = 'Category1'   
 AND [Description] ='ABC'  
 AND SubDescription1 = (Case WHEN @SubDesc1 IS NOT NULL 
                             Then @SubDesc1
                             ELSE SubDescription1 
                             END)

如果我的，他們都沒有解決@SubDesc1 = 'def'

但是，它會讓我記錄在哪里SubDescription1 Is null和SubDescription1 = 'def'

我怎樣才能做到這一點？

如果 NULL 值@SubDesc1表示“返回所有記錄，不應用過濾”，則

SELECT * 
FROM Table1   
WHERE Category = 'Category1'   
 AND [Description] ='ABC'   
 AND (    SubDescription1 = @SubDesc1
       OR @SubDesc1 IS NULL)

如果 NULL 值的@SubDesc1意思是“只返回那些欄位也為 NULL 的記錄”，那麼

SELECT * 
FROM Table1   
WHERE Category = 'Category1'   
 AND [Description] ='ABC'   
 AND (    SubDescription1 = @SubDesc1 
       OR COALESCE(@SubDesc1, SubDescription1) IS NULL )

引用自：https://dba.stackexchange.com/questions/255002