COALESCE functions in SQL

The COALESCE function in SQL is used to return the first non-NULL expression among its arguments. It takes a list of expressions and returns the first one that is not NULL. If all expressions evaluate to NULL, the function returns NULL.

SQL Syntax

COALESCE(expression1, expression2, ..., expressionN)

Example: Let's create an employees table.

SQL

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    middle_name VARCHAR(50),
    last_name VARCHAR(50)
);

Load the data into the Employees table.

SQL

INSERT INTO Employees VALUES
(1, ‘Ashish’, NULL, 'Goel'),
(2, 'Arjun', 'Singh', 'Reddy'),
(3, 'Mika’, NULL, NULL);

By executing the below query, we can see the data from the table.

SQL

SELECT * FROM Employees;

Table: Employees

Employee_ID	First_Name	Middle_Name	Last_Name
1	Ashish	NULL	Goel
2	Arjun	Singh	Reddy
3	Mika	NULL	NULL

We want to get a full name where:

Use the middle_name if it's available.
If the middle_name is NULL, use the last_name.

Here’s the query using COALESCE:

SQL

SELECT employee_id, first_name,
COALESCE(middle_name, last_name) AS preferred_name
FROM employees;

The result of the above query is shown below:

Employee_ID	First_Name	Preferred_Name
1	Ashish	Goel
2	Arjun	Singh
3	Mika	NULL

For employee 1: middle_name is NULL, so last_name ("Goel") is used.
For employee 2: middle_name ("Singh") is used since it’s not NULL.
For employee 3: Both middle_name and last_name are NULL, so the result is NULL.

Previous Next