Анти-джойн в SQL: как найти строки без пары

Анти-джойн — это приём в SQL, который отвечает на простой вопрос:

«Какие строки из одной таблицы не имеют подходящей строки в другой таблице?»

Например:

• пользователи без заказов;
• товары без продаж;
• заказы без платежей;
• сотрудники без менеджера;
• курсы, на которые никто не записался;
• клиенты, которые давно ничего не покупали.

То есть обычный JOIN помогает найти совпадения, а анти-джойн — наоборот, найти отсутствующие совпадения.

В большинстве SQL-баз нет отдельного ключевого слова ANTI JOIN. Поэтому анти-джойн обычно записывают одним из трёх способов:

1. через LEFT JOIN и IS NULL;
2. через NOT EXISTS;
3. через NOT IN.

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

Разберём всё на понятном примере.

Исходные таблицы

Допустим, у нас есть интернет-магазин. В нём есть пользователи и заказы.

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

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  user_id bigint,
  amount numeric NOT NULL
);

В таблице users лежат пользователи:

id	email
1	alice@example.com
2	bob@example.com
3	kate@example.com
4	max@example.com

В таблице orders лежат заказы:

id	user_id	amount
101	1	1200
102	1	800
103	3	2500
104	NULL	500

Обратите внимание: у заказа 104 в поле user_id стоит NULL. Пусть это будет гостевой заказ, который не привязан к зарегистрированному пользователю.

Наша задача:

Найти пользователей, у которых нет ни одного заказа.

По данным выше это будут:

id	email
2	bob@example.com
4	max@example.com

Теперь посмотрим, как получить такой результат в SQL.

Способ 1: 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.id IS NULL;

Как это работает:

1. LEFT JOIN берёт всех пользователей из users.
2. Если у пользователя есть заказы, SQL подставляет строки из orders.
3. Если заказов нет, SQL всё равно оставляет пользователя, но вместо колонок заказа ставит NULL.
4. Условие WHERE o.id IS NULL оставляет только тех пользователей, для которых заказ не нашёлся.

То есть логика такая:

«Покажи всех пользователей, но оставь только тех, у кого правая таблица не подставилась».

Это и есть анти-джойн.

Почему проверяем именно o.id IS NULL

В запросе выше мы проверяем:

WHERE o.id IS NULL

А не так:

WHERE o.user_id IS NULL

Это важная деталь.

o.id — первичный ключ заказа. В настоящей строке заказа он не может быть NULL. Поэтому если после LEFT JOIN мы видим o.id IS NULL, значит строки заказа действительно не было.

А вот o.user_id в нашей таблице может быть NULL, потому что бывают гостевые заказы. Если проверять nullable-колонку, запрос становится менее надёжным и его сложнее читать.

Хорошее правило:

В LEFT JOIN ... IS NULL проверяйте колонку из правой таблицы, которая точно не бывает NULL в реальных данных. Обычно это первичный ключ.

Пример с отладкой LEFT JOIN

LEFT JOIN удобен тем, что его легко «развернуть» и посмотреть, что происходит.

Например, сначала можно выполнить запрос без фильтра:

SELECT
  u.id,
  u.email,
  o.id AS order_id,
  o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.id, o.id;

Результат будет примерно таким:

id	email	order_id	amount
1	alice@example.com	101	1200
1	alice@example.com	102	800
2	bob@example.com	NULL	NULL
3	kate@example.com	103	2500
4	max@example.com	NULL	NULL

Теперь видно глазами: у bob@example.com и max@example.com заказов нет. Поэтому после добавления WHERE o.id IS NULL останутся только они.

Плюсы и минусы LEFT JOIN

У LEFT JOIN ... IS NULL есть сильные стороны:

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

Но есть и минусы:

• смысл «найти отсутствующие строки» выражен не напрямую;
• можно ошибиться и проверить nullable-колонку;
• на составных условиях запрос становится длиннее;
• при сложных JOIN легко случайно поменять логику фильтрации.

Поэтому LEFT JOIN ... IS NULL — рабочий способ, но не всегда самый аккуратный.

Способ 2: 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 после LEFT JOIN, не думаем, какую колонку справа лучше проверить. Мы просто говорим SQL:

«Оставь строку, если подходящей строки в другой таблице нет».

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

Новички часто спрашивают: почему внутри подзапроса пишут SELECT 1?

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

Потому что EXISTS проверяет не сами значения, а факт существования строки.

