In Oracle Database, the NULLIF() function compares two expressions, and returns null if both expressions are equal. If they are not equal, then the function returns the first expression.

Syntax

The syntax goes like this:

NULLIF(expr1, expr2)

Example

Here’s an example to demonstrate:

SELECT NULLIF(5, 7)
FROM DUAL;

Result:

5

In this case, the arguments were not equal, and so the function returned the first argument.

Here’s what happens when both arguments are equal:

SET NULL 'null';
SELECT NULLIF(7, 7)
FROM DUAL;

Result:

null

We can see that NULLIF() returns null when both arguments are equal

Regarding the first line SET NULL 'null';, I added that so that my SQLcl session returns null whenever the result is null.

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Compared to CASE

The NULLIF() function is the equivalent of the following CASE expression:

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

Invalid Argument Count

Calling the function without passing any arguments results in an error:

SELECT NULLIF()
FROM DUAL;

Result:

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"

And passing too many arguments also causes an error:

SELECT NULLIF(1, 2, 3)
FROM DUAL;

Result:

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"