COALESCE

Returns the first argument that is not NULL — the standard way to supply a default.

Syntax

COALESCE(value1, value2 [, ...])

COALESCE evaluates its arguments left to right and returns the first non-NULL one, or NULL if all are NULL. Arguments after the first non-NULL one are not evaluated.

All arguments must share a common type: COALESCE(price, 'n/a') fails for a numeric price — cast first: COALESCE(price::text, 'n/a').
Common uses: labels for missing values (COALESCE(city, 'Unknown')), zero after a LEFT JOIN (COALESCE(sum(amount), 0)), fallback chains (COALESCE(mobile, phone, email)).
Replacing NULL with 0 changes averages and counts — only do it when "missing" really means zero.
GREATEST/LEAST in PostgreSQL ignore NULLs (they return NULL only if all arguments are NULL).

Examples

Default label starter

SELECT customer_name, COALESCE(email, 'no email on file') AS contact
FROM customers
ORDER BY customer_id;

Rows with a NULL email show the label instead.

Zero after a LEFT JOIN starter

SELECT p.product_name, COALESCE(sum(o.quantity), 0) AS units_ordered
FROM products p
LEFT JOIN orders o ON o.product_id = p.product_id
GROUP BY p.product_id, p.product_name
ORDER BY units_ordered, p.product_name;

Products that were never ordered show 0 rather than NULL.

Fallback chain shop

SELECT customer_id, COALESCE(email, phone, 'unreachable') AS best_contact
FROM customers
WHERE email IS NULL
ORDER BY customer_id
LIMIT 10;

Uses phone when email is missing, and the label when both are missing.

Pitfalls

Mixing types: COALESCE(numeric_col, 'none') is an error.
AVG(COALESCE(x, 0)) when you meant AVG(x): zeros drag the average down.
Using COALESCE to hide an unexpected NULL that signals a data or join problem.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: ISNULL(a, b) takes exactly two arguments and uses the type of the first one.
MySQL: IFNULL(a, b) is the two-argument form.
Oracle: NVL(a, b) is the two-argument form (it always evaluates both); remember '' is NULL.
BigQuery: IFNULL(a, b) is the two-argument form.