SQL - IFNULL(), ISNULL(), COALESCE() and NVL() Functions


Advertisements

Previous Page Next Page

The SQL NULL functions (IFNULL(), ISNULL(), COALESCE() and NVL()) are used to provided alternate value of a column if it contains NULL value.

Example:

Consider a database table called Product with the following records:

ProductNamePriceStock QuantityOrder Quantity
Apple1.0010020
Banana1.2512030
Orange2.15105NULL
Watermelon3.507515

If the Order Quantity is optional and can contain NULL values. The SQL code mentioned below will give NULL value.

SELECT ProductName, Price * ([Stock Quantity] + [Order Quantity]) 
FROM Product;

To avoid such situations, the NULL functions is used which provides alternative value to a column if it contains NULL value.

MySQL

The MySQL IFNULL() function or COALESCE() function can be used to provide an alternative value if column value is NULL:

SELECT ProductName, Price * ([Stock Quantity] + IFNULL([Order Quantity],0)) 
FROM Product;

OR

SELECT ProductName, Price * ([Stock Quantity] + COALESCE([Order Quantity],0)) 
FROM Product;

SQL Server

The SQL Server ISNULL() function can be used to provide an alternative value if column value is NULL:

SELECT ProductName, Price * ([Stock Quantity] + ISNULL([Order Quantity],0)) 
FROM Product;

Oracle

The Oracle NVL() function can be used to provide an alternative value if column value is NULL:

SELECT ProductName, Price * ([Stock Quantity] + NVL([Order Quantity],0)) 
FROM Product;

MS Access

In MS Access, IIF() function along with ISNULL() function can be used to achieve the same result:

SELECT ProductName, Price * ([Stock Quantity] + IIF(ISNULL([Order Quantity]),0,[Order Quantity])) 
FROM Product;

Previous Page Next Page
Advertisements