Oracle database

NULLIF oracle

Written by meda

NULLIF Function

NULLIF Function has  two parameters and checks whether the value of these two parameters are equal or not.

The syntax  is :

NULLIF(ifunequal, comparision_term)

If the ‘ifunequal‘ parameter value  and the ‘comparision_term‘ parameter  value are equal,  the function returns NULL value unless it returns the ‘ifunequal’ parameter value.

Example: Connect to your oracle database through SQL*PLUS command-line client tool and run the following SQL statement.

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Fri Feb 25 22:09:15 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn sys/orcl@orcl as sysdba
Connected.

---When the value of 'ifunequal' parameter and 'comparision_term' parameter are unequal(date)
SQL> select nullif('25-FEB-22','24-FEB-22') as value from dual;

VALUE
---------
25-FEB-22

---When the value of 'ifunequal' parameter and 'comparision_term' parameter are equal(number)
SQL> select nullif(567,567) as value from dual;

VALUE
----------

---When the value of 'ifunequal' parameter and 'comparision_term' parameter are unequal(string)
SQL> select nullif('Aman','Messi') as value from dual;

VALU
----
Aman


---When the value of 'ifunequal' parameter and 'comparision_term' parameter are equal(string)
SQL> select nullif('Messi','Messi') as value from dual;

V
-
SQL>

Here is also another example, just  select from tbl_customer table, which has a column named ‘swift_code‘ of null value.

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 7 10:27:25 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> CONN mereba/mereba@orcl
Connected.
SQL> col cust_no format a30;
SQL> col customer_type format a10;
SQL> col cust_name format a23;
SQL> col country format a15;
SQL> col swift_code format a14;
SQL> SELECT cust_no,NULLIF(swift_code,null)swift_code,cust_name,country FROM TBL_CUSTOMER;

CUST_NO SWIFT_CODE CUST_NAME COUNTRY
---------- -------------- ----------------------- ---------------
########## Messi John Dave USA
########## Mereba Technology UK
########## Swiss Bank Switzerland

SQL>

As we can see in the query above, since the value of swift_code and null are equal, the function returns null value in the query result.