Categories:: Conditional expression functions

IFF¶

Returns one of two values depending on whether a Boolean expression evaluates to true or false. This function is similar to a single-level if-then-else expression. It is similar to CASE, but only allows a single condition. You can use it to add conditional logic to SQL statements.

Syntax¶

IFF( <condition> , <expr1> , <expr2> )

Copy

Arguments¶

condition

The condition is an expression that should evaluate to a BOOLEAN value (TRUE, FALSE, or NULL).

If condition evaluates to TRUE, returns expr1, otherwise returns expr2.

expr1

A general expression. This value is returned if the condition is true.

expr2

A general expression. This value is returned if the condition is not true (i.e. if it is false or NULL).

Returns¶

This function returns a value of VARCHAR data type.

Usage notes¶

The condition can include a SELECT statement containing set operators, such as UNION, INTERSECT, EXCEPT, and MINUS. When using set operators, make sure that data types are compatible. For details, see the General usage notes in the Set operators topic.

Collation details¶

The value returned from the function retains the collation specification of the highest-precedence collation of the expr1 and expr2 arguments.

Examples¶

The following examples use the IFF function.

Return expr1 because the condition evaluates to true:

SELECT IFF(TRUE, 'true', 'false');

Copy

+----------------------------+
| IFF(TRUE, 'TRUE', 'FALSE') |
|----------------------------|
| true                       |
+----------------------------+

Return expr2 because the condition evaluates to false:

SELECT IFF(FALSE, 'true', 'false');

Copy

+-----------------------------+
| IFF(FALSE, 'TRUE', 'FALSE') |
|-----------------------------|
| false                       |
+-----------------------------+

Return expr2 because the condition evaluates to NULL:

SELECT IFF(NULL, 'true', 'false');

Copy

+----------------------------+
| IFF(NULL, 'TRUE', 'FALSE') |
|----------------------------|
| false                      |
+----------------------------+

Return expr1 (integer) if the value is an integer, or return expr2 (non-integer) if the value is not an integer:

SELECT value, IFF(value::INT = value, 'integer', 'non-integer')
  FROM ( SELECT column1 AS value
           FROM VALUES(1.0), (1.1), (-3.1415), (-5.000), (NULL) )
  ORDER BY value DESC;

Copy

+---------+---------------------------------------------------+
|   VALUE | IFF(VALUE::INT = VALUE, 'INTEGER', 'NON-INTEGER') |
|---------+---------------------------------------------------|
|    NULL | non-integer                                       |
|  1.1000 | non-integer                                       |
|  1.0000 | integer                                           |
| -3.1415 | non-integer                                       |
| -5.0000 | integer                                           |
+---------+---------------------------------------------------+

Return expr1 (High) if the value is greater than 50, or return expr2 (Low) if the value is 50 or lower (or NULL):

SELECT value, IFF(value > 50, 'High', 'Low')
FROM ( SELECT column1 AS value
         FROM VALUES(22), (63), (5), (99), (NULL) );

Copy

+-------+--------------------------------+
| VALUE | IFF(VALUE > 50, 'HIGH', 'LOW') |
|-------+--------------------------------|
|    22 | Low                            |
|    63 | High                           |
|     5 | Low                            |
|    99 | High                           |
|  NULL | Low                            |
+-------+--------------------------------+