NOT EXISTS против NOT IN: как безопасно искать строки, которых нет в другой таблице

В SQL часто встречается задача: найти записи, у которых нет пары в другой таблице.

Например:

• пользователи, которые ещё ни разу не сделали заказ;
• сотрудники, у которых нет подчинённых;
• товары, которые ни разу не покупали;
• студенты, которые не сдали ни одного экзамена.

На первый взгляд кажется, что для этого идеально подходит NOT IN:

SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (
    SELECT user_id
    FROM orders
);

Запрос читается почти как обычный русский текст:

“Выбери пользователей, чей id не находится среди user_id из заказов”.

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

Без ошибки. Без предупреждения. Просто запрос внезапно вернёт не то, что вы ожидали.

Разберёмся спокойно и по шагам.

---

Задача: найти пользователей без заказов

Представим две таблицы.

Таблица users:

id	email
1	anna@example.com
2	boris@example.com
3	katya@example.com

Таблица orders:

id	user_id	amount
101	1	1500
102	NULL	900

Что здесь происходит?

Пользователь с id = 1 сделал заказ. А пользователи с id = 2 и id = 3 заказов не делали.

Значит, мы хотим получить:

id	email
2	boris@example.com
3	katya@example.com

Интуитивно можно написать так:

SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (
    SELECT user_id
    FROM orders
);

Но из-за NULL в orders.user_id такой запрос может вернуть пустой результат.

То есть вообще ничего.

---

Почему NOT IN ломается из-за NULL

Чтобы понять проблему, нужно вспомнить важную вещь:

NULL в SQL — это не значение “пусто”. NULL означает “неизвестно”.

Если мы спрашиваем базу:

1 <> NULL

она не может ответить TRUE или FALSE.

Почему?

Потому что NULL — это неизвестное значение. Может быть, там 1. А может быть, 5. А может быть, вообще что-то другое.

Поэтому результат такого сравнения — не TRUE и не FALSE, а:

UNKNOWN

В SQL есть трёхзначная логика:

• TRUE — истина;
• FALSE — ложь;
• UNKNOWN — неизвестно.

Теперь посмотрим, как работает NOT IN.

Запрос:

WHERE u.id NOT IN (1, NULL)

по смыслу похож на такую проверку:

WHERE u.id <> 1
  AND u.id <> NULL

Возьмём пользователя с id = 2.

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

2 <> 1

это TRUE.

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

2 <> NULL

это UNKNOWN.

Итог:

TRUE AND UNKNOWN

даёт UNKNOWN.

А в WHERE строка проходит только тогда, когда условие равно TRUE.

Если условие равно FALSE — строка отбрасывается. Если условие равно UNKNOWN — строка тоже отбрасывается.

Поэтому пользователь с id = 2 не попадёт в результат.

С пользователем id = 3 произойдёт то же самое.

И в итоге запрос вернёт пустоту, хотя пользователи без заказов в таблице есть.

---

Проблемный пример

Вот тот самый опасный вариант:

SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (
    SELECT user_id
    FROM orders
);

Он выглядит нормально, но становится опасным, если orders.user_id может содержать NULL.

А такое бывает чаще, чем кажется:

• заказ создали без привязанного пользователя;
• пользователь был удалён, а заказ остался;
• данные приехали из внешней системы;
• колонку сделали nullable “на всякий случай”;
• старые данные не до конца вычищены.

Главная проблема в том, что запрос не упадёт с ошибкой. Он просто начнёт возвращать неправильный результат.

Именно поэтому NOT IN с подзапросами часто считают небезопасным вариантом.

---

Можно ли починить NOT IN?

Да, можно явно убрать NULL из подзапроса:

SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (
    SELECT user_id
    FROM orders
    WHERE user_id IS NOT NULL
);

Теперь внутри NOT IN будут только настоящие значения:

1

А NULL туда не попадёт.

Такой запрос уже будет работать корректно.

Но у этого подхода есть минус: об этом легко забыть.

Сегодня вы написали фильтр IS NOT NULL, а завтра другой разработчик написал похожий запрос без него. Или вы работаете не с одним столбцом, а с составным ключом из нескольких колонок — там ошибиться ещё проще.

