coalesce
Return the first non-NULL argument — the standard fix for Nullable(T) columns leaking into results.
Signature
coalesce(x1: Nullable(T), x2: T, ...): TReturns
T (the non-nullable supertype of the arguments)
What it does
Evaluates its arguments left to right and returns the first one that is not NULL. The standard way to substitute a default for a Nullable(T) column so the result never contains NULL.
coalesce is the workhorse for handling nullable columns in ClickHouse. It scans its arguments in order and returns the first non-NULL value, falling back to the next argument whenever the previous one is NULL. In analytics this is how you keep NULLs out of GROUP BY keys, chart labels, and API payloads: coalesce(region, 'unknown') collapses missing regions into a single labelled bucket instead of a separate NULL group. In hypequery this matters even more, because the generated types map a Nullable(T) column to T | null, so TypeScript forces you to handle the null case. Wrapping the column in coalesce at query time pushes the default down into SQL, and the result column comes back as a plain T — no null to reason about downstream.
Notes
- All arguments should share a common supertype — coalesce(revenue, 0) is fine, coalesce(name, 0) is not.
- For the common two-argument case, ifNull(x, alt) is the shorter equivalent of coalesce(x, alt).
- assumeNotNull(x) strips the Nullable wrapper without supplying a default — it is unsafe and returns garbage if the value is actually NULL, so prefer coalesce.
- coalesce over a GROUP BY key collapses NULLs into one labelled bucket instead of a separate NULL group.
Example SQL
coalesce in ClickHouse SQL
TypeScript with hypequery
Use coalesce in a typed TypeScript query
hypequery gives you a type-safe query builder for ClickHouse. The generated schema maps your ClickHouse columns to TypeScript types, and raw SQL expressions let you incorporate functions like coalesce when you need them inside a builder query.
Common questions
What developers search for with coalesce
Replace NULL with a default in ClickHouse
coalesce(col, fallback) returns the first non-NULL argument, so coalesce(region, 'unknown') swaps every NULL region for a default. It is the standard fix for Nullable(T) columns leaking NULLs into your results.
coalesce vs ifNull in ClickHouse
ifNull(x, alt) is the two-argument shortcut; coalesce(x1, x2, ...) takes any number of fallbacks and returns the first non-NULL. Use ifNull for a single default, coalesce when you have a chain of fallbacks.
Handle Nullable columns in TypeScript
hypequery generates Nullable(T) columns as T | null, so TypeScript forces you to handle the null. Wrapping the column in selectExpr("coalesce(col, 'default')", 'col') pushes the default into SQL and returns a plain, non-nullable T.
FAQ
Frequently asked questions about coalesce
What does coalesce return in ClickHouse?
It evaluates its arguments left to right and returns the first one that is not NULL. If every argument is NULL, the result is NULL, so end the list with a non-nullable default like coalesce(region, 'unknown').
What is the difference between coalesce and ifNull?
ifNull(x, alt) is the two-argument special case of coalesce. coalesce(x1, x2, ...) accepts any number of arguments and returns the first non-NULL, so reach for it when you have a chain of fallbacks rather than a single default.
How do I remove the null from a Nullable column in hypequery?
hypequery types a Nullable(T) column as T | null. Wrap it in coalesce inside selectExpr — selectExpr("coalesce(region, 'unknown')", 'region') — to push the default into SQL so the result column comes back as a plain, non-nullable T.
Next step
Use coalesce in a type-safe TypeScript query
hypequery generates TypeScript types from your ClickHouse schema. Use coalesce alongside the builder, and reach for raw SQL expressions when the function is not exposed as a dedicated helper.