COALESCE

Return the first non-NULL argument from a list of expressions.

Category: conditionalReturns: ANYDialect: Standard

Syntax

COALESCE(expr1, expr2, ...)

Description

## Overview Evaluates its arguments left-to-right and returns the first non-NULL value. If all arguments are NULL, COALESCE returns NULL. This is the standard SQL way to supply a default for nullable expressions. Use COALESCE to simplify fallback chains across multiple columns or tables, to compute a user-visible label from best-available fields, or to coerce aggregate results to a non-NULL numeric. ## Behavior - Short-circuits: arguments after the first non-NULL are not evaluated. This matters when later arguments would raise errors or perform expensive work. - Returns NULL when every argument is NULL. - Result type is the common supertype of all argument types; arguments must be coercible to that supertype. - Accepts two or more arguments (most engines also accept a single-argument form). - Works with scalars, composite types, and arrays whose element types share a common supertype. ## Compatibility - COALESCE is part of ANSI SQL and is supported identically across all major SQL engines. - The equivalent CASE expression is `CASE WHEN expr1 IS NOT NULL THEN expr1 WHEN expr2 IS NOT NULL THEN expr2 ... END`.

Parameters

Name	Type	Description
`expr`		Specifies one or more expressions evaluated left-to-right. The first non-NULL value is returned. All expressions must be coercible to a common supertype.

Examples

-- First non-NULL wins
SELECT COALESCE(NULL, NULL, 'hello') AS v;  -- 'hello'

-- First argument is non-NULL, later args not evaluated
SELECT COALESCE('first', 'second', 'third') AS v;  -- 'first'

-- All NULL returns NULL
SELECT COALESCE(NULL, NULL, NULL) AS v;  -- NULL

-- Realistic: provide a default for nullable columns
SELECT user_id, COALESCE(display_name, email, 'unknown') AS label
FROM iam.directory.users;

-- Chain across tables for a fallback
SELECT o.order_id, COALESCE(s.shipping_address, c.default_address) AS ship_to
FROM ecommerce.sales.orders o
LEFT JOIN ecommerce.sales.order_shipping s ON o.order_id = s.order_id
LEFT JOIN ecommerce.sales.customers c ON o.customer_id = c.customer_id;

-- Guard against NULL in aggregations
SELECT COALESCE(SUM(amount), 0) AS total
FROM ecommerce.sales.orders
WHERE order_date = DATE '2026-04-19' AND customer_id = 0;

Pitfalls

All arguments must share a common supertype; mixing incompatible types (for example STRING and ARRAY) raises an error at parse time.
Short-circuit evaluation means later arguments are not evaluated once a non-NULL is found. Side effects in later arguments (such as nextval) may not fire.
COALESCE on a numeric column returning 0 as a default changes the semantics of AVG and similar aggregates; prefer aggregate-level COALESCE (COALESCE(SUM(x), 0)) rather than per-row coalescing when the intent is a default for empty groups.
Empty string '' is not NULL. COALESCE(column, 'default') returns '' for empty-string columns. Use NULLIF(column, '') first if you need to treat empty strings as missing.
The result type is the common supertype. This can cause unexpected widening (for example INT to DECIMAL) when arguments have different numeric precisions.