IN и NOT IN: список значений
IN и NOT IN: список значений
В предыдущем уроке вы научились использовать LIKE для поиска по шаблону. Теперь рассмотрим другую часто встречающуюся задачу: «верни строки, где значение этой колонки — одно из нескольких конкретных значений». Например, товары только из трёх определённых категорий, или пользователи с одним из нескольких статусов. Можно написать через OR:
WHERE status = 'active' OR status = 'pending' OR status = 'trial'
Это работает, но становится громоздким, когда значений много. Оператор IN решает ту же задачу компактно и читабельно.
Синтаксис IN
IN проверяет, входит ли значение колонки в заданный список:
SELECT колонки
FROM таблица
WHERE колонка IN (значение1, значение2, значение3, ...);
Тот же запрос через IN:
SELECT name, email, status
FROM users
WHERE status IN ('active', 'pending', 'trial');
Запрос с OR из вступления и запрос с IN возвращают абсолютно одинаковый результат. Разница — только в читаемости и компактности. Когда список значений пять и более — IN выигрывает по наглядности.
Числовые значения в IN
IN работает не только со строками. Числа перечисляются без кавычек:
SELECT product_name, category_id
FROM products
WHERE category_id IN (1, 3, 7, 12);
Это вернёт товары из категорий с id 1, 3, 7 и 12. Эквивалентно четырём условиям через OR:
WHERE category_id = 1 OR category_id = 3 OR category_id = 7 OR category_id = 12
Видите, насколько IN короче и понятнее?
Проверь себя: как переписать WHERE year = 2022 OR year = 2023 OR year = 2024 через IN?
NOT IN: исключение списка значений
NOT IN — обратная операция: строки, где значение не входит в список:
SELECT name, status
FROM users
WHERE status NOT IN ('banned', 'deleted', 'inactive');
Это вернёт всех пользователей, кроме забаненных, удалённых и неактивных. Аналог через AND NOT:
WHERE status <> 'banned' AND status <> 'deleted' AND status <> 'inactive'
NOT IN опять выигрывает по краткости.
IN с однострочным списком
IN с одним значением технически работает, но смысла нет — это то же самое, что =:
WHERE status IN ('active') -- то же самое, что WHERE status = 'active'
Используйте = для одного значения и IN для двух и более.
NULL в IN и NOT IN: важная ловушка
Здесь скрывается серьёзная ловушка. Если список содержит NULL, результат может вас удивить.
Поведение IN с NULL в колонке:
SELECT name FROM users WHERE status IN ('active', NULL);
Это не вернёт строки с status IS NULL. Потому что NULL IN ('active', NULL) вычисляется как NULL (не TRUE). Строки, где условие NULL, отфильтровываются.
Более опасный случай — NOT IN с NULL в списке:
-- Предположим, список содержит NULL
SELECT name FROM users WHERE status NOT IN ('active', NULL);
Этот запрос вернёт ноль строк — независимо от данных в таблице. Почему? Потому что 'pending' NOT IN ('active', NULL) вычисляется как NULL (ведь нельзя утверждать, что 'pending' <> NULL). А раз результат NULL, строка отфильтровывается.
Правило: никогда не передавайте NULL в список NOT IN. Если в данных может встречаться NULL в колонке, добавляйте явную проверку:
WHERE status NOT IN ('active', 'pending')
AND status IS NOT NULL;
IN с подзапросом
Мощная особенность IN: список значений может быть результатом другого запроса — подзапроса. Это мы подробно разберём в модуле 7. Пока просто покажем, как это выглядит:
-- Найти пользователей, у которых есть хотя бы один заказ
SELECT name FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);
Подзапрос SELECT DISTINCT user_id FROM orders возвращает список ID пользователей, которые делали заказы. IN проверяет, входит ли id каждого пользователя в этот список. Мощно и читабельно.
Размер списка IN
Технически в IN можно перечислить сколько угодно значений. Но на практике:
- Если список большой (сотни значений) — это сигнал, что данные лучше хранить в таблице и использовать
JOINили подзапрос - PostgreSQL и большинство СУБД хорошо оптимизируют
INсо списком до нескольких десятков значений - Очень большие списки (тысячи значений) могут быть медленнее, чем подзапрос с временной таблицей
Для обычной работы (5–50 значений) IN работает отлично.
IN vs OR: когда что предпочесть
Практически всегда IN читабельнее нескольких OR. Единственный случай, где OR может быть предпочтительнее — когда условия разнотипные и нельзя объединить в список:
-- OR: разные колонки или условия — IN не применимо
WHERE status = 'active' OR created_at < '2020-01-01'
-- IN: одна колонка, несколько значений — идеально для IN
WHERE status IN ('active', 'pending', 'trial')
IN применим только тогда, когда вы проверяете одну и ту же колонку на несколько конкретных значений.
Практический пример: фильтрация по отделам
Задача: HR-менеджер хочет выгрузить данные по трём конкретным отделам для отчёта:
SELECT employee_id,
name,
department,
salary
FROM employees
WHERE department IN ('Разработка', 'QA', 'DevOps')
ORDER BY department, salary DESC;
Читается как: «сотрудники из Разработки, QA или DevOps, отсортированные по отделу и зарплате». Ясно и лаконично.
Обратный запрос — исключить административные отделы:
SELECT employee_id, name, department
FROM employees
WHERE department NOT IN ('Бухгалтерия', 'Юридический', 'Административный')
ORDER BY department, name;
Типичные ошибки
1. Пропущена запятая:
WHERE id IN (1 2 3); -- синтаксическая ошибка
WHERE id IN (1, 2, 3); -- правильно
2. Смешение типов:
WHERE age IN ('25', '30'); -- строки вместо чисел
WHERE age IN (25, 30); -- правильно для числовой колонки
Хотя PostgreSQL иногда автоматически конвертирует типы, явное соответствие типов — лучшая практика.
3. NULL в NOT IN:
-- Этот запрос вернёт 0 строк, если users содержит id IS NULL
WHERE id NOT IN (SELECT user_id FROM orders)
-- Если orders.user_id может быть NULL, добавьте:
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL)
Комбинирование IN с другими условиями
IN прекрасно сочетается с другими клаузами WHERE:
-- Пользователи из нескольких городов с активной подпиской
SELECT name, city, subscription_status
FROM users
WHERE city IN ('Москва', 'Санкт-Петербург', 'Казань')
AND subscription_status = 'active'
ORDER BY city, name;
-- Товары из нескольких категорий, отсортированные по цене
SELECT name, category, price
FROM products
WHERE category IN ('Электроника', 'Гаджеты', 'Аксессуары')
AND price < 10000
ORDER BY price DESC
LIMIT 20;
Комбинация IN + AND + другие условия — стандартный паттерн при составлении реальных запросов к базам данных приложений.
IN и производительность
IN с небольшим списком (до 20-30 значений) оптимизируется СУБД так же, как OR. PostgreSQL умеет использовать индексы для IN-запросов. Если список содержит сотни значений, СУБД может использовать другую стратегию — например, HashSet.
Если список значений очень большой (1000+ элементов), и он формируется динамически из другой таблицы — лучше использовать IN с подзапросом или JOIN. Оба подхода разберём в соответствующих модулях.
Краткий итог
IN (val1, val2, ...)— строки, где значение совпадает хотя бы с одним из спискаNOT IN (val1, val2, ...)— строки, где значение не совпадает ни с одним из спискаINчитабельнее цепочкиORпри нескольких значениях одной колонкиNULLв спискеNOT INделает запрос бесполезным — он вернёт 0 строк- Список в
INможет быть подзапросом:IN (SELECT ...) INхорошо сочетается сANDи другими условиями вWHERE
Что дальше
Вы умеете проверять вхождение в список. Следующая задача — диапазоны значений: не «одно из нескольких», а «от X до Y». Оператор BETWEEN делает это компактно и читабельно.