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:
ProductName | Price | StockQuantity | OrderQuantity |
---|---|---|---|
Apple | 1.00 | 100 | 20 |
Banana | 1.25 | 120 | 30 |
Orange | 2.15 | 105 | NULL |
Watermelon | 3.50 | 75 | 15 |
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:
ProductName | Price | StockQuantity | OrderQuantity | Inventory |
---|---|---|---|---|
Apple | 1.00 | 100 | 20 | 120.0 |
Banana | 1.25 | 120 | 30 | 187.5 |
Orange | 2.15 | 105 | NULL | NULL |
Watermelon | 3.50 | 75 | 15 | 315.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:
ProductName | Price | StockQuantity | OrderQuantity | Inventory |
---|---|---|---|---|
Apple | 1.00 | 100 | 20 | 120.0 |
Banana | 1.25 | 120 | 30 | 187.5 |
Orange | 2.15 | 105 | NULL | 225.75 |
Watermelon | 3.50 | 75 | 15 | 315.0 |
❮ MySQL Functions