How to Use Coalesce in MySQL

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.

How to Use Coalesce in MySQL

Here are the steps to use COALSCE in MySQL

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.

Here are a couple of simple examples to show the functionality of COALESCE.

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

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

Also read : How to Query JSON column in MySQL

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.

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 coalesce(id, product, order_date, amount) from sales;
+-------------------------------------------+
| coalesce(id, product, order_date, amount) |
+-------------------------------------------+
| A                                         |
| 2                                         |
| C                                         |
| 4                                         |
+-------------------------------------------+

As you can see, COALESCE returns non-null values from each row.

Also read : How to Avoid Inserting Duplicate Records in MySQL

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     |
 +------+---------+------------+--------+

Also read : How to Get Multiple Counts with Single Query in MySQL

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.

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.