MySQL COALESCE Function

The MySQL COALESCE function returns the first not Null value from the series of expressions, or NULL if there are no Not Null values.

Let us see how to use this MySQL Coalesce function with an example, and the syntax behind this is as shown below:

COALESCE (exp1, exp2, ......, expN)

MySQL COALESCE Function example

In this example, we show you how to use this Coalesce function on String data. The first statement returns Cherry because this function returns the first not Null value. The second statement returns Apple because the first value is NULL.

SELECT COALESCE('Cherry', 'Apple', 'Orange');

SELECT COALESCE(NULL, 'Apple', 'Orange');

SELECT COALESCE('Cherry', NULL, 'Orange');

The second statement returns Orange because the first two arguments or values are NULLs.

SELECT COALESCE('Cherry', 'Apple', NULL);

SELECT COALESCE(NULL, NULL, 'Orange');

SELECT COALESCE(NULL, 'Orange', NULL);

This example shows the working functionality of MySQL Coalesce function on Numerical values. It is the same as the above example.

SELECT COALESCE(15, NULL, NULL);

SELECT COALESCE(NULL, 25, NULL);

SELECT COALESCE(NULL, NULL, 35);

COALESCE Example 2

In this example, we show you how to use this function on table data. For this demo, we will use the MySQL Employee Details table, which contains the following records.

In this example, we find the Employees contact number using this Coalesce function:

1. If an employee has a Personal Phone number, return the Phone number.
2. If he does not have a Personal Phone number, but he/she has an Office Phone number, then return the Office Phone number.
3. And If an employee does not have a Personal Phone number or Office Phone, then the function looks for a Mobile phone number and returns the same.
4. If the user has all the numbers, then the MySQL Coalesce function returns the Personal Phone number.

SELECT 
       CustomerKey,
       CONCAT(FirstName,  ' ', LastName) AS FullName,
       EmailAddress,
       Phone,
       Office,
       Mobile,
       COALESCE(Phone, Office, Mobile)
 FROM EmployeeDetails;

In this example, we used Office and Mobile numbers.

SELECT 
       CustomerKey,
       CONCAT(FirstName,  ' ', LastName) AS FullName,
       EmailAddress,
       Phone,
       Office,
       Mobile,
       COALESCE(Office, Mobile)
 FROM EmployeeDetails;