BETWEEN, IN и LIKE: короткие фильтры

Удобные операторы условий

Реставраторы старой Земли восстанавливали книги по фрагменту узора на переплёте. Им не всегда было нужно точное название книги. Иногда хватало признака: начинается на такой символ, относится к такому разделу, лежит в таком диапазоне дат.

Архивист данных работает похожим образом.

В WHERE не всегда удобно писать много условий через AND и OR. Иногда мысль проще выразить коротким оператором:

price >= 1000 AND price <= 3000

можно записать так:

price BETWEEN 1000 AND 3000

А длинную цепочку:

category = 'Книги'
OR category = 'Игрушки'
OR category = 'Аксессуары'

можно заменить на:

category IN ('Книги', 'Игрушки', 'Аксессуары')

А если нужно найти товары, название которых начинается со слова «Кофе», можно использовать шаблон:

name LIKE 'Кофе%'

В этом уроке три главных инструмента:

• BETWEEN a AND b — значение попадает в диапазон от a до b;
• IN (...) — значение входит в список;
• LIKE 'шаблон' — текст подходит под текстовый шаблон.

Все три оператора не делают ничего магического. Они просто помогают писать условия короче и понятнее.

КВЕРИ: Хороший фильтр похож на точную команду дрону-разведчику: не «ищи что-нибудь полезное», а «покажи товары из этих категорий, в этом диапазоне, с таким началом имени».

Три разных образца для поиска

У этих операторов разные роли.

BETWEEN отвечает на вопрос:

Значение лежит между двумя границами?

Например:

price BETWEEN 1000 AND 3000

Читается как:

цена от 1000 до 3000 включительно.

IN отвечает на вопрос:

Значение входит в этот набор вариантов?

Например:

category IN ('Книги', 'Игрушки')

Читается как:

категория — Книги или Игрушки.

LIKE отвечает на вопрос:

Текст похож на этот шаблон?

Например:

name LIKE 'Кофе%'

Читается как:

название начинается с «Кофе».

То есть:

BETWEEN: значение между двумя границами

BETWEEN проверяет, что значение находится в диапазоне.

Например, нужно найти товары с ценой от 1000 до 3000:

SELECT name, price
FROM products
WHERE price BETWEEN 1000 AND 3000;

Это то же самое, что написать:

SELECT name, price
FROM products
WHERE price >= 1000
  AND price <= 3000;

Главная деталь:

BETWEEN включает обе границы.

То есть условие:

price BETWEEN 1000 AND 3000

вернёт товары с ценой:

• 1000
• 1500
• 2999
• 3000

Цена 1000 входит.
Цена 3000 тоже входит.

Если нужно строго больше 1000 и строго меньше 3000, BETWEEN не подходит. Тогда нужно писать обычные сравнения:

WHERE price > 1000
  AND price < 3000

Важные детали BETWEEN

1. Границы не переставляются автоматически

Такой фильтр нормальный:

WHERE price BETWEEN 1000 AND 3000

А такой почти всегда вернёт пустой результат:

WHERE price BETWEEN 3000 AND 1000

Потому что база читает это как:

WHERE price >= 3000
  AND price <= 1000

Одновременно быть больше или равно 3000 и меньше или равно 1000 обычное число не может.

Поэтому порядок границ важен: сначала нижняя, потом верхняя.

2. Есть обратная форма NOT BETWEEN

Если нужно найти товары вне диапазона, можно написать:

SELECT name, price
FROM products
WHERE price NOT BETWEEN 1000 AND 3000;

Это похоже на:

WHERE price < 1000
   OR price > 3000

То есть товар за 500 попадёт.
Товар за 4000 попадёт.
Товар за 1000 не попадёт.
Товар за 3000 не попадёт.

Потому что границы входят в сам диапазон BETWEEN.

3. С датами и временем нужно быть аккуратнее

С датами BETWEEN тоже работает:

WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'

Но если created_at — это не просто дата, а дата со временем, например:

2024-01-31 18:45:00

то такой фильтр может случайно не захватить весь последний день.

Почему? Потому что '2024-01-31' для timestamp часто означает начало дня:

2024-01-31 00:00:00

Поэтому для периода по времени часто безопаснее писать полуоткрытый интервал:

WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01'

Так мы берём всё с 1 января включительно и всё до 1 февраля, но не включая 1 февраля.

Для новичка главное правило такое:

