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.