In SQLite, iif() is a conditional function that returns the second or third argument based on the evaluation of the first argument.

It’s logically equivalent to CASE WHEN X THEN Y ELSE Z END.

iif() is an abbreviation for Immediate IF.

The iif() function was introduced in SQLite 3.32.0, which was released on 22 May 2020.

Syntax

The syntax goes like this:

iif(X,Y,Z)

Where:

• X is the expression to be evaluated.
• Y defines what is returned if the evaluation of X returns true.
• Z defines what is returned if the evaluation of X returns false.

Another way to think of it is like this:

iif(expr, truepart, falsepart)

Example

Here’s a basic example to demonstrate how iif() works.

SELECT iif( 1 < 2, "True", "False" );

Result:

True

In this case, the expression to evaluate was 1 < 2. It is true that 1 is less than 2 so the second argument was returned.

This is the equivalent of doing the following.

SELECT 
  CASE WHEN 1 < 2 THEN "True"
    ELSE "False"
  END;

Result:

True

In these examples I used the words “True” and “False” but I could have used anything.

For example, I could have done this instead:

SELECT iif( 1 < 2, "Fail", "Pass" );

Result:

Fail

Or it could be something completely removed from a “true” or “false” binary-type answer.

For example:

SELECT iif( 1 < 2, "Sticky Rice", "Banana Smoothie" );

Result:

Sticky Rice

Database Example

Here’s an example that uses columns from a database.

SELECT 
  ProductName,
  Qty,
  iif( Qty < 10, "Order more", "Sufficient stock" ) AS Notes
FROM Products;

Result:

ProductName  Qty         Notes           
-----------  ----------  ----------------
Hammer       10          Sufficient stock
Saw          5           Order more      
Wrench       7           Order more      
Chisel       9           Order more      
Bandage      70          Sufficient stock