Ему всё равно, что именно написано после SELECT: 1, *, o.id или o.amount. Важен только вопрос:

«Вернул ли подзапрос хотя бы одну строку?»

Если вернул — EXISTS даёт TRUE.

Если не вернул — EXISTS даёт FALSE.

А NOT EXISTS переворачивает результат:

• заказ есть — пользователь не подходит;
• заказа нет — пользователь подходит.

Поэтому SELECT 1 — просто короткая привычная запись. Она показывает: «нам не нужны данные из подзапроса, нам нужен только факт существования».

Почему NOT EXISTS безопаснее при NULL

Главная проблема анти-джойнов — NULL.

Но NOT EXISTS с ним справляется спокойно.

В нашем примере в таблице orders есть гостевой заказ:

id	user_id	amount
104	NULL	500

Когда SQL проверяет пользователя, он выполняет условие:

o.user_id = u.id

Если o.user_id равен NULL, сравнение не становится истинным. Такая строка просто не считается совпадением.

И это именно то, что нам нужно: гостевой заказ не должен мешать поиску пользователей без заказов.

Поэтому NOT EXISTS обычно считается самым безопасным и понятным способом записать анти-джойн.

NOT EXISTS с несколькими условиями

Ещё одно преимущество NOT EXISTS хорошо видно на составных условиях.

Допустим, заказ привязан не только к пользователю, но и к региону. Нужно найти пользователей, у которых нет заказа в их регионе.

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

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

В варианте с LEFT JOIN это тоже можно написать, но при усложнении условий NOT EXISTS обычно выглядит спокойнее и надёжнее.

Производительность NOT EXISTS

В PostgreSQL оптимизатор обычно понимает, что вы пишете анти-джойн.

То есть NOT EXISTS и LEFT JOIN ... IS NULL часто превращаются во внутреннем плане выполнения в один и тот же тип операции: например, в Hash Anti Join.

На практике это значит: не нужно выбирать LEFT JOIN только потому, что он якобы быстрее. В современных базах нормальный NOT EXISTS обычно оптимизируется хорошо.

Если есть индекс по колонке связи, например по orders.user_id, запросу будет проще искать совпадения.

CREATE INDEX idx_orders_user_id
ON orders (user_id);

Такой индекс полезен и для обычных соединений, и для анти-джойнов.

Способ 3: NOT IN

Третий вариант выглядит самым коротким:

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

На первый взгляд всё прекрасно:

«Покажи пользователей, чьих id нет среди user_id в заказах».

Но именно этот вариант чаще всего ломается из-за NULL.

Ловушка NOT IN и NULL

В нашей таблице orders.user_id может быть NULL.

Подзапрос вернёт примерно такой список:

1
1
3
NULL

И тогда условие становится похожим на:

u.id NOT IN (1, 1, 3, NULL)

Проблема в том, что SQL использует трёхзначную логику:

• TRUE;
• FALSE;
• UNKNOWN.

Сравнение с NULL не даёт ни TRUE, ни FALSE. Оно даёт UNKNOWN.

Например:

SELECT 5 <> NULL;

Результат не будет TRUE. Он будет неизвестным, потому что NULL означает «значение неизвестно».

А NOT IN фактически превращается в цепочку сравнений через AND:

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

Последняя часть даёт UNKNOWN. Из-за этого всё выражение не становится TRUE, и строка не проходит фильтр WHERE.

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

Это не баг. Это стандартное поведение SQL.

Как сделать NOT IN безопаснее

Если вы всё-таки хотите использовать NOT IN, нужно явно убрать NULL из подзапроса:

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

Теперь подзапрос вернёт только реальные значения user_id, и ловушка с NULL исчезнет.

Но проблема в том, что такой фильтр легко забыть. Сегодня колонка user_id не содержит NULL, завтра бизнес-логика поменялась, появились гостевые заказы — и старый запрос начал давать неверный результат.

Поэтому для анти-джойна по nullable-колонкам лучше сразу использовать NOT EXISTS.

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

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

Например, если вы сравниваете с первичным ключом:

SELECT
  p.id,
  p.title
FROM products p
WHERE p.category_id NOT IN (
  SELECT c.id
  FROM categories c
);

Если categories.id — первичный ключ, он не может быть NULL. В такой ситуации NOT IN не провалится из-за NULL в списке.

Но всё равно нужно быть внимательным: если сомневаетесь, лучше выбрать NOT EXISTS.

