Зачем нужен отдельный урок про IN

IN — самый удобный способ сказать «значение принадлежит этому набору». Кажется простым: WHERE country IN ('RU', 'DE', 'IL') — и всё. Но за этим коротким синтаксисом стоит три разных операции с разным поведением и стоимостью:

1. IN (literal_list) — членство в литеральном списке.
2. IN (subquery) — членство в результате другого запроса.
3. = ANY (array) — эквивалент IN через массив, иногда эффективнее.

В этом уроке разложим все три, и заодно — операторы ANY, SOME, ALL, которые обобщают сравнения за пределы простого равенства.

IN с литеральным списком

Базовая форма: x IN (a, b, c) эквивалент x = a OR x = b OR x = c.

Преимущества IN-списка перед цепочкой OR:

• Короче и читаемее.
• Оптимизатор PostgreSQL преобразует IN-список в = ANY(ARRAY[...]) под капотом — это иногда быстрее обходится на больших списках, чем длинная цепочка OR.

NOT IN — ловушка с NULL

NOT IN (a, b, c) означает x != a AND x != b AND x != c. Кажется безобидно, но есть подвох: если в списке встретится NULL, весь предикат становится NULL для любого x.

Почему: NOT IN (1, 2, NULL) это x != 1 AND x != 2 AND x != NULL. Последнее условие — NULL для любого x, и AND с NULL даёт NULL, а не TRUE. Строка отбрасывается.

В IN ситуация другая: x = 1 OR x = 2 OR x = NULL. Если x = 1 истинно, OR коротко замыкается на TRUE. NULL в списке просто игнорируется.

Практическое правило: если в подзапросе результат может содержать NULL, используй NOT EXISTS вместо NOT IN. Или явно отфильтруй NULL: NOT IN (SELECT x FROM t WHERE x IS NOT NULL).

IN с подзапросом

x IN (SELECT ... FROM ...) — членство в динамическом наборе.

В большинстве случаев IN (subquery) оптимизируется в semi-join — поиск «существует ли совпадение», без материализации полного списка. Для небольших подзапросов это эффективно.

Альтернатива через EXISTS:

SELECT id, full_name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'delivered'
);

С точки зрения семантики результат одинаковый. С точки зрения NULL — EXISTS безопаснее (не падает на NULL’ах в подзапросе). С точки зрения производительности — обычно одинаково; оптимизатор их преобразует друг в друга.

ANY, SOME, ALL — обобщённые сравнения

PostgreSQL поддерживает квантификаторы ANY, SOME (синонимы), ALL. Синтаксис: value op ANY (set), где op — любой оператор сравнения.

x IN (set) — это синтаксический сахар для x = ANY (set). Полностью эквивалентны.

> ALL эквивалентен > (SELECT MAX(...) ...). На практике через MAX обычно быстрее, потому что СУБД может использовать индекс для агрегата.

ANY и массивы — самый частый случай

Когда в подзапросе только литеральный список, удобно сразу написать массив:

WHERE country = ANY (ARRAY['RU', 'DE', 'IL'])
-- эквивалент:
WHERE country IN ('RU', 'DE', 'IL')

Но = ANY(array) имеет огромное преимущество: список можно передать параметром из приложения, не строя строку с IN (?, ?, ?, ?) динамически.

Это важно для prepared statements: IN (?, ?, ?, ...) требует разного prepared-плана для разной длины списка. = ANY($1) использует один план для любой длины массива.

IN с большими списками — где проходит граница

PostgreSQL умеет обрабатывать IN (...) даже с тысячами элементов, но есть нюансы:

• До ~100 элементов: оптимизатор может развернуть в hash или линейный поиск, эффективно.
• 100-1000: всё ещё ок, но прироста от IN относительно простой джойн-таблицы уже нет.
• 10000+: начинается проблема. Парсинг длинного списка стоит времени; план запроса может стать неоптимальным.
• 100000+: лучше создать временную таблицу или CTE и сделать JOIN.

-- На большом списке вместо IN — JOIN через CTE
WITH ids AS (
  SELECT * FROM (VALUES (1), (2), (3), ...) AS t(id)
)
SELECT p.* FROM products p JOIN ids ON p.id = ids.id;

JOIN через CTE/temp table даёт оптимизатору больше информации для выбора стратегии — он может построить hash-таблицу или использовать индекс осмысленно.

BETWEEN — уже видели

Напомню: x BETWEEN a AND b это x >= a AND x <= b. Inclusive с обеих сторон. Для timestamp-дат предпочитай >= a AND < next_period_start (см. урок 1).

BETWEEN и IN — разные инструменты: BETWEEN для непрерывных диапазонов, IN для дискретных множеств.

Углубление: IN и индексы

IN-список на индексированной колонке транслируется в Index Scan с серией поисков (один на элемент списка). На небольших списках это эффективно. На больших — оптимизатор может переключиться на Bitmap Index Scan, который читает индекс «в один проход» и собирает множество строк.

IN (subquery) обычно становится

Чек-лист

• IN (literal_list) — синтаксический сахар над цепочкой OR; короче и часто быстрее.
• IN (subquery) обычно превращается в semi-join, эффективен на индексах.
• NOT IN с NULL в наборе — ловушка: весь предикат становится NULL, строки исчезают. Используй NOT EXISTS для подзапросов.
• x op ANY (set) — обобщение: = это IN, > это «больше хоть одного», <> это «не равен ни одному».
• x op ALL (set) — «для каждого элемента»: > это «больше всех», <> это NOT IN.
• = ANY(array) лучше IN (?, ?, ?, ...) для prepared statements — один план на любую длину.
• На списках 10000+ элементов лучше JOIN через CTE/temp table, не IN.
• BETWEEN для непрерывных диапазонов, IN для дискретных значений.