Подзапросы в SQL: скалярные, строчные, табличные и коррелированные

Подзапрос — это SELECT внутри другого SQL-запроса. Они бывают разных видов, и каждый подходит для своей задачи. Разберём все типы с примерами.

Где можно использовать подзапросы

SELECT (подзапрос),           -- скалярный: в списке SELECT
FROM   (подзапрос) AS alias,  -- табличный: в FROM
WHERE  col = (подзапрос),     -- скалярный: в условии
WHERE  col IN (подзапрос),    -- строчный: проверка вхождения
WHERE  EXISTS (подзапрос);    -- коррелированный: проверка существования

1. Скалярный подзапрос

Возвращает одно значение (одна строка, один столбец):

-- Пользователи с суммой выше среднего
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > (
    SELECT AVG(total) FROM (
        SELECT SUM(amount) AS total FROM orders GROUP BY user_id
    ) sub
);

-- Скалярный в SELECT
SELECT
    name,
    price,
    price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

⚠️ Скалярный подзапрос в SELECT выполняется для каждой строки! Для постоянного значения лучше вынести в CTE или переменную.

2. Строчный подзапрос

Возвращает одну строку с несколькими столбцами:

-- Сравнение строки с кортежем
SELECT * FROM orders
WHERE (user_id, status) = (
    SELECT user_id, status FROM users WHERE email = 'alice@example.com'
    -- Предполагаем что возвращается одна строка
);

-- Проверка что подзапрос вернул конкретную строку
SELECT * FROM orders
WHERE (user_id, amount) IN (
    SELECT user_id, MAX(amount) FROM orders GROUP BY user_id
);
-- Заказ с максимальной суммой для каждого пользователя

3. Табличный подзапрос (Derived Table)

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

-- Пользователи с > 3 заказов за последний месяц
SELECT u.name, u.email, monthly.order_count
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY user_id
    HAVING COUNT(*) > 3
) AS monthly ON monthly.user_id = u.id;

-- Можно вынести в CTE для читаемости
WITH monthly AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY user_id
    HAVING COUNT(*) > 3
)
SELECT u.name, u.email, m.order_count
FROM users u
JOIN monthly m ON m.user_id = u.id;

4. Коррелированный подзапрос

Ссылается на столбцы внешнего запроса — выполняется для каждой строки:

-- Для каждого пользователя — его последний заказ
SELECT u.name, (
    SELECT MAX(created_at)
    FROM orders o
    WHERE o.user_id = u.id  -- ссылка на u.id из внешнего запроса
) AS last_order_at
FROM users u;

-- Найти пользователей с заказом выше их личного среднего
SELECT o.user_id, o.amount, o.created_at
FROM orders o
WHERE o.amount > (
    SELECT AVG(amount)
    FROM orders sub
    WHERE sub.user_id = o.user_id  -- коррелированный по user_id
);

Проблема производительности: коррелированный подзапрос выполняется N раз (по одному на строку внешнего запроса). Часто можно переписать через JOIN:

-- Эффективнее через JOIN:
SELECT o.user_id, o.amount, o.created_at
FROM orders o
JOIN (
    SELECT user_id, AVG(amount) AS avg_amount
    FROM orders GROUP BY user_id
) avg_orders ON avg_orders.user_id = o.user_id
WHERE o.amount > avg_orders.avg_amount;

5. Подзапрос в UPDATE

-- Обновить цены на основе прайс-листа
UPDATE products p
SET price = (
    SELECT new_price FROM price_updates pu
    WHERE pu.product_id = p.id
    AND pu.effective_date = CURRENT_DATE
)
WHERE EXISTS (
    SELECT 1 FROM price_updates pu
    WHERE pu.product_id = p.id AND pu.effective_date = CURRENT_DATE
);

Или элегантнее через UPDATE ... FROM:

UPDATE products p
SET price = pu.new_price
FROM price_updates pu
WHERE pu.product_id = p.id AND pu.effective_date = CURRENT_DATE;

6. Подзапрос vs CTE vs JOIN

-- Вариант 1: подзапрос в FROM
SELECT * FROM (SELECT ...) sub WHERE ...;

-- Вариант 2: CTE
WITH sub AS (SELECT ...) SELECT * FROM sub WHERE ...;

-- Вариант 3: JOIN
SELECT ... FROM table JOIN (SELECT ...) sub ON ...;

PostgreSQL 12+ улучшил оптимизацию CTE — теперь они по умолчанию встраиваются как подзапросы (не материализуются).

Оптимизация коррелированных подзапросов

-- Медленно: коррелированный подзапрос = N вызовов
SELECT name,
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- Быстро: LEFT JOIN + GROUP BY
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- Быстро: оконная функция (без GROUP BY)
SELECT DISTINCT u.name,
       COUNT(o.id) OVER (PARTITION BY u.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Итог

Правило: если подзапрос коррелированный и медленный → попробуйте переписать через JOIN или оконные функции.