Snowflake IFF: Simplified Conditional SQL Logic
The IFF function in Snowflake is a conditional function that works similarly to an “if-then-else” statement in many programming languages, but it’s adapted for SQL. This function evaluates a Boolean condition and returns one of two values based on whether the condition is true or not.
Syntax
_10IFF(<condition>, <expr1>, <expr2>)
where:
condition: An expression that evaluates toTRUE,FALSE, orNULL.expr1: The value returned if condition isTRUE.expr2: The value returned if condition isFALSEorNULL.
Returns
The function returns a value of the same data type as expr1 and expr2. If their data types differ, the return type will be coerced to the type of the expression with the higher data type precedence.
Usage Notes
- When using IFF, ensure that the condition can properly evaluate to a Boolean value. If the condition is NULL, the function treats it as FALSE.
- This function is useful for inline calculations in queries where a simple conditional choice between two options is needed.
- You can incorporate more complex expressions as part of the condition, including subqueries that use set operators (
UNION,INTERSECT, etc.).
Examples
Basic Usage
Snowflake SQL Query
_10SELECT IFF(TRUE, 'true', 'false'); -- Returns 'true' because the condition is true_10_10SELECT IFF(FALSE, 'true', 'false'); -- Returns 'false' because the condition is false_10_10SELECT IFF(NULL, 'true', 'false'); -- Returns 'false' because the condition is NULL
Conditional Categorization
Input
Table: planets
| planet_name | has_dangerous_feature |
|---|---|
| Magrathea | FALSE |
| Vogsphere | TRUE |
| Betelgeuse Seven | FALSE |
| Kakrafoon | TRUE |
| Earth | FALSE |
Snowflake SQL Query
We’ll write a query using the IFF function to classify each planet’s safety for visitors:
_10SELECT _10 planet_name, _10 IFF(has_dangerous_feature, 'Risky', 'Safe') AS visit_safety_10FROM _10 planets;
Output
| planet_name | visit_safety |
|---|---|
| Magrathea | Safe |
| Vogsphere | Risky |
| Betelgeuse Seven | Safe |
| Kakrafoon | Risky |
| Earth | Safe |
