# Conditional functions¶

Conditional functions allow encoding conditional expressions and make decisions based on the values found in a column.

## CASE expr¶

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

Compare values in a column to each consecutive WHEN clause and returns the first match. If no match found in any of the WHEN clauses then, if present, the value from the ELSE clause is returned. If the ELSE is not present nor any match is found, a NULL is returned.

### Example¶

SELECT CASE <col_1>
WHEN <literal_1> THEN <result_1>
WHEN <literal_2> THEN <result_2>
ELSE <result_3>
END
FROM <table_name>


## CASE¶

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

Compare values in a column to each consecutive WHEN clause and returns the first match. If no match found in any of the WHEN clauses then, if present, the value from the ELSE clause is returned. If the ELSE is not present nor any match is found, a NULL is returned.

The difference between this function and CASE expr is that the conditions can mix more than one column and different operators.

### Example¶

SELECT CASE WHEN <col_1> IN (<literal_1>, <literal_2) THEN <result_1>
WHEN <col_2> > 0 THEN <result_2>
ELSE <result_3>
END
FROM <table_name>


## COALESCE¶

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

This function returns a default if the value in the column is NULL.

Note

The column types and/or literals used in this function need to be of compatible data types or types that can be converted to a compatible type.

### Example¶

The simplest example returns a literal for every NULL in a column.

SELECT COALESCE(<col_1>, <literal>)
FROM <table_name>


More than one column can be included and the function will return the first value from the series that is not NULL.

Note

The list of columns will be evaluated left-to-right thus, i.e. in an expression COALESCE(<col_1, <col_2>, <literal>), if col_1 is NULL but col_2 is not, the value found in col_2 will be returned, not the literal.

SELECT COALESCE(<col_1>, <col_2>, <literal>)
FROM <table_name>


## NULLIF¶

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

This function returns a NULL if the value matches the expression.

Note

The column types and/or literals used in this function need to be of compatible data types or types that can be converted to a compatible type.

COALESCE

### Example¶

SELECT NULLIF(<col_1>, <literal_1>)
FROM <table_name>


## NVL¶

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

This function returns a default if the value in the column is NULL.

Note

The column types and/or literals used in this function need to be of compatible data types or types that can be converted to a compatible type.

SELECT NVL(<col_1>, <literal>)