Oracle NULLIF() Function

Oracle NULLIF() is a built-in function that compares two arguments and returns NULL if they are equal, otherwise it returns the first argument.

Oracle NULLIF() Syntax

Here is the syntax for the Oracle NULLIF() function:

NULLIF(expr1, expr2)

The NULLIF(expr1, expr2) function is logically equivalent to this CASE expression: CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

Parameters

expr1

Required. A value or expression.

expr2

Required. Another value or expression.

If both arguments are numeric data types, then the Oracle database determines which argument has the higher numeric precedence, implicitly converts the other argument to that data type, and then returns that data type. If the arguments are not numeric, then they must be of the same data type, otherwise Oracle returns an error.

Return Value

The Oracle NULLIF() function compares two arguments and returns NULL if they are equal, otherwise it returns the first argument.

If both arguments are NULL, NULLIF() returns an error.

Oracle NULLIF() Examples

Here are some examples that demonstrate the usage of the Oracle NULLIF() function.

Basic Usage

SELECT
    NULLIF(1, 1),
    NULLIF(1, 2)
FROM dual;

Output:

   NULLIF(1,1)    NULLIF(1,2)
______________ ______________
          NULL              1

The following statement achieves the same result using a CASE expression:

SELECT
    CASE WHEN 1 = 1 THEN NULL ELSE 1 END,
    CASE WHEN 1 = 2 THEN NULL ELSE 1 END
FROM dual;

Output:

   CASEWHEN1=1THENNULLELSE1END    CASEWHEN1=2THENNULLELSE1END
______________________________ ______________________________
                          NULL                              1

NULL Arguments

If both arguments are NULL, NULLIF() returns an error.

SET NULL 'NULL';
SELECT
    NULLIF(NULL, NULL)
FROM dual;

Output:

SQL Error: ORA-00932: inconsistent datatypes: expected - got CHAR

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle NULLIF() is a built-in function that compares two arguments and returns NULL if they are equal, otherwise it returns the first argument.