Conditional Functions
if
Performs conditional branching.
If the condition cond
evaluates to a non-zero value, the function returns the result of the expression then
. If cond
evaluates to zero or NULL
, then the result of the else
expression is returned.
Setting short_circuit_function_evaluation controls whether short-circuit evaluation is used. If enabled, the then
expression is evaluated only on rows where cond
is true
and the else
expression where cond
is false
. For example, with short-circuit evaluation, no division-by-zero exception is thrown when executing the query SELECT if(number = 0, 0, intDiv(42, number)) FROM numbers(10)
.
then
and else
must be of a similar type.
Syntax
Alias: cond ? then : else
(ternary operator)
Arguments
cond
– The evaluated condition. UInt8, Nullable(UInt8) or NULL.then
– The expression returned ifcondition
is true.else
– The expression returned ifcondition
isfalse
or NULL.
Returned values
The result of either the then
and else
expressions, depending on condition cond
.
Example
Result:
multiIf
Allows to write the CASE operator more compactly in the query.
Syntax
Setting short_circuit_function_evaluation controls whether short-circuit evaluation is used. If enabled, the then_i
expression is evaluated only on rows where ((NOT cond_1) AND (NOT cond_2) AND ... AND (NOT cond_{i-1}) AND cond_i)
is true
, cond_i
will be evaluated only on rows where ((NOT cond_1) AND (NOT cond_2) AND ... AND (NOT cond_{i-1}))
is true
. For example, with short-circuit evaluation, no division-by-zero exception is thrown when executing the query SELECT multiIf(number = 2, intDiv(1, number), number = 5) FROM numbers(10)
.
Arguments
The function accepts 2N+1
parameters:
cond_N
— The N-th evaluated condition which controls ifthen_N
is returned.then_N
— The result of the function whencond_N
is true.else
— The result of the function if none of conditions is true.
Returned values
The result of either any of the then_N
or else
expressions, depending on the conditions cond_N
.
Example
Assuming this table:
Using Conditional Results Directly
Conditionals always result to 0
, 1
or NULL
. So you can use conditional results directly like this:
NULL Values in Conditionals
When NULL
values are involved in conditionals, the result will also be NULL
.
So you should construct your queries carefully if the types are Nullable
.
The following example demonstrates this by failing to add equals condition to multiIf
.
greatest
Returns the greatest across a list of values. All of the list members must be of comparable types.
Examples:
The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison.
The type returned is a DateTime64 as the DateTime32 must be promoted to 64 bit for the comparison.
least
Returns the least across a list of values. All of the list members must be of comparable types.
Examples:
The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison.
The type returned is a DateTime64 as the DateTime32 must be promoted to 64 bit for the comparison.
clamp
Constrain the return value between A and B.
Syntax
Arguments
value
– Input value.min
– Limit the lower bound.max
– Limit the upper bound.
Returned values
If the value is less than the minimum value, return the minimum value; if it is greater than the maximum value, return the maximum value; otherwise, return the current value.
Examples:
CASE statement
The CASE expression in ClickHouse provides conditional logic similar to the SQL CASE operator. It evaluates conditions and returns values based on the first matching condition.
ClickHouse supports two forms of CASE:
CASE WHEN ... THEN ... ELSE ... END
This form allows full flexibility and is internally implemented using the multiIf function. Each condition is evaluated independently, and expressions can include non-constant values.
CASE <expr> WHEN <val1> THEN ... WHEN <val2> THEN ... ELSE ... END
This more compact form is optimized for constant value matching and internally uses caseWithExpression()
.
For example, the following is valid:
This form also does not require return expressions to be constants.
Caveats
ClickHouse determines the result type of a CASE expression (or its internal equivalent, such as multiIf
) before evaluating any conditions. This is important when the return expressions differ in type, such as different timezones or numeric types.
- The result type is selected based on the largest compatible type among all branches.
- Once this type is selected, all other branches are implicitly cast to it - even if their logic would never be executed at runtime.
- For types like DateTime64, where the timezone is part of the type signature, this can lead to surprising behavior: the first encountered timezone may be used for all branches, even when other branches specify different timezones.
For example, below all rows return the timestamp in the timezone of the first matched branch i.e. Asia/Kolkata
Here, ClickHouse sees multiple DateTime64(3, <timezone>)
return types. It infers the common type as DateTime64(3, 'Asia/Kolkata'
as the first one it sees, implicitly casting other branches to this type.
This can be addressed by converting to a string to preserve intended timezone formatting: