Conditional Functions

This page provides a comprehensive overview of Conditional functions in Databend, organized by functionality for easy reference.

Basic Conditional Functions

Function	Description	Example
IF / IFF	Returns a value based on a condition	`IF(1 > 0, 'yes', 'no')` → `'yes'`
CASE	Evaluates conditions and returns a matching result	`CASE WHEN 1 > 0 THEN 'yes' ELSE 'no' END` → `'yes'`
DECODE	Compares expression to search values and returns result	`DECODE(2, 1, 'one', 2, 'two', 'other')` → `'two'`
COALESCE	Returns the first non-NULL expression	`COALESCE(NULL, 'hello', 'world')` → `'hello'`
NULLIF	Returns NULL if two expressions are equal, otherwise the first expression	`NULLIF(5, 5)` → `NULL`
IFNULL	Returns the first expression if not NULL, otherwise the second	`IFNULL(NULL, 'default')` → `'default'`
NVL	Returns the first non-NULL expression	`NVL(NULL, 'default')` → `'default'`
NVL2	Returns expr2 if expr1 is not NULL, otherwise expr3	`NVL2('value', 'not null', 'is null')` → `'not null'`

Function	Description	Example
GREATEST	Returns the largest value from a list	`GREATEST(1, 5, 3)` → `5`
LEAST	Returns the smallest value from a list	`LEAST(1, 5, 3)` → `1`
GREATEST_IGNORE_NULLS	Returns the largest non-NULL value	`GREATEST_IGNORE_NULLS(NULL, 5, 3)` → `5`
LEAST_IGNORE_NULLS	Returns the smallest non-NULL value	`LEAST_IGNORE_NULLS(NULL, 5, 3)` → `3`
BETWEEN	Checks if a value is within a range	`5 BETWEEN 1 AND 10` → `true`
IN	Checks if a value matches any value in a list	`5 IN (1, 5, 10)` → `true`

Function	Description	Example
IS_NULL	Checks if a value is NULL	`IS_NULL(NULL)` → `true`
IS_NOT_NULL	Checks if a value is not NULL	`IS_NOT_NULL('value')` → `true`
IS_DISTINCT_FROM	Checks if two values are different, treating NULLs as equal	`NULL IS DISTINCT FROM 0` → `true`
IS_ERROR	Checks if an expression evaluation resulted in an error	`IS_ERROR(1/0)` → `true`
IS_NOT_ERROR	Checks if an expression evaluation did not result in an error	`IS_NOT_ERROR(1/1)` → `true`
ERROR_OR	Returns the first expression if it's not an error, otherwise the second	`ERROR_OR(1/0, 0)` → `0`