ClickHouse coalesce Function: NULL Handling with Examples

The coalesce function in ClickHouse returns the first non-NULL value from a list of expressions, evaluated left to right. It accepts any number of parameters and is the standard way to substitute defaults for missing data in SELECT, WHERE, and GROUP BY clauses. All arguments must be of mutually compatible types; the result type is the common supertype. Returns NULL only when every argument is NULL.

Syntax

coalesce(expr1, expr2[, expr3, ...])

See the official ClickHouse functions-for-nulls reference for the canonical definition.

Parameters

Return type: the common supertype of the argument types (e.g. String, Float64, Date). If every argument is NULL, returns NULL.

Examples

Substituting defaults for missing columns

SELECT
    user_id,
    coalesce(display_name, first_name, 'Anonymous') AS name,
    coalesce(age, 0) AS age
FROM users;

coalesce tries display_name first, falls back to first_name, and finally uses the literal 'Anonymous' if both are NULL.

Filtering with NULL-aware comparisons

SELECT count() FROM events
WHERE coalesce(country, 'unknown') = 'unknown';

This counts rows where country is NULL or literally the string 'unknown', which a plain country = 'unknown' would miss because NULL = 'unknown' evaluates to NULL (not true).

Combining columns with mixed nullability

SELECT
    order_id,
    coalesce(shipped_at, delivered_at, created_at) AS event_time
FROM orders;

Picks the most-recent meaningful timestamp without nested `if` calls. All three columns must share a compatible type (here, DateTime).

NULL-safe aggregation

SELECT
    region,
    sum(coalesce(revenue, 0)) AS total_revenue
FROM sales
GROUP BY region;

Wrapping revenue in coalesce(..., 0) makes NULL revenue rows contribute zero instead of being ignored (which is the default sum behavior, but explicit is safer when joining tables).

When you only need two arguments

ifNull(x, alt) is the two-argument form of coalesce and is slightly more readable for that case:

SELECT ifNull(nickname, full_name) FROM users;

coalesce and ifNull produce identical results for two arguments; coalesce is the only choice for three or more.

Common Issues and Gotchas

• Type incompatibility raises an exception. coalesce('text', 0) fails because String and Int have no common supertype. Cast explicitly: coalesce(text_col, toString(int_col)).
• coalesce short-circuits. Once a non-NULL value is found, later arguments are not evaluated. This is useful for expensive fallbacks (e.g. a subquery) but means side effects in later arguments do not run.
• Nullable is contagious. The result type is Nullable(T) unless at least one argument is non-nullable. If you need a non-nullable result, end the call with a literal default: coalesce(col, 0) returns Int64, not Nullable(Int64).
• coalesce is not equivalent to assumeNotNull. Use assumeNotNull when you know a column has no NULLs; use coalesce when you want a fallback. Calling assumeNotNull on a NULL value triggers undefined behavior in some versions.

Performance Notes

coalesce is vectorized over the underlying Nullable bitmap, so cost is proportional to the number of arguments, not to the percentage of NULLs. Using coalesce inside WHERE blocks index pruning on the wrapped column, so prefer col IS NULL OR col = 'x' when filtering on indexed columns.

Frequently Asked Questions

Q: What's the difference between coalesce and ifNull in ClickHouse?
A: ifNull(x, alt) is the two-argument special case of coalesce(x, alt). They produce the same result for two arguments. coalesce accepts any number of arguments and is the only choice for three or more fallback values.

Q: Does coalesce evaluate all its arguments?
A: No. coalesce short-circuits: as soon as a non-NULL value is found, the remaining arguments are not evaluated. This matters when later arguments are expensive subqueries or include functions with side effects.

Q: Can I use coalesce in a WHERE clause?
A: Yes, but it can prevent index usage. WHERE coalesce(country, 'us') = 'us' may force a full scan because the optimizer cannot push the predicate to the MergeTree primary index. Prefer WHERE country = 'us' OR country IS NULL when the column is indexed.

Q: What does coalesce return when all arguments are NULL?
A: NULL. If you want a guaranteed non-NULL return, end the argument list with a non-nullable literal: coalesce(a, b, 'fallback') returns 'fallback' whenever both a and b are NULL.

Q: Do all coalesce arguments need to be the same type?
A: They must share a common supertype. ClickHouse will widen Int8 to Int64 or Float32 to Float64 automatically, but String and numeric types cannot be mixed without an explicit toString() or toInt*() cast.

Q: Is coalesce the same as the SQL standard COALESCE?
A: Functionally yes; ClickHouse's coalesce follows the SQL:1999 semantics of returning the first non-NULL value. The case-insensitive COALESCE(...) keyword form also works.

Pulse Catches NULL-Heavy Query Slowdowns

Columns with high NULL rates often hide behind ETL bugs that surface as gradual query slowdowns. Pulse provides AI-powered monitoring for ClickHouse, with automated root-cause analysis that correlates query regressions to data-shape changes - including unexpected spikes in NULL ratios that defeat predicate pushdown.

Related Reading

• ClickHouse if Function: single-condition ternary, often paired with coalesce
• ClickHouse multiIf Function: multi-branch conditional for complex NULL fallback chains
• ClickHouse concat Function: string concatenation that propagates NULL
• ClickHouse case expressions: CASE WHEN ... THEN ... ELSE ... END semantics
• ClickHouse join-use-nulls setting: controls whether OUTER JOIN fills missing values with NULL or defaults
• ClickHouse MergeTree: how NULL handling interacts with the primary index