Returns a NULL value for expression1 if it is equal to expression2. It is useful for converting values to NULL from applications that use some other representation for missing or unknown data. The NULLIF scalar function is a type of conditional expression.

Syntax

NULLIF ( expression1, expression2 )

Notes

  • This function is not allowed in a GROUP BY clause.
  • Arguments to this function cannot be query expressions.
  • The NULLIF expression is shorthand notation for a common case that can also be represented in a CASE expression, as shown:
CASE  
    WHEN expression1 = expression2 THEN NULL    
    ELSE expression1  
END

Example

This example uses the NULLIF scalar function to insert a NULL value into an address column if the host‑language variable contains a single space character:

INSERT INTO employee (add1) VALUES (NULLIF (:address1, ' '));

Compatibility

SQL compatible