How to Use the COALESCE() Function in MySQL

08/04/2021

Contents

In this article, you will learn how to use the COALESCE() function in MySQL.

Using the COALESCE() function in MySQL

The COALESCE() function in MySQL is used to return the first non-null value from a set of values.

Syntax

The syntax for using the COALESCE() function in MySQL is as follows:

SELECT COALESCE(value1, value2, value3, ..., valuen) FROM table_name;

Examples

Suppose we have a table named “employees” with columns “employee_id”, “first_name”, “last_name”, and “salary”. We can use the COALESCE() function to find the first non-null value in the “first_name” and “last_name” columns for each employee as follows:

SELECT employee_id, COALESCE(first_name, last_name) AS name, salary FROM employees;

This query will return the employee ID, first non-null value in “first_name” and “last_name” columns (combined as “name”), and salary of all employees in the “employees” table.

We can also use the COALESCE() function with the IFNULL() function to handle null values appropriately. For example:

SELECT employee_id, COALESCE(first_name, IFNULL(nickname, 'Unknown')), last_name, salary FROM employees;

This query will return the employee ID, first non-null value in “first_name” column (replaced with “Unknown” if both “first_name” and “nickname” columns are null), last name, and salary of all employees in the “employees” table.

#MySQL