Для чисел BETWEEN удобен и понятен. Для дат со временем проверяй границы особенно внимательно.

IN: значение входит в список

IN используют, когда нужно проверить значение на несколько возможных вариантов.

Например, нужно найти товары только из категорий «Книги» и «Игрушки»:

SELECT name, category, price
FROM products
WHERE category IN ('Книги', 'Игрушки');

Это короче и понятнее, чем:

SELECT name, category, price
FROM products
WHERE category = 'Книги'
   OR category = 'Игрушки';

IN читается так:

категория входит в список: Книги, Игрушки.

Особенно хорошо IN смотрится, когда вариантов много:

WHERE category IN ('Книги', 'Игрушки', 'Аксессуары', 'Корм', 'Домики')

Вместо длинной цепочки OR получается один компактный фильтр.

Важные детали IN

1. IN удобен для списка точных значений

IN не ищет похожие значения. Он проверяет точное совпадение с одним из вариантов.

WHERE category IN ('Книги', 'Игрушки')

Найдёт категорию 'Книги'.

Но не найдёт:

Книга
книги
КНИГИ
Электронные книги

Потому что это уже другие строки.

2. NOT IN исключает значения из списка

Если нужно убрать несколько категорий, можно написать:

SELECT name, category, price
FROM products
WHERE category NOT IN ('Книги', 'Игрушки');

Это читается так:

покажи товары, категория которых не входит в список «Книги», «Игрушки».

Но у NOT IN есть важная ловушка с NULL. Она будет ниже в отдельном блоке.

3. IN хорошо сочетается с BETWEEN

Например:

SELECT name, category, price
FROM products
WHERE category IN ('Книги', 'Игрушки')
  AND price BETWEEN 1000 AND 3000
ORDER BY price;

Читается так:

покажи книги и игрушки с ценой от 1000 до 3000.

Это уже полноценный рабочий фильтр: список категорий плюс диапазон цены.

LIKE: поиск по текстовому шаблону

LIKE используют, когда нужно найти текст не по точному совпадению, а по шаблону.

Например, нужно найти товары, название которых начинается со слова «Кофе»:

SELECT name, price
FROM products
WHERE name LIKE 'Кофе%';

Знак % означает:

любое количество любых символов.

В том числе ноль символов.

Поэтому шаблон:

'Кофе%'

найдёт:

Кофе
Кофе молотый
Кофейная станция
Кофе для котонавтов

Но не найдёт:

Большой кофе
Набор: кофе и кружка

Потому что шаблон требует, чтобы текст начинался с Кофе.

%: любое количество символов

% — самый частый символ в LIKE.

Он означает:

здесь может быть любое количество символов.

Посмотри на разные шаблоны.

Начинается с текста

WHERE name LIKE 'Кофе%'

Найдёт названия, которые начинаются с Кофе.

Примеры совпадений:

Кофе
Кофе молотый
Кофейный набор

Заканчивается текстом

WHERE name LIKE '%кофе'

Найдёт названия, которые заканчиваются на кофе.

Примеры совпадений:

Большой кофе
Капсульный кофе
Набор для кофе

Содержит текст где угодно

WHERE name LIKE '%кофе%'

Найдёт названия, где кофе встречается в любой позиции.

Примеры совпадений:

кофе
Большой кофе
Набор для кофе и чая
Свежие кофейные зёрна

А вот «Кофейная станция» этот шаблон не найдёт: LIKE учитывает регистр, а К и к — разные символы. Подробнее про регистр — чуть ниже в этом уроке.

Если сказать образно:

'Кофе%'

— текст должен начаться с «Кофе».

'%кофе'

— текст должен закончиться на «кофе».

'%кофе%'

— текст должен где-то содержать «кофе».

_: ровно один символ

Кроме %, у LIKE есть второй специальный символ:

_

Он означает:

ровно один любой символ.

Например:

WHERE code LIKE 'A_1'

Подойдут:

AB1
AX1
A71

Но не подойдут:

A1
ABCD1
AA21

Почему?

Шаблон A_1 требует:

1. сначала A
2. потом ровно один любой символ
3. потом 1

Ещё пример:

WHERE name LIKE 'Кот_'

Подойдут строки из четырёх символов:

Коты
Котя
Кот1

Но не подойдёт:

Кот
Котик

Потому что _ требует ровно один дополнительный символ.

Разница такая:

Регистр: LIKE, ILIKE и LOWER

