NULLIF Function [Miscellaneous]

Provides an abbreviated CASE expression by comparing expressions.

Syntax

NULLIFexpression1, expression2 )

Parameters

Parameter

Description

expression1

An expression to be compared.

expression2

An expression to be compared.

Returns

Data type of the first argument.

Examples

The following statement returns a:

SELECT NULLIF( 'a', 'b' ) FROM iq_dummy

The following statement returns NULL:

SELECT NULLIF( 'a', 'a' ) FROM iq_dummy

Usage

NULLIF compares the values of the two expressions.

If the first expression equals the second expression, NULLIF returns NULL.

If the first expression does not equal the second expression, or if the second expression is NULL, NULLIF returns the first expression.

The NULLIF function provides a short way to write some CASE expressions. NULLIF is equivalent to:
CASE WHEN expression1 = expression2 THEN NULL 
ELSE expression1 END

Standards and Compatibility

  • SQL—Transact-SQL extension to ISO/ANSI SQL grammar.

  • Sybase—Not supported by Adaptive Server Enterprise.

Related reference
CASE Expressions
NULLIF Function for Abbreviated CASE Expressions