Выражения CASE в SQLite: WHEN, THEN, ELSE и IIF

CASE — это if/else в SQL

CASE — это способ положить условную логику внутрь запроса. Он по очереди проходит по веткам WHEN, выбирает первую совпавшую и возвращает значение после THEN. Если ничего не совпало, возвращается значение из ELSE — или NULL, если ELSE вы не написали.

Ключевое слово здесь — выражение. CASE возвращает значение, поэтому его можно поставить везде, где допустимо значение: столбец SELECT, ключ ORDER BY, правая часть сравнения, аргумент функции.

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

Вот и вся форма: CASE, одна или несколько WHEN ... THEN ..., опциональный ELSE, и потом END. END обязателен — забыть его — самая частая опечатка.

Реалистичный пример

Допустим, есть таблица заказов и хочется проставить каждой строке метку по размеру. Соберём небольшой набор данных прямо в запросе, чтобы можно было запустить:

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;

Ветки проверяются сверху вниз. Побеждает первое совпадение, поэтому упорядочивайте их от самого узкого условия к самому общему. ELSE ловит всё, что не совпало с предыдущими ветками — без него 1200.00 вернулся бы как NULL вместо 'large'.

Поисковая форма против простой

Выше — поисковая форма: у каждой WHEN своё булево условие. Когда вы сравниваете одно выражение с несколькими константами, есть более короткая — простая — форма:

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;

Выражение после CASE вычисляется один раз и сравнивается через = с каждым значением WHEN. Так чище, когда вы делаете лукапы по равенству на одном столбце.

Один нюанс: простой CASE использует =, а в SQL NULL = NULL — это не true. Если status может оказаться NULL, ветки 'A'/'B'/'C' его не поймают — и сработает ELSE. Чтобы обработать NULL явно, переключайтесь на поисковую форму и пишите WHEN status IS NULL THEN ....

CASE в ORDER BY

ORDER BY принимает любое выражение, и CASE там нормально работает. Полезно, когда нужен свой порядок сортировки, не совпадающий с алфавитным или числовым:

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', что для триажа бесполезно. Сопоставление каждого приоритета числу через CASE даёт тот порядок, который вам реально нужен. Хвостовое , id стабильно ломает ничьи.

CASE в WHERE

CASE можно засунуть и в WHERE, но обычно в этом нет смысла — обычные цепочки AND/OR читаются понятнее. Где CASE правда блестит — это когда само условие зависит от другого значения:

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;

Распродажные товары проходят при цене ниже 20, обычные — ниже 30. Сам порог условный. Без CASE пришлось бы писать (on_sale = 1 AND price < 20) OR (on_sale = 0 AND price < 30) — тот же результат, только шумнее.

CASE внутри агрегатов

Вот где CASE по-настоящему окупается. Скомбинируйте его с SUM или COUNT, и вы за один проход посчитаете итоги по подмножеству строк — SQL-эквивалент «посчитай, сколько из этих подходят»:

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;

CASE возвращает 1 на совпавших строках и 0 на остальных, поэтому SUM превращается в условный счётчик. Тот же приём работает и для выручки — на нужных строках возвращайте total, а на остальных 0. Один проход по таблице — несколько условных агрегатов сразу.

IIF: короткая форма для двух веток

Для одного условия с двумя исходами в SQLite есть IIF(cond, when_true, when_false). Это чистая короткая запись для 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;

Берите IIF, когда логика бинарная и так читается лучше в одну строку. К CASE переходите, как только появляется три и более ветки, нужно отдельно обработать NULL или хочется ясный порядок проваливания через несколько WHEN.

Грабли, которые стоит знать

Вещи, на которые регулярно наступают:

• Забыли END. CASE открывает блок; END его закрывает. SQLite даст ошибку парсинга где-то заметно позже самой ошибки.
• Без ELSE будет NULL. Если ни одна WHEN не совпала, а ELSE вы пропустили, результат — NULL. Иногда это и нужно, но обычно — нет.
• Порядок веток важен. В поисковой форме побеждает первая совпавшая WHEN. Поставите WHEN total < 500 раньше WHEN total < 100 — и до второй ветки выполнение никогда не дойдёт.
• Смесь типов. Каждая ветка может вернуть свой тип, и SQLite на это не пожалуется — но дальше по конвейеру такой код легко ломает другую логику. Старайтесь, чтобы ветки возвращали совместимые типы (все текст, все числа).
• Простой CASE и NULL. Как уже говорили: простая форма использует =, который никогда не совпадает с NULL. Если NULL-ы в игре — переходите на поисковую форму.

Дальше: строковые функции

CASE помогает ветвиться по значениям; следующая глава — про преобразование значений. Строковые функции — UPPER, LOWER, SUBSTR, REPLACE, шаблоны LIKE — закрывают повседневную работу с очисткой и переформатированием текстовых столбцов. С них и начнём.