What is the IIF(X,Y,Z) function in SQLite?

The IIF(X, Y, Z) function behaves like if-else logic. In the IIF(X, Y, Z) function:

• X is the expression to be evaluated and checked for the true condition.
• Y is returned if the evaluation of X is true.
• Z is returned if the evaluation of X is false.

Syntax

iif(condition, true_expression_or_value, false_expression_or_value)

Logically, this is equivalent to:

CASE 
  WHEN CONDITION 
    THEN 
       TRUE_VALUE 
    ELSE 
       FALSE_VALUE
END

Example

Checking True value

SELECT IIF( 100 > 30, 'True' , 'False' ) RESULT;

Output

RESULT
---------
True

In the above code, we have checked if 100 > 30. This is a true condition, so the true value True is returned.

Checking False value

SELECT IIF( 100 < 30, 'True' , 'False' ) RESULT;

Output

RESULT
---------
False

In the above code, we have checked if 100 < 30. This is a false condition, so the false value False is returned.

Example in accessing query

Let’s say we have a table with some data about students:

We will write a query to show if the user is eligible to vote in the output:

In the above code, we have created a select query to fetch name and age, and added an IIF function to check if the age is less than 18age < 18. If the age is less than, the IIF function will return Not Eligible; otherwise, the IIF function will return Eligible. The returned values are available in the can_vote column.

Output

You can test the SQLite online here.