# NULLIF (Transact-SQL)NULLIF (Transact-SQL)

## 語法Syntax

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

## 引數Arguments

expressionexpression

## RemarksRemarks

NULLIF 相當於兩個運算式相等且產生的運算式為 NULL 的搜尋 CASE 運算式。NULLIF is equivalent to a searched CASE expression in which the two expressions are equal and the resulting expression is NULL.

## 範例Examples

### A.A.傳回尚未變更的預算數量Returning budget amounts that have not changed

``````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.B.比較 NULLIF 和 CASEComparing NULLIF and 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.傳回未包含資料的預算金額C: Returning budget amounts that contain no data

``````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
``````