Skip to content

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