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.

It’s a simplified version of the CASE expression but is restricted to a single condition.

Syntax

IFF(<condition>, <expr1>, <expr2>)

where:

condition: An expression that evaluates to TRUE, FALSE, or NULL.
expr1: The value returned if condition is TRUE.
expr2: The value returned if condition is FALSE or NULL.

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

SELECT IFF(TRUE, 'true', 'false'); -- Returns 'true' because the condition is true

SELECT IFF(FALSE, 'true', 'false'); -- Returns 'false' because the condition is false

SELECT 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:

SELECT 
    planet_name, 
    IFF(has_dangerous_feature, 'Risky', 'Safe') AS visit_safety
FROM 
    planets;

Output

planet_name	visit_safety
Magrathea	Safe
Vogsphere	Risky
Betelgeuse Seven	Safe
Kakrafoon	Risky
Earth	Safe

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started