Поэтому в реальной работе для таких задач чаще выбирают другой вариант — NOT EXISTS.

---

NOT EXISTS: безопасный вариант по умолчанию

Для поиска строк без совпадения лучше использовать NOT EXISTS.

Вот правильный запрос для пользователей без заказов:

SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

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

“Выбери пользователя, для которого не существует ни одного заказа с таким же user_id”.

Это именно то, что нам нужно.

---

Почему NOT EXISTS не боится NULL

NOT EXISTS не сравнивает значение пользователя со всем списком значений из подзапроса.

Он работает иначе.

Для каждого пользователя база проверяет:

“Есть ли хотя бы одна строка в orders, где o.user_id = u.id?”

Если такая строка есть — пользователь заказ делал. Если такой строки нет — пользователь заказ не делал.

Посмотрим на условие:

WHERE o.user_id = u.id

Если в orders.user_id лежит NULL, сравнение будет таким:

NULL = 2

Результат — UNKNOWN.

Такая строка просто не считается совпадением.

И это нормально. Она не ломает весь запрос. Она просто не подходит под условие.

Поэтому NOT EXISTS спокойно вернёт пользователей без заказов:

id	email
2	boris@example.com
3	katya@example.com

---

Что означает SELECT 1 внутри EXISTS

Новички часто спрашивают:

SELECT 1

Почему именно 1? Почему не SELECT *? Почему не SELECT o.id?

Внутри EXISTS базе не важны сами данные из строки. Ей важен только факт:

существует строка или нет?

Поэтому обычно пишут:

SELECT 1

Это привычная SQL-идиома.

Такой запрос не означает “выбери число 1”. Он означает: “мне не нужны колонки, мне важен только факт существования строки”.

Можно было бы написать и так:

WHERE NOT EXISTS (
    SELECT *
    FROM orders o
    WHERE o.user_id = u.id
);

Результат был бы тем же. Но SELECT 1 выглядит аккуратнее и сразу показывает намерение разработчика.

---

Ещё один пример: сотрудники без подчинённых

Допустим, есть таблица сотрудников employees.

В ней есть поля:

• id — идентификатор сотрудника;
• name — имя;
• manager_id — id руководителя.

Пример:

id	name	manager_id
1	Анна	NULL
2	Борис	1
3	Катя	1
4	Дима	2

Анна — руководитель Бориса и Кати. Борис — руководитель Димы. Катя и Дима никем не руководят.

Чтобы найти сотрудников, у которых нет подчинённых, можно написать так:

SELECT e.id, e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM employees s
    WHERE s.manager_id = e.id
);

Здесь таблица employees используется два раза:

• FROM employees e — это сотрудник, которого мы проверяем;
• FROM employees s — это возможный подчинённый.

Условие:

s.manager_id = e.id

означает:

“Найди сотрудника s, у которого руководитель — текущий сотрудник e”.

А NOT EXISTS говорит:

“Оставь только тех сотрудников, для которых таких подчинённых не существует”.

В результате получим Катю и Диму.

---

Третий способ: LEFT JOIN ... IS NULL

Есть ещё один популярный способ найти строки без совпадения — через LEFT JOIN.

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
WHERE o.user_id IS NULL;

Что здесь происходит?

LEFT JOIN берёт всех пользователей из users и пытается найти им заказы в orders.

Если заказ найден, поля из orders заполняются. Если заказ не найден, поля из orders становятся NULL.

Поэтому потом мы пишем:

WHERE o.user_id IS NULL

То есть оставляем только тех пользователей, для которых заказ не нашёлся.

Такой вариант тоже часто работает правильно и тоже не ломается из-за NULL в orders.user_id.

---

Важная ошибка с LEFT JOIN

Когда используете LEFT JOIN ... IS NULL, проверяйте NULL именно по колонке из правой таблицы, которая показывает факт соединения.

Хорошо:

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
WHERE o.user_id IS NULL;

Опаснее:

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
WHERE o.comment IS NULL;

Почему второй вариант плохой?

Потому что o.comment может быть NULL даже у существующего заказа.

Например, заказ есть, но комментарий к нему не оставили. Тогда пользователь попадёт в результат, хотя заказ у него на самом деле был.

