ClickHouse multiIf Function: Multi-Branch Conditional with Examples

The multiIf function in ClickHouse evaluates pairs of (condition, result) arguments left to right and returns the result of the first condition that is true. A final else argument is required and is returned when no condition matches. multiIf is ClickHouse's native form of CASE WHEN: the SQL CASE statement compiles directly into a multiIf call. Return type is the common supertype of all branches; NULL conditions are treated as false.

Syntax

multiIf(cond_1, then_1, cond_2, then_2[, ...], else)

See the official ClickHouse conditional functions reference.

Parameters

Return type: the common supertype of all then_N and else arguments.

Examples

Bucketing by range

SELECT
    user_id,
    multiIf(
        age < 13, 'child',
        age < 20, 'teen',
        age < 65, 'adult',
        'senior'
    ) AS bracket
FROM users;

Branches are evaluated top-down, so order matters: the first matching condition wins. Equivalent CASE WHEN ... END compiles to the same multiIf call.

Normalizing HTTP status codes

SELECT
    multiIf(
        status_code BETWEEN 200 AND 299, 'success',
        status_code BETWEEN 300 AND 399, 'redirect',
        status_code BETWEEN 400 AND 499, 'client_error',
        status_code BETWEEN 500 AND 599, 'server_error',
        'unknown'
    ) AS status_class,
    count()
FROM requests
GROUP BY status_class;

Inside an aggregate (conditional bucketing)

SELECT
    sum(multiIf(category = 'electronics', price, 0)) AS electronics_revenue,
    sum(multiIf(category = 'books',       price, 0)) AS books_revenue
FROM sales;

For simpler per-bucket aggregates, prefer `sumIf` and the other `-If` combinators; multiIf is better when one expression needs to produce multiple distinct values.

NULL handling

SELECT multiIf(x > 0, 'pos', x < 0, 'neg', 'zero_or_null') AS sign
FROM numbers_with_nulls;

If x is NULL, both x > 0 and x < 0 evaluate to NULL, which multiIf treats as false. The else branch ('zero_or_null') is returned. To distinguish NULL from zero explicitly, branch on x IS NULL first.

Combining branches with different types

SELECT multiIf(flag = 1, toFloat64(score), flag = 2, 0.0, NaN) AS metric;

All branches must share a common supertype. Mixing Int and Float widens to Float64. Mixing String and Int raises an exception; cast explicitly.

Common Issues and Gotchas

• The else argument is required. Unlike SQL CASE, omitting the default raises a parser error. If you want NULL when nothing matches, pass NULL explicitly: multiIf(cond, val, NULL).
• Branches must share a common supertype. Mixing String and numeric branches fails. Wrap numeric branches with toString() or coerce strings to a numeric type.
• NULL conditions are treated as false. A predicate that evaluates to NULL (because of a Nullable operand) does not match. Guard with IS NULL / IS NOT NULL when operating on nullable columns.
• All branches are type-checked, not all are evaluated. multiIf short-circuits at runtime: branches after the first true condition are not executed. But all branches are still parsed and type-checked at planning time, so a syntax error in an unreachable branch still fails the query.
• CASE WHEN is just syntactic sugar. CASE WHEN a THEN x WHEN b THEN y ELSE z END compiles to multiIf(a, x, b, y, z). They produce identical query plans.

Performance Notes

multiIf is fully vectorized and compiles into a tight loop with branch evaluation per row. The cost scales linearly with the number of conditions: ten branches cost roughly five times as much per row as two. For very wide branching (50+ buckets), a dictionary lookup or a join against a static mapping table is faster. Each condition is independent of the others - the optimizer cannot reorder them - so put cheap, highly-selective conditions first.

Frequently Asked Questions

Q: How does multiIf differ from a CASE WHEN statement in ClickHouse?
A: They produce identical results. CASE WHEN ... END is parsed into a multiIf call by ClickHouse, so the query plan and runtime cost are the same. multiIf is the canonical form; CASE WHEN exists for SQL compatibility.

Q: Does multiIf evaluate all its branches?
A: At runtime, no: multiIf short-circuits after the first true condition. But at parse time, every branch is type-checked. A type error in an unreachable branch still fails the query.

Q: How does multiIf handle NULL conditions?
A: NULL conditions are treated as false - the branch is skipped and evaluation continues. To branch on NULL explicitly, write multiIf(x IS NULL, 'unknown', x > 0, 'pos', 'neg').

Q: Can I use multiIf inside an aggregate function?
A: Yes. sum(multiIf(cond, value, 0)) is a common idiom for conditional sums. For a single condition, `sumIf(value, cond)` is faster and clearer.

Q: What's the maximum number of branches multiIf supports?
A: No documented hard limit, but parsing and per-row cost grow linearly with branch count. For more than ~30 branches, a dictionary lookup or join is typically faster and easier to maintain.

Q: Do all multiIf branches need to return the same type?
A: They must share a common supertype. ClickHouse widens Int* to Int64 and Float32 to Float64 automatically. Mixing String and numeric types fails; cast one side explicitly.

Pulse Tracks Conditional Aggregates at Scale

Wide multiIf and CASE WHEN expressions are common in observability and business-metrics queries. When dashboards using them slow down, the root cause is usually cluster-side: memory pressure, hot shards, slow merges. Pulse provides AI-powered monitoring for ClickHouse with automated root-cause analysis, linking query regressions to the cluster condition that caused them.

Related Reading

• ClickHouse if Function: single-condition ternary, the base case of multiIf
• ClickHouse case expressions: CASE WHEN syntax that compiles to multiIf
• ClickHouse coalesce Function: simpler choice when you only need NULL fallback
• ClickHouse countIf Function: conditional row counting via the -If combinator
• ClickHouse sumIf Function: conditional sum
• ClickHouse MergeTree: how branch conditions interact with primary-key pruning
• ClickHouse dictionary: faster alternative to large multiIf chains for static mappings