SQL Tutorial SQL Advanced SQL Database SQL References

MySQL IFNULL() Function



The MySQL IFNULL() function is used to provide alternate value if an expression is NULL. This function returns the expression, if the expression is NOT NULL.

Syntax

IFNULL(expression, value_if_null)

Parameters

expression Required. Specify the value to test as NULL.
value_if_null Required. Specify the value to return if expression is NULL.

Return Value

Returns expression, if expression is NOT NULL. Returns value_if_null, if expression is NULL.

Example 1:

The example below shows the usage of IFNULL() function.

mysql> SELECT IFNULL('Paris', 'London');
Result: 'Paris'

mysql> SELECT IFNULL(NULL, 'London');
Result: 'London'

mysql> SELECT IFNULL(5/0, 'Dividing by 0 returns NULL');
Result: 'Dividing by 0 returns NULL'

mysql> SELECT IFNULL(DATE('2018-10-15'), '2018-10-31');
Result: '2018-10-15'

mysql> SELECT IFNULL(DATE(NULL), '2018-10-31');
Result: '2018-10-31'

Example 2:

Consider a database table called Product with the following records:

ProductNamePriceStockQuantityOrderQuantity
Apple1.0010020
Banana1.2512030
Orange2.15105NULL
Watermelon3.507515

If the OrderQuantity is optional and can contain NULL values. The statement mentioned below will give NULL value.

SELECT *, Price * (StockQuantity + OrderQuantity) AS Inventory  
FROM Product;

This will produce the result as shown below:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULLNULL
Watermelon3.507515315.0

To avoid such situations, the IFNULL() function is used which provides alternative value to the column if it contains NULL value.

SELECT *, Price * (StockQuantity + IFNULL(OrderQuantity, 0)) AS Inventory
FROM Product;

This will produce the result as shown below:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULL225.75
Watermelon3.507515315.0

❮ MySQL Functions