Выражение SQLite CASE вычисляет условия и возвращает результат, аналогично оператору IF-THEN-ELSE в других языках
Выражение CASE можно применять в любом предложении или операторе, которые принимают допустимое выражение, таких как WHERE, ORDER BY, HAVING, SELECT, а также в операторах SELECT, UPDATE и DELETE
SQLite предоставляет две формы выражения CASE: простое CASE (simple CASE) и поисковое CASE (searched CASE). Разберём каждую из них подробно
Простое выражение CASE WHEN в SQLite
Простое выражение CASE сравнивает одно значение с заранее заданным списком и возвращает соответствующий результат
CASE case_expression WHEN when_expression_1 THEN result_1 WHEN when_expression_2 THEN result_2 ... [ ELSE result_else ]
ENDПростое выражение CASE сравнивает case_expression с выражением, указанным в первом предложении WHEN, то есть с when_expression_1, на предмет равенства
Если case_expression равно when_expression_1, простое CASE возвращает выражение из соответствующего предложения THEN, то есть result_1
В противном случае простое выражение CASE сравнивает case_expression с выражением в следующем предложении WHEN
Если ни одно из when_expression не совпадает с case_expression, выражение CASE возвращает result_else из предложения ELSE. Если предложение ELSE опущено, выражение CASE возвращает NULL
Простое выражение CASE возвращает результат и прекращает вычисление остальных условий, как только находит совпадение
По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы
По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы
Пример простого CASE
Рассмотрим таблицу customers в учебной базе данных
Предположим, нужно сформировать отчёт о группах клиентов по следующей логике: если клиент находится в США, он относится к группе «Domestic» (внутренний), в противном случае — к группе «Foreign» (иностранный)
Для формирования этого отчёта используем простое выражение CASE в операторе SELECT следующим образом:
SELECT customerid, firstname, lastname, CASE country WHEN 'USA' THEN 'Domestic' ELSE 'Foreign' END CustomerGroup
FROM customers
ORDER BY LastName, FirstName;Простое CASE проверяет лишь равенство; для более сложных сравнений следует использовать поисковое
Поисковое выражение CASE WHEN: гибкие условия
Поисковое выражение CASE использует различные формы сравнения и вычисляет булевы выражения в порядке их указания, возвращая соответствующий результат
Ниже показан синтаксис поискового выражения CASE:
CASE WHEN bool_expression_1 THEN result_1 WHEN bool_expression_2 THEN result_2 [ ELSE result_else ]
ENDПоисковое выражение CASE оценивает булевы выражения по порядку и возвращает соответствующий результат, если хотя бы одно из них истинно
Если все условия ложные, поисковое выражение CASE вернёт значение из блока ELSE, если он указан, или NULL в противном случае
Аналогично простому выражению CASE, поисковое выражение CASE прекращает вычисление при выполнении первого подходящего условия
Пример поискового CASE
Для демонстрации используем таблицу tracks
Например, можно классифицировать треки по их длительности: короче одной минуты — короткий (short), от 1 до 5 минут — средний (medium), более 5 минут — длинный (long)
Для этого применяем поисковое выражение CASE следующим образом:
SELECT trackid, name, CASE WHEN milliseconds < 60000 THEN 'short' WHEN milliseconds BETWEEN 60000 AND 300000 THEN 'medium' ELSE 'long' END category FROM tracks;Я нахожу поисковую форму CASE значительно более гибкой на практике: она позволяет комбинировать условия через AND/OR, использовать функции и подзапросы прямо внутри предложений WHEN
Где использовать CASE WHEN: SELECT, WHERE, ORDER BY, UPDATE
Выражение CASE не ограничено только списком SELECT. Вот типичные сценарии использования:
- WHERE — фильтрация строк по условной логике
- ORDER BY — сортировка с нестандартным приоритетом, например сначала «Domestic», затем «Foreign»
- HAVING — условная фильтрация агрегированных групп
- UPDATE — обновление разных столбцов в зависимости от условия в одном операторе
- DELETE — условное удаление строк
Во всех этих контекстах синтаксис CASE остаётся одинаковым — меняется только место его вставки в запрос
Типичные ошибки при работе с CASE
На практике я встречал несколько ошибок, которые повторяются чаще всего
Пропущенный ELSE. Если ни одно условие WHEN не выполнилось и предложение ELSE отсутствует, выражение CASE вернёт NULL. Это может неожиданно сломать агрегации или сортировку. Мой совет — явно указывать ELSE с разумным значением по умолчанию
Порядок условий в поисковом CASE. Поскольку вычисление останавливается на первом совпадении, более специфичные условия нужно ставить раньше более общих. Если поменять условия местами, часть строк попадёт в неправильную категорию
Простое CASE вместо поискового для диапазонов. Простое CASE сравнивает только на равенство. Попытка написать WHEN > 60000 в простой форме приведёт к синтаксической ошибке — для диапазонов нужна поисковая форма
Несовместимые типы результатов. Все ветки THEN и ELSE должны возвращать совместимые типы данных. Смешивание числовых значений и строк может привести к неожиданному приведению типов
Ответы на эти вопросы могут быть для вас полезными
В чём разница между простым и поисковым выражением CASE? Простое CASE сравнивает одно выражение со списком значений только на равенство. Поисковое CASE вычисляет произвольные булевы выражения в каждом предложении WHEN, что позволяет использовать диапазоны, операторы сравнения и логические комбинации
Что вернёт CASE, если ни одно условие не выполнилось и ELSE не указан? Выражение CASE вернёт NULL
Можно ли использовать CASE внутри ORDER BY? Да. Например, можно задать нестандартный порядок сортировки: ORDER BY CASE country WHEN 'USA' THEN 1 ELSE 2 END — это поставит американских клиентов первыми
Останавливается ли CASE после первого совпадения? Да, обе формы используют вычисление с коротким замыканием и прекращают проверку условий, как только найдено первое совпадение
Можно ли вкладывать одно выражение CASE внутрь другого? Да, вложенные CASE допустимы. Результат одного выражения CASE может быть использован как case_expression или как значение в предложении THEN другого выражения CASE
