SQL Server IIF in Snowflake, Databricks and BigQuery
SQL Server IIF(cond, a, b) is IFF in Snowflake, if or iff in Databricks, and IF in BigQuery. Note the spelling: Snowflake and Databricks use IFF with one I and two Fs.
SQL Server function: IIF(boolean_expression, true_value, false_value) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Different name | IFF(condition, expr1, expr2) | Returns expr2 when the condition is FALSE or NULL. | Snowflake docs |
| Databricks | Different name | iff(cond, expr1, expr2) | Synonym of if(cond, expr1, expr2). | Databricks docs |
| BigQuery | Different name | IF(expr, true_result, else_result) | Returns else_result when expr is FALSE or NULL. | BigQuery docs |
Behavior differences to check
- A NULL condition returns the false branch in all three targets, the same as SQL Server. Nested IIF calls translate one to one, but a CASE expression is easier to read when there are more than two branches.
Examples
Label large orders
SELECT order_id, IIF(amount >= 1000, 'large', 'regular') AS size FROM orders;SELECT order_id, IFF(amount >= 1000, 'large', 'regular') AS size FROM orders;SELECT order_id, iff(amount >= 1000, 'large', 'regular') AS size FROM orders;SELECT order_id, IF(amount >= 1000, 'large', 'regular') AS size FROM orders;