SQLite BETWEEN: фильтрация значений по диапазону

Оператор BETWEEN позволяет фильтровать строки по диапазону значений.

Ниже — разделы про синтаксис оператора BETWEEN, включающий диапазон и поведение с NULL и оператор NOT BETWEEN, чтобы быстро понять прикладную ценность материала, ограничения и реальные узкие места.

Оператор BETWEEN позволяет фильтровать строки по диапазону значений

Синтаксис оператора BETWEEN

BETWEEN — логический оператор, который проверяет, находится ли значение в определённом диапазоне. Он возвращает true, если значение входит в указанный диапазон и может быть использован в предложениях WHERE для операторов SELECT, DELETE, UPDATE и REPLACE

Синтаксис выглядит так:

test_expression BETWEEN low_expression AND high_expression

Здесь:

test_expression — выражение, которое проверяется на вхождение в диапазон, определённый low_expression и high_expression

low_expression и high_expression — любые допустимые выражения, задающие нижнее и верхнее значения диапазона. Значение low_expression должно быть меньше или равно high_expression, иначе BETWEEN всегда возвращает false

Ключевое слово AND является разделителем, указывающим, что test_expression должно находиться в диапазоне, заданном low_expression и high_expression

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Включающий диапазон и поведение с NULL

BETWEEN включает границы: возвращает true, если значение равно границам

test_expression >= low_expression AND test_expression <= high_expression

Чтобы задать исключающий диапазон, используйте операторы больше (>) и меньше (<) напрямую

Если один из параметров оператора BETWEEN равен NULL, результат также будет NULL. Это стандартное поведение SQL, которое следует учитывать при формулировании условий фильтрации

Оператор NOT BETWEEN

Чтобы инвертировать результат, используйте NOT BETWEEN:

test_expression NOT BETWEEN low_expression AND high_expression

NOT BETWEEN возвращает true, если значение test_expression меньше low_expression или больше high_expression. По сути, это отрицание включающего диапазона

BETWEEN с числами: примеры запросов

Для демонстрации используем таблицу invoices из примера базы данных

BETWEEN с числами

Следующий запрос находит счета, общая сумма которых находится между 14.91 и 18.86:

SELECT InvoiceId, BillingAddress, Total
FROM invoices
WHERE Total BETWEEN 14.91 and 18.86
ORDER BY Total;

Счета с итоговой суммой 14.91 или 18.86 включены в выборку из-за включающего диапазона

NOT BETWEEN с числами

Чтобы найти счета, общая сумма которых не находится между 1 и 20, используйте NOT BETWEEN:

SELECT InvoiceId, BillingAddress, Total
FROM invoices
WHERE Total NOT BETWEEN 1 and 20
ORDER BY Total;

Результат включает счета с итоговой суммой меньше 1 и больше 20 — граничные значения 1 и 20 в выборку не попадают

BETWEEN с датами в SQLite: примеры запросов

Работа с датами — распространённый случай использования BETWEEN. SQLite хранит даты как текст в формате YYYY-MM-DD, что позволяет корректно сравнивать их лексикографически

BETWEEN с датами

Следующий запрос находит счета, даты которых находятся в диапазоне от 1 января 2010 года по 31 января 2010 года:

SELECT InvoiceId, BillingAddress, InvoiceDate, Total
FROM invoices
WHERE InvoiceDate BETWEEN '2010-01-01' AND '2010-01-31'
ORDER BY InvoiceDate;

Обе граничные даты включены в выборку

NOT BETWEEN с датами

Следующий запрос находит счета, даты которых не находятся в диапазоне от 3 января 2009 года по 1 декабря 2013 года:

SELECT InvoiceId, BillingAddress, date(InvoiceDate) InvoiceDate, Total
FROM invoices
WHERE InvoiceDate NOT BETWEEN '2009-01-03' AND '2013-12-01'
ORDER BY InvoiceDate;

Здесь используется функция date() для нормализации формата отображения даты в результирующем наборе

Типичные ошибки при работе с BETWEEN

На практике я встречал несколько повторяющихся проблем, которые стоит держать в голове

Перепутаны местами low и high. Если написать BETWEEN 20 AND 1, оператор всегда вернёт false, потому что нижняя граница больше верхней. SQLite не выдаёт ошибку — запрос просто не вернёт строк

NULL в одном из операндов. Если test_expression, low_expression или high_expression равен NULL, результат BETWEEN будет NULL, а не false. Строка не попадёт в выборку, но и явной ошибки не будет — это молчаливое исключение легко пропустить

Ожидание исключающего диапазона. BETWEEN всегда включает граничные значения. Если нужно исключить границы, пишите явные условия: Total > 1 AND Total < 20

Сравнение дат без единого формата. SQLite сравнивает даты как строки. Если часть дат хранится в формате MM/DD/YYYY, а часть — в YYYY-MM-DD, результат BETWEEN будет непредсказуемым. Мой совет — всегда используйте функцию date() для нормализации и фиксируйте единый формат хранения на уровне схемы

BETWEEN или операторы сравнения: когда что выбрать

BETWEEN делает запрос читаемее, когда диапазон включающий и оба конца известны заранее. Вместо Total >= 14.91 AND Total <= 18.86 достаточно написать Total BETWEEN 14.91 AND 18.86 — намерение сразу понятно

Явные операторы >, <, >=, <= предпочтительнее в трёх случаях: когда нужен исключающий диапазон, когда одна из границ вычисляется динамически и условие становится сложным, или когда важно явно задокументировать, что граница не входит в выборку

На нашем опыте разницы в производительности нет: SQLite транслирует BETWEEN в те же операции сравнения, что и явные условия

Ответы на эти вопросы могут быть для вас полезными

Включает ли BETWEEN граничные значения? Да. BETWEEN является включающим оператором: BETWEEN 10 AND 20 эквивалентно >= 10 AND <= 20. Оба граничных значения попадают в результат

Что произойдёт, если low_expression больше high_expression? BETWEEN всегда вернёт false, и запрос не вернёт ни одной строки. SQLite не выдаёт ошибку — просто пустой результат

Как BETWEEN работает с NULL? Если любой из трёх операндов — test_expression, low_expression или high_expression — равен NULL, результат BETWEEN будет NULL. Строка не попадёт в выборку

Можно ли использовать BETWEEN с датами в SQLite? Да. SQLite хранит даты как текст в формате YYYY-MM-DD, и лексикографическое сравнение строк в этом формате даёт корректный результат. Для нормализации отображения используйте функцию date()

Чем NOT BETWEEN отличается от написания двух условий через OR? NOT BETWEEN low AND high эквивалентно test_expression < low OR test_expression > high. Оба варианта дают одинаковый результат; NOT BETWEEN просто короче и читаемее