В PostgreSQL обычный LIKE чувствителен к регистру.

Это значит, что шаблон:

WHERE name LIKE 'смарт%'

может не найти строку:

Смарт-часы

Потому что с и С — разные символы.

Чтобы искать без учёта регистра, в PostgreSQL есть ILIKE:

SELECT name, price
FROM products
WHERE name ILIKE 'смарт%';

ILIKE — это регистронезависимый вариант LIKE.

Он найдёт:

смарт-часы
Смарт-часы
СМАРТ-ЧАСЫ

Но важно помнить: ILIKE — это удобство PostgreSQL, а не универсальная часть стандартного SQL.

Более переносимый подход — привести текст к одному регистру:

SELECT name, price
FROM products
WHERE LOWER(name) LIKE 'смарт%';

Здесь база сначала превращает name в нижний регистр, а потом сравнивает с шаблоном в нижнем регистре.

Можно сделать и так:

WHERE LOWER(name) LIKE LOWER('Смарт%')

Но чаще шаблон просто сразу пишут в нужном регистре:

WHERE LOWER(name) LIKE 'смарт%'

Как искать сами символы % и _

У LIKE есть проблема: % и _ — специальные символы.

% означает любое количество символов.
_ означает ровно один символ.

Но иногда нужно найти именно знак процента или подчёркивание как обычный текст.

Например, есть товары:

Скидка 50%
Корм 50 кг
QA_набор
QA-набор

Если написать:

WHERE name LIKE '50%'

это не значит «найди текст 50%».

Это значит:

найди строки, которые начинаются с 50, а дальше что угодно.

Чтобы сказать базе: «этот % — обычный символ процента», используют ESCAPE.

Например:

SELECT name
FROM products
WHERE name LIKE '%50!%%' ESCAPE '!';

Разберём шаблон:

'%50!%%'

• первый % — любое начало строки
• 50 — обычные символы
• !% — обычный знак процента, потому что ! назначен escape-символом
• последний % — любое продолжение строки

Фраза:

ESCAPE '!'

говорит SQL:

если перед % или _ стоит !, считай следующий символ обычным текстом.

Так же можно искать подчёркивание:

SELECT name
FROM products
WHERE name LIKE 'QA!_%' ESCAPE '!';

Этот шаблон найдёт строки, которые начинаются с обычного текста QA_.

Без экранирования шаблон:

'QA_%'

означал бы:

QA, потом любой один символ, потом любое продолжение.

И мог бы найти лишние строки вроде:

QA-набор
QA1набор
QA набор

КВЕРИ: В трафарете LIKE символы % и _ — не краска, а дырки. Если хочешь найти саму дырку на чертеже, сначала скажи архиву, что это обычный знак.

Цена ведущего %

На маленькой таблице разницы почти не видно. Но на миллионах строк разные шаблоны LIKE могут работать очень по-разному.

Сравни два условия:

WHERE name LIKE 'Кофе%'

и

WHERE name LIKE '%кофе'

Первое условие знает начало строки. База может рассуждать примерно как с бумажным словарём:

Открой раздел на «Кофе» и ищи рядом.

Такой поиск по префиксу иногда можно ускорить индексом.

А второе условие начинается с %:

'%кофе'

Это значит:

перед словом «кофе» может быть что угодно.

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

Поэтому запросы вида:

WHERE name LIKE '%кофе%'

часто приводят к просмотру большого количества строк: базе приходится проверять каждое название.

Простая мысль:

В PostgreSQL есть технические нюансы: для ускорения префиксного LIKE иногда нужен индекс с text_pattern_ops или подходящая локаль, а для поиска по середине строки используют триграммные индексы pg_trgm.

Но на этом этапе важно понять саму идею:

Если шаблон начинается с обычных символов, базе легче искать. Если шаблон начинается с %, базе часто приходится перечитывать таблицу шире.

Свойство условия «уметь использовать индекс» называют sargability. Подробно оно будет в модуле про оптимизацию.

UNKNOWN в связках и ловушка NOT IN

В прошлом уроке ты уже видел, что NULL даёт третий логический результат:

UNKNOWN

Это важно и для IN.

Пока список написан руками и в нём только обычные значения, всё просто:

WHERE category IN ('Книги', 'Игрушки')

Это похоже на:

WHERE category = 'Книги'
   OR category = 'Игрушки'

А вот NOT IN работает как цепочка условий через AND.

