SQLPostgreSQLJOINсобеседованиеаналитика данных

ANTI JOIN и LATERAL JOIN в PostgreSQL: когда нужны

2026-07-20 10 мин

# ANTI JOIN и LATERAL JOIN в PostgreSQL: когда и как использовать

Коротко: ANTI JOIN возвращает строки одной таблицы, для которых нет пары во второй. В PostgreSQL его пишут через NOT EXISTS или LEFT JOIN ... WHERE правая_колонка IS NULL. LATERAL JOIN — это подзапрос, который может ссылаться на колонки внешней таблицы, то есть выполняется отдельно для каждой её строки. Он нужен, когда обычным JOIN задачу не решить: например, «взять по 3 последних заказа для каждого клиента» (top-N на группу).

Обе конструкции постоянно всплывают на собеседованиях аналитика и в реальных задачах: «найди клиентов без заказов», «покажи товары, которые ни разу не купили», «топ-3 события на пользователя». Ниже — синтаксис, данные, числовые примеры и разбор производительности.

Что такое ANTI JOIN и почему его нет как ключевого слова?

В стандарте SQL нет оператора ANTI JOIN — это логическое понятие, а не синтаксис. ANTI JOIN — противоположность SEMI JOIN: SEMI возвращает строки, у которых есть совпадение, ANTI — у которых совпадения нет. Формулой: если A и B — множества, то

ANTI JOIN = A − (A ∩ B)   — строки A, для которых нет пары в B

Классический кейс — «клиенты без заказов». Возьмём две таблицы.

CREATE TABLE customers (
    id   int PRIMARY KEY,
    name text
);

CREATE TABLE orders (
    id          int PRIMARY KEY,
    customer_id int,
    amount      numeric
);

INSERT INTO customers VALUES
    (1, 'Анна'), (2, 'Борис'), (3, 'Вера'), (4, 'Глеб');

INSERT INTO orders VALUES
    (10, 1, 1500),
    (11, 1, 2300),
    (12, 3,  900);

Заказы есть у клиентов 1 и 3. Значит ANTI JOIN должен вернуть ровно двух клиентов: Бориса (id=2) и Глеба (id=4).

Как написать ANTI JOIN через LEFT JOIN ... IS NULL?

Первый способ — LEFT JOIN с фильтром IS NULL. Идея: делаем левое соединение (все клиенты + подтянутые заказы), а затем оставляем только те строки, где заказ не подтянулся — то есть его колонка равна NULL.

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;

Результат:

idname
2Борис
4Глеб

Важно: в WHERE проверяйте колонку из правой таблицы, которая гарантированно не бывает NULL при совпадении — обычно это ключ соединения или первичный ключ (o.id, o.customer_id). Если возьмёте nullable-колонку (например o.amount), можно случайно отфильтровать строки, где заказ есть, но сумма пустая.

Чем NOT EXISTS отличается от LEFT JOIN ... IS NULL?

Второй способ — NOT EXISTS с коррелированным подзапросом. Он читается как прямое описание задачи: «клиенты, для которых не существует заказа».

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

Результат тот же — Борис и Глеб. В PostgreSQL планировщик обычно превращает оба варианта в один и тот же физический план — Anti Join (в EXPLAIN вы увидите узел Hash Anti Join или Nested Loop Anti Join). Поэтому по скорости NOT EXISTS и LEFT JOIN ... IS NULL чаще всего эквивалентны.

Разница проявляется в двух ситуациях:

Почему NOT IN опасен при NULL?

Третий способ, который приходит в голову новичкам, — NOT IN с подзапросом. И это главный подвох на собеседовании.

-- ОПАСНО, если в подзапросе может быть NULL
SELECT c.id, c.name
FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders);

Пока customer_id в заказах не содержит NULL, запрос работает. Но добавим одну «битую» строку:

INSERT INTO orders VALUES (13, NULL, 500);

Теперь запрос с NOT IN вернёт ноль строк — вообще пусто, хотя Борис и Глеб по-прежнему без заказов. Причина в трёхзначной логике SQL. Выражение c.id NOT IN (1, 3, NULL) разворачивается в

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

а c.id <> NULL даёт не TRUE/FALSE, а UNKNOWN. ... AND UNKNOWN никогда не станет TRUE, поэтому ни одна строка не проходит фильтр. Это классический баг «NOT IN съел все данные».

Вывод: для ANTI JOIN используйте NOT EXISTS или LEFT JOIN ... IS NULL. NOT EXISTS устойчив к NULL автоматически (он сравнивает через = внутри подзапроса и просто не находит совпадений). Сравнение трёх подходов:

СпособУстойчив к NULLДублиЧитаемостьПлан в PG
NOT EXISTSДаНетВысокаяAnti Join
LEFT JOIN ... IS NULLДа (если проверять ключ)НетСредняяAnti Join
NOT INНет — ломается на NULLНетВысокаячасто хуже

Потренировать этот паттерн на живых данных можно в SQL-тренажёре — там PostgreSQL 16 прямо в браузере, а разбор JOIN-типов есть в гайде про все виды JOIN.

Что такое LATERAL JOIN и зачем он нужен?

