SQL Anti-Joins: Finding Rows With No Match

Анти-джойн — это запрос, который отвечает на вопрос «какие строки из таблицы A не имеют пары в таблице B». Пользователи без заказов, заказы без платежей, сотрудники без менеджера. В SQL нет ключевого слова ANTI JOIN, поэтому его выражают тремя классическими способами. Все три дают «правильный» результат на чистых данных, но ведут себя по-разному, когда в игру вступают NULL. Разберём их на одной схеме и выясним, какой выбрать.

Работаем с такими таблицами (PostgreSQL):

CREATE TABLE users (
  id    bigint PRIMARY KEY,
  email text NOT NULL
);

CREATE TABLE orders (
  id       bigint PRIMARY KEY,
  user_id  bigint,          -- может быть NULL: «гостевой» заказ
  amount   numeric NOT NULL
);

Задача-сквозняк: найти пользователей, у которых нет ни одного заказа.

LEFT JOIN ... WHERE b.id IS NULL

Самый наглядный приём. Делаем LEFT JOIN, который сохраняет все строки users, а затем выбрасываем те, где совпадение нашлось.

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;   -- совпадения не было => вся правая сторона NULL

Как это читать: LEFT JOIN подставляет NULL во все колонки orders, когда пары нет. Условие WHERE o.id IS NULL оставляет ровно эти «несовпавшие» строки. Важная деталь — проверять на IS NULL нужно колонку, которая никогда не бывает NULL в реальной строке: первичный ключ o.id идеален. Если взять o.user_id (а он у нас nullable), вы можете случайно поймать гостевые заказы и получить мусор.

• Плюс: интуитивно, легко добавить колонки из orders для отладки.
• Минус: семантика «убери совпавшее» не очевидна тому, кто читает запрос позже.
• Подвох: матчинг по нескольким колонкам делает условие IS NULL многословным и хрупким.

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
);

Почему это обычно лучший выбор:

• Читается как ТЗ. «Нет такого заказа» — и точка.
• Безопасен к NULL. EXISTS проверяет только наличие строк, а не сравнивает значения, поэтому NULL в o.user_id не ломает логику: такая строка просто не удовлетворяет o.user_id = u.id и не считается совпадением.
• Производительность. Планировщик PostgreSQL выполняет и NOT EXISTS, и LEFT JOIN / IS NULL через один и тот же anti join (например, Hash Anti Join в EXPLAIN). На практике они эквивалентны по скорости; NOT EXISTS выигрывает на составных ключах.

Составной ключ выглядит ровно — без россыпи IS NULL:

WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.region = u.region
)

NOT IN и ловушка NULL

NOT IN выглядит компактно и соблазнительно, но это самый коварный вариант.

-- ОПАСНО, если orders.user_id содержит NULL
SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

Если в подзапросе окажется хотя бы один NULL (а user_id у нас nullable — гостевые заказы!), запрос вернёт ноль строк. Это не баг СУБД, а трёхзначная логика SQL. x NOT IN (1, 2, NULL) разворачивается в x <> 1 AND x <> 2 AND x <> NULL. Последнее сравнение даёт UNKNOWN, и всё выражение никогда не становится TRUE — строка молча отбрасывается.

• Подвох: ошибка тихая. Запрос не падает, просто отдаёт пустой (или урезанный) результат.
• Если уверены, что значений NULL нет, добавьте явный фильтр — но это легко забыть при изменении данных:

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

Различия по СУБД:

• MySQL ведёт себя так же — та же трёхзначная логика, тот же провал на NULL.
• ClickHouse тоже подвержен; плюс там у JOIN свои особенности, поэтому NOT IN с гарантированно не-NULL колонкой (или LEFT ANTI JOIN) часто чище. К слову, ClickHouse — одна из немногих СУБД с явным синтаксисом LEFT ANTI JOIN.

Что выбрать

Короткое правило:

• По умолчанию — NOT EXISTS. Выразителен, безопасен к NULL, оптимизатор разворачивает его в anti join. Это ваш дефолт.
• LEFT JOIN ... IS NULL — когда хочется заодно вытащить колонки из правой таблицы или вам так читается понятнее. Проверяйте IS NULL по не-nullable ключу.
• NOT IN — только когда столбец подзапроса гарантированно NOT NULL (например, первичный ключ). Иначе явно фильтруйте NULL или просто не используйте.

Финальный чек-лист: подзапрос может вернуть NULL? Если да — не NOT IN. Нужны поля справа? Тогда LEFT JOIN. В остальных случаях пишите NOT EXISTS и не думайте о трёхзначной логике вообще.