Подзапросы vs JOIN vs CTE: когда что выбрать в SQL

На SQL-собеседованиях часто просят решить задачу несколькими способами. Понимание разницы между подзапросами, JOIN и CTE — признак зрелости.

Одна задача — три решения

Задача: найти клиентов, которые совершили хотя бы один заказ на сумму больше 5000.

Решение 1: Подзапрос

SELECT name
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE amount > 5000
);

Решение 2: JOIN

SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 5000;

Решение 3: CTE

WITH big_orders AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE amount > 5000
)
SELECT c.name
FROM customers c
JOIN big_orders bo ON c.id = bo.customer_id;

Все три дадут одинаковый результат. Когда что лучше?

Подзапросы

Когда использовать

• Скалярный подзапрос — возвращает одно значение:

SELECT name, salary,
       salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

• Фильтрация IN / NOT IN / EXISTS:

SELECT * FROM products
WHERE category_id NOT IN (SELECT id FROM categories WHERE is_archived = true);

• Коррелированный (ссылается на внешний запрос):

SELECT name
FROM employees e
WHERE salary = (
    SELECT MAX(salary) FROM employees WHERE department = e.department
);

Минусы

• Коррелированные подзапросы выполняются для каждой строки — медленно на больших таблицах
• IN (SELECT ...) с большим результатом — может быть медленнее EXISTS
• Глубокая вложенность — нечитаемо

IN vs EXISTS — что быстрее?

-- IN: загружает весь список в память, потом проверяет
WHERE id IN (SELECT customer_id FROM orders WHERE ...)

-- EXISTS: останавливается при первом совпадении
WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id AND ...)

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

EXISTS работает как цикл: для каждой строки внешнего запроса он проверяет наличие совпадения и прекращает проверку, как только находит первое. IN же сначала выполняет подзапрос полностью, собирает все ID в список в памяти, а потом проверяет принадлежность — даже если совпадение нашлось бы с первой же записи.

Осторожно с NOT IN и NULL. Если подзапрос может вернуть хотя бы один NULL, WHERE id NOT IN (SELECT customer_id FROM orders WHERE ...) вернёт ноль строк — это следствие трёхзначной логики. NOT EXISTS лишён этого недостатка и работает корректно в любом случае.

JOIN

Когда использовать

• Нужны данные из нескольких таблиц в одном результате
• Нужна фильтрация по условиям связанной таблицы
• Нужна агрегация с данными из разных таблиц

SELECT c.name, COUNT(o.id) AS orders_cnt, SUM(o.amount) AS total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

LEFT JOIN vs NOT EXISTS для «отсутствующих» строк

-- Клиенты без заказов через LEFT JOIN
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

-- Через NOT EXISTS — часто быстрее
SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id);

Общее правило: если вам не нужны данные из правой таблицы, а нужно только проверить наличие или отсутствие связи — EXISTS/NOT EXISTS часто эффективнее и понятнее, чем LEFT JOIN ... WHERE ... IS NULL.

Осторожно с декартовым произведением

-- ❌ Если связь 1:N — COUNT(*) будет дублироваться
SELECT c.name, COUNT(*) AS orders_cnt
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id;
-- order_items умножит строки orders — неверный результат!

-- ✅ Считай через подзапрос или CTE
WITH order_counts AS (
    SELECT customer_id, COUNT(*) AS cnt FROM orders GROUP BY customer_id
)
SELECT c.name, oc.cnt
FROM customers c
JOIN order_counts oc ON c.id = oc.customer_id;

CTE (WITH)

Когда использовать

• Запрос сложный и многоступенчатый — CTE разбивает его на понятные шаги
• Один подзапрос нужен несколько раз — CTE можно переиспользовать
• Нужна рекурсия (иерархии, деревья)
• Хочешь объяснить логику коллеге или проверяющему

WITH
  monthly_revenue AS (
      SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue
      FROM orders WHERE status = 'paid'
      GROUP BY 1
  ),
  prev_month AS (
      SELECT month, revenue,
             LAG(revenue) OVER (ORDER BY month) AS prev_revenue
      FROM monthly_revenue
  )
SELECT
    month,
    revenue,
    ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS growth_pct
FROM prev_month
ORDER BY month;

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

В PostgreSQL 12+ оптимизатор сам решает, материализовать CTE или встроить. До 12 — CTE всегда материализовался (мог быть медленнее).

-- Явно запрещаем материализацию (встраиваем как подзапрос)
WITH cte AS NOT MATERIALIZED (SELECT ...)

-- Явно материализуем (кешируем результат)
WITH cte AS MATERIALIZED (SELECT ...)

MATERIALIZED полезен, когда CTE тяжёлое и используется несколько раз в основном запросе. NOT MATERIALIZED (встраивание) лучше, если CTE лёгкое или используется один раз — это даёт оптимизатору больше свободы для перестановки операций.

Какой бы способ вы ни выбрали, окончательное решение за оптимизатором. Регулярно обновляйте статистику (ANALYZE), чтобы он принимал правильные решения.

Сравнительная таблица

Практическое правило

1. Нужны данные из связанных таблиц → JOIN
2. Нужна фильтрация «есть / нет» → EXISTS / NOT EXISTS
3. Запрос сложнее 3 шагов → CTE
4. Простая вложенность для одного значения → подзапрос