Сравнение трёх способов

Посмотрим на все варианты рядом.

Через LEFT JOIN:

SELECT
  u.id,
  u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS 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:

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

Все три могут дать правильный результат. Но по надёжности и читаемости чаще всего выигрывает NOT EXISTS.

Как выбрать правильный вариант

Обычно можно пользоваться таким правилом:

Ситуация	Что выбрать
Нужен безопасный вариант по умолчанию	NOT EXISTS
Нужно наглядно показать отсутствие строки справа	LEFT JOIN ... IS NULL
Нужно отладить соединение и посмотреть колонки справа	LEFT JOIN ... IS NULL
Подзапрос может вернуть NULL	не использовать NOT IN
Подзапрос точно возвращает NOT NULL	NOT IN допустим
Условие связи составное	чаще удобнее NOT EXISTS

Для реальной разработки самый спокойный выбор — NOT EXISTS.

Он хорошо читается, не боится NULL в правой таблице и прямо говорит, что вы ищете строки без пары.

Частая ошибка: фильтр по правой таблице в WHERE

С LEFT JOIN есть ещё одна ловушка.

Допустим, нужно найти пользователей, у которых нет оплаченных заказов.

Можно случайно написать так:

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

Такой запрос противоречит сам себе.

С одной стороны, он требует:

o.status = 'paid'

То есть строка заказа должна существовать.

С другой стороны, он требует:

o.id IS NULL

То есть строки заказа быть не должно.

Правильнее перенести условие по правой таблице в ON:

SELECT
  u.id,
  u.email
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
 AND o.status = 'paid'
WHERE o.id IS 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
    AND o.status = 'paid'
);

То есть:

«Выбери пользователей, у которых не существует оплаченного заказа».

Пример: товары без продаж

Анти-джойн нужен не только для пользователей и заказов.

Допустим, есть товары и позиции заказов. Нужно найти товары, которые ни разу не продавались.

SELECT
  p.id,
  p.name
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items oi
  WHERE oi.product_id = p.id
);

Такой запрос полезен для склада: можно найти товары, которые занимают место, но не приносят продаж.

Пример: заказы без платежей

Ещё один частый пример — найти заказы, по которым нет платежа.

SELECT
  o.id,
  o.user_id,
  o.amount
FROM orders o
WHERE NOT EXISTS (
  SELECT 1
  FROM payments p
  WHERE p.order_id = o.id
);

Такой запрос может показать проблемные заказы: пользователь оформил заказ, но оплата не прошла или запись о платеже не создалась.

Пример: клиенты без активности

Можно искать пользователей, у которых не было событий после определённой даты.

SELECT
  u.id,
  u.email
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM events e
  WHERE e.user_id = u.id
    AND e.created_at >= DATE '2025-01-01'
);

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

Обратите внимание: условие по дате находится внутри подзапроса. Это важно, потому что мы ищем отсутствие не любых событий, а именно событий после нужной даты.

Анти-джойн и индексы

Чтобы анти-джойн работал быстрее на больших таблицах, обычно нужен индекс на колонке, по которой ищется совпадение.

Если мы часто ищем пользователей без заказов, полезен индекс:

CREATE INDEX idx_orders_user_id
ON orders (user_id);

Если ищем пользователей без оплаченных заказов, может пригодиться составной индекс:

CREATE INDEX idx_orders_user_id_status
ON orders (user_id, status);

Индекс не меняет смысл запроса. Он просто помогает базе быстрее проверять, есть ли подходящая строка справа.

Что с ClickHouse, MySQL и другими СУБД

В PostgreSQL анти-джойн обычно пишут через NOT EXISTS или LEFT JOIN ... IS NULL.

В MySQL логика с NULL у NOT IN такая же опасная: если подзапрос возвращает NULL, результат может оказаться неожиданным.

В ClickHouse есть явный синтаксис LEFT ANTI JOIN, то есть там анти-джойн можно выразить напрямую:

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

Но если вы пишете обычный SQL для PostgreSQL, самый универсальный и понятный вариант — NOT EXISTS.

Главное

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

В SQL его чаще всего записывают через NOT EXISTS, LEFT JOIN ... IS NULL или NOT IN.

Лучший вариант по умолчанию — NOT EXISTS. Он читается почти как техническое задание: «не существует подходящей строки». Он хорошо работает с NULL и удобно расширяется на несколько условий.

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

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

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

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