NULLIF function in Oracle with examples|techgoeasy.com

This SQL tutorial provides explanations, examples for NULLIF function in Oracle

NULLIF function 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 oracle 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;

Another use case of NULLIF function will be if you want to list the employees who have changed jobs since they were hired, as indicated by a job_id in the job_history table different from the current job_id in the employees table

select emp_name,nullif(b.job_id,a.job_id) old_job from employees a,   job_history  b where a.emp_id=b.emp_id;

FAQ for Nullif function

How to avoid divide by zero error with the help of NULLIF function

We get the divide by zero error if the denominator in the division is zero. We can avoid by using the NULLIF function as below
select 100/ nullif(0,0) from dual;
Now here since both the values are equal ,Nullif will return null and whole expression will return null instead of any error. Here is how you can use when dealing with real column
select col2/ nullif(col1,0) from exp;

Related Articles

Update statement in oracle
NVL2 function in Oracle
NVL function in Oracle
Coalesce Function in Oracle
Single row functions in sql
Delete from table statement in Oracle

External Resource
Oracle Reference for NULLIF

Reader Interactions

Leave a Reply Cancel reply