SQLite CASE Expressions: WHEN, THEN, ELSE, and IIF

CASE Is SQL's If/Else

CASE is how you put conditional logic inside a query. It walks through WHEN branches in order, picks the first one that matches, and returns the value after THEN. If nothing matches, it returns the ELSE value — or NULL if you didn't write one.

The important word is expression. CASE produces a value, so it goes anywhere a value is allowed: a SELECT column, an ORDER BY key, the right-hand side of a comparison, an argument to a function.

SELECT
    'apple' AS item,
    CASE
        WHEN 5 > 3 THEN 'yes'
        ELSE 'no'
    END AS bigger;

That's the whole shape: CASE, one or more WHEN ... THEN ..., optional ELSE, then END. The END is mandatory — forgetting it is the most common typo.

A Realistic Example

Say you've got an orders table and want to label each row by size. Build a small one inline so you can run the query:

WITH orders(id, total) AS (
    VALUES
        (1, 12.00),
        (2, 89.50),
        (3, 250.00),
        (4, 5.25),
        (5, 1200.00)
)
SELECT
    id,
    total,
    CASE
        WHEN total < 10 THEN 'tiny'
        WHEN total < 100 THEN 'small'
        WHEN total < 500 THEN 'medium'
        ELSE 'large'
    END AS size_bucket
FROM orders;

Branches are checked top-down. The first match wins, so order them from most specific to most general. The ELSE catches everything that didn't match — without it, 1200.00 would come back as NULL instead of 'large'.

Searched vs Simple

What you saw above is the searched form: each WHEN has its own boolean condition. There's a shorter form when you're comparing one expression to several constants — the simple form:

WITH users(id, status) AS (
    VALUES
        (1, 'A'),
        (2, 'B'),
        (3, 'C'),
        (4, 'A')
)
SELECT
    id,
    status,
    CASE status
        WHEN 'A' THEN 'active'
        WHEN 'B' THEN 'banned'
        WHEN 'C' THEN 'closed'
        ELSE 'unknown'
    END AS status_label
FROM users;

The expression after CASE is evaluated once and compared with = against each WHEN value. Cleaner when you're doing equality lookups on a single column.

One catch: simple CASE uses =, and NULL = NULL is not true in SQL. If status could be NULL, the 'A'/'B'/'C' branches won't match it — you'd hit ELSE. To handle NULL explicitly, switch to searched form and use WHEN status IS NULL THEN ....

CASE in ORDER BY

ORDER BY takes any expression, so CASE works fine there. Useful when you want a custom sort order that doesn't match alphabetical or numeric order:

WITH tickets(id, priority) AS (
    VALUES
        (1, 'low'),
        (2, 'high'),
        (3, 'medium'),
        (4, 'high'),
        (5, 'low')
)
SELECT id, priority
FROM tickets
ORDER BY
    CASE priority
        WHEN 'high' THEN 1
        WHEN 'medium' THEN 2
        WHEN 'low' THEN 3
    END,
    id;

'high' < 'low' < 'medium' alphabetically, which is useless for triage. Mapping each priority to a number through CASE gives you the order you actually want. The trailing , id breaks ties stably.

CASE in WHERE

You can put CASE inside WHERE, but most of the time you don't need to — chained AND/OR is clearer. Where it shines is when the condition itself depends on another value:

WITH products(id, name, price, on_sale) AS (
    VALUES
        (1, 'mug', 12.00, 1),
        (2, 'shirt', 25.00, 0),
        (3, 'cap', 18.00, 1),
        (4, 'bag', 45.00, 0)
)
SELECT id, name, price
FROM products
WHERE price < CASE WHEN on_sale = 1 THEN 20 ELSE 30 END;

Sale items qualify under 20, regular items under 30. The threshold itself is conditional. Without CASE you'd have to write (on_sale = 1 AND price < 20) OR (on_sale = 0 AND price < 30) — same result, more noise.

CASE Inside Aggregates

This is where CASE earns its keep. Combine it with SUM or COUNT to compute totals over a subset of rows in a single pass — the SQL equivalent of "count how many of these match":

WITH orders(id, total, status) AS (
    VALUES
        (1, 50.00, 'paid'),
        (2, 30.00, 'pending'),
        (3, 75.00, 'paid'),
        (4, 20.00, 'cancelled'),
        (5, 100.00, 'paid')
)
SELECT
    COUNT(*) AS all_orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
    SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid_revenue
FROM orders;

The CASE returns 1 for matching rows and 0 otherwise, so SUM becomes a conditional count. The same trick works for revenue — return total on the matching rows and 0 everywhere else. One scan of the table, several conditional aggregates.

IIF: The Two-Branch Shortcut

For a single condition with two outcomes, SQLite has IIF(cond, when_true, when_false). It's pure shorthand for CASE WHEN cond THEN when_true ELSE when_false END:

WITH accounts(id, balance) AS (
    VALUES
        (1, 100.00),
        (2, -25.00),
        (3, 0.00),
        (4, 1500.00)
)
SELECT
    id,
    balance,
    IIF(balance < 0, 'overdrawn', 'ok') AS state
FROM accounts;

Use IIF when the logic is binary and reads better as one line. Switch to CASE once you have three or more branches, need to handle NULL separately, or want the fall-through ordering of multiple WHEN clauses.

Pitfalls Worth Knowing

A few things that bite people:

• Forgetting END. CASE opens a block; END closes it. SQLite will give you a parse error well after the actual mistake.
• No ELSE means NULL. If none of your WHEN branches match and you skipped ELSE, the result is NULL. Sometimes that's what you want; usually it isn't.
• Branch order matters. With searched form, the first matching WHEN wins. Putting WHEN total < 500 before WHEN total < 100 means the second branch is unreachable.
• Type mixing. Each branch can return a different type, and SQLite won't complain — but downstream code might. Try to keep all branches returning compatible types (all text, all numeric).
• Simple CASE and NULL. As mentioned: simple form uses =, which never matches NULL. Use searched form when nulls are in play.

Next: String Functions

CASE lets you branch on values; the next chapter starts on transforming values. String functions — UPPER, LOWER, SUBSTR, REPLACE, LIKE patterns — handle the day-to-day work of cleaning and reformatting text columns. That's up next.