• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » Oracle NULLIF() function

Oracle NULLIF() function

December 8, 2019 by techgoeasy Leave a Comment

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

NULLIF function in Oracle

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

Filed Under: Oracle, Oracle Sql Tagged With: NULLIF function in Oracle

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle
  • How to use sed replace string in file
  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us