Фильтрация данных с помощью IN и NOT IN

Представьте, что вы работаете в базе данных университета и вам нужно найти студентов, которые учатся на конкретных курсах. Например, на "Программировании", "Математике" и "Физике". Конечно, вы можете написать длинный запрос со множеством условий вроде:

SELECT *
FROM students 
WHERE course = 'Программирование'
   OR course = 'Математика'
   OR course = 'Физика';

Но давайте будем честными. Писать такие конструкции утомительно и выглядят они не слишком элегантно. К счастью, на помощь приходит оператор IN, который позволяет записать тот же запрос компактно и сэкономить время:

SELECT *
FROM students 
WHERE course IN ('Программирование', 'Математика', 'Физика');

Звучит как магия, не так ли? Вместо множества условий OR мы просто говорим SQL искать значения в этом списке. А если нужно проверить, что значение не входит в список, используем NOT IN — Искать всё, что не в этом списке.

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

Вот общий синтаксис оператора IN:

SELECT колонки
FROM таблица
WHERE колонка IN (значение1, значение2, значение3, ...);

Теперь давайте рассмотрим некоторые примеры.

Пример 1: Студенты, изучающие несколько курсов

Допустим, у нас есть таблица students:

Мы хотим найти всех студентов, которые изучают "Программирование", "Математику" или "Физику". Используем IN:

SELECT name, course
FROM students
WHERE course IN ('Программирование', 'Математика', 'Физика');

Результат:

Как видите, оператор IN значительно упростил задачу. Нам не нужно писать длинные условия через OR, просто указываем список значений, которые нас интересуют.

Пример 2: Студенты, не изучающие определенные курсы

Теперь, предположим, что вы хотите найти студентов, которые не изучают "Программирование", "Математику" и "Физику". Для этого пригодится оператор NOT IN:

SELECT name, course
FROM students
WHERE course NOT IN ('Программирование', 'Математика', 'Физика');

Результат:

Таким образом, оператор NOT IN возвращает все строки, где значения столбца course не входят в указанный список.

Использование IN и NOT IN с подзапросами

Операторы IN и NOT IN особенно полезны, когда нужно сравнить данные между двумя таблицами. Например, пусть у нас есть две таблицы:

Таблица students:

Таблица courses:

Представим, что нам нужно найти студентов, которые зарегистрированы на курсы, существующие в таблице courses. Здесь на помощь приходит вложенный запрос с использованием IN:

SELECT name
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
);

Этот запрос работает следующим образом: подзапрос SELECT id FROM courses возвращает список всех идентификаторов курсов. Затем оператор IN проверяет, есть ли course_id в этом списке.

Результат:

Почему пропустился Dan? Потому что его course_id (104) отсутствует в таблице courses.

Особенности работы с NULL

Оператор IN имеет одну важную особенность: если в списке значений встречается NULL, это может повлиять на результат запроса. Рассмотрим пример.

Таблица grades:

Запрос, который ищет студентов с оценкой в ('A', 'B', 'C'), может выглядеть так:

SELECT student_id
FROM grades
WHERE grade IN ('A', 'B', 'C');

Результат:

Запись с NULL в столбце grade игнорируется, потому что NULL не считается частью любого списка.

Теперь представьте, что вы используете оператор NOT IN. Например:

SELECT student_id
FROM grades
WHERE grade NOT IN ('A', 'B', 'C');

Вы ожидаете увидеть строку с student_id = 2, но результат будет пустым! Почему? Потому что NULL сравнивается с каждым значением в списке, и результат такого сравнения всегда неопределенный (UNKNOWN). Это поведение может сбить с толку, поэтому, работая с NOT IN, следует учитывать наличие столбцов с NULL. Лучшим вариантом в таких случаях может быть использование явной проверки на NULL:

SELECT student_id
FROM grades
WHERE grade NOT IN ('A', 'B', 'C')
   OR grade IS NULL;

Результат:

Рекомендации по использованию IN и NOT IN

Используйте IN для улучшения читаемости вашего SQL-кода
Если вам нужно проверить, входит ли столбец в определенный список значений, всегда отдавайте предпочтение IN, а не множеству условий с OR.

Будьте осторожны с NOT IN и NULL
Если в данных есть столбцы с NULL, они могут вызвать неожиданные результаты. Лучше явно обработать NULL при использовании NOT IN.

Используйте индексы для ускорения подзапросов
Если IN используется с вложенным запросом, убедитесь, что столбец в подзапросе индексирован, чтобы избежать проблем с производительностью.

Пример реальной задачи

Представьте, что вы работаете в системе интернет-магазина. У вас есть таблицы orders и users. Вы хотите найти всех пользователей, которые никогда не делали заказ.

Таблица users:

Таблица orders:

Используем NOT IN для решения задачи:

SELECT name
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM orders
);

Результат:

Этот запрос работает так: сначала подзапрос SELECT user_id FROM orders возвращает идентификаторы всех пользователей, которые сделали заказы (1 и 3). Затем оператор NOT IN исключает их, оставляя только тех, кто не делал заказов (Mello и Dan).