MySQL COALESCE() Function

By /
COALESCE Function

In this tutorial, we will study the MySQL COALESCE() function. Suppose you have a table of contact details of a thousand people. The contact details can be a mobile number, a home number or a work phone.

There is a column for each of these contact details. Note that not all contact detail columns are filled in, some are empty. Instead of going through every contact and picking the contact number manually, you want to display a view of the table such that we get the name of the person and a non-null contact number from the Mobile, Home and Work phone columns. 

This is where the COALESCE() function comes into play. The COALESCE() function is used to return the first non-null value from a list. Let us take a look at the syntax of this function before exploring it deeper.

Syntax of MySQL COALESCE()

COALESCE(value1, value2, ...., value_n)Code language: SQL (Structured Query Language) (sql)

Where, ‘value1, value2, …., value_n’ are the list of values from which the first non-null value is to be returned.

Example of MySQL COALESCE()

Let us start things with a basic example. Consider the below queries.

SELECT COALESCE(NULL, 12, NULL, 2) AS Result; 
SELECT COALESCE(NULL, NULL, NULL, NULL, 'India', 'Japan', NULL) AS Result;Code language: SQL (Structured Query Language) (sql)
  • In the first query, our list of values are – NULL, 12, NULL, 2. MySQL COALESCE() will return the first non-null value from this list. In this case, it is 12.
  • In our second query, our list of values is – NULL, NULL, NULL, NULL, ‘India’, ‘Japan’, NULL. COALESCE() will return the first non-null value from this list too. In this case, it is ‘India’.

We use the SELECT statement to display our result and use an alias to make the output readable. The output is – 

MySQL COALESCE Basic Example

MySQL COALESCE() With A List of Null Values

What do you think would COALESCE() return if we pass a list of NULL values to it? Let’s see this using the below example.

SELECT COALESCE(NULL, NULL, NULL, NULL) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL COALESCE Null List

As you can see, MySQL COALESCE() will return NULL only if we pass a list of NULL values to it.

Working With Tables

Consider the below Persons table.

Persons Table MySQL COALESCE
Persons Table

Handling NULL Values with COALESCE()

One of the most important use cases of COALESCE() is it helps handle NULL values in a table. The idea is, you provide as many arguments as you need to the COALESCE() function. The function will then return the first non-NULL value in the list, or NULL if there are no non-NULL values. Let’s consider the below query.

SELECT ID, FirstName, City, COALESCE(Mobile, 'N/A') AS 'Mobile Number' FROM Persons;Code language: SQL (Structured Query Language) (sql)

We want to display the ID, first name, city and mobile number of every person in the table. We pass the Mobile column and ‘N/A’ to the COALESCE() function. COALESCE() will check for the value in the Mobile column.

If it has a non-null value, it will be displayed. However, if it has a NULL value, then ‘N/A’ will be displayed since ‘N/A’ is the next non-null value in the list of values passed to the COALESCE() function. This helps us to display a better output if we have NULL values in it. The output is –

Coalesce Handling Null

Let us look at the below query now.

SELECT ID, FirstName, City, COALESCE(NULL, Mobile, 'None', 'N/A') AS 'Mobile Number' FROM Persons;Code language: SQL (Structured Query Language) (sql)

The idea is similar to what we did in the previous query. However, just to complicate matters a little, we passed a list of NULL, the Mobile column, ‘None’ and ‘N/A’. The query will work similar to the first one, however, if the Mobile column has a NULL value, ‘None’ will always be displayed as ‘None’ is a string in the list and the first non-null value in this case. The output is –

Coalesce Handling Null2

Example of MySQL COALESCE() function

Now let us come to the problem I mentioned in the beginning.

Suppose you have the above Persons table. Instead of going through every contact and picking the contact number manually, you want to display a view of the table such that we get the name of the person and a non-null contact number from the Mobile, Home and Work phone columns.

Here, we will pass a list of columns in the COALESCE() function – the columns being – Mobile, Work and HomePhone. The query is –

SELECT ID, FirstName, LastName, COALESCE(Mobile, Work, HomePhone) AS Contact FROM Persons;Code language: SQL (Structured Query Language) (sql)

And the output is –

Coalesce Table Example Contact

Example 2

Lastly, let us use COALESCE() to display the Given Name of a person. A given name of the person can be in one of the three formats:

  • First name + Middle name + Last Name
  • First name + Last name
  • First Name

We will also use the CONCAT() function here. The query is –

SELECT ID, COALESCE(CONCAT(FirstName,' ', MiddleName, ' ',LastName), 
CONCAT(FirstName,' ', LastName), FirstName) AS 'Given Name' FROM Persons;Code language: SQL (Structured Query Language) (sql)

And the output is –

Coalesce Table Example Name

Conclusion

COALESCE() proves very useful in handling NULL values while displaying them. I would encourage you to practice examples of this function.