SQL Tutorial SQL Advanced SQL Database SQL References

MySQL COALESCE() Function



The MySQL COALESCE() function returns the first non-null expression in the list. If all expressions evaluate to NULL, then this function will return NULL.

Syntax

COALESCE(expression1, expression2, ... expression_n)

Parameters

expression1, expression2, ... expression_n Required. Specify the expressions to test for non-null values.

Return Value

Returns the first non-null expression from the given list.

Example 1:

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

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

mysql> SELECT COALESCE('Mumbai', NULL, NULL, 'London', 'Paris');
Result: 'Mumbai'

mysql> SELECT COALESCE(NULL, NULL, NULL, NULL, NULL);
Result: NULL

mysql> SELECT COALESCE(1, 2, NULL, NULL, 3);
Result: 1

mysql> SELECT COALESCE(NULL, 10, NULL, 20, 30);
Result: 10

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 COALESCE() function can be used to provide an alternative value to the column if it contains NULL value.

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

❮ MySQL Functions