LATERAL — это ключевое слово, которое разрешает подзапросу в FROM видеть колонки таблиц, стоящих левее. Без LATERAL подзапрос в FROM изолирован: он не знает про c.id внешней таблицы. С LATERAL подзапрос выполняется как бы в цикле — по разу для каждой строки внешней таблицы, подставляя её значения.

Формально это коррелированное соединение:

для каждой строки L из левой таблицы:
    выполнить правый подзапрос R(L)  -- R видит колонки L
    соединить L с результатом R(L)

Ключевая задача, которую нельзя решить обычным JOIN + LIMIT, — top-N на группу. Обычный LIMIT в подзапросе применился бы ко всему результату, а не к каждой группе отдельно.

Как сделать top-N на группу через LATERAL JOIN?

Задача: для каждого клиента показать его 2 самых дорогих заказа. Расширим данные.

TRUNCATE orders;
INSERT INTO orders VALUES
    (10, 1, 1500),
    (11, 1, 2300),
    (12, 1,  800),
    (13, 3,  900),
    (14, 3, 4100),
    (15, 3, 1200);

Запрос с LATERAL:

SELECT c.name, top.id AS order_id, top.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.id, o.amount
    FROM orders o
    WHERE o.customer_id = c.id     -- ссылка на внешнюю таблицу!
    ORDER BY o.amount DESC
    LIMIT 2
) AS top
ORDER BY c.name, top.amount DESC;

Результат — по 2 строки на клиента, у кого есть заказы:

nameorder_idamount
Анна112300
Анна101500
Вера144100
Вера151200

Клиенты Борис и Глеб не попали, потому что CROSS JOIN LATERAL отбрасывает строки, для которых подзапрос пуст (аналог INNER JOIN). Если нужно сохранить всех клиентов, даже без заказов, используйте LEFT JOIN LATERAL ... ON true:

SELECT c.name, top.id AS order_id, top.amount
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.id, o.amount
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.amount DESC
    LIMIT 2
) AS top ON true
ORDER BY c.name;

Теперь Борис и Глеб появятся со значениями NULL в колонках заказа.

LATERAL или оконные функции — что выбрать для top-N?

Ту же задачу top-N часто решают через оконную функцию ROW_NUMBER():

SELECT name, id AS order_id, amount
FROM (
    SELECT c.name, o.id, o.amount,
           ROW_NUMBER() OVER (
               PARTITION BY c.id ORDER BY o.amount DESC
           ) AS rn
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
) t
WHERE rn <= 2
ORDER BY name, amount DESC;

Результат идентичен. Какой подход лучше?

КритерийLATERAL + LIMITROW_NUMBER()
N маленькое (2-5), групп многоОбычно быстрееМедленнее
Нужны все строки, потом фильтрХужеХорошо
Есть индекс (customer_id, amount DESC)Отлично — index scan на группуНе всегда использует
Разное N для разных группЛегкоСложно

Практика: если групп много, N мало и есть подходящий индекс, LATERAL выигрывает, потому что для каждой группы делает быстрый Index Scan с ранней остановкой на LIMIT, не читая все заказы. ROW_NUMBER() вынужден отсортировать/пронумеровать все строки, прежде чем отбросить лишние. Разбор оконных функций — в гайде по window functions.

Какая производительность у ANTI JOIN и LATERAL?

Несколько практических правил, проверяемых через EXPLAIN (ANALYZE, BUFFERS):

Мини-чеклист перед деплоем запроса: посмотрите EXPLAIN, убедитесь что для ANTI JOIN стоит узел Anti Join, а не Seq Scan по большой таблице; для LATERAL — что внутри Nested Loop идёт Index Scan, а не Sort + Seq Scan.

Где это спрашивают и как закрепить?

ANTI JOIN и LATERAL — типичные вопросы среднего уровня на собеседовании аналитика и дата-инженера. Формулировки бывают такие: «найди пользователей, которые зарегистрировались, но не сделали ни одной покупки», «покажи последнюю транзакцию каждого счёта», «топ-5 товаров в каждой категории по выручке». Первая — про ANTI JOIN, две другие — про LATERAL или ROW_NUMBER().

Разбор смежных метрик, которые часто идут в паре с такими запросами (retention, конверсия), — в карточках /metrics/retention и /metrics/conversion-rate. А отработать сами конструкции на десятках реальных заданий с проверкой можно в разделе тестовых заданий и в SQL-тренажёре с настоящим PostgreSQL 16.

Итого: для «строк без пары» пишите NOT EXISTS или LEFT JOIN ... IS NULL и держитесь подальше от NOT IN там, где возможен NULL. Для «top-N на группу» и любых коррелированных подзапросов в FROM берите LATERAL, а под него — составной индекс. Оба паттерна решают задачи, которые обычным INNER JOIN не закрыть.

Хотите системно прокачать SQL до уровня senior-собеса? В Pro открыты все 425 SQL-задач с настоящим PostgreSQL, разбор оконных функций, CTE и AntiJoin-паттернов, плюс безлимитный AI мок-собес. Попробовать бесплатно →

Потренируй JOIN на реальных данных
576 SQL-задач: JOIN, подзапросы, окна. Настоящий PostgreSQL в браузере.
Открыть SQL-тренажёр →

Похожие статьи