> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8c05c8a2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for Conditional Functions

# Conditional Functions

<h2 id="overview">
  Overview
</h2>

<h3 id="using-conditional-results-directly">
  Using Conditional Results Directly
</h3>

Conditionals always result to `0`, `1` or `NULL`. So you can use conditional results directly like this:

```sql theme={null}
SELECT left < right AS is_small
FROM LEFT_RIGHT

┌─is_small─┐
│     ᴺᵁᴸᴸ │
│        1 │
│        0 │
│        0 │
│     ᴺᵁᴸᴸ │
└──────────┘
```

<h3 id="null-values-in-conditionals">
  NULL Values in Conditionals
</h3>

When `NULL` values are involved in conditionals, the result will also be `NULL`.

```sql theme={null}
SELECT
    NULL < 1,
    2 < NULL,
    NULL < NULL,
    NULL = NULL

┌─less(NULL, 1)─┬─less(2, NULL)─┬─less(NULL, NULL)─┬─equals(NULL, 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`.

```sql theme={null}
SELECT
    left,
    right,
    multiIf(left < right, 'left is smaller', left > right, 'right is smaller', 'Both equal') AS faulty_result
FROM LEFT_RIGHT

┌─left─┬─right─┬─faulty_result────┐
│ ᴺᵁᴸᴸ │     4 │ Both equal       │
│    1 │     3 │ left is smaller  │
│    2 │     2 │ Both equal       │
│    3 │     1 │ right is smaller │
│    4 │  ᴺᵁᴸᴸ │ Both equal       │
└──────┴───────┴──────────────────┘
```

<h3 id="case-statement">
  CASE statement
</h3>

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`
   <br />
   This form allows full flexibility and is internally implemented using the [multiIf](/reference/functions/regular-functions/conditional-functions#multiIf) function. Each condition is evaluated independently, and expressions can include non-constant values.

```sql theme={null}
SELECT
    number,
    CASE
        WHEN number % 2 = 0 THEN number + 1
        WHEN number % 2 = 1 THEN number * 10
        ELSE number
    END AS result
FROM system.numbers
WHERE number < 5;

-- is translated to
SELECT
    number,
    multiIf((number % 2) = 0, number + 1, (number % 2) = 1, number * 10, number) AS result
FROM system.numbers
WHERE number < 5

┌─number─┬─result─┐
│      0 │      1 │
│      1 │     10 │
│      2 │      3 │
│      3 │     30 │
│      4 │      5 │
└────────┴────────┘

5 rows in set. Elapsed: 0.002 sec.
```

2. `CASE <expr> WHEN <val1> THEN ... WHEN <val2> THEN ... ELSE ... END`
   <br />
   This more compact form is optimized for constant value matching and internally uses `caseWithExpression()`.

For example, the following is valid:

```sql theme={null}
SELECT
    number,
    CASE number
        WHEN 0 THEN 100
        WHEN 1 THEN 200
        ELSE 0
    END AS result
FROM system.numbers
WHERE number < 3;

-- is translated to

SELECT
    number,
    caseWithExpression(number, 0, 100, 1, 200, 0) AS result
FROM system.numbers
WHERE number < 3

┌─number─┬─result─┐
│      0 │    100 │
│      1 │    200 │
│      2 │      0 │
└────────┴────────┘

3 rows in set. Elapsed: 0.002 sec.
```

This form also does not require return expressions to be constants.

```sql theme={null}
SELECT
    number,
    CASE number
        WHEN 0 THEN number + 1
        WHEN 1 THEN number * 10
        ELSE number
    END
FROM system.numbers
WHERE number < 3;

-- is translated to

SELECT
    number,
    caseWithExpression(number, 0, number + 1, 1, number * 10, number)
FROM system.numbers
WHERE number < 3

┌─number─┬─caseWithExpr⋯0), number)─┐
│      0 │                        1 │
│      1 │                       10 │
│      2 │                        2 │
└────────┴──────────────────────────┘

3 rows in set. Elapsed: 0.001 sec.
```

<h4 id="caveats">
  Caveats
</h4>

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`

```sql theme={null}
SELECT
    number,
    CASE
        WHEN number = 0 THEN fromUnixTimestamp64Milli(0, 'Asia/Kolkata')
        WHEN number = 1 THEN fromUnixTimestamp64Milli(0, 'America/Los_Angeles')
        ELSE fromUnixTimestamp64Milli(0, 'UTC')
    END AS tz
FROM system.numbers
WHERE number < 3;

-- is translated to

SELECT
    number,
    multiIf(number = 0, fromUnixTimestamp64Milli(0, 'Asia/Kolkata'), number = 1, fromUnixTimestamp64Milli(0, 'America/Los_Angeles'), fromUnixTimestamp64Milli(0, 'UTC')) AS tz
FROM system.numbers
WHERE number < 3

┌─number─┬──────────────────────tz─┐
│      0 │ 1970-01-01 05:30:00.000 │
│      1 │ 1970-01-01 05:30:00.000 │
│      2 │ 1970-01-01 05:30:00.000 │
└────────┴─────────────────────────┘

3 rows in set. Elapsed: 0.011 sec.
```

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:

