Oracle NULLIF function

Preamble

Oracle/PLSQL NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the function NULLIF returns NULL. Otherwise, it returns expr1.

Oracle/PLSQL syntax of the function NULLIF

NULLIF( expr1_id, expr2_id )

Parameters or arguments

• expr1_id and expr2_id must either have numerical values or values that have the same data type.

Note:

• The function NULLIF returns NULL if expr1_id and expr2_id are equal.
• The function NULLIF returns expr1 if expr1_id and expr2_id are not equal.
• expr1 can be an expression which is evaluated as NULL but cannot be literal NULL.

The function NULLIF can be used in the following versions of Oracle/PLSQL

Let’s consider some examples of Oracle NULLIF function and learn how to use NULLIF function in Oracle PLSQL.

SELECT NULLIF(12, 12) AS RESULT FROM DUAL;

-result: NULL

SELECT NULLIF(12, 13) AS RESULT FROM DUAL;

-result: 12

SELECT NULLIF('apples', 'apples') AS RESULT FROM DUAL;

-RESULT: NULL

SELECT NULLIF('apples', 'oranges') AS RESULT FROM DUAL;

-RESULT: apples

SELECT NULLIF(NULL, 12) AS RESULT FROM DUAL;
SELECT NULLIF(NULL, 12) AS RESULT FROM DUAL
ORA-00932: inconsistent datatypes: expected - got CHAR
-- ORA-00932 error because expr1 can't be literal NULL

NULLIF FUNCTION IN ORACLE SQL WITH EXAMPLE

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​