Categories:

Conditional Expression Functions

IFF¶

Single-level if-then-else expression. Similar to CASE, but only allows a single condition.

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

Syntax¶

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

Arguments¶

condition

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

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).

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 THEN/ELSE arguments.

Examples¶

The following examples demonstrate the IFF function:

select iff(True, 'true', 'false');
+----------------------------+
| IFF(TRUE, 'TRUE', 'FALSE') |
|----------------------------|
| true                       |
+----------------------------+
Copy
select iff(False, 'true', 'false');
+-----------------------------+
| IFF(FALSE, 'TRUE', 'FALSE') |
|-----------------------------|
| false                       |
+-----------------------------+
Copy
select iff(NULL, 'true', 'false');
+----------------------------+
| IFF(NULL, 'TRUE', 'FALSE') |
|----------------------------|
| false                      |
+----------------------------+
Copy
SELECT val, IFF(val::int = val, 'integer', 'non-integer')
    FROM ( SELECT column1 as val
               FROM values(1.0), (1.1), (-3.1415), (-5.000), (null) )
    ORDER BY val DESC;
+---------+-----------------------------------------------+
|     VAL | IFF(VAL::INT = VAL, 'INTEGER', 'NON-INTEGER') |
|---------+-----------------------------------------------|
|    NULL | non-integer                                   |
|  1.1000 | non-integer                                   |
|  1.0000 | integer                                       |
| -3.1415 | non-integer                                   |
| -5.0000 | integer                                       |
+---------+-----------------------------------------------+
Copy