--- title: Conditional Functions reference meta: description: Functions for conditional logic. headingMaxLevels: 2 --- # Conditional functions The following functions are used to perform conditional logic. ## if This function evaluates a condition and returns one of two expressions based on whether the condition is true or false. It provides a way to implement basic conditional logic within a query. ### Syntax ```sql if(cond, then, else) ``` Alias: `cond ? then : else` (ternary operator) ### Arguments - `cond`: The condition to evaluate. It should resolve to a `UInt8`, `Nullable(UInt8)`, or `NULL`. A non-zero value is considered true, while zero or `NULL` is considered false. - `then`: The expression to return if `cond` is true. - `else`: The expression to return if `cond` is false or `NULL`. ### Returns The result of either the `then` or `else` expression, depending on the evaluation of `cond`. The return type will be a common supertype of `then` and `else` expressions. ### Example ```sql SELECT if(1, plus(2, 2), plus(2, 6)) ``` Result: ```text ┌─plus(2, 2)─┐ │ 4 │ └────────────┘ ``` ## multiIf This function allows for multiple conditional checks, similar to a `CASE` statement, returning the result of the first condition that evaluates to true. If no conditions are met, it returns a default `else` value. ### Syntax ```sql multiIf(cond_1, then_1, cond_2, then_2, ..., else) ``` ### Arguments The function accepts `2N+1` parameters: - `cond_N`: The N-th condition to evaluate. - `then_N`: The expression to return if `cond_N` is true and all preceding conditions were false. - `else`: The expression to return if none of the `cond_N` conditions are true. ### Returns The result of the `then_N` expression corresponding to the first true `cond_N`, or the `else` expression if no conditions are met. The return type will be a common supertype of all `then_N` and `else` expressions. ### Example Assuming this table: ```text ┌─left─┬─right─┐ │ ᴺᵁᴸᴸ │ 4 │ │ 1 │ 3 │ │ 2 │ 2 │ │ 3 │ 1 │ │ 4 │ ᴺᵁᴸᴸ │ └──────┴───────┘ ``` ```sql 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 ``` Result: ```text ┌─left─┬─right─┬─result──────────┐ │ ᴺᵁᴸᴸ │ 4 │ Null value │ │ 1 │ 3 │ left is smaller │ │ 2 │ 2 │ Both equal │ │ 3 │ 1 │ left is greater │ │ 4 │ ᴺᵁᴸᴸ │ Null value │ └──────┴───────┴─────────────────┘ ``` ## Using Conditional Results Directly Conditional expressions in Tinybird, such as comparisons (`<`, `>`, `=`), always resolve to `0` (false), `1` (true), or `NULL`. This allows you to use the direct result of a conditional expression where a boolean or integer value is expected. ### Example ```sql SELECT left < right AS is_small FROM LEFT_RIGHT ``` Result: ```text ┌─is_small─┐ │ ᴺᵁᴸᴸ │ │ 1 │ │ 0 │ │ 0 │ │ ᴺᵁᴸᴸ │ └──────────┘ ``` ## NULL Values in Conditionals When `NULL` values are involved in conditional comparisons, the result of the comparison will typically also be `NULL`. This behavior is important to consider when constructing queries, especially with `Nullable` data types, as `NULL` does not equal `NULL` and `NULL` is not less than or greater than any value. ### Example ```sql SELECT NULL < 1, 2 < NULL, NULL < NULL, NULL = NULL ``` Result: ```text ┌─less(NULL, 1)─┬─less(2, NULL)─┬─less(NULL, NULL)─┬─equals(NULL, NULL)─┐ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ └───────────────┴───────────────┴──────────────────┴────────────────────┘ ``` The following example demonstrates how `NULL` values can affect `multiIf` if not handled explicitly: ```sql SELECT left, right, multiIf(left < right, 'left is smaller', left > right, 'right is smaller', 'Both equal') AS faulty_result FROM LEFT_RIGHT ``` Result: ```text ┌─left─┬─right─┬─faulty_result────┐ │ ᴺᵁᴸᴸ │ 4 │ Both equal │ │ 1 │ 3 │ left is smaller │ │ 2 │ 2 │ Both equal │ │ 3 │ 1 │ right is smaller │ │ 4 │ ᴺᵁᴸᴸ │ Both equal │ └──────┴───────┴──────────────────┘ ``` ## greatest This function returns the largest value from a list of input expressions. All provided values must be of comparable data types. ### Syntax ```sql greatest(value1, value2, ...) ``` ### Arguments - `value1, value2, ...`: Multiple expressions of comparable types. ### Returns The greatest value among the inputs. The return type will be the common supertype to which all input values are promoted for comparison. ### Example ```sql SELECT greatest(1, 2, toUInt8(3), 3.) result, toTypeName(result) type ``` Result: ```result ┌─result─┬─type────┐ │ 3 │ Float64 │ └────────┴─────────┘ ``` {% callout type="info" %} The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison. {% /callout %} ```sql SELECT greatest(['hello'], ['there'], ['world']) ``` Result: ```result ┌─greatest(['hello'], ['there'], ['world'])─┐ │ ['world'] │ └───────────────────────────────────────────┘ ``` ```sql SELECT greatest(toDateTime32(now() + toIntervalDay(1)), toDateTime64(now(), 3)) ``` Result: ```result ┌─greatest(toDateTime32(plus(now(), toIntervalDay(1))), toDateTime64(now(), 3))─┐ │ 2023-05-12 01:16:59.000 │ └──---──────────────────────────────────────────────────────────────────────────┘ ``` {% callout type="info" %} The type returned is a DateTime64 as the DateTime32 must be promoted to 64 bit for the comparison. {% /callout %} ## least This function returns the smallest value from a list of input expressions. All provided values must be of comparable data types. ### Syntax ```sql least(value1, value2, ...) ``` ### Arguments - `value1, value2, ...`: Multiple expressions of comparable types. ### Returns The least value among the inputs. The return type will be the common supertype to which all input values are promoted for comparison. ### Example ```sql SELECT least(1, 2, toUInt8(3), 3.) result, toTypeName(result) type ``` Result: ```result ┌─result─┬─type────┐ │ 1 │ Float64 │ └────────┴─────────┘ ``` {% callout type="info" %} The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison. {% /callout %} ```sql SELECT least(['hello'], ['there'], ['world']) ``` Result: ```result ┌─least(['hello'], ['there'], ['world'])─┐ │ ['hello'] │ └────────────────────────────────────────┘ ``` ```sql SELECT least(toDateTime32(now() + toIntervalDay(1)), toDateTime64(now(), 3)) ``` Result: ```result ┌─least(toDateTime32(plus(now(), toIntervalDay(1))), toDateTime64(now(), 3))─┐ │ 2023-05-12 01:16:59.000 │ └────────────────────────────────────────────────────────────────────────────┘ ``` {% callout type="info" %} The type returned is a DateTime64 as the DateTime32 must be promoted to 64 bit for the comparison. {% /callout %} ## clamp This function constrains an input `value` to be within a specified minimum and maximum range. If the `value` falls outside this range, it returns the nearest boundary; otherwise, it returns the original `value`. ### Syntax ```sql clamp(value, min, max) ``` ### Arguments - `value`: The input value to be constrained. - `min`: The lower bound of the desired range. - `max`: The upper bound of the desired range. ### Returns If `value` is less than `min`, `min` is returned. If `value` is greater than `max`, `max` is returned. Otherwise, `value` is returned. The return type will be a common supertype of `value`, `min`, and `max`. ### Example ```sql SELECT clamp(1, 2, 3) result, toTypeName(result) type ``` Result: ```result ┌─result─┬─type────┐ │ 2 │ Float64 │ └────────┴─────────┘ ```