NULLIF Function in Oracle SQL – PLSQL

Oracle SQL / PLSQL used the NULLIF function to compare 2 expressions.

If the expressions are equal then the SQL NULLIF function returns NULL, else SQL NULLIF function will return the first expression.

Oracle SQL / PLSQL Syntax for the NULLIF function is:

SELECT NULLIF (expression_1, expression_2)
FROM table_name;

Here, expression_1 and expression_2 are the expressions to be compared.

Example: Using Oracle SQL /PLSQL NULLIF Function in SQL SELECT Statement

 SELECT NULLIF(1,1)
FROM dual;

Will return NULL

 SELECT NULLIF(1,2)
FROM dual;

Will return 2

 SELECT NULLIF('Tech Honey','Tech Honey')
FROM dual;

Will return NULL

 SELECT NULLIF('Tech','Honey')
FROM dual;

Will return ‘Tech’

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

Will return an ORA-00932 error because expression_1 cannot be literal NULL.

The example above shows use of Oracle SQL / PLSQL NULLIF Function.


Tagged , , . Bookmark the permalink.