Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

NULLIF (SQL-92 compatible)


NULLIF ( expression1, expression2 )


The NULLIF scalar function is a type of conditional expression (See Conditional Expressions for more information and a summary of all the conditional expressions).

The NULLIF scalar function returns a null value for expression1 if it is equal to expression2. It’s useful for converting values to null from applications that use some other representation for missing or unknown data.


  • 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 follows:


WHEN expression1 = expression2 THEN NULL

ELSE expression1


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, ' '));