NULLIF (TSQL Function)

Returns NULL if the two specified expressions are equal.

Syntax

NULLIF ( expression_1 , expression_2 )

Arguments

expression_1

is an expression of any type.

expression_2

is an expression of any type.

Return Types

Returns the type of expression_1.

Remarks

This function returns expression_1 if expression_1 is not equal to expression_2. If the expressions are equal, it returns NULL.

NULLIF(a, b) is the exactly the same as IIF(a=b, NULL, a)

This means that the expression a is evaluated twice.
So, you should not use non-deterministic functions such as RAND() within NULLIF().
MS SQL Server has the same issue.

Examples

PRINT NULLIF(123, 456);
PRINT NULLIF(123, 123);
PRINT NULLIF(123, '456');   -- string '456' is converted to int
PRINT NULLIF(123, 'Hello'); -- string 'Hello' cannot be converted to int

PRINT NULLIF(CAST(RAND()*4 AS INT), 2);  -- as CAST(RAND()*4 AS INT) is evaluated twice, NULLIF can return 2 !!!

The result is:

123
<NULL>
123
<Conversion error. VARCHAR "Hello" cannot be cast into INT, invalid string.>
3       <-- may return  NULL, 0, 1, 2, or 3