MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

MySQL - IFNULL() and COALESCE() Functions



The NULL function can be used to provided alternate value of a column if it contains NULL value. In MySQL, there are two functions which can be used for this purpose:

  • IFNULL() Function
  • COALESCE() Function

Example:

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 NULL function is used which provides alternative value to a column if it contains NULL value.

MySQL IFNULL() Function

The MySQL IFNULL() function lets you to provide an alternative value if column value is NULL. The statement below returns 0 if the value is NULL.

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 COALESCE() Function

The MySQL COALESCE() function serves the same purpose as MySQL IFNULL() function. The above query is same as below:

SELECT *, Price * (StockQuantity + COALESCE(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