Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Scalar Functions

SQL IIF() function

Reference for SQL IIF(): a compact inline conditional in SQLite and SQL Server, how it maps to CASE, NULL conditions, and the IF() and DECODE() relatives.

IIF(condition, if_true, if_false) evaluates a condition and returns one of two values. It is a compact form of a two-branch CASE expression: handy for flags, labels and conditional arithmetic.

IIF is available in SQLite (3.32+) and SQL Server (2012+). It is not part of the SQL standard. PostgreSQL and Oracle do not have it; CASE works everywhere.

SQL
SELECT title, IIF(stock = 0, 'sold out', 'available') AS status
FROM books;

Try it

SQL A label from a condition ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL IIF is a two-branch CASE ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Counting with IIF, and a NULL condition ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
IIF(condition, value_if_true, value_if_false)
Part Description
condition A boolean expression.
value_if_true Returned when the condition is true.
value_if_false Returned when the condition is false or unknown (NULL).

Returns: one of the two values. It is equivalent to CASE WHEN condition THEN value_if_true ELSE value_if_false END.

Dialect differences

Database Support
SQLite IIF(cond, a, b) since 3.32; also accepts more pairs: IIF(c1, a, c2, b, else).
SQL Server IIF(cond, a, b) since 2012, translated internally to CASE; limited to 10 nested levels.
PostgreSQL None. Use CASE WHEN.
MySQL IF(cond, a, b) with the same meaning (the name IIF does not exist).
Oracle None. Use CASE WHEN, or DECODE(expr, search, result, ..., default) for equality tests.
SQL
-- MySQL
SELECT IF(stock = 0, 'sold out', 'available') FROM books;

-- Oracle
SELECT DECODE(stock, 0, 'sold out', 'available') FROM books;

Usage notes

  • Unknown is false. If the condition evaluates to NULL, you get the third argument. IIF(stock > 5, ...) treats rows with NULL stock as "not greater". Test IS NULL first if that matters.
  • Types. The two result values should be of compatible types. SQL Server picks the type with higher precedence; SQLite allows mixing.
  • Conditional aggregates. SUM(IIF(c, 1, 0)) counts rows that meet c; FILTER or COUNT(CASE ...) are alternatives. See FILTER.
  • Many branches. Use CASE; nested IIF calls get hard to read quickly.
  • Portability. If code must run on several databases, use CASE from the beginning.
  • Short-circuit. Only the chosen branch is evaluated in practice, but do not rely on it to avoid errors such as division by zero; use NULLIF.