Поэтому для анти-соединения проверяйте NULL по колонке, которая участвует в связи, или по надёжному обязательному полю правой таблицы, например o.id:

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
WHERE o.id IS NULL;

Если orders.id — первичный ключ и не может быть NULL, это тоже хороший вариант.

---

NOT EXISTS или LEFT JOIN: что выбрать?

Оба варианта могут быть правильными.

Но для начинающего разработчика лучше запомнить простое правило:

Для поиска строк без совпадения по умолчанию используйте NOT EXISTS.

Почему?

Потому что он:

• хорошо читается;
• безопасен при NULL;
• не требует вручную фильтровать NULL в подзапросе;
• удобно работает со сложными условиями;
• хорошо оптимизируется современными базами данных.

Например, если условие совпадения состоит не из одной колонки, а из нескольких, NOT EXISTS остаётся понятным:

SELECT p.id, p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.id
      AND oi.created_at >= DATE '2024-01-01'
);

Такой запрос можно прочитать так:

“Найди товары, которые ни разу не встречались в заказах с 1 января 2024 года”.

---

А когда можно использовать NOT IN?

NOT IN не нужно запрещать полностью. Он нормален, когда вы работаете с небольшим статическим списком, где точно нет NULL.

Например:

SELECT id, status, amount
FROM orders
WHERE status NOT IN ('paid', 'shipped', 'cancelled');

Здесь список задан руками: ('paid', 'shipped', 'cancelled'). В нём нет NULL, поэтому ловушки нет.

Такой запрос читается хорошо:

“Покажи заказы, статус которых не входит в список оплаченных, отправленных и отменённых”.

Но если справа находится подзапрос, особенно по nullable-колонке, лучше выбрать NOT EXISTS.

Потенциально опасно:

SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (
    SELECT user_id
    FROM orders
);

Безопаснее:

SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

---

Что с производительностью?

В современных базах данных NOT EXISTS обычно оптимизируется хорошо.

Например, PostgreSQL умеет превращать такие запросы во внутренний план типа Anti Join. Это специальный способ выполнения запроса, когда база ищет строки без совпадения в другой таблице.

Упрощённо это можно представить так:

“Пробеги по пользователям и быстро проверь, есть ли для каждого хотя бы один заказ. Если есть — дальше можно не искать. Если нет — добавь пользователя в результат”.

Чтобы такой запрос работал быстрее, полезно иметь индекс на колонке, по которой идёт сопоставление.

Для нашего примера:

CREATE INDEX idx_orders_user_id
ON orders(user_id);

Тогда запрос:

SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

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

Индекс особенно важен, если таблица orders большая.

---

Короткая шпаргалка

Используйте NOT EXISTS, когда ищете строки без совпадения

SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Это безопасный вариант по умолчанию.

Осторожно используйте NOT IN с подзапросами

SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (
    SELECT user_id
    FROM orders
);

Если orders.user_id содержит NULL, результат может стать неправильным.

Если всё-таки используете NOT IN, убирайте NULL

SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (
    SELECT user_id
    FROM orders
    WHERE user_id IS NOT NULL
);

Так безопаснее, но всё равно требует внимательности.

LEFT JOIN ... IS NULL тоже подходит

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
WHERE o.id IS NULL;

Главное — проверять NULL по надёжной колонке из правой таблицы.

---

Главное правило

Если нужно найти строки, для которых нет связанной записи в другой таблице, выбирайте NOT EXISTS.

Например:

SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

NOT IN хорошо выглядит, но с подзапросами может быть опасен из-за NULL.

LEFT JOIN ... IS NULL тоже рабочий вариант, но в нём легче ошибиться с колонкой для проверки.

Поэтому простое правило для практики такое:

NOT EXISTS — основной выбор для поиска отсутствующих строк. NOT IN — только для списков, где вы точно контролируете значения. LEFT JOIN ... IS NULL — нормальная альтернатива, если она лучше вписывается в ваш запрос.

Перед тем как отправлять такой запрос в работу, задайте себе два вопроса:

1. Может ли колонка справа содержать NULL?
2. Есть ли индекс по колонке, по которой таблицы сопоставляются?

Если вы используете NOT EXISTS и не забыли про индекс, запрос будет не только корректным, но и предсказуемым по производительности.