Conditional Expressions

Applies to: ✅ Data Cloud SQL ✅ Tableau Hyper API

The conditional expressions select one of multiple result values.

The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages. CASE clauses can be used wherever an expression is valid CASE expressions have two variants.

In the first variant, each condition is an expression that returns a boolean result. If the condition’s result is true, the value of the CASE expression is the result that follows the condition, and the rest of the CASE expression isn’t processed. If the condition’s result isn’t true, any subsequent WHEN clauses are examined in the same manner. If no WHEN condition yields true, the value of the CASE expression is the result of the ELSE clause. If the ELSE clause is omitted and no condition is true, the result is null.

For example, the query:

Returns the result:

The second form of CASE expressions, called a simple case expressions, is similar to a switch statement in C.

First, expression is computed and then compared to each of the value expressions in the WHEN clauses until one is found that is equal to it. If no match is found, the result of the ELSE clause (or a null value) is returned.

For example, the previous example can be written as a simple CASE expression.

A CASE expression doesn’t evaluate any subexpressions that aren’t required to determine the result. For example, this CASE expression is a potential way to avoid a division-by-zero failure.

The data types of all the result expressions must be convertible to a single output type.

The COALESCE function returns the first of its arguments that isn't null. Null is returned only if all arguments are null. It’s often used to substitute a default value for null values if data is retrieved for display, for example:

This function returns description if it isn’t null. Or it returns short_description if it isn’t null. Otherwise, it returns (none).

COALESCE only evaluates the arguments that are needed to determine the result. Arguments after the first non-null argument aren’t evaluated.

The NULLIF function returns a null value if value1 equals value2. Otherwise, it returns value1. This function can be used to perform the inverse operation of the previous COALESCE example.

In this example, if value is (none), NULLIF returns null, otherwise the value of value is returned.

The GREATEST and LEAST functions choose the largest or smallest value from a list of expressions. The expressions must all be convertible to a common data type, and is the type of the result.

By default, NULL values in the list are ignored. The result is NULL if all of the inputs evaluate to NULL. To preserve NULL inputs, use `PRESERVE NULLS. In this case, the result is NULL if any input is NULL.