Conditional functions

if

Controls conditional branching. Unlike most systems, ClickHouse always evaluate both expressions then and else.

Syntax

  1. SELECT if(cond, then, else)

If the condition cond evaluates to a non-zero value, returns the result of the expression then, and the result of the expression else, if present, is skipped. If the cond is zero or NULL, then the result of the then expression is skipped and the result of the else expression, if present, is returned.

Parameters

  • cond – The condition for evaluation that can be zero or not. The type is UInt8, Nullable(UInt8) or NULL.
  • then - The expression to return if condition is met.
  • else - The expression to return if condition is not met.

Returned values

The function executes then and else expressions and returns its result, depending on whether the condition cond ended up being zero or not.

Example

Query:

  1. SELECT if(1, plus(2, 2), plus(2, 6))

Result:

  1. ┌─plus(2, 2)─┐
  2. │          4 │
  3. └────────────┘

Query:

  1. SELECT if(0, plus(2, 2), plus(2, 6))

Result:

  1. ┌─plus(2, 6)─┐
  2. │          8 │
  3. └────────────┘
  • then and else must have the lowest common type.

Example:

Take this LEFT_RIGHT table:

  1. SELECT *
  2. FROM LEFT_RIGHT
  4. ┌─left─┬─right─┐
  5. │ ᴺᵁᴸᴸ │     4 │
  6. │    1 │     3 │
  7. │    2 │     2 │
  8. │    3 │     1 │
  9. │    4 │  ᴺᵁᴸᴸ │
  10. └──────┴───────┘

The following query compares left and right values:

  1. SELECT
  2.     left,
  3.     right,
  4.     if(left < right, 'left is smaller than right', 'right is greater or equal than left') AS is_smaller
  5. FROM LEFT_RIGHT
  6. WHERE isNotNull(left) AND isNotNull(right)
  8. ┌─left─┬─right─┬─is_smaller──────────────────────────┐
  9. │    1 │     3 │ left is smaller than right          │
  10. │    2 │     2 │ right is greater or equal than left │
  11. │    3 │     1 │ right is greater or equal than left │
  12. └──────┴───────┴─────────────────────────────────────┘

Note: NULL values are not used in this example, check NULL values in conditionals section.

Ternary operator

It works same as if function.

Syntax: cond ? then : else

Returns then if the cond is truthy(greater than zero), otherwise returns else.

  • cond must be of type of UInt8, and then and else must have the lowest common type.

  • then and else can be NULL

multiIf

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

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

Parameters:

  • cond_N — The condition for the function to return then_N.
  • then_N — The result of the function when executed.
  • else — The result of the function if none of the conditions is met.

The function accepts 2N+1 parameters.

Returned values

The function returns one of the values then_N or else, depending on the conditions cond_N.

Example

Again using LEFT_RIGHT table.

  1. SELECT
  2.     left,
  3.     right,
  4.     multiIf(left < right, 'left is smaller', left > right, 'left is greater', left = right, 'Both equal', 'Null value') AS result
  5. FROM LEFT_RIGHT
  7. ┌─left─┬─right─┬─result──────────┐
  8. │ ᴺᵁᴸᴸ │     4 │ Null value      │
  9. │    1 │     3 │ left is smaller │
  10. │    2 │     2 │ Both equal      │
  11. │    3 │     1 │ left is greater │
  12. │    4 │  ᴺᵁᴸᴸ │ Null value      │
  13. └──────┴───────┴─────────────────┘

Using conditional results directly

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

  1. SELECT left < right AS is_small
  2. FROM LEFT_RIGHT
  4. ┌─is_small─┐
  5. │     ᴺᵁᴸᴸ │
  6. │        1 │
  7. │        0 │
  8. │        0 │
  9. │     ᴺᵁᴸᴸ │
  10. └──────────┘

NULL values in conditionals

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

  1. SELECT
  2.     NULL < 1,
  3.     2 < NULL,
  4.     NULL < NULL,
  5.     NULL = NULL
  7. ┌─less(NULL, 1)─┬─less(2, NULL)─┬─less(NULL, NULL)─┬─equals(NULL, NULL)─┐
  8. │ ᴺᵁᴸᴸ          │ ᴺᵁᴸᴸ          │ ᴺᵁᴸᴸ             │ ᴺᵁᴸᴸ               │
  9. └───────────────┴───────────────┴──────────────────┴────────────────────┘

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.

  1. SELECT
  2.     left,
  3.     right,
  4.     multiIf(left < right, 'left is smaller', left > right, 'right is smaller', 'Both equal') AS faulty_result
  5. FROM LEFT_RIGHT
  7. ┌─left─┬─right─┬─faulty_result────┐
  8. │ ᴺᵁᴸᴸ │     4 │ Both equal       │
  9. │    1 │     3 │ left is smaller  │
  10. │    2 │     2 │ Both equal       │
  11. │    3 │     1 │ right is smaller │
  12. │    4 │  ᴺᵁᴸᴸ │ Both equal       │
  13. └──────┴───────┴──────────────────┘