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.