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 CHARSQL> 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
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