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.
SHOWPIN 122 in 1 Precision Computer Screwdriver Kit, Laptop Screwdriver Sets with 101 Magnetic Drill Bits, Electronics Tool Kit Compatible for Tablet, PC, iPhone, PS4 Repair
(5123)
Powerful Functions: 122 in 1 precision screwdriver set contains 98 screwdriver bits, 3 Torx TR screwdriver bits and 21 auxiliary tools, such as screwdriver handle, flexible shaft, extension rod, magnetizer, magnetic pad, cleaning brush etc. Own this ... read more
ASUS Vivobook 15.6” FHD Laptop, AMD Ryzen 3 3250U, 8GB RAM, 128GB SSD, Windows 11 Home in S Mode, Transparent Silver, M515DA-WS33
$319.33 (as of 28/06/2024 15:42 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)【Day-to-day Productivity】: This Windows laptop equipped with Windows 11 Home in S Mode, an AMD Ryzen 3 Processor, and AMD Radeon graphics, is designed to deliver a fast and smooth experience. 【Enhanced Protection】: Crafted with exceptional durability... read more
Compressed Air Duster, 3 Gear to 51000RPM Cordless Electric Air Duster, 6000mAh Rechargeable Air Blower with LED Light for Laptop Computer Keyboard Cleaning Pet Hair Crumb Replaces Compressed Air Can
(492)
TRUE 51000RPM: Our air duster adopters the most advanced motor, the maximum speed can reach TRUE 51000RPM, low energy consumption and longer lifespan. Great to help you deep cleaning, computers, Camera, electronic products, or for inflation. The cool... read more
Baseus USB C to HDMI Adapter, 4K@60Hz USB C Docking Station, 7 in 1 USB C Hub with 3 USB-A, PD 100W, TF/SD Card Reader, USB C Dock Compatible for iPhone 15/Mac/Dell/Acer/HP/ASUS/Steam Deck/Rog Ally
(1355)
【7-in-1 Design】USB hub for laptop with 1 HDMI port, 3 USB3.0 ports, 1 SD/TF card slot, and 1 USB-C PD port, excellent connectivity to satisfy all your expansion needs at once, much higher efficiency at work. 【4K@60Hz Ultra Clarity】USB c to hdmi adapt... read more