MySQL COALESCE Function

MySQL COALESCE Function with Examples

In this article, I am going to discuss MySQL COALESCE Function with Examples. Please read our previous article where we discussed MySQL SUBSTR Function with Examples.

MySQL COALESCE Function

The MySQL COALESCE function returns the first non-NULL value from the given list. If there are no non-null values The COALESCE function returns NULL.

Syntax:

Following is the syntax to use the MySQL COALESCE function.

MySQL COALESCE Function with Examples

Parameters:
Expression1 to Expression_n: The expressions to test for non-null values.

Return Value: The COALESCE function returns the first non-null value in a list of expressions or NULL if all the values in a list are NULL.

Examples:

Let’s understand the COALESCE function with some examples and also understand how to use the COALESCE function in MySQL.

Example1:
SELECT COALESCE(null, null, ‘Apple’, ‘Ball’);
Output: Apple

Example2:
SELECT COALESCE(‘Apple’, ‘Ball’, null, ‘Cat’, ‘Dog’);
Output: Apple

Example3:
SELECT COALESCE(null, 10, 20, 30, null, 40);
Output: 10

Example4:
SELECT COALESCE(null, ‘MySQLTutorials’, ‘dotnettutorials.net’);
Output: MySQLTutorials

Example5:
SELECT COALESCE(null, null, null, null, null);
Output: NULL

Example6:
SELECT COALESCE(‘dotnettutorials.net’, ‘Tutorials’, ‘MySQLTutorials’);
Output: ‘dotnettutorials.net

COALESCE Function with Database Table:

We are going to use the following Employee table to understand how to use the COALESCE function with the database table column with Examples.

MySQL COALESCE Function with Database Table

Please use the following SQL Script to create and populate the Employee table with the required sample data.

CREATE DATABASE Company;
USE Company;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  FirstName VARCHAR(45),
  MiddleName VARCHAR(45),
  LastName VARCHAR(45),
  Gender VARCHAR(45) NOT NULL,
  AGE INT NOT NULL,
  City VARCHAR(45)
);

INSERT INTO Employee VALUES (1001, 'John', NULL, 'Doe', 'Male', 25, 'London');
INSERT INTO Employee VALUES (1002, 'Mary', 'Smith', NULL, 'Female', 27, NULL);
INSERT INTO Employee VALUES (1003, NULL, 'James', 'Brown', 'Male', 28, 'London');
INSERT INTO Employee VALUES (1004, NULL, NULL, NULL, 'Male', 28, NULL);
INSERT INTO Employee VALUES (1005, 'Anurag', NULL, 'Mohanty', 'Male', 25, NULL);
INSERT INTO Employee VALUES (1006, 'Pranaya', 'Kumar', 'Rout', 'Female', 27, 'Mumbai');
INSERT INTO Employee VALUES (1007, NULL, NULL, 'Sambit', 'Male', 28, 'Mumbai');
Example1:

In the below example, we use COALESCE function with First, Middle, and Last Name column values.

SELECT Id, COALESCE(FirstName, MiddleName, LastName) AS Name, Gender, AGE, City FROM Employee;

Once you execute the above query, you will get the following output.

COALESCE Function in MySQL with Examples

Example2:

In the below example, we use COALESCE function with the City column. Instead of showing NULL, we are showing NA where the city column value is null.

SELECT Id, 
       COALESCE(FirstName, MiddleName, LastName) AS Name, 
       Gender, AGE, COALESCE(City, 'NA') AS City 
FROM Employee;

When you execute the above SQL Statement, you will get the following output.

MySQL COALESCE Function

Note: The COALESCE function accepts one parameter which is the list that can contain various values. The value returned by the COALESCE function is the first non-null value or NULL if all the values in a list are NULL.

In the next article, I am going to discuss MySQL CAST Function with Examples. Here, in this article, I try to explain the MySQL COALESCE Function with Examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published.