MySQL COALESCE() 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.

Syntax

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

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 MySQL COALESCE() function with the following examples. We can use the COALESCE() function with the SELECT statement directly.

Example 1

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

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

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

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:

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.

Next TopicMySQL Wildcards

← prev next →