Javatpoint Logo
Javatpoint Logo

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:

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:

MySQL COALESCE() Function

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:

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

Execute the below query to show the table data:

We will get the output below:

MySQL COALESCE() Function

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

It will give the following output:

MySQL COALESCE() Function

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.

We will get the output as below:

MySQL COALESCE() Function

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




Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA