Appearance
IIF
Return two specific conditional values for the same expression: one value if the expression evaluates to true and the other value if the expression evaluates to false.
IIF(test_expr, true_expr, false_expr)
- test_expr
- Valid Boolean expression.
- true_expr
- Value to return if
test_expr
evaluates to true. - false_expr
- Value to return if
test_expr
evaluates to false.
Examples
For example, use IIF
to state whether a team's average stadium attendance was above or below the 2018-19 season average of 38,000:
premdb=# SELECT name, stadium, IIF(avg_att > 38.000, 'Above 2018-19 season average', 'Below 2018-19 season average') AS Average_Attendance
FROM team ORDER BY teamid LIMIT 5;
name | stadium | average_attendance
------------------+------------------+------------------------------
Arsenal | Emirates Stadium | Above 2018-19 season average
Aston Villa | Villa Park | Below 2018-19 season average
Barnsley | Oakwell Stadium | Below 2018-19 season average
Birmingham City | St. Andrew's | Below 2018-19 season average
Blackburn Rovers | Ewood Park | Below 2018-19 season average
(5 rows)
The IIF
conditional expression is equivalent to the following simple CASE expression:
CASE
WHEN test_expr is true THEN true_expr
ELSE false_expr
END
Using the previous IIF
example, the corresponding CASE statement returns the same results:
premdb=# SELECT name, stadium, IIF(avg_att > 38.000, 'Above 2018-19 season average', 'Below 2018-19 season average') AS Average_attendance,
CASE WHEN avg_att > 38.000 is true THEN 'Above 2018-19 season average' ELSE 'Below 2018-19 season average' END AS Average_attendance
FROM team ORDER BY teamid LIMIT 5;
name | stadium | average_attendance | average_attendance
------------------+------------------+------------------------------+------------------------------
Arsenal | Emirates Stadium | Above 2018-19 season average | Above 2018-19 season average
Aston Villa | Villa Park | Below 2018-19 season average | Below 2018-19 season average
Barnsley | Oakwell Stadium | Below 2018-19 season average | Below 2018-19 season average
Birmingham City | St. Andrew's | Below 2018-19 season average | Below 2018-19 season average
Blackburn Rovers | Ewood Park | Below 2018-19 season average | Below 2018-19 season average
(5 rows)
Parent topic:Conditional Expressions