Conditional functions in ClickHouse allow you to implement if-then-else logic directly in your queries.

Key conditional functions include:

  • if: Basic conditional branching
  • multiIf: Multiple condition checking
  • case: SQL-style conditional expressions

​
ClickHouse function reference

​
if

Performs conditional branching.

Syntax:

if(cond, then, else)

Alternatively, you can use the ternary operator:

cond ? then : else

Arguments:

  • cond (UInt8, Nullable(UInt8) or NULL): The condition to evaluate.
  • then (any): The expression to return if the condition is true.
  • else (any): The expression to return if the condition is false or NULL.

Returns:

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

Example:

SELECT
  taco_order_id,
  num_tacos,
  if(num_tacos > 5, 'Large Order', 'Small Order') AS order_size
FROM
  taco_orders;

Result:

| taco_order_id | num_tacos | order_size  |
|---------------|-----------|-------------|
| 1             | 3         | Small Order |
| 2             | 7         | Large Order |
| 3             | 4         | Small Order |

In this example, if categorizes orders as ‘Large Order’ if they have more than 5 tacos, and ‘Small Order’ otherwise.

The then and else expressions must be of similar types.

​
multiIf

Allows you to write the CASE operator more compactly in a query.

Syntax:

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

Arguments:

  • cond_N (Boolean): The N-th condition to evaluate.
  • then_N (any): The result to return when cond_N is true.
  • else (any): The result to return if none of the conditions are true.

Returns:

The result of the first true condition’s corresponding then expression, or the else expression if no conditions are true.

Example:

SELECT
  taco_order_id,
  num_tacos,
  multiIf(
    num_tacos < 3, 'Small Order',
    num_tacos < 6, 'Medium Order',
    num_tacos < 9, 'Large Order',
    'Extra Large Order'
  ) AS order_size
FROM
  taco_orders;

Result:

| taco_order_id | num_tacos | order_size        |
|---------------|-----------|-------------------|
| 1             | 2         | Small Order       |
| 2             | 5         | Medium Order      |
| 3             | 7         | Large Order       |
| 4             | 10        | Extra Large Order |

In this example, multiIf categorizes taco orders into different sizes based on the number of tacos ordered.

​
greatest

Returns the greatest value across a list of values.

Syntax:

greatest(value1, value2[, ...])

Arguments:

  • value1, value2, … (any comparable types): The values to compare.

Returns:

The greatest value among the input arguments.

Example:

SELECT
  taco_order_id,
  beef_tacos,
  chicken_tacos,
  veggie_tacos,
  greatest(beef_tacos, chicken_tacos, veggie_tacos) AS most_popular_taco
FROM
  taco_orders;

Result:

| taco_order_id | beef_tacos | chicken_tacos | veggie_tacos | most_popular_taco |
|---------------|------------|---------------|--------------|-------------------|
| 1             | 3          | 5             | 2            | 5                 |
| 2             | 4          | 4             | 6            | 6                 |
| 3             | 7          | 3             | 1            | 7                 |

In this example, greatest determines the most popular type of taco for each order based on the quantity ordered.

All input values must be of comparable types. The return type may be promoted to accommodate the comparison (e.g., mixing integer and float types will return a float).

​
least

Returns the smallest value across a list of values.

Syntax:

least(value1, value2[, ...])

Arguments:

  • value1, value2, … (any comparable types): The values to compare.

Returns:

The smallest value among the input arguments.

Example:

SELECT
  taco_order_id,
  beef_tacos,
  chicken_tacos,
  veggie_tacos,
  least(beef_tacos, chicken_tacos, veggie_tacos) AS least_popular_taco
FROM
  taco_orders;

Result:

| taco_order_id | beef_tacos | chicken_tacos | veggie_tacos | least_popular_taco  |
|---------------|------------|---------------|--------------|---------------------|
| 1             | 3          | 5             | 2            | 2                   |
| 2             | 4          | 4             | 6            | 4                   |
| 3             | 7          | 3             | 1            | 1                   |

