NULLIF

Transact-SQL Reference

Transact-SQL Reference

NULLIF

Returns a null value if the two specified expressions are equivalent.

Syntax

NULLIF ( expression , expression )

Arguments

expression

Is a constant, column name, function, subquery, or any combination of arithmetic, bitwise, and string operators.

Return Types

Returns the same type as the first expression.

NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a null value of the type of the first expression.

Remarks

NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.

Examples

This example creates a budgets table to show a department (dept) its current budget (current_year) and its previous budget (previous_year). For the current year, NULL is used for departments with budgets that have not changed from the previous year, and 0 is used for budgets that have not yet been determined. To find out the average of only those departments that receive a budget as well as to include the budget value from the previous year (use the previous_year value, where the current_year is 0), combine the NULLIF and COALESCE functions.

USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'budgets')
   DROP TABLE budgets
GO
SET NOCOUNT ON
CREATE TABLE 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

Here is the result set:

Average Budget                           
---------------------------------------- 
212500.000000

(1 row(s) affected)

See Also

CASE

decimal and numeric

System Functions