Skip to main content
Skip to main content
Edit this page

Conditional Functions

Overview​

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.

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:

  1. 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.
  1. 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:

clamp​

Introduced in: v24.5

Syntax

Arguments

  • value — The value to clamp.
  • min — The minimum bound.
  • max — The maximum bound.

Returned value

Returns the value, restricted to the [min, max] range.

Examples

Basic usage

Value below minimum

Value above maximum

greatest​

Introduced in: v1.1

Syntax

Arguments

  • x1[, x2, ..., xN] — One or multiple values to compare. All arguments must be of comparable types.

Returned value

The greatest value among the arguments, promoted to the largest compatible type.

Examples

Numeric types

Arrays

DateTime types

if​

Introduced in: v1.1

Syntax

Arguments

  • cond — The evaluated condition. UInt8, Nullable(UInt8) or NULL.
  • then — The expression returned if cond is true.
  • else — The expression returned if cond is false or NULL.

Returned value

The result of either the then or else expressions, depending on condition cond.

Examples

Example usage

least​

Introduced in: v1.1

Syntax

Arguments

  • x1[, x2, ..., xN] — One or multiple values to compare. All arguments must be of comparable types.

Returned value

The least value among the arguments, promoted to the largest compatible type.

Examples

Numeric types

Arrays

DateTime types

multiIf​

Introduced in: v1.1

Syntax

Arguments

  • cond_N — The N-th evaluated condition which controls if then_N is returned. Each must be UInt8, Nullable(UInt8), or NULL.
  • then_N — The result of the function when cond_N is true.
  • else — The result of the function if none of the conditions is true.

Returned value

Returns the result of then_N for matching cond_N, otherwise returns the else condition.

Examples

Example usage