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 with an example. And the syntax behind this MySQL Coalesce 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 Coalesce 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');
MySQL COALESCE Function 1

Here, 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);
MySQL COALESCE Function 2

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

SELECT COALESCE(15, NULL, NULL);

SELECT COALESCE(NULL, 25, NULL);

SELECT COALESCE(NULL, NULL, 35);
MySQL COALESCE Function 3

MySQL COALESCE example 2

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

MySQL COALESCE Function 4

In this example, we find the Employees contact number using this MySQL 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 Coalesce function looks for a Mobile phone number and returns the same.
  4. If the user had all the numbers, then MySQL Coalesce function returns Personal Phone number.
SELECT 
    CustomerKey,
    CONCAT(FirstName,  ' ', LastName) AS FullName,
    EmailAddress,
    Phone,
    Office,
    Mobile,
    COALESCE(Phone, Office, Mobile)
FROM `MySQL Tutorial`.EmployeeDetails;
MySQL COALESCE Function 5

In this example, we used Office and Mobile numbers

SELECT 
    CustomerKey,
    CONCAT(FirstName,  ' ', LastName) AS FullName,
    EmailAddress,
    Phone,
    Office,
    Mobile,
    COALESCE(Office, Mobile)
FROM `MySQL Tutorial`.EmployeeDetails;
MySQL COALESCE Function 6