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 ?