In this example, least determines the least popular type of taco for each order based on the quantity ordered.

All input values must be of comparable types. The return type may be promoted to accommodate the comparison (e.g., mixing integer and float types will return a float).

​
multiIf

Allows you to write the CASE operator more compactly in a query.

Syntax:

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

Arguments:

  • cond_N (UInt8): The N-th evaluated condition which controls if then_N is returned.
  • then_N (Any): The result of the function when cond_N is true.
  • else (Any): The result of the function if none of the conditions are true.

Returns:

The result of either any of the then_N or else expressions, depending on the conditions cond_N.

Example:

SELECT
  taco_order_id,
  num_tacos,
  multiIf(
    num_tacos < 3, 'Small order',
    num_tacos < 6, 'Medium order',
    num_tacos < 10, 'Large order',
    'Extra large order'
  ) AS order_size
FROM
  taco_orders;

Result:

| taco_order_id | num_tacos | order_size        |
|---------------|-----------|-------------------|
| 1             | 2         | Small order       |
| 2             | 4         | Medium order      |
| 3             | 7         | Large order       |
| 4             | 12        | Extra large order |

In this example, multiIf categorizes taco orders based on the number of tacos ordered.

​
greatest

Returns the greatest value across a list of values. All of the list members must be of comparable types.

Syntax:

greatest(value1, value2, ...)

Arguments:

  • value1, value2, … (any): The values to compare.

Returns:

The greatest value among the input arguments. The return type is determined by the types of the input arguments.

Example:

SELECT
  greatest(3, 5, 1) AS max_number,
  greatest('beef', 'chicken', 'fish') AS last_alphabetically,
  greatest(toDate('2023-05-01'), toDate('2023-06-15')) AS latest_date
FROM
  taco_orders;

Result:

| max_number | last_alphabetically | latest_date |
|------------|---------------------|-------------|
| 5          | fish                | 2023-06-15  |

In this example:

  • max_number returns 5, the greatest numeric value.
  • last_alphabetically returns ‘fish’, which comes last alphabetically.
  • latest_date returns the most recent date.

When comparing different numeric types, type promotion may occur. For example, comparing an Int32 with a Float32 will result in a Float32 return type.

​
least

Returns the smallest value from a list of arguments.

Syntax:

least(a, b, ...)

Arguments:

  • a, b, … (any): The values to compare.

Returns:

The smallest value among the arguments. The return type is determined by the types of the input arguments.

Example:

SELECT
  taco_order_id,
  num_tacos,
  num_sides,
  least(num_tacos, num_sides) AS min_items
FROM
  taco_orders;

Result:

| taco_order_id | num_tacos | num_sides | min_items |
|---------------|-----------|-----------|-----------|
| 1             | 5         | 3         | 3         |
| 2             | 2         | 4         | 2         |
| 3             | 6         | 6         | 6         |

In this example, least returns the smaller value between num_tacos and num_sides for each order.

All arguments must be of comparable types. If the types are different, type conversion may occur.

​
clamp

Constrains a value between a minimum and maximum.

Syntax:

clamp(value, min, max)

Arguments:

  • value (numeric): The input value to constrain.
  • min (numeric): The lower bound.
  • max (numeric): The upper bound.

Returns:

  • If value is less than min, returns min.
  • If value is greater than max, returns max.
  • Otherwise, returns value.

Example:

SELECT
  taco_order_id,
  num_tacos,
  clamp(num_tacos, 1, 5) AS clamped_tacos
FROM
  taco_orders;

Result:

| taco_order_id | num_tacos | clamped_tacos |
|---------------|-----------|---------------|
| 1             | 3         | 3             |
| 2             | 7         | 5             |
| 3             | 0         | 1             |

In this example, clamp ensures that the number of tacos per order is between 1 and 5. Orders with more than 5 tacos are capped at 5, and orders with less than 1 taco are increased to 1.

Was this page helpful?

ComparisonDates and Times