NULLIF Function

The NULLIF expression returns different results, depending on whether its two arguments are equal.
NULLIF Function

1  NULLIF ( expr1 , expr2 )
Element Description Restrictions Syntax
expr1 expr2 Expressions that return values of a compatible data type Cannot be a BYTE or TEXT data type Expression
NULLIF evaluates its two arguments, expr1 and expr2.
  • If their values are equal. then NULLIF returns NULL.
  • If their values are not equal. then NULLIF returns expr1.
The expr1 and expr2 arguments can be of any data type for which a built-in comparison function exists, or any two data types that can be cast to a compatible data type that has a built-in comparison function.
The following example uses the NULLIF function to convert Boolean FALSE values ('f') to NULL values: 
SELECT name, answer, NULLIF(answer, 'f') FROM booktab;

Here the first argument is a Boolean column expression that can have true ('t') or false ('f') values, and the second Boolean argument is always 'f" (for FALSE). For rows that have 'f' in the answer column, the value returned by the NULLIF function will be NULL (because the NULL value is returned when the arguments are equal). For rows that have 't' as the first argument, however, the value returned by NULLIF is always 't', because the two arguments cannot be equal when one is 't' and the other is 'f'; the first argument is returned when the two values are not equal.