The Oracle NULLIF function

The NULLIF function evaluates two expressions in the following way:

• If they are equal, it returns NULL.
• If they are not equal, it returns the first expression.

Syntax

NULLIF (expression1, expression2)

Example

select
new.employee_name NAME,
nullif(old.job_id,new.job_id)
from
employeeshist old, employees new
where
old.employee_id=new.employee_id;

In this example, we compare the Job_ID from the employees table and the historical employees table.
This query will show the name of the employees along with the job_id if they have changed position anytime. If they have never changed position, it won’t show the job_id, just the name.
This function is not very useful by itself, but with NVL2 it can be used for simple conditional logic sentences.
For instance:
NVL2(NULLIF(expression1,expression2),’The results are equal’,’The results are not equal’)