Подзапросы в JOIN

Подзапросы и CTE (Common Table Expressions) внутри JOIN позволяют создавать сложные запросы с предварительной обработкой данных, фильтрацией и агрегацией.

#Типы подзапросов в JOIN

Подзапросы в FROM — табличные подзапросы как виртуальные таблицы
CTE (WITH clause) — именованные временные результаты
Коррелированные подзапросы — подзапросы, ссылающиеся на внешнюю таблицу
LATERAL JOIN — подзапросы, ссылающиеся на предыдущие таблицы в FROM

#Подзапросы в предложении FROM

Подзапрос в FROM действует как виртуальная таблица.

#Синтаксис

SELECT <колонки>
FROM (
    SELECT ...
) AS подзапрос
JOIN другая_таблица ON условие;

Важно: Обязательно указывайте псевдоним (AS подзапрос) для подзапроса в FROM.

#Пример: агрегация перед JOIN

-- Общая сумма заказов по каждому пользователю
SELECT 
    u.name,
    user_orders.total_amount,
    user_orders.order_count
FROM users u
JOIN (
    SELECT 
        user_id,
        SUM(amount) AS total_amount,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) AS user_orders ON u.id = user_orders.user_id;

Преимущества:

Агрегация выполняется до JOIN — меньше строк для соединения
Логика разделена: подзапрос отвечает за агрегацию, основной запрос — за соединение
Часто производительнее, чем агрегация после JOIN

#Пример: фильтрация в подзапросе

-- Только последние заказы каждого пользователя
SELECT 
    u.name,
    latest_orders.order_id,
    latest_orders.amount,
    latest_orders.order_date
FROM users u
JOIN (
    SELECT DISTINCT ON (user_id)
        id AS order_id,
        user_id,
        amount,
        created_at AS order_date
    FROM orders
    ORDER BY user_id, created_at DESC
) AS latest_orders ON u.id = latest_orders.user_id;

#CTE (Common Table Expressions)

CTE определяют именованные временные результаты, которые можно использовать в основном запросе.

#Синтаксис

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name
JOIN ...;

#Пример: базовый CTE

WITH user_order_stats AS (
    SELECT 
        user_id,
        SUM(amount) AS total_amount,
        COUNT(*) AS order_count,
        AVG(amount) AS avg_amount
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.name,
    uos.total_amount,
    uos.order_count,
    uos.avg_amount
FROM users u
JOIN user_order_stats uos ON u.id = uos.user_id
WHERE uos.order_count > 1;

#Преимущества CTE

Читаемость: Именованные блоки кода легче понимать
Переиспользование: Один CTE можно использовать несколько раз
Рекурсия: Поддержка рекурсивных запросов
Оптимизация: PostgreSQL может материализовать CTE для повторного использования

#Пример: несколько CTE

WITH 
-- Активные пользователи
active_users AS (
    SELECT id, name
    FROM users
    WHERE last_login_at > NOW() - INTERVAL '30 days'
),
-- Их заказы за последний месяц
recent_orders AS (
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
),
-- Топ категории
top_categories AS (
    SELECT 
        o.user_id,
        c.name AS category_name,
        COUNT(*) AS category_orders
    FROM orders o
    JOIN products p ON o.product_id = p.id
    JOIN categories c ON p.category_id = c.id
    GROUP BY o.user_id, c.name
)
SELECT 
    au.name,
    ro.order_count,
    ro.total_amount,
    tc.category_name AS favorite_category
FROM active_users au
JOIN recent_orders ro ON au.id = ro.user_id
JOIN top_categories tc ON au.id = tc.user_id
WHERE ro.order_count = (
    SELECT MAX(category_orders) 
    FROM top_categories tc2 
    WHERE tc2.user_id = au.id
);

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

Рекурсивные CTE полезны для иерархических данных.

#Синтаксис

WITH RECURSIVE cte_name AS (
    -- Базовый случай (якорь)
    SELECT ...
    
    UNION ALL
    
    -- Рекурсивный случай
    SELECT ...
    FROM cte_name
    JOIN ... ON ...
)
SELECT * FROM cte_name;

#Пример: иерархия сотрудников

WITH RECURSIVE employee_hierarchy AS (
    -- Базовый случай: CEO
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        name AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Рекурсивный случай: подчинённые
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        eh.level + 1,
        eh.path || ' -> ' || e.name
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
    WHERE eh.level < 10  -- Ограничение глубины
)
SELECT * FROM employee_hierarchy
ORDER BY level, name;

#Пример: все категории с родителями

WITH RECURSIVE category_tree AS (
    -- Корневые категории
    SELECT 
        id,
        name,
        parent_id,
        name AS full_path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Дочерние категории
    SELECT 
        c.id,
        c.name,
        c.parent_id,
        ct.full_path || ' > ' || c.name
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree
ORDER BY full_path;

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

Коррелированные подзапросы ссылаются на колонки внешней таблицы.

#Пример: последний заказ пользователя

SELECT 
    u.name,
    (
        SELECT o.amount
        FROM orders o
        WHERE o.user_id = u.id
        ORDER BY o.created_at DESC
        LIMIT 1
    ) AS last_order_amount
FROM users u;

#Пример: подзапрос в условии JOIN

SELECT 
    u.name,
    o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount = (
    SELECT MAX(o2.amount)
    FROM orders o2
    WHERE o2.user_id = u.id
);

Производительность: Коррелированные подзапросы могут выполняться для каждой строки внешней таблицы. Часто эффективнее использовать LATERAL JOIN или оконные функции.

#LATERAL JOIN

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

#Синтаксис

SELECT ...
FROM таблица1
JOIN LATERAL (
    SELECT ... WHERE ... = таблица1.колонка
) AS подзапрос ON true;

#Пример: последние N заказов каждого пользователя

SELECT 
    u.name,
    lo.order_id,
    lo.amount,
    lo.created_at
FROM users u
JOIN LATERAL (
    SELECT 
        id AS order_id,
        amount,
        created_at
    FROM orders
    WHERE user_id = u.id  -- Ссылка на u из внешней таблицы!
    ORDER BY created_at DESC
    LIMIT 3
) AS lo ON true;

Без LATERAL подзапрос не мог бы ссылаться на u.id.

#Пример: агрегация с фильтрацией

SELECT 
    u.name,
    lo.total_amount,
    lo.order_count
FROM users u
JOIN LATERAL (
    SELECT 
        SUM(amount) AS total_amount,
        COUNT(*) AS order_count
    FROM orders
    WHERE user_id = u.id
        AND created_at > NOW() - INTERVAL '30 days'
) AS lo ON true
WHERE lo.order_count > 0;

#LATERAL с несколькими таблицами

SELECT 
    u.name,
    lo.last_order,
    lp.last_payment
FROM users u
JOIN LATERAL (
    SELECT amount AS last_order
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 1
) AS lo ON true
JOIN LATERAL (
    SELECT amount AS last_payment
    FROM payments
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 1
) AS lp ON true;

#CROSS JOIN LATERAL

Генерация строк на основе данных из другой таблицы.

#Пример: генерация дат

SELECT 
    o.id AS order_id,
    o.created_at,
    d.date AS followup_date
FROM orders o
CROSS JOIN LATERAL (
    SELECT generate_series(
        o.created_at::date,
        o.created_at::date + 7,
        '1 day'::interval
    )::date AS date
) AS d;

#Пример: разворачивание массива

CREATE TABLE orders (
    id INTEGER,
    product_ids INTEGER[]  -- Массив ID товаров
);

SELECT 
    o.id AS order_id,
    pid AS product_id
FROM orders o
CROSS JOIN LATERAL unnest(o.product_ids) AS pid;

#UNION/INTERSECT/EXCEPT в подзапросах

Комбинирование результатов нескольких запросов в подзапросе.

#Пример: UNION в подзапросе

SELECT 
    u.name,
    all_transactions.amount
FROM users u
JOIN (
    SELECT user_id, amount, created_at FROM orders
    UNION ALL
    SELECT user_id, -amount AS refund, created_at FROM refunds
) AS all_transactions ON u.id = all_transactions.user_id;

#Пример: EXCEPT для исключения

SELECT u.name
FROM users u
JOIN (
    SELECT id FROM users WHERE active = true
    EXCEPT
    SELECT user_id FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
) AS inactive_active ON u.id = inactive_active.id;

#Производительность подзапросов в JOIN

#1. CTE vs подзапрос в FROM

В PostgreSQL 12+ CTE могут быть "inlined" (встроены) как подзапросы:

-- CTE может быть встроен
WITH stats AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT * FROM stats;

-- Эквивалентный подзапрос
SELECT * FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) AS stats;

Для принудительной материализации CTE используйте MATERIALIZED:

WITH stats AS MATERIALIZED (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT * FROM stats;

#2. LATERAL vs коррелированный подзапрос

-- Медленно: коррелированный подзапрос для каждой строки
SELECT 
    u.name,
    (SELECT SUM(amount) FROM orders WHERE user_id = u.id) AS total
FROM users u;

-- Быстрее: LATERAL JOIN
SELECT 
    u.name,
    lo.total
FROM users u
JOIN LATERAL (
    SELECT SUM(amount) AS total
    FROM orders
    WHERE user_id = u.id
) AS lo ON true;

#3. Оконные функции вместо подзапросов

-- Медленно: подзапрос для последнего заказа
SELECT 
    u.name,
    (SELECT o.amount FROM orders o 
     WHERE o.user_id = u.id 
     ORDER BY o.created_at DESC LIMIT 1) AS last_amount
FROM users u;

-- Быстрее: оконная функция
SELECT DISTINCT ON (u.id)
    u.name,
    o.amount AS last_amount
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.created_at DESC;

#Распространённые ошибки

#1. Забытый псевдоним подзапроса

-- ОШИБКА: нет псевдонима
SELECT * FROM (SELECT id, name FROM users) JOIN orders ON id = user_id;

-- Правильно:
SELECT * FROM (SELECT id, name FROM users) AS u JOIN orders ON u.id = user_id;

#2. Неправильная ссылка на CTE

-- ОШИБКА: CTE должен быть определён перед использованием
SELECT * FROM order_stats
JOIN users u ON order_stats.user_id = u.id
WITH order_stats AS (
    SELECT user_id, COUNT(*) FROM orders GROUP BY user_id
);

-- Правильно:
WITH order_stats AS (
    SELECT user_id, COUNT(*) FROM orders GROUP BY user_id
)
SELECT * FROM order_stats
JOIN users u ON order_stats.user_id = u.id;

#3. Бесконечная рекурсия

-- Опасно: нет ограничения глубины
WITH RECURSIVE hierarchy AS (
    SELECT id, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.manager_id FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)

-- Безопасно: ограничение глубины
WITH RECURSIVE hierarchy AS (
    SELECT id, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.manager_id, h.level + 1 FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
    WHERE h.level < 10
)