Например:

WHERE category NOT IN ('Книги', 'Игрушки')

похоже на:

WHERE category <> 'Книги'
  AND category <> 'Игрушки'

И здесь появляется ловушка.

Посмотри:

SELECT 'нашлось'
WHERE 1 NOT IN (2, 3);

Этот запрос вернёт строку, потому что:

1 <> 2 AND 1 <> 3

это:

TRUE AND TRUE

Итог — TRUE.

А теперь:

SELECT 'нашлось'
WHERE 1 NOT IN (2, NULL);

Этот запрос не вернёт строку.

Почему?

NOT IN (2, NULL) разворачивается примерно так:

1 <> 2 AND 1 <> NULL

Первая часть:

1 <> 2

даёт TRUE.

Вторая часть:

1 <> NULL

даёт UNKNOWN.

Итог:

TRUE AND UNKNOWN

даёт UNKNOWN.

А WHERE пропускает только TRUE.

Поэтому строка не попадает в результат.

Самая опасная ситуация возникает не тогда, когда список написан руками. Там NULL видно глазами.

Опасность появляется с подзапросами:

WHERE product_id NOT IN (
    SELECT product_id
    FROM archived_products
)

Если подзапрос вернёт хотя бы один NULL, результат может стать неожиданно пустым.

Пока запомни сигнал тревоги:

NOT IN + возможный NULL = риск пустой выдачи.

В следующих модулях, когда появятся подзапросы, ты увидишь более безопасные варианты через NOT EXISTS.

Что будет, если значение NULL

BETWEEN, IN и LIKE тоже подчиняются логике NULL.

Если значение отсутствует, обычная проверка не становится TRUE.

Например:

price BETWEEN 1000 AND 3000

Если price равен NULL, результат будет UNKNOWN.

category IN ('Книги', 'Игрушки')

Если category равен NULL, результат будет UNKNOWN.

name LIKE 'Кофе%'

Если name равен NULL, результат будет UNKNOWN.

А WHERE пропускает только TRUE.

Поэтому строки с NULL не проходят такие фильтры сами по себе.

Если их нужно включить, добавляй явное условие:

WHERE price BETWEEN 1000 AND 3000
   OR price IS NULL

или:

WHERE category IN ('Книги', 'Игрушки')
   OR category IS NULL

Не нужно думать, что BETWEEN, IN или LIKE как-то отдельно «понимают» отсутствующие значения. Для NULL по-прежнему нужны IS NULL и IS NOT NULL.

Частые ошибки

Ошибка 1. Забыть, что BETWEEN включает границы

WHERE price BETWEEN 1000 AND 3000

Это включает и 1000, и 3000.

Если границы не должны входить, пиши:

WHERE price > 1000
  AND price < 3000

Ошибка 2. Использовать BETWEEN с датами и случайно отрезать последний день

Осторожно:

WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'

Если created_at хранит время, часть записей за 31 января может не попасть.

Часто безопаснее:

WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01'

Ошибка 3. Писать много OR вместо IN

Можно так:

WHERE category = 'Книги'
   OR category = 'Игрушки'
   OR category = 'Аксессуары'

Но читаемее так:

WHERE category IN ('Книги', 'Игрушки', 'Аксессуары')

Ошибка 4. Думать, что IN ищет похожий текст

WHERE category IN ('Книги')

Не найдёт:

Электронные книги

Потому что IN проверяет точное совпадение.

Для похожего текста нужен LIKE:

WHERE category LIKE '%книги%'

Или ILIKE, если нужен поиск без учёта регистра в PostgreSQL:

WHERE category ILIKE '%книги%'

Ошибка 5. Путать % и _

LIKE 'A%'

A, потом любое количество символов.

LIKE 'A_'

A, потом ровно один символ.

Ошибка 6. Забыть экранировать % и _

Если нужно найти обычный знак %, не пиши так:

WHERE name LIKE '%50%%'

Лучше явно экранировать:

WHERE name LIKE '%50!%%' ESCAPE '!'

Ошибка 7. Ставить % в начало шаблона и ждать быстрой работы

WHERE name LIKE '%кофе%'

Такой поиск может быть нормальным на маленькой таблице, но на большой часто становится дорогим.

КВЕРИ: Диапазон, список и трафарет — три быстрых способа спросить архив. Главное — помнить, где образец помогает, а где заставляет архив перечитать всё с начала до конца.