Conditional expressions
CASE
Goes through conditions in sequence and returns the value of the result associated with the matching condition.
Syntax 1
Parameters
Parameter | Description |
---|---|
condition | An expression that evaluates to a BOOLEAN value. |
result | A value or an expression that evaluates to a value. The CASE expression returns result if its associated condition evaluates to true. |
Returns
- The result associated with the first condition that evaluates to true. Or,
- The result in the ELSE clause if no condition evaluates to true. Or,
- NULL if there is no ELSE clause and no condition evaluates to true.
Syntax 2
Parameters
Parameter | Description |
---|---|
expression | An expression that evaluates to a value. expression is computed in the first place and its value will be compared with value in the WHEN clause. |
value | A value or an expression that evaluates to a value. Each value is a potential match for the expression. |
result | A value or an expression that evaluates to a value. The CASE expression returns result if its associated value matches the expression. |
Returns
- The result associated with the first value that matches the expression. Or,
- The result in the ELSE clause if no value matches the expression. Or,
- NULL if there is no ELSE clause and no value matches the expression.
Example
The following statement (using Syntax 1) classifies the distance of each trip in the table ‘taxi_trips’ into four levels.
The following statement (using Syntax 2) classifies the distance of each trip according to its digits.
COALESCE
Returns the first non-null value or null if all values are null.
NULLIF
Returns null if value1 equals to value2, otherwise returns value1.
greatest
This function returns the largest value in a list of expressions. NULL values in the argument list are ignored. The result will be NULL only if all the expressions evaluate to NULL.
least
This function returns the smallest value in a list of expressions. NULL values in the argument list are ignored. The result will be NULL only if all the expressions evaluate to NULL.
Was this page helpful?