NULLIF (Transact-SQL)

語法

``````NULLIF ( expression , expression )
``````

expression

備註

NULLIF 相當於兩個運算式相等且產生的運算式為 NULL 的搜尋 CASE 運算式。

範例

A. 傳回尚未變更的預算數量

``````CREATE TABLE dbo.budgets
(
dept            TINYINT   IDENTITY,
current_year    DECIMAL   NULL,
previous_year   DECIMAL   NULL
);
INSERT budgets VALUES(100000, 150000);
INSERT budgets VALUES(NULL, 300000);
INSERT budgets VALUES(0, 100000);
INSERT budgets VALUES(NULL, 150000);
INSERT budgets VALUES(300000, 250000);
GO
SET NOCOUNT OFF;
SELECT AVG(NULLIF(COALESCE(current_year,
previous_year), 0.00)) AS [Average Budget]
FROM budgets;
GO
``````

``````Average Budget
--------------
212500.000000
(1 row(s) affected)
``````

B. 比較 NULLIF 和 CASE

``````USE AdventureWorks2012;
GO
SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag) AS [Null if Equal]
FROM Production.Product
WHERE ProductID < 10;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag, [Null if Equal] =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO
``````

C：傳回未包含資料的預算金額

``````
Copy
CREATE TABLE budgets (
dept           TINYINT,
current_year   DECIMAL(10,2),
previous_year  DECIMAL(10,2)
);

INSERT INTO budgets VALUES(1, 100000, 150000);
INSERT INTO budgets VALUES(2, NULL, 300000);
INSERT INTO budgets VALUES(3, 0, 100000);
INSERT INTO budgets VALUES(4, NULL, 150000);
INSERT INTO budgets VALUES(5, 300000, 300000);

SELECT dept, NULLIF(current_year,
previous_year) AS LastBudget
FROM budgets;
``````

``````dept   LastBudget
----   -----------
1      100000.00
2      null
3      0.00
4      null
5      null
``````