Функции в WHERE и ORDER BY

Функции нужны не только в SELECT. Их можно использовать везде, где можно использовать значение, то есть и в WHERE, и в ORDER BY. Какие клиенты зарегистрировались в 2024 году?

SELECT name, joined
FROM customers
WHERE strftime('%Y', joined) = '2024';

Для каждой строки SQLite вычисляет функцию, а потом проверяет условие. Здесь LIKE '2024-%' нашёл бы тех же клиентов, но с функциями можно спросить о любой части значения, а условие ясно говорит, о чём оно: strftime('%m', joined) = '03' находит клиентов, которые зарегистрировались в марте любого года, а LENGTH(name) > 11 — тех, у кого длинное имя.

Типичная ошибка: сравнение результата strftime с числом. WHERE strftime('%Y', joined) = 2024 ничего не находит, но и не выдаёт ошибку. strftime возвращает текст '2024', а для SQLite текст '2024' не равен числу 2024: это разные виды значений. Поэтому сравнивайте с текстом в кавычках. То же относится к SUBSTR, результат которой тоже текст.

Функции помогают и сравнивать тексты, когда регистр неизвестен: WHERE LOWER(genre) = 'rpg' находит игры RPG, как бы ни был записан жанр.

В ORDER BY функция сортирует строки по вычисленному значению. Здесь — от самого короткого имени:

SELECT name, LENGTH(name) AS characters
FROM customers
ORDER BY LENGTH(name), name
LIMIT 3;

Как вы знаете, ORDER BY может использовать и псевдоним столбца: ORDER BY characters, name сортирует так же. В WHERE большинство баз данных псевдонимы не разрешают, потому что проверяют условия раньше, чем вычисляют столбцы результата. SQLite снисходителен и разрешает их, но в WHERE лучше написать функцию целиком, чтобы ваши запросы работали и в других базах данных.