MySQL COALESCE() Function

If you are interested to know about the MySQL Convert() Function

The COALESCE() function in MySQL is used to return the first non-null value in a specified series of expressions. If this function evaluates all values of the list are null, or it does not find any non-null value, then it returns NULL. 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

The following are the syntax of using COALESCE() function in MySQL:

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

In the above syntax, we can see that the function takes many arguments, and when it finds the first value other than null, it returns that non-null value. Sometimes all the values of the list are null; in that case, it returns NULL. The COALESCE() function is similar to the IF_ELSE statement, as given below

IF (value1!= NULL) THEN  
   result = value1;  
ELSIF (value2 != NULL) THEN  
   result = value2;  
ELSE  
   result = NULL;  
END IF;  

Parameter Explanation

This function accepts only one parameter, which is the list that has various values. value1, value2,…..,valueN: It specifies the values of the list to return the non-null or NULL value in the output. We can understand it more clearly through the following illustration:

CASE1: COALESCE(NULL, NULL);

CASE2: COALESCE(0, NULL);

In both cases, the function always returns NULL because it cannot find any non-null value.

MySQL version support

The COALESCE() function can support the following MySQL versions:

• MySQL 8.0
• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0

Let us understand the MySQLCOALESCE() function with the following examples. We can use the COALESCE() function with the SELECT statementdirectly.

Example 1

SELECT COALESCE(NULL, 'A', 'B', NULL)

Output

When we implement the coalesce function on the list, it will give the output “A” because it is the first non-null value of the list.

Example 2

SELECT COALESCE('Mango', 'Apple', 'Orange', NULL);

Output

When we implement the coalesce function on the list, it will give the output “Mango” because it is the first non-null value of the list.

Example 3

SELECT COALESCE(NULL, 1, 2, 'MySQL', NULL, 'JAVA', NULL);

Output

When we implement the coalesce function on the list, it will return the numeric value “1” because it is the first non-null value of the list.

Example 4

SELECT COALESCE(NULL, NULL, 'javatpoint', NULL);

Output

When we implement the coalesce function on the list, it will return “javatpoint” because it is the first non-null value of the list.

Let us see all of the above examples in the MySQL command-line tool:

Example 5

In this example, we are going to understand how to COALESCE() function works on the table data. First, create a table “employee” using the following statement:

CREATE TABLE employee (  
  Emp_id INT NOT NULL,  
  Name VARCHAR(45) NULL,  
  Designation VARCHAR(45) NULL,  
  Phone VARCHAR(45) NULL,  
  Mobile VARCHAR(45) NULL,  
  Office VARCHAR(45) NULL,  
  PRIMARY KEY (Emp_id)  
);  

Next, add data into the table using INSERT statement, as given below:

INSERT INTO employee(Emp_id, Name, Designation, Phone, Mobile, Office)   
VALUES (1, 'Peter', 'Engineer', '101-101-101', '111-111-111', '100-100-100'),  
(2, 'Joseph', 'Developer', '201-201-201', '222-222-222', NULL),  
(3, 'John', 'Leader', '301-301-301', NULL, '300-300-300'),  
(4, 'Stephen', 'Scientist', '401-401-401', '444-444-444', '400-400-400'),  
(5, 'Suzi', 'Carpenter', NULL, '555-555-555', '500-500-500'),  
(6, 'Bob', 'Actor', '601-601-601', '666-666-666', '600-600-600'),  
(7, 'Donald', 'Engineer', NULL, NULL, NULL);  

Execute the below query to show the table data:

SELECT * FROM employee; 

We will get the output below:

Now, execute this statement that uses COALESCE() function to returns only the mobile number if the employee has all contacts numbers:

SELECT Emp_id, Name, Designation, COALESCE(Mobile, Office, Phone) FROM employee;

It will give the following output:

If the employee has an only office or mobile contacts, then execute this statement that returns an office number. When it does not find office number, return phone contact.

SELECT Emp_id, Name, Designation, Mobile, Office, Phone, COALESCE(Office, Mobile) FROM employee; 

We will get the output as below:

IFNULL() vs COALESCE() Function

The main difference between these functions are:

IFNULL()	COALESCE()
IFNULL() function takes only two expressions. After doing an evaluation, it returns the first expression if this expression is not NULL; otherwise, it returns the second expression.	The COALESCE() function return first non-null value in a specified series of expression. If this function evaluates all values of the list are null, or it does not find any non-null value, then it returns NULL.

Example of MySQL COALESCE()

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

12	SELECT COALESCE(NULL, 12, NULL, 2) AS Result; SELECT COALESCE(NULL, NULL, NULL, NULL, 'India', 'Japan', NULL) AS Result;

• 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() 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.

1	SELECT COALESCE(NULL, NULL, NULL, NULL) AS Result;

And the output is –

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.

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.

1	SELECT ID, FirstName, City, COALESCE(Mobile, 'N/A') AS 'Mobile Number' FROM Persons;

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 –

Let us look at the below query now.

1	SELECT ID, FirstName, City, COALESCE(NULL, Mobile, 'None', 'N/A') AS 'Mobile Number' FROM Persons;

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 –

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 –

1	SELECT ID, FirstName, LastName, COALESCE(Mobile, Work, HomePhone) AS Contact FROM Persons;

And the output is –

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;

And the output is –