SQL COALESCE

What is SQL COALESCE and how does it work?

Returns the first non-NULL value from a list of expressions.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL COALESCE:

SQL COALESCE Full Explanation

COALESCE is a scalar function defined in the SQL standard that scans its arguments from left to right and returns the first one that is not NULL. If all arguments are NULL, the function itself evaluates to NULL.Because COALESCE follows the same type-coercion rules as CASE, the result data type is the one with the highest precedence among the supplied expressions. This means you can mix numeric and character types, but the final data type will be promoted according to the database’s precedence rules.COALESCE is often used to provide default values, clean up NULLs in reports, or simplify CASE statements. It is deterministic, side-effect free, and can be nested or combined with other functions. All arguments are evaluated in most databases, so avoid heavy subqueries in later positions if performance matters.In distributed query engines, COALESCE behaves the same way but be mindful of NULL semantics across joined or unioned datasets.

SQL COALESCE Syntax

COALESCE ( expression1 , expression2 [ , expressionN ] );

SQL COALESCE Parameters

• expression1, expression2, … expressionN - Any scalar expression. The function requires at least two arguments and supports an arbitrary number. Each expression can be of any data type that can be implicitly converted to a common type.

Example Queries Using SQL COALESCE

-- Provide a default string when middle_name is NULL
SELECT first_name,
       COALESCE(middle_name, '') AS middle_name,
       last_name
FROM   employees;

-- Replace NULL prices with 0.00
SELECT product_id,
       COALESCE(price, 0.00) AS price
FROM   products;

-- Pick the first filled phone number
SELECT user_id,
       COALESCE(mobile_phone, work_phone, home_phone) AS best_phone
FROM   contacts;

-- Combine with aggregation
SELECT COALESCE(SUM(revenue), 0) AS total_revenue
FROM   sales
WHERE  sale_date = CURRENT_DATE;

Expected Output Using SQL COALESCE

Each row shows the first non-NULL value from the provided list
If every argument is NULL for a row, the output will be NULL

Use Cases with SQL COALESCE

Substitute NULL with a default value in reports
Chain multiple optional columns to pick the first populated field
Simplify CASE expressions that check for NULLs
Safely aggregate data when some rows may be missing values

Common Mistakes with SQL COALESCE

Forgetting that all arguments are evaluated – heavy subqueries placed late can hurt performance
Mixing incompatible data types that cannot be implicitly converted
Assuming COALESCE stops after finding a non-NULL in databases that always evaluate all arguments
Using it with only one argument – at least two are required

First Introduced In

SQL-92 standard

Frequently Asked Questions

What data type does COALESCE return?

It returns the data type with the highest precedence among its arguments, following the database’s implicit conversion rules.

Is COALESCE faster than CASE WHEN expression IS NULL?

Functionally they are similar. COALESCE is more concise but performance is usually the same because many optimizers rewrite it as a CASE expression.

Can COALESCE be indexed?

Indexes are applied to the underlying columns, not the function output. However, you can create a computed column using COALESCE and index that computed column in databases that support it.

How do I replace NULL with today’s date?

Use COALESCE(date_column, CURRENT_DATE). The function will return date_column when it is not NULL or the current date when it is NULL.