Back to Function Index

IIF()

Overview

IIF() (Immediate IF) is a conditional function that returns one of two values based on the evaluation of a Boolean expression. It's similar to the IF-THEN-ELSE construct in programming languages and is useful for conditional logic in queries and calculations.

Function Format

IIF(Boolean test, true value, false value)

Return Value

IIF() returns a value of the same type as the true and false values

Examples

IIF(1 + 1 = 2, "CORRECT", "INCORRECT")     = "CORRECT"
IIF(1 + 1 = 3, "CORRECT", "INCORRECT")     = "INCORRECT"
IIF("A" + "B" = "AB", 1, -1)              = 1
IIF("A" + "B" <> "AB", 1, -1)             = -1
IIF(SALARY > 50000, "High", "Low")         = "High"  (if salary is 60000)
IIF(AGE >= 18, "Adult", "Minor")          = "Adult" (if age is 20)
IIF(ISNULL(NAME), "Unknown", NAME)         = "Unknown" (if name is NULL)

Notes

• Parameter requirements:
  • Boolean test: Any expression that evaluates to TRUE or FALSE
  • True value: The value to return if the test is TRUE
  • False value: The value to return if the test is FALSE
• Common use cases include:
  • Conditional calculations
  • Data categorization
  • NULL value handling
  • Data validation
  • Dynamic value selection
• Type compatibility rules:
  • True and false values must be of compatible types
  • Numeric types (integer, double) are compatible with each other
  • String values must be of the same type
  • Date values must be of the same type
• Important behaviors:
  • Both true and false values are evaluated before the function returns
  • NULL values in the Boolean test are treated as FALSE
  • The function can be nested for complex conditions
• This function is equivalent to CASE WHEN ... THEN ... ELSE ... END in some database systems.

See Also

• ISNULL()