602SQL Documentation Index  

NULLIF Function

NULLIF ( expression1, expression2 )

The NULLIF function returns a NULL value if the values of both expressions are equal, otherwise it returns the first expression value. Expression1 and expression2 must be compatible types.

This function is basically a simplified notation of the following CASE statement:

CASE 
  WHEN expression1=expression2 THEN NULL
  ELSE expression1
END

NOTE: One of the reasons to include this function is for historical reasons. Some older databases do not understand the NULL value, therefore this value had to be represented in another way (e.g -1). When utilizing SQL that requires the NULL value, these values must be transformed using the NULLIF function:

... NULLIF(salary, -1) ...