MySQL COALESCE() Function

The MySQL COALESCE() function returns the first non-null value in a list of expressions.

The COALESCE function in MySQL is used to get the first non-null value from a list of expressions.

If all the values in the list are evaluated to NULL, then the COALESCE() function returns NULL. The COALESCE() function accepts one parameter, which is the list, which can contain various values.

The MySQL COALESCE function syntax is:

COALESCE(value_1, value_2, …., value_n)

Parameters:

value_1: It is used to specify the first value in the list.

COALESCE( ) function is supported in following versions of MySQL

• MySQL 5.7, MySQL 5.6 ,MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

Let’s look at some examples of the COALESCE() function in MySQL and understand how to use COALESCE function.

Using COALESCE() function on a list example

In this example, we will use the COALESCE function on a list of values where the first value is NULL.

Query:

SELECT COALESCE(NULL, 'A', 'B', NULL); 

Output:

A 

Example 2

In this example, we will use the COALESCE function on a list of values where the first value is not NULL

Query

SELECT COALESCE('A', NULL, 'B', NULL); 

Output:

A 

• MySQL COALESCE() function is used to return the first non-NULL value from a list of expressions.
• If all the expressions evaluate to NULL, the COALESCE() function will return NULL.
• COALESCE() can be used to substitute NULL values in table columns with a default value or an expression.
• COALESCE() is more flexible than IFNULL() as it can handle any number of arguments, while IFNULL() only takes two arguments.