Conditional functions

**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[​](#greatest "Direct link to 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`).

Note

Use setting `least_greatest_legacy_null_behavior` to change `NULL` behavior

Version [24.12](/docs/whats-new/changelog/2024#a-id2412a-clickhouse-release-2412-2024-12-19) introduced a backwards-incompatible change such that `NULL` values are ignored, while previously it returned `NULL` if one of the arguments was `NULL`.
To retain the previous behavior, set setting `least_greatest_legacy_null_behavior` (default: `false`) to `true`.

**Syntax**

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

**Arguments**

- `x1[, x2, ...]` — One or multiple values to compare. All arguments must be of comparable types. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns the greatest value among the arguments, promoted to the largest compatible type. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Numeric types**

```
SELECT greatest(1, 2, toUInt8(3), 3.) AS result, toTypeName(result) AS type;
-- The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison.
```

```
┌─result─┬─type────┐
│      3 │ Float64 │
└────────┴─────────┘
```

**Arrays**

```
SELECT greatest(['hello'], ['there'], ['world']);
```

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

**DateTime types**

```
SELECT greatest(toDateTime32(now() + toIntervalDay(1)), toDateTime64(now(), 3));
-- The type returned is a DateTime64 as the DateTime32 must be promoted to 64 bit for the comparison.
```

```
┌─greatest(toD⋯(now(), 3))─┐
│  2025-05-28 15:50:53.000 │
└──────────────────────────┘
```

## if[​](#if "Direct link to 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`](/docs/operations/settings/settings#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`](/docs/sql-reference/data-types/int-uint) or [`Nullable(UInt8)`](/docs/sql-reference/data-types/nullable) or [`NULL`](/docs/sql-reference/syntax#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[​](#least "Direct link to 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).

Note

Use setting `least_greatest_legacy_null_behavior` to change `NULL` behavior

Version [24.12](/docs/whats-new/changelog/2024#a-id2412a-clickhouse-release-2412-2024-12-19) introduced a backwards-incompatible change such that `NULL` values are ignored, while previously it returned `NULL` if one of the arguments was `NULL`.
To retain the previous behavior, set setting `least_greatest_legacy_null_behavior` (default: `false`) to `true`.

**Syntax**

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

**Arguments**

- `x1[, x2, ...]` — A single value or multiple values to compare. All arguments must be of comparable types. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns the least value among the arguments, promoted to the largest compatible type. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Numeric types**

```
SELECT least(1, 2, toUInt8(3), 3.) AS result, toTypeName(result) AS type;
-- The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison.
```

```
┌─result─┬─type────┐
│      1 │ Float64 │
└────────┴─────────┘
```

**Arrays**

```
SELECT least(['hello'], ['there'], ['world']);
```

```
┌─least(['hell⋯ ['world'])─┐
│ ['hello']                │
└──────────────────────────┘
```

**DateTime types**

```
SELECT least(toDateTime32(now() + toIntervalDay(1)), toDateTime64(now(), 3));
-- The type returned is a DateTime64 as the DateTime32 must be promoted to 64 bit for the comparison.
```

```
┌─least(toDate⋯(now(), 3))─┐
│  2025-05-27 15:55:20.000 │
└──────────────────────────┘
```

## multiIf[​](#multiIf "Direct link to multiIf")

Introduced in: v1.1.0

Allows writing the [`CASE`](/docs/sql-reference/operators#conditional-expression) 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`](/docs/operations/settings/settings#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)
```

**Aliases**: `caseWithoutExpression`, `caseWithoutExpr`

**Arguments**

- `cond_N` — The N-th evaluated condition which controls if `then_N` is returned. [`UInt8`](/docs/sql-reference/data-types/int-uint) or [`Nullable(UInt8)`](/docs/sql-reference/data-types/nullable) or [`NULL`](/docs/sql-reference/syntax#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      │
└──────┴───────┴─────────────────┘
```