SQL - IFNULL(), ISNULL(), COALESCE() and NVL() Functions
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:
ProductName | Price | Stock Quantity | Order Quantity |
---|---|---|---|
Apple | 1.00 | 100 | 20 |
Banana | 1.25 | 120 | 30 |
Orange | 2.15 | 105 | NULL |
Watermelon | 3.50 | 75 | 15 |
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;