```sql theme={null}
SELECT
    number,
    multiIf(
        number = 0, formatDateTime(fromUnixTimestamp64Milli(0), '%F %T', 'Asia/Kolkata'),
        number = 1, formatDateTime(fromUnixTimestamp64Milli(0), '%F %T', 'America/Los_Angeles'),
        formatDateTime(fromUnixTimestamp64Milli(0), '%F %T', 'UTC')
    ) AS tz
FROM system.numbers
WHERE number < 3;

-- is translated to

SELECT
    number,
    multiIf(number = 0, formatDateTime(fromUnixTimestamp64Milli(0), '%F %T', 'Asia/Kolkata'), number = 1, formatDateTime(fromUnixTimestamp64Milli(0), '%F %T', 'America/Los_Angeles'), formatDateTime(fromUnixTimestamp64Milli(0), '%F %T', 'UTC')) AS tz
FROM system.numbers
WHERE number < 3

┌─number─┬─tz──────────────────┐
│      0 │ 1970-01-01 05:30:00 │
│      1 │ 1969-12-31 16:00:00 │
│      2 │ 1970-01-01 00:00:00 │
└────────┴─────────────────────┘

3 rows in set. Elapsed: 0.002 sec.
```

{/*AUTOGENERATED_START*/}

<h2 id="clamp">
  clamp
</h2>

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**

```sql theme={null}
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**

```sql title=Query theme={null}
SELECT clamp(5, 1, 10) AS result;
```

```response title=Response theme={null}
┌─result─┐
│      5 │
└────────┘
```

**Value below minimum**

```sql title=Query theme={null}
SELECT clamp(-3, 0, 7) AS result;
```

```response title=Response theme={null}
┌─result─┐
│      0 │
└────────┘
```

**Value above maximum**

```sql title=Query theme={null}
SELECT clamp(15, 0, 7) AS result;
```

```response title=Response theme={null}
┌─result─┐
│      7 │
└────────┘
```

<h2 id="greatest">
  greatest
</h2>

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`).

<Info>
  **Use setting `least_greatest_legacy_null_behavior` to change `NULL` behavior**

  Version [24.12](/resources/changelogs/oss/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`.
</Info>

**Syntax**

```sql theme={null}
greatest(x1[, x2, ...])
```

**Arguments**

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

**Returned value**

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

**Examples**

**Numeric types**

```sql title=Query theme={null}
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.
```

```response title=Response theme={null}
┌─result─┬─type────┐
│      3 │ Float64 │
└────────┴─────────┘
```

**Arrays**

```sql title=Query theme={null}
SELECT greatest(['hello'], ['there'], ['world']);
```

```response title=Response theme={null}
┌─greatest(['hello'], ['there'], ['world'])─┐
│ ['world']                                 │
└───────────────────────────────────────────┘
```

**DateTime types**

```sql title=Query theme={null}
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.
```

```response title=Response theme={null}
┌─greatest(toD⋯(now(), 3))─┐
│  2025-05-28 15:50:53.000 │
└──────────────────────────┘
```

<h2 id="if">
  if
</h2>

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`](/reference/settings/session-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:

```sql theme={null}
SELECT if(number = 0, 0, intDiv(42, number)) FROM numbers(10)
```

`then` and `else` must be of a similar type.

**Syntax**

```sql theme={null}
if(cond, then, else)
```

**Arguments**

* `cond` — The evaluated condition. [`UInt8`](/reference/data-types/int-uint) or [`Nullable(UInt8)`](/reference/data-types/nullable) or [`NULL`](/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**

```sql title=Query theme={null}
SELECT if(1, 2 + 2, 2 + 6) AS res;
```

```response title=Response theme={null}
┌─res─┐
│   4 │
└─────┘
```

<h2 id="least">
  least
</h2>

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).

<Info>
  **Use setting `least_greatest_legacy_null_behavior` to change `NULL` behavior**

  Version [24.12](/resources/changelogs/oss/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`.
</Info>

**Syntax**

```sql theme={null}
least(x1[, x2, ...])
```

**Arguments**

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

**Returned value**

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

**Examples**

**Numeric types**

```sql title=Query theme={null}
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.
```

```response title=Response theme={null}
┌─result─┬─type────┐
│      1 │ Float64 │
└────────┴─────────┘
```

**Arrays**

```sql title=Query theme={null}
SELECT least(['hello'], ['there'], ['world']);
```

```response title=Response theme={null}
┌─least(['hell⋯ ['world'])─┐
│ ['hello']                │
└──────────────────────────┘
```

**DateTime types**

```sql title=Query theme={null}
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.
```

```response title=Response theme={null}
┌─least(toDate⋯(now(), 3))─┐
│  2025-05-27 15:55:20.000 │
└──────────────────────────┘
```

<h2 id="multiIf">
  multiIf
</h2>

Introduced in: v1.1.0

Allows writing the [`CASE`](/reference/operators/index#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`](/reference/settings/session-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:

```sql theme={null}
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**

```sql theme={null}
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`](/reference/data-types/int-uint) or [`Nullable(UInt8)`](/reference/data-types/nullable) or [`NULL`](/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**

```sql title=Query theme={null}
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;
```

```response title=Response theme={null}
┌─left─┬─right─┬─result──────────┐
│ ᴺᵁᴸᴸ │     4 │ Null value      │
│    1 │     3 │ left is smaller │
│    2 │     2 │ Both equal      │
│    3 │     1 │ left is greater │
│    4 │  ᴺᵁᴸᴸ │ Null value      │
└──────┴───────┴─────────────────┘
```