NULLIF function in Oracle

This is very useful function .It compare two values and return null if the values are same , else return the first value

expr1 : it is the source values or expression which is going to be compared with expr2
expr2 : It is also the source value which is compared with expr1

You cannot specify the literal NULL for the first value

SQL> select nullif(NULL,1) from dual;
select nullif(NULL,1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected – got CHAR

Usage and Example

SQL> select nullif(1,1) from dual;

NULLIF(1,1)
———–

SQL> select nullif(1,2) from dual;

NULLIF(1,2)
———–
1

 

Important Note

1)This statement is very similar to case statement and it is equivalent as
CASE WHEN expr1=expr2 THEN NULL ELSE expr1 END

2) If both arguments are numeric datatypes, then Oracle Database determines the argument with the higher numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype. If the arguments are not numeric, then they must be of the same datatype, or Oracle returns an error.

SQL> select nullif(1,’apple’) from dual;
select nullif(1,’apple’) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

SQL> select nullif(‘apple’,1) from dual;
select nullif(‘apple’,1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

More Examples

select first_name, length(first_name),last_name,length(last_name), nullif(length(first_name),length(last_name)) from emp;SELECT NULLIF(‘Sunday’, ‘Friday’) AS check FROM dual;

Leave a Reply