Skip to content
ClickHouse Docs
49.6kGet Started

Conditional Functions

View as MarkdownEdit this pageReport an issueSet up docs MCP

Autogenerated from ClickHouse system tables

clamp

Introduced in: v24.5.0

Restricts a value to be within the specified minimum and maximum bounds.

If the value is less than the minimum, returns the minimum. If the value is greater than the maximum, returns the maximum. Otherwise, returns the value itself.

All arguments must be of comparable types. The result type is the largest compatible type among all arguments.

Syntax

clamp(value, min, max)

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

SELECT clamp(5, 1, 10) AS result;
┌─result─┐
│      5 │
└────────┘

Value below minimum

SELECT clamp(-3, 0, 7) AS result;
┌─result─┐
│      0 │
└────────┘

Value above maximum

SELECT clamp(15, 0, 7) AS result;
┌─result─┐
│      7 │
└────────┘

greatest

Introduced in: v1.1.0

Returns the greatest value among the arguments. NULL arguments are ignored.

  • For arrays, returns the lexicographically greatest array.
  • For DateTime types, the result type is promoted to the largest type (e.g., DateTime64 if mixed with DateTime32).

Syntax

greatest(x1[, x2, ...])

Arguments

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

Returned value

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

Examples

Numeric types

-- The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison.
SELECT greatest(1, 2, toUInt8(3), 3.) AS result, toTypeName(result) AS type;
┌─result─┬─type────┐
│      3 │ Float64 │
└────────┴─────────┘

Arrays

SELECT greatest(['hello'], ['there'], ['world']);
┌─greatest(['hello'], ['there'], ['world'])─┐
│ ['world']                                 │
└───────────────────────────────────────────┘

DateTime types

-- The type returned is a DateTime64 as the DateTime32 must be promoted to 64 bit for the comparison.
SELECT greatest(toDateTime32('2025-01-02 12:00:00'), toDateTime64('2025-01-01 12:00:00.000', 3));
┌─greatest(toDateTime32('2025-01-02 12:00:00'), toDateTime64('2025-01-01 12:00:00.000', 3))─┐
│                                                                   2025-01-02 12:00:00.000 │
└───────────────────────────────────────────────────────────────────────────────────────────┘

if

Introduced in: v1.1.0

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, the result of the else expression is returned.

The 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 following query:

SELECT if(number = 0, 0, intDiv(42, number)) FROM numbers(10)

then and else must be of a similar type.

Syntax

if(cond, then, else)

Arguments

  • cond — The evaluated condition. UInt8 or 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

SELECT if(1, 2 + 2, 2 + 6) AS res;
┌─res─┐
│   4 │
└─────┘

least

Introduced in: v1.1.0

Returns the smallest value among the arguments. NULL arguments are ignored.

  • For arrays, returns the lexicographically least array.
  • For DateTime types, the result type is promoted to the largest type (e.g., DateTime64 if mixed with DateTime32).

Syntax

least(x1[, x2, ...])

Arguments

  • x1[, x2, ...] — A single value or multiple values to compare. All arguments must be of comparable types. Any

Returned value

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

Examples

Numeric types

-- The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison.
SELECT least(1, 2, toUInt8(3), 3.) AS result, toTypeName(result) AS type;
┌─result─┬─type────┐
│      1 │ Float64 │
└────────┴─────────┘

Arrays

SELECT least(['hello'], ['there'], ['world']);
┌─least(['hello'], ['there'], ['world'])─┐
│ ['hello']                              │
└────────────────────────────────────────┘

DateTime types

-- The type returned is a DateTime64 as the DateTime32 must be promoted to 64 bit for the comparison.
SELECT least(toDateTime32('2025-01-02 12:00:00'), toDateTime64('2025-01-01 12:00:00.000', 3));
┌─least(toDateTime32('2025-01-02 12:00:00'), toDateTime64('2025-01-01 12:00:00.000', 3))─┐
│                                                                2025-01-01 12:00:00.000 │
└────────────────────────────────────────────────────────────────────────────────────────┘

multiIf

Introduced in: v1.1.0

Allows writing the CASE operator more compactly in the query. Evaluates each condition in order. For the first condition that is true (non-zero and not NULL), returns the corresponding branch value. If none of the conditions are true, returns the else value.

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 ... AND (NOT cond_{i-1}) AND cond_i) is true.

For example, with short-circuit evaluation, no division-by-zero exception is thrown when executing the following query:

SELECT multiIf(number = 2, intDiv(1, number), number = 5) FROM numbers(10)

All branch and else expressions must have a common supertype. NULL conditions are treated as false.

Syntax

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

Arguments

  • cond_N — The N-th evaluated condition which controls if then_N is returned. UInt8 or 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

CREATE TABLE LEFT_RIGHT (left Nullable(UInt8), right Nullable(UInt8)) ENGINE = Memory;
INSERT INTO LEFT_RIGHT VALUES (NULL, 4), (1, 3), (2, 2), (3, 1), (4, NULL);

SELECT
    left,
    right,
    multiIf(left < right, 'left is smaller', left > right, 'left is greater', left = right, 'Both equal', 'Null value') AS result
FROM LEFT_RIGHT;
┌─left─┬─right─┬─result──────────┐
│ ᴺᵁᴸᴸ │     4 │ Null value      │
│    1 │     3 │ left is smaller │
│    2 │     2 │ Both equal      │
│    3 │     1 │ left is greater │
│    4 │  ᴺᵁᴸᴸ │ Null value      │
└──────┴───────┴─────────────────┘
Navigation