How to Use Coalesce in MySQL

Last updated on June 27th, 2024 at 09:20 am

Coalesce is a useful MySQL function that automatically returns first non-null value from a list of values. These values can be provided using literals, column names, or other MySQL functions. In this article, we will look at how to use Coalesce in MySQL.

Table of Contents

What is COALESCE in MySQL

COALESCE function returns the first non NULL value from a list of values. If all values in the list are NULL, then it returns NULL. Here is the syntax of Coalesce function in MySQL.

select coalesce(value1, value2, ..., valuen) 
from table_name;

In the above statement, you need to specify table name, and provide a list of comma-separated values. These values can be of any data type. They can all be of same or different data type. Please note, an empty value will not be treated as null. You need to explicitly state it, or it has to be column value.

When you call coalesce() function, it will evaluate the list of values from left to right and return the first non-null value. It is similar to CASE statement.

Why COALESCE is used in MySQL

COALESCE is used in MySQL to substitute null values with some other string of your choice. This is very useful for reporting and analytics where you don’t want to change the actual data but need to report it in a different way.

COALESCE vs IFNULL MySQL

Although IFNULL also can be used to substitute NULL values, it can accept only 2 arguments, returns the 1st argument if it is null, else returns second argument. COALESCE works with any number of arguments, not just two.

How to Use Coalesce in MySQL

We will look at some of the most common uses cases to use COALSCE in MySQL.

Here are some simple examples to show the functionality of COALESCE.

1. Using Literal Values

In the following two examples, we use coalesce() function to get the first non-null value from a list of literal values. In the first case, the first value itself is null so the second value is returned. In the second example, all the values are null so the result is null.

mysql> select coalesce(NULL, 2, 3);
+----------------------+
| coalesce(NULL, 2, 3) |
+----------------------+
|                    2 |
+----------------------+

mysql> select coalesce(NULL, NULL);
+----------------------+
| coalesce(NULL, NULL) |
+----------------------+
|                 NULL |
+----------------------+

2. Using Columns

In this example, we will call coalesce() function on multiple column names of a table.

Let us say you have the following table sales(id, product, order_date, amount) which contains null values.

mysql> create table sales(id int, 
        product varchar(5),
        order_date date, 
        amount int);

mysql> insert into sales(id, product, order_date, amount)
       values(null, 'A','2020-01-01',150),
       (2, null,'2020-01-01',150),
       (null, 'C',null,150),
       (4, 'D','2020-01-01',null);

mysql> select * from sales;
+------+---------+------------+--------+
| id   | product | order_date | amount |
+------+---------+------------+--------+
| NULL | A       | 2020-01-01 |    150 |
|    2 | NULL    | 2020-01-01 |    150 |
| NULL | C       | NULL       |    150 |
|    4 | D       | 2020-01-01 |   NULL |
+------+---------+------------+--------+

Now we will use COALESCE to get first non-null value from each row.

mysql> select id,coalesce(id, product, order_date, amount) from sales;
+----+-------------------------------------------+
| id | coalesce(id, product, order_date, amount) |
+----+-------------------------------------------+
| 1  | A                                         |
| 2  | 2                                         |
| 3  | C                                         |
| 4  | 4                                         |
+----+-------------------------------------------+

As you can see, COALESCE returns non-null values from each row. For each row, coalesce() will evaluate the list of column values id, product, order_date, amount and return the first non-null value. We also display the id value for each column.

3. Using SQL Functions

In this example, we will call coalesce() function on result of other SQL functions and operators.

mysql> select coalesce(id*10, product, order_date - interval 10 days, floor(amount/10)) from sales;

In the above example, the first argument of coalesce function is id column multiplied by 10, the next column product as it is, the 3rd column is order_date minus 10 days, and the last column is a result of floor() function called on amount/10. All these are valid and work in coalesce function.

4. To Substitute Null Values

You can also use COALESCE function to substitute null values with custom strings. In the following example, we will replace each null value with ‘NA’ string using COALESCE.

mysql> select coalesce(id, 'NA') id, 
       coalesce(product,'NA') product, 
       coalesce(order_date,'NA') order_date, 
       coalesce(amount,'NA') amount 
       from sales;
 +------+---------+------------+--------+
 | id   | product | order_date | amount |
 +------+---------+------------+--------+
 | NA   | A       | 2020-01-01 | 150    |
 | 2    | NA      | 2020-01-01 | 150    |
 | NA   | C       | NA         | 150    |
 | 4    | D       | 2020-01-01 | NA     |
 +------+---------+------------+--------+

This is a useful way to clean up your data and replace null values in it with something more intuitive or user friendly.

Conclusion

In this article, we have learnt what is coalesce() function in MySQL, why it is used and how to use it. We have learnt several common use cases such as with literals and columns. We have also learnt how to call coalesce on result of SQL operators and functions. Lastly, we have learnt how to use it to substitute null values with other suitable strings, for reporting purposes. Coalesce is commonly used in numerous organizations for data analysis and reporting. It allows you to easily detect null values in your data.

COALESCE in Ubiq

Ubiq Reporting tool supports all the above SQL queries and makes it easy to visualize SQL results in different ways. Here is the COALESCE SQL query mentioned above, in Ubiq.