SQL IFNULL(), NVL() AND ISNULL() functions

Look, below itemdetails table.

Item Id Item Name Unit Price Unit
1 Item 1 10 2
2 Item 2 30
3 Item 3 50 2

Here, Unit is optional column so it contains either some value or NULL.

Now let’s execute below query

select itemname, (unitprice * unit) as price
from itemdetails

Output should be:

item1 20
item2 NULL
item3 100

In above example, if any of “unit” values are NULL, the result is NULL.

The IFNULL(), NVL() and ISNULL() functions are used to replace NULL values.

Syntex:

IFNULL(UNIT, 0);
NVL(UNIT, 0);
ISNULL(UNIT, 0);

In this case you want NULL values to be zero.

SQL Server / MS Access

SELECT itemname , (unitprice*isNULL(unit,0))
FROM itemdetails

Oracle

Oracle hasn’t ISNULL() OR IFNULL() function. It used NVL() to produce same result:

SELECT itemname , (unitprice*NVL(unit,0))
FROM itemdetails

MySQL

MySQL hasn’t ISNULL() and NVL() function.

If you want to produce same result on MySQL so you need to use IFNULL() function:

SELECT itemname, (unitprice*ifNULL(unit,0))
FROM itemdetails

Leave a Reply

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