COALESCE() - MySQL Misc

The COALESCE() function in MySQL is used to return the first non-null value among its arguments. If all the arguments evaluate to null, then the function returns null. In this tutorial, we'll explore the syntax of the COALESCE() function and its use in MySQL.

Syntax

The syntax for the COALESCE() function is as follows:

COALESCE(value1, value2, value3, ..., valuen)

This function takes one or more arguments, separated by commas, and returns the first non-null value among them.

Example

Let's consider a table named Sales that contains the following data:

ID	Product	Quantity Sold	Price
1	Apples	20	0.50
2	Oranges	NULL	0.75
3	Grapes	30	NULL
4	Pears	NULL	NULL

Here's how we can use the COALESCE() function to select the Product, Quantity Sold, and Price for each row in the table:

SELECT Product, COALESCE(Quantity_Sold, 0), COALESCE(Price, 0.00) FROM Sales;

In the query above, we used COALESCE() to return the first non-null value between Quantity_Sold and 0, and between Price and 0.00.

Output

When we run the query from the example above, the output will be:

Product	COALESCE(Quantity_Sold, 0)	COALESCE(Price, 0.00)
Apples	20	0.50
Oranges	0	0.75
Grapes	30	0.00
Pears	0	0.00

Explanation

In the example above, we used the COALESCE() function to return the first non-null value among the Quantity_Sold and Price columns in the Sales table. For the Quantity_Sold column, if a value is null, then the function returns 0. For the Price column, if a value is null, then the function returns 0.00.

Use

The COALESCE() function is a useful tool for handling null values in MySQL. By using COALESCE(), we can avoid errors that occur when a value is null, and we can ensure that our queries return expected results.

Important Points

• The COALESCE() function returns the first non-null value among its arguments.
• If all the arguments evaluate to null, then the function returns null.
• The COALESCE() function can take any number of arguments.
• The arguments are evaluated from left to right until a non-null value is found.

Summary

In this tutorial, we explored the COALESCE() function in MySQL and saw how it can be used to handle null values. We discussed the syntax of the function, provided an example of its use, and explained its output. We also discussed the importance of using COALESCE() to handle null values in MySQL queries.