Чем отличаются JOIN и subquery в PostgreSQL?
JOIN и Subquery: ключевые различия
JOIN — операция соединения двух или более таблиц по условию. Результирующий набор содержит столбцы из всех соединённых таблиц. JOIN выполняется на уровне плана запроса и, как правило, позволяет оптимизатору PostgreSQL выбирать наиболее эффективный алгоритм: Nested Loop, Hash Join или Merge Join.
Subquery (подзапрос) — запрос, вложенный внутрь другого запроса. Подзапросы бывают трёх видов:
- Скалярные — возвращают одно значение.
- Строчные / табличные — возвращают набор строк и используются в
FROM(производные таблицы) илиIN/EXISTS. - Коррелированные — ссылаются на столбцы внешнего запроса и выполняются заново для каждой строки внешнего запроса.
Производительность
PostgreSQL умеет автоматически «разворачивать» многие подзапросы в JOIN (процесс называется subquery unnesting). Это означает, что разница в производительности часто нивелируется на уровне планировщика. Тем не менее:
- Коррелированный подзапрос в
SELECTилиWHEREвыполняется N раз (по одному разу на строку), что может стать узким местом при большом наборе данных. JOINпозволяет оптимизатору выбирать алгоритм соединения и использовать индексы эффективнее.- Подзапрос в
FROM(CTE или производная таблица) материализуется и выполняется один раз, что иногда выгоднее.
Когда использовать JOIN
- Нужны столбцы из нескольких таблиц в одном результате.
- Соединение идёт по индексированным столбцам — оптимизатор выберет Hash Join или Merge Join.
- Логика соединения прямолинейна и не требует агрегации перед фильтрацией.
Когда использовать Subquery
- Нужно отфильтровать строки по агрегированному значению (
WHERE salary > (SELECT AVG(salary) FROM employees)). - Требуется проверка существования (
EXISTS) — часто быстрееINна больших выборках. - Логика запроса читается чище с подзапросом, чем с серией JOIN.
- CTE (
WITH) нужен для повторного использования промежуточного результата.
Практический совет
Всегда проверяйте план выполнения через EXPLAIN ANALYZE. Планировщик PostgreSQL достаточно умён, чтобы переписать подзапрос в JOIN, но не всегда. Коррелированные подзапросы — первые кандидаты на переписывание в JOIN при проблемах с производительностью.
Что хочет услышать интервьюер
Кандидат объясняет семантическое различие: JOIN — горизонтальное объединение таблиц, subquery — вложенная логика фильтрации или вычисления
Упоминает виды подзапросов: скалярный, табличный (производная таблица), коррелированный
Понимает разницу в производительности: коррелированный subquery может выполняться N раз, JOIN планируется единожды
Знает, что PostgreSQL может автоматически трансформировать некоторые subquery в JOIN (subquery unnesting)
Умеет читать EXPLAIN ANALYZE и принимать решение на основе реального плана запроса
Пример: JOIN — получить заказы с данными о клиентах
-- Соединяем таблицы напрямую, оптимизатор выбирает Hash Join
SELECT
o.id AS order_id,
c.name AS customer_name,
o.total_price
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.total_price > 5000;
Пример: Subquery — фильтрация по агрегату
-- Выбираем клиентов, чья сумма заказов выше средней
SELECT name, email
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(total_price) > (
-- Скалярный подзапрос — вычисляется один раз
SELECT AVG(total_sum)
FROM (
SELECT SUM(total_price) AS total_sum
FROM orders
GROUP BY customer_id
) AS per_customer
)
);
Пример: EXISTS vs IN — поиск клиентов с хотя бы одним заказом
-- EXISTS прекращает поиск сразу при первом совпадении — эффективнее для больших таблиц
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id -- коррелированный подзапрос
);
-- Эквивалент через JOIN (часто идентичный план после оптимизации)
SELECT DISTINCT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id;
Пример: EXPLAIN ANALYZE — сравнение планов
-- Смотрим реальный план для JOIN
EXPLAIN ANALYZE
SELECT c.name, o.total_price
FROM customers c
JOIN orders o ON o.customer_id = c.id;
-- Смотрим реальный план для subquery в FROM
EXPLAIN ANALYZE
SELECT c.name, sub.total_price
FROM customers c
JOIN (
SELECT customer_id, total_price
FROM orders
WHERE total_price > 1000 -- фильтр внутри подзапроса
) sub ON sub.customer_id = c.id;
-- Если планы совпадают — PostgreSQL уже всё оптимизировал за нас
Типичные ошибки
Считают, что JOIN всегда быстрее subquery — без учёта того, что планировщик PostgreSQL нередко превращает их в одно и то же
Путают коррелированный и некоррелированный подзапросы, не понимая, что первый выполняется многократно
Используют IN (SELECT ...) там, где EXISTS будет быстрее из-за оценки наличия, а не построения полного набора
Забывают, что CTE в PostgreSQL до версии 12 всегда материализовался — это могло ухудшать, а не улучшать производительность
Не проверяют реальный план через EXPLAIN ANALYZE, полагаясь только на интуицию
AI-тренажеры
4.7
Практика в студии
