MySQL COALESCE() function

Last update on May 22 2024 12:13:28 (UTC/GMT +8 hours)

COALESCE() function

MySQL COALESCE() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values.

This function is useful in -

It allows you to set a default value when a field is NULL.
It returns the first non-NULL value from a list of expressions.
This is particularly useful when dealing with potentially null fields, allowing you to provide a default value.
This is handy in cases where you want to display a specific value instead of a blank space.
It can be used in conjunction with aggregate functions to handle NULL values within calculations or expressions.
It helps to prevent errors that may occur due to operations involving NULL values.
In dynamic SQL, where values might be unknown or changeable, COALESCE() is useful for providing a fallback value.
It aids in data transformation by providing a way to replace NULL values with meaningful alternatives, making it easier to work with the data.

MySQL Version: 8.0

Syntax:

COALESCE(value1,value2,value3,...)

The above syntax is equivalent to the following IF-THEN-ELSE statement

Code:

IF value1 is not NULL THEN
     result = value1;
  ELSIF value2 is not NULL THEN
     result = value2;
  ELSIF value3 is not NULL THEN
     result = value3;
  ELSE
     result = NULL;
  END IF;

Example-1: MySQL COALESCE() function

mysql> SELECT COALESCE(NULL, 2, 3);
+----------------------+
| COALESCE(NULL, 2, 3) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (0.02 sec)
mysql> SELECT COALESCE(NULL, NULL, NULL);
+----------------------------+
| COALESCE(NULL, NULL, NULL) |
+----------------------------+
|                       NULL |
+----------------------------+
1 row in set (0.00 sec)

Pictorial Presentation:

Example-2:

The following MySQL statement returns date of establishment for the Jex Max Publication, BPP Publication, Ultra Press Inc., Night Publication and Novel Publisher Ltd. For New Harrold Publication,Mountain Publication Summer, Pieterson Grp. of Publishers, the query returns the country, since they don't have any date of establishment (NULL).

Sample table: newpublisher

Code:


-- This query selects the publisher name and a non-null value from the columns 'estd', 'country', or 'pub_city' in the 'newpublisher' table.
SELECT pub_NAME, COALESCE(estd, country, pub_city)
-- This statement specifies the columns to be retrieved: 'pub_NAME' and a non-null value from 'estd', 'country', or 'pub_city'.
FROM newpublisher;
-- This part of the query specifies the table from which data is being retrieved, which is 'newpublisher'.

Explanation:

The purpose of this SQL query is to retrieve the publisher name and the first non-null value from the 'estd', 'country', or 'pub_city' columns for each publisher in the 'newpublisher' table.

SELECT pub_NAME, COALESCE(estd, country, pub_city): This part of the query selects two columns. The first is pub_NAME, which is the name of the publisher. The second is the result of the COALESCE function, which returns the first non-null value from the columns estd, country, and pub_city.

pub_NAME: The name of the publisher.

COALESCE(estd, country, pub_city): The COALESCE function checks each column in the order they are listed and returns the first non-null value it finds. If estd is not null, it will return estd; if estd is null, it checks country; if both estd and country are null, it returns pub_city.

FROM newpublisher: This part specifies the table from which the data is being selected, which is the 'newpublisher' table.

Output:

mysql> SELECT pub_NAME,COALESCE(estd,country,pub_city)
-> FROM newpublisher;
+------------------------------+---------------------------------+
| pub_NAME                     | COALESCE(estd,country,pub_city) |
+------------------------------+---------------------------------+
| Jex Max Publication          | 1969-12-25                      | 
| BPP Publication              | 1985-10-01                      | 
| New Harrold Publication      | Australia                       | 
| Ultra Press Inc.             | 1948-07-10                      | 
| Mountain Publication         | USA                             | 
| Summer Night Publication     | 1990-12-10                      | 
| Pieterson Grp. of Publishers | UK                              | 
| Novel Publisher Ltd.         | 2000-01-01                      | 
+------------------------------+---------------------------------+
8 rows in set (0.04 sec)

Difference between IFNULL() and COALESCE() function in MySQL

In MySQL, IFNULL() takes two expressions and if the first expression is not NULL, it returns the first expression otherwise it returns the second expression whereas COALESCE() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values. See the following examples :

mysql> SELECT IFNULL('Red', 'Green');
+------------------------+
| IFNULL('Red', 'Green') |
+------------------------+
| Red                    |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT IFNULL(NULL, 'Green');
+-----------------------+
| IFNULL(NULL, 'Green') |
+-----------------------+
| Green                 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT COALESCE(NULL, 'Green');
+-------------------------+
| COALESCE(NULL, 'Green') |
+-------------------------+
| Green                   |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT COALESCE(NULL, 'Red', 'Green');
+--------------------------------+
| COALESCE(NULL, 'Red', 'Green') |
+--------------------------------+
| Red                            |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT COALESCE(NULL, NULL, NULL, 'Red');
+-----------------------------------+
| COALESCE(NULL, NULL, NULL, 'Red') |
+-----------------------------------+
| Red                               |
+-----------------------------------+
1 row in set (0.00 sec)