What is IFF function in Snowflake

Are you looking to find how to use the IFF expression within the Snowflake cloud data warehouse or maybe you are looking for a solution to use the conditional expression in the Snowflake? You may also want to check what could be real-world use case scenarios where you wanted to use the IFF expression.  I will also show you what is the difference between the IFF expression and the if-else block and how it is different from the CASE. I will explain the iff expression by taking a practical example and  we will discuss what are the real-world use case in areas where using the if expression would be very handy so don’t waste time let’s start step by step guide to understanding what is the iff expression

Iff conditional expression function takes three arguments first is the condition second is an expression for true condition, third is an expression for false condition.

Syntax : IFF(condition, ‘true expression’, ‘false expression’)

How to do time travel in Snowflake

How to use IF, Switch,Do-while, While and for loop in Snowflake

Iff is used to verify the condition and return a single expression based on the weather condition is true or false. It will just return a single line of expression only.

IFF function argument details

IFF expression example in Snowflake:

-- IFF expression example in Snowflake
select  name, IFF(salary> 2000, 'High Salary', 'Low Salary) as Salary from  Employee

-- Query Result

+--------+-------------+
| NAME   | SALARY      |
|--------+-------------|
| John   | Low Salary  |
| Mike   | Low Salary  |
| Peter  | Low Salary  |
| Sheena | High Salary |
+--------+-------------+

Use case scenarios for iff Expression

IFF expression could be used at a place where you want to modify some number or condition with a specific value for example if you want to print the user’s exam result as Pass or Fail where whether the student is Passes if gets more than 40 marks is otherwise considered as Fail.

IFF expression cannot be used at a place where there are set of statements to be executed once the condition becomes true or false.

What is the difference between the if-else and iff expression

• IFF expression has been used for returning a single expression based on the condition we cannot have a set of statements that can be executed based on the true condition or a false condition.

• However, if we use the if-else block then we can run a number of statements based upon the condition whether it is true or false.

• IFF condition could be used as a shorthand operator for an if-else block but if you have a more complex control flow then probably it is preferable to use if-else instead of iff.

What is the difference between the IFF expression and the CASE (IFF vs CASE) in Snowflake.

• In the Case conditional expression we can have multiple if then else condition for example if your marks if greater than 70 then your grade is A, if it is greater than 50 and less than 60 then your grade is B. For such kind of business scenario where we have multiple conditions there, it is preferable to use the CASE.

• However, iff expression has been used only for a single conditional if then else. For example, the business use case could be,  if the number is greater than 40 then the student is passed or failed

Example of using the CASE in Snowflake

CASE conditional expression in Snowflake example:

select name, CASE when marks < 40 THEN  'Pass'
                      when marks > 40 and marks <60 THEN  'B'
                      when marks > 60 THEN  'A'
            END as Result
            from  Student

-- Output Result

+--------+-------------+
| NAME   | RESULT      |
|--------+-------------|
| John   | A |
| Mike   | Fail  |
| Peter  | B |
| Sheena | A |
+--------+-------------+

What are the different alternatives for IFF expression?

Different alternatives for the IFF expression which can be used are as follows:

1. If-else block
2. CASE

IFF Snowflake official Documentation Link

Invalid argument types for function ‘IFF snowflake?

Argument type for the IFF function should be condition only as first parameter. For example

iff(‘this needs to be condition which result in either true or false’, ‘true expression’, ‘false expression’)

If you pass string or other data type value in the first argument then it will through error. To resolve it you need to pass on the correct data type based argument.

Final Thoughts

By this, we have reached the last section of this article where we have discussed a lot of insightful information about the IFF expression in the Snowflake. In this article, we have also seen what is the difference between the case expression and the IFF conditional expression. We have also gone through the various use cases where iff expression could be very useful.  I’ve also shared various alternatives for iff expression as well within the blog post.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

• For Azure Study material Join Telegram group : Telegram group link:
• Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
• Azure Tutorial Videos: Videos Link

• Date_Trunc() in Snowflake
• Date_Add() in Snowflake
• Parse_Json() in Snowflake
• Insert() in Snowflake
• Right() in Snowflake
• StartsWith() in Snowflake
• Replace () in Snowflake
• Position() function
• ListAgg() function
• Like() Function
• LikeAll() Function
• Like Any() Function
• Current_Date() Function
• Row_Number() Function
• Cast() Function
• Show() Function
• ISNULL() Function
• Lag() Function
• ILIKE() Function
• DateDiff() Function
• Substring() Function
• ILIKE ANY() Function
• Round() Function
• To_Date() Function
• Concat() Function
• Length() Function
• TRIM(), LTRIM(), RTRIM()
• SPLIT() Function
• Reverse() Function
• Repeat() Function
• Unicode() Function
• IFNULL() Function
• Pivot() Function
• IFF() Function

Deepak Goyal

Deepak Goyal is certified Azure Cloud Solution Architect. He is having around decade and half experience in designing, developing and managing enterprise cloud solutions. He is also Big data certified professional and passionate cloud advocate.