SQL COALESCE Function

Summary: in this tutorial, you’ll learn how to use the SQL COALESCE() function to handle NULL effectively.

Introduction to the SQL COALESCE function #

In SQL, the COALESCE() function takes one or more arguments and returns the first non-NULL argument.

Here’s the syntax of the COALESCE function:

COALESCE(argument1, argument2,...);Code language: SQL (Structured Query Language) (sql)

The COALESCE function evaluates its arguments from left to right and returns the first non-NULL argument.

The COALESCE function will return NULL in case all input arguments are NULL.

Basic SQL COALESCE function examples #

The following example shows how to use the COALESCE function with numbers 1, 2, and 3:

SELECT COALESCE(1, 2, 3) AS result;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 result -------- 1Code language: SQL (Structured Query Language) (sql)

The result is 1 because it is the first non-NULL argument.

The following statement returns the string SQL because it is the first non-NULL argument.

SELECT COALESCE(NULL, 'SQL', 'Tutorial') AS result;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 result -------- SQLCode language: SQL (Structured Query Language) (sql)

Short-circuit evaluation #

If you divide by zero, you’ll encounter an error:

SELECT 1/0;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

ERROR: division by zeroCode language: SQL (Structured Query Language) (sql)

But the following statement returns one instead of an error:

SELECT COALESCE(1, 1 / 0) result;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 result -------- 1Code language: SQL (Structured Query Language) (sql)

The reason is that the COALESCE function uses short-circuit evaluation. It means that the COALESCE function does not evaluate the remaining arguments after it encounters the first non-NULL arguments.

Using SQL COALESCE function to substitute NULL #

First, create a new table called bonuses to store employee bonuses:

CREATE TABLE bonuses ( employee_id INT PRIMARY KEY, amount DECIMAL(10, 2) NULL );Code language: SQL (Structured Query Language) (sql)

Try it

Some employees may have bonuses so that the amount column can be NULL.

Second, insert some rows into the bonuses table:

INSERT INTO bonuses (employee_id, amount) VALUES (101, 1000.00), (102, NULL), (103, 1500.00), (104, NULL), (105, 2000.00);Code language: SQL (Structured Query Language) (sql)

Try it

Third, use the COALESCE function to replace NULL with zero when calculating the total compensation that includes both salary and bonus:

SELECT e.first_name, e.last_name, e.salary, e.salary + COALESCE(b.amount, 0) AS total_compensation FROM employees e LEFT JOIN bonuses b ON e.employee_id = b.employee_id;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name | salary | total_compensation -------------+-------------+----------+-------------------- Neena | Kochhar | 17000.00 | 18000.00 Lex | De Haan | 17000.00 | 17000.00 Alexander | Hunold | 9000.00 | 10500.00 Bruce | Ernst | 6000.00 | 6000.00 ...Code language: SQL (Structured Query Language) (sql)

SQL COALESCE and CASE expression #

The COALESCE function is functionally equivalent to the following CASE expression:

CASE WHEN (argument1 IS NOT NULL) THEN argument1 WHEN (argument2 IS NOT NULL) THEN argument2 ELSE argument3 ENDCode language: SQL (Structured Query Language) (sql)

The following query uses the CASE expression instead of the COALESCE function:

SELECT e.first_name, e.last_name, e.salary, e.salary + CASE WHEN b.amount IS NULL THEN 0 ELSE b.amount END AS total_compensation FROM employees e LEFT JOIN bonuses b ON e.employee_id = b.employee_id;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name | salary | total_compensation -------------+-------------+----------+-------------------- Neena | Kochhar | 17000.00 | 18000.00 Lex | De Haan | 17000.00 | 17000.00 Alexander | Hunold | 9000.00 | 10500.00 Bruce | Ernst | 6000.00 | 6000.00 ...Code language: SQL (Structured Query Language) (sql)

Summary #

Use the SQL COALESCE function to return the first non-NULL argument from a list of arguments.

Quiz #

Databases #

Was this tutorial helpful ?