SQL подзапросы и CTE (WITH)¶

Когда одного SELECT не хватает — нужно вложить запрос в запрос. Подзапросы и CTE позволяют разбить сложную логику на читаемые шаги: сначала найти, потом отфильтровать, потом агрегировать.

В этой статье ты пройдёшь от простых вложений до рекурсивного обхода деревьев и LATERAL. Все примеры на схеме etl_kitchen_db.

Скалярные подзапросы¶

Скалярный подзапрос возвращает одно значение — его можно использовать как колонку в SELECT или как значение в WHERE.

В SELECT: добавить вычисленное поле¶

SQL

-- Каждый товар + отклонение от средней цены в категории
SELECT
    p.name,
    p.price,
    cat.name AS category,
    p.price - (
        SELECT ROUND(AVG(p2.price), 2)
        FROM products p2
        WHERE p2.category_id = p.category_id
    ) AS diff_from_avg
FROM products p
JOIN categories cat ON p.category_id = cat.category_id
ORDER BY diff_from_avg DESC
LIMIT 5;

name	price	category	diff_from_avg
Ноутбук MacBook Pro	89990.00	Электроника	36661.67
Телевизор Samsung 55"	74990.00	Электроника	21661.67
Стиральная машина LG	54990.00	Бытовая техника	10242.50

В WHERE: сравнение с агрегатом¶

SQL

-- Товары дороже средней цены по всем товарам
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

Скалярный = ровно одно значение

Если подзапрос вернёт больше одной строки, запрос упадёт с ошибкой. Убедись, что используешь агрегат (AVG, MAX, MIN) или LIMIT 1.

IN и NOT IN¶

IN фильтрует строки по списку значений из подзапроса.

SQL

-- Клиенты, которые делали заказы
SELECT name, email
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id FROM orders
);

Ловушка NOT IN + NULL¶

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

SQL

-- Допустим, в orders есть строка с customer_id = NULL
-- Этот запрос НИЧЕГО не вернёт!
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Почему? NOT IN (1, 2, NULL) вычисляется как x != 1 AND x != 2 AND x != NULL. Последнее условие — всегда UNKNOWN, и весь результат — UNKNOWN.

SQL

-- Правильно: используй NOT EXISTS
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

`NOT IN`	`NOT EXISTS`
Ломается при NULL в подзапросе	Работает всегда корректно
Формирует полный список в памяти	Останавливается на первом совпадении

Правило: всегда используй NOT EXISTS вместо NOT IN.

EXISTS и NOT EXISTS¶

EXISTS проверяет, есть ли хотя бы одна строка. СУБД прекращает поиск после первого совпадения — это быстро.

SQL

-- Категории, в которых есть хотя бы один товар
SELECT cat.name
FROM categories cat
WHERE EXISTS (
    SELECT 1 FROM products p WHERE p.category_id = cat.category_id
);

Anti-join: NOT EXISTS¶

SQL

-- Клиенты, которые зарегистрировались, но ни разу не заказали
SELECT c.name, c.email, c.city
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

IN vs EXISTS vs JOIN — когда что¶

Подход	Когда использовать
`IN (подзапрос)`	Маленький список значений, нет NULL
`EXISTS`	Большие таблицы, коррелированная проверка
`JOIN`	Когда нужны колонки из обеих таблиц
`NOT EXISTS`	Всегда вместо `NOT IN`

Оптимизатор PostgreSQL

На практике PostgreSQL часто превращает IN в JOIN под капотом. Разница в производительности минимальна для простых случаев. Но NOT IN + NULL — это логическая ловушка, а не вопрос скорости.

Коррелированные подзапросы¶

Коррелированный подзапрос ссылается на внешний запрос — СУБД выполняет его для каждой строки внешнего запроса.

SQL

-- Товары, цена которых выше средней в своей категории
SELECT p.name, p.price, cat.name AS category
FROM products p
JOIN categories cat ON p.category_id = cat.category_id
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category_id = p.category_id   -- ← ссылка на внешний запрос
)
ORDER BY p.price DESC;

SQL

-- Последний заказ каждого клиента (через коррелированный подзапрос)
SELECT o.order_id, c.name, o.order_date, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
)
ORDER BY o.order_date DESC;

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

Коррелированный подзапрос = вложенный цикл. На больших таблицах переписывай через JOIN + оконные функции или LATERAL.

Подзапросы в FROM (Derived Tables)¶

Подзапрос в FROM создаёт временную таблицу (inline view). Обязателен алиас.

SQL

-- Средний чек по городам (только города с 3+ заказами)
SELECT
    city_stats.city,
    city_stats.avg_check,
    city_stats.orders_count
FROM (
    SELECT
        c.city,
        ROUND(AVG(o.total_amount), 2) AS avg_check,
        COUNT(*)                       AS orders_count
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.city
) AS city_stats
WHERE city_stats.orders_count >= 3
ORDER BY city_stats.avg_check DESC;

SQL

-- Агрегат от агрегата: средняя выручка по категориям
SELECT ROUND(AVG(cat_revenue), 2) AS avg_category_revenue
FROM (
    SELECT
        p.category_id,
        SUM(oi.quantity * oi.unit_price) AS cat_revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY p.category_id
) AS category_totals;

CTE: WITH-запросы¶

CTE (Common Table Expression) — именованный подзапрос, объявленный перед основным SELECT. Делает код читаемым как пошаговый рецепт.

Базовый CTE¶

SQL

WITH delivered_orders AS (
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE status = 'delivered'
)
SELECT
    c.name,
    COUNT(*)              AS delivered_count,
    SUM(d.total_amount)   AS delivered_revenue
FROM delivered_orders d
JOIN customers c ON d.customer_id = c.customer_id
GROUP BY c.name
ORDER BY delivered_revenue DESC;

Цепочка CTE¶

Каждый последующий CTE может ссылаться на предыдущие. Это как конвейер:

SQL

WITH
-- Шаг 1: считаем метрики по клиентам
customer_stats AS (
    SELECT
        customer_id,
        COUNT(*)              AS orders_count,
        SUM(total_amount)     AS total_spent,
        MAX(order_date)       AS last_order
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY customer_id
),
-- Шаг 2: определяем VIP (больше 3 заказов или потратили > 100K)
vip_customers AS (
    SELECT customer_id
    FROM customer_stats
    WHERE orders_count > 3 OR total_spent > 100000
)
-- Шаг 3: выводим
SELECT
    c.name,
    c.city,
    cs.orders_count,
    cs.total_spent,
    cs.last_order
FROM vip_customers v
JOIN customers c      ON v.customer_id  = c.customer_id
JOIN customer_stats cs ON v.customer_id = cs.customer_id
ORDER BY cs.total_spent DESC;

Материализация: MATERIALIZED / NOT MATERIALIZED¶

В PostgreSQL 12+ можно управлять тем, как планировщик обрабатывает CTE:

SQL

-- Принудительно материализовать (вычислить один раз, сохранить в памяти)
WITH heavy_calc AS MATERIALIZED (
    SELECT customer_id, SUM(total_amount) AS total
    FROM orders GROUP BY customer_id
)
SELECT * FROM heavy_calc WHERE total > 50000
UNION ALL
SELECT * FROM heavy_calc WHERE total < 10000;

-- Принудительно «вклеить» в основной запрос (inline)
WITH simple_filter AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE status = 'delivered'
)
SELECT * FROM simple_filter WHERE total_amount > 50000;

Режим	Когда полезен
`MATERIALIZED`	CTE используется несколько раз, вычисление тяжёлое
`NOT MATERIALIZED`	CTE используется один раз, планировщик должен «протолкнуть» фильтры внутрь
По умолчанию	PostgreSQL решает сам (inline при одном использовании)

До PostgreSQL 12

Все CTE материализовались принудительно — это была известная проблема производительности. С версии 12+ поведение по умолчанию стало умнее.

Рекурсивные CTE¶

WITH RECURSIVE позволяет обходить деревья и графы. Структура: базовый терм + UNION ALL + рекурсивный терм.

Дерево сотрудников¶

SQL

WITH RECURSIVE org_tree AS (
    -- Базовый терм: начинаем с CEO (manager_id IS NULL)
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level,
        name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсивный терм: ищем подчинённых
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        t.level + 1,
        t.path || ' → ' || e.name
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.employee_id
)
SELECT
    REPEAT('  ', level - 1) || name AS employee,
    level,
    path
FROM org_tree
ORDER BY path;

employee	level	path
Иван Директоров	1	Иван Директоров
Анна Менеджерова	2	Иван Директоров → Анна Менеджерова
Пётр Разработчиков	3	Иван Директоров → Анна Менеджерова → Пётр Разработчиков

Дерево категорий¶

SQL

WITH RECURSIVE cat_tree AS (
    -- Корневые категории (без родителя)
    SELECT category_id, name, parent_id, 1 AS depth,
           name AS full_path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.category_id, c.name, c.parent_id, t.depth + 1,
           t.full_path || ' > ' || c.name
    FROM categories c
    JOIN cat_tree t ON c.parent_id = t.category_id
)
SELECT full_path, depth
FROM cat_tree
ORDER BY full_path;

full_path	depth
Электроника	1
Электроника > Смартфоны	2
Электроника > Ноутбуки	2

Защита от бесконечных циклов¶

SQL

-- Способ 1: ограничение глубины
WITH RECURSIVE tree AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN tree t ON e.manager_id = t.employee_id
    WHERE t.level < 50    -- ← предохранитель
)
SELECT * FROM tree;

-- Способ 2: CYCLE (PostgreSQL 14+)
WITH RECURSIVE tree AS (
    SELECT employee_id, name, manager_id
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e JOIN tree t ON e.manager_id = t.employee_id
) CYCLE employee_id SET is_cycle USING path
SELECT * FROM tree WHERE NOT is_cycle;

LATERAL: подзапрос, который видит соседей¶

LATERAL позволяет подзапросу в FROM ссылаться на колонки предыдущих таблиц. Это как коррелированный подзапрос, но в FROM.

Топ-3 последних заказа для каждого клиента¶

SQL

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

Без LATERAL пришлось бы использовать оконную функцию ROW_NUMBER() с подзапросом — LATERAL даёт тот же результат, но читабельнее.

Последнее событие каждого клиента¶

SQL

SELECT c.name, c.email, e.event_type, e.event_date
FROM customers c
LEFT JOIN LATERAL (
    SELECT ev.event_type, ev.event_date
    FROM events ev
    WHERE ev.customer_id = c.customer_id
    ORDER BY ev.event_date DESC
    LIMIT 1
) AS e ON true
ORDER BY e.event_date DESC NULLS LAST;

LEFT JOIN LATERAL ... ON true

LEFT JOIN LATERAL + ON true сохраняет клиентов без событий (как обычный LEFT JOIN). CROSS JOIN LATERAL отбросит таких клиентов.

CTE vs подзапрос: когда что¶

Критерий	Подзапрос	CTE
Читаемость	Хуже при вложенности > 2	Лучше — шаги идут сверху вниз
Переиспользование	Нельзя (копипаста)	Можно ссылаться несколько раз
Отладка	Сложнее — нужно выделять части	Проще — можно запустить CTE отдельно
Производительность	Планировщик может «протолкнуть» фильтры	До PG 12 — barrier для оптимизатора
Рекурсия	Невозможна	`WITH RECURSIVE`

Практическое правило: начинай с CTE для читаемости. Если EXPLAIN ANALYZE показывает проблему — попробуй NOT MATERIALIZED или перепиши в подзапрос.

Типичные ошибки¶

1. NOT IN + NULL¶

SQL

-- ПУСТО! Потому что в подзапросе может быть NULL
WHERE customer_id NOT IN (SELECT customer_id FROM orders)

-- Правильно:
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)

2. Бесконечная рекурсия¶

SQL

-- Если A → B → A — рекурсия зациклится
-- Добавь WHERE level < 50 или CYCLE (PG 14+)

3. Лишняя материализация CTE¶

SQL

-- До PG 12: CTE всегда материализуется — фильтр total > 100000
-- НЕ проталкивается внутрь, сканируются все заказы
WITH all_orders AS (
    SELECT * FROM orders
)
SELECT * FROM all_orders WHERE total_amount > 100000;

-- Быстрее: подзапрос (или NOT MATERIALIZED в PG 12+)
SELECT * FROM orders WHERE total_amount > 100000;

4. Скалярный подзапрос возвращает > 1 строки¶

SQL

-- ОШИБКА: подзапрос вернёт несколько строк
WHERE price > (SELECT price FROM products WHERE category_id = 1)

-- Правильно: используй агрегат
WHERE price > (SELECT MAX(price) FROM products WHERE category_id = 1)

Шпаргалка¶

Задача	Инструмент
Фильтр по списку из другой таблицы	`IN` / `EXISTS`
«Кто НЕ сделал X»	`NOT EXISTS` (не `NOT IN`!)
Сравнение со средним/макс	Скалярный подзапрос
Агрегат от агрегата	Подзапрос в `FROM` или CTE
Пошаговая логика	Цепочка CTE
Обход дерева / иерархии	`WITH RECURSIVE`
Топ-N для каждой группы	`LATERAL`
Переиспользование промежуточного результата	CTE (`MATERIALIZED`)

Что запомнить¶

Подзапрос — запрос внутри запроса: в SELECT, WHERE, FROM
NOT IN + NULL = пустой результат. Всегда используй NOT EXISTS
EXISTS останавливается на первом совпадении — быстрее IN на больших таблицах
CTE (WITH) — именованные шаги, читаемые сверху вниз
В PostgreSQL 12+ CTE по умолчанию инлайнятся; используй MATERIALIZED / NOT MATERIALIZED для контроля
WITH RECURSIVE — обход деревьев (сотрудники, категории). Добавляй ограничение глубины
LATERAL — коррелированный подзапрос в FROM, идеален для «топ-N на группу»
Коррелированный подзапрос = вложенный цикл. На больших данных — переписывай через JOIN/LATERAL

Попробуй сам¶

Проверь себя¶

Что дальше?¶

Ты умеешь строить многоуровневые запросы и обходить деревья. Следующий шаг — оконные функции, которые позволяют считать агрегаты без схлопывания строк.

Источники¶

PostgreSQL: WITH Queries (CTE) — CTE, рекурсия, MATERIALIZED, CYCLE
PostgreSQL: Subquery Expressions — IN, EXISTS, ANY, ALL
PostgreSQL: LATERAL Subqueries — LATERAL в FROM
PostgreSQL: Table Expressions — derived tables, подзапросы в FROM