Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 IFF with conditions that return NULL, which can lead to unexpected results.
  • Confusing IFF with CASE for multiple conditions; IFF only handles two outcomes.
  • Not ensuring the true_value and false_value are 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

PartDescriptionExample
conditionTest that returns true or falsenumber > 0
true_valueValue if condition is true'Positive'
false_valueValue 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.