How to Use IFF Function in Snowflake: Syntax and Examples
In Snowflake, use the
IFF function to return one value if a condition is true and another if false. The syntax is IFF(condition, true_value, false_value), which works like a simple if-else statement in SQL.Syntax
The IFF function takes three parts:
- condition: A test that returns true or false.
- true_value: The value returned if the condition is true.
- false_value: The value returned if the condition is false.
This function helps you choose between two values based on a condition.
sql
IFF(condition, true_value, false_value)
Example
This example shows how to use IFF to check if a number is positive or not:
sql
SELECT number, IFF(number > 0, 'Positive', 'Not Positive') AS result FROM (VALUES (10), (0), (-5)) AS t(number);
Output
NUMBER | RESULT
-------|----------
10 | Positive
0 | Not Positive
-5 | Not Positive
Common Pitfalls
Common mistakes when using IFF include:
- Using
IFFwith conditions that return NULL, which can lead to unexpected results. - Confusing
IFFwithCASEfor multiple conditions;IFFonly handles two outcomes. - Not ensuring the
true_valueandfalse_valueare of compatible data types.
Always test your conditions and data types to avoid errors.
sql
/* Wrong: mixing data types */ SELECT IFF(1=1, 'Yes', 0) AS mixed_types; /* Right: consistent data types */ SELECT IFF(1=1, 'Yes', 'No') AS consistent_types;
Output
mixed_types
-----------
Yes
consistent_types
----------------
Yes
Quick Reference
| Part | Description | Example |
|---|---|---|
| condition | Test that returns true or false | number > 0 |
| true_value | Value if condition is true | 'Positive' |
| false_value | Value if condition is false | 'Not Positive' |
Key Takeaways
Use IFF(condition, true_value, false_value) for simple two-way conditional logic in Snowflake.
Ensure true_value and false_value have compatible data types to avoid errors.
IFF only handles two outcomes; use CASE for multiple conditions.
Test conditions carefully, especially when NULL values may appear.
IFF is a concise alternative to CASE for simple if-else logic.
