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

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:

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:

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: