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'

Comparison Functions

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

NULL and Error Handling Functions

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