COALESCE
COALESCE returns the first non-NULL value from a list of expressions, commonly used to supply default values or merge multiple possibly-NULL columns.
Overview
COALESCE(expr1, expr2, ..., exprN) evaluates its arguments in order and returns the first one that is not NULL. If every argument is NULL, the result is NULL. It's part of standard ANSI SQL and available in essentially every SQL database, including DuckDB.
Copy code
SELECT COALESCE(preferred_name, first_name, 'Unknown') AS display_name
FROM users;
Common use cases
Default values. Replace missing data with a fallback:
Copy code
SELECT product_id, COALESCE(discount_pct, 0) AS discount_pct
FROM products;
Merging columns from different sources, such as picking whichever address field is populated:
Copy code
SELECT COALESCE(shipping_address, billing_address) AS effective_address
FROM orders;
Safe arithmetic, since any arithmetic involving NULL produces NULL:
Copy code
SELECT quantity * COALESCE(unit_price, 0) AS line_total
FROM order_items;
COALESCE vs CASE vs NULLIF
COALESCE(a, b) is shorthand for CASE WHEN a IS NOT NULL THEN a ELSE b END, extended to any number of arguments. It's the inverse in spirit of NULLIF, which turns a specific value into NULL rather than replacing NULL with a value.
DuckDB notes
DuckDB implements COALESCE with short-circuit evaluation -- later arguments aren't evaluated once a non-NULL value is found, which matters if a later argument is an expensive expression or subquery. DuckDB also supports the related IFNULL(a, b) as a two-argument convenience function equivalent to COALESCE(a, b).
Type considerations
All arguments to COALESCE should share (or be implicitly convertible to) a common type; DuckDB will attempt to find a common supertype across the arguments and cast accordingly, but mixing incompatible types (e.g., a date and a string that isn't a valid date literal) will raise an error.
Related terms
NULLIF returns NULL if two expressions are equal, and otherwise returns the first expression -- commonly used to avoid divide-by-zero errors or convert sentinel values into NULL.
NULL →NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL.
GREATEST and LEAST →GREATEST and LEAST return the largest or smallest value, respectively, among a list of expressions passed as arguments.
CASE expression →The CASE expression evaluates a list of conditions and returns a corresponding value for the first one that's true, functioning as SQL's if/else logic inside a query.
aggregate functions →Aggregate functions are SQL operations that perform calculations across multiple rows of data to produce a single summary value.
IN operator →The IN operator tests whether a value matches any value in a given list or subquery, providing a concise alternative to multiple OR conditions.
FAQS
NULL -- COALESCE only returns a non-NULL value if at least one argument is non-NULL.
IFNULL(a, b) is a two-argument shorthand equivalent to COALESCE(a, b); COALESCE is the more general, standard form that accepts any number of arguments.
No -- DuckDB and most databases short-circuit, stopping at the first non-NULL argument, so later arguments (including subqueries) aren't necessarily evaluated.
