Подзапросы в SELECT и FROM

В предыдущем уроке подзапросы использовались в WHERE для фильтрации. Но это не единственное место, куда можно вложить запрос. Подзапросы в SELECT добавляют вычисляемые значения к каждой строке результата, а подзапросы в FROM создают производные таблицы — временные наборы данных, с которыми можно работать как с обычными таблицами.

Подзапрос в SELECT

Скалярный подзапрос в SELECT вычисляется для каждой строки результата. Это похоже на добавление нового поля с вычисленным значением:

SELECT name,
       salary,
       (SELECT AVG(salary) FROM employees) AS company_avg,
       salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM   employees
ORDER BY diff_from_avg DESC;

Каждая строка получает два дополнительных поля: среднюю зарплату по компании и разность от средней. Подзапрос (SELECT AVG(salary) FROM employees) одинаков для всех строк, поэтому PostgreSQL оптимизирует его как одно вычисление.

Осторожно: если подзапрос в SELECT коррелированный (ссылается на внешнюю строку), он выполняется для каждой строки отдельно. Это дорого на больших данных.

-- Каждому сотруднику добавить среднюю зарплату его отдела
SELECT name, department, salary,
       (SELECT AVG(salary) FROM employees e2
        WHERE e2.department = e.department) AS dept_avg
FROM   employees e;

Этот запрос работает, но менее эффективен, чем аналогичный через JOIN с GROUP BY.

Подзапрос в FROM: производная таблица

Подзапрос в FROM называется производной таблицей (derived table) или встроенным представлением. Он создаёт временный набор данных, с которым можно работать как с обычной таблицей — применять WHERE, JOIN, GROUP BY и всё остальное.

SELECT dept_stats.department,
       dept_stats.avg_salary,
       dept_stats.headcount
FROM (
    SELECT department,
           AVG(salary) AS avg_salary,
           COUNT(*)    AS headcount
    FROM   employees
    GROUP BY department
) dept_stats
WHERE dept_stats.headcount >= 3;

Внутренний запрос вычисляет статистику по отделам. Внешний запрос фильтрует результат — оставляет только отделы с 3+ сотрудниками.

Почему не просто HAVING COUNT(*) >= 3 в одном запросе? Иногда нет — они эквивалентны. Но производная таблица становится необходимой, когда нужно фильтровать по псевдониму из внутреннего запроса или когда логика слишком сложна для одного запроса.

Псевдоним обязателен. Производная таблица в FROM всегда должна иметь псевдоним (здесь dept_stats). Без него — синтаксическая ошибка.

Пример: проблема псевдонима, которую решает подзапрос в FROM

Вспомним: псевдонимы из SELECT недоступны в WHERE. Обходное решение — производная таблица:

-- Нельзя: annual_salary не существует в WHERE
SELECT name, salary * 12 AS annual_salary
FROM   employees
WHERE  annual_salary > 1200000;   -- ERROR

-- Можно: annual_salary «закрепляется» во внутреннем SELECT
SELECT name, annual_salary
FROM (
    SELECT name, salary * 12 AS annual_salary
    FROM   employees
) sub
WHERE annual_salary > 1200000;

Внешний запрос уже «видит» annual_salary как обычную колонку производной таблицы.

Производная таблица с JOIN

Производные таблицы можно соединять с другими таблицами через JOIN:

SELECT c.name,
       c.city,
       stats.orders_count,
       stats.total_spent
FROM   customers c
JOIN (
    SELECT customer_id,
           COUNT(*)      AS orders_count,
           SUM(amount)   AS total_spent
    FROM   orders
    WHERE  status = 'completed'
    GROUP BY customer_id
) stats ON c.id = stats.customer_id
ORDER BY stats.total_spent DESC;

Внутренний запрос предагрегирует заказы по клиенту. Внешний соединяет это с данными клиентов. Такой подход часто эффективнее, чем агрегирование после JOIN: сначала сжимаем данные, потом соединяем.

Проверь себя: зачем в примере выше агрегировать во внутреннем запросе, а не в внешнем? Что изменится если переписать без производной таблицы?

Многоуровневые производные таблицы

Производные таблицы можно вкладывать друг в друга:

SELECT *
FROM (
    SELECT department, avg_salary,
           RANK() OVER (ORDER BY avg_salary DESC) AS rank
    FROM (
        SELECT department, AVG(salary) AS avg_salary
        FROM   employees
        GROUP BY department
    ) dept_avgs
) ranked
WHERE rank <= 3;

Внутренний запрос считает среднюю зарплату по отделам. Средний — добавляет ранг. Внешний — фильтрует топ-3.

(Функция RANK() — оконная функция; подробнее она будет в более продвинутых темах. Здесь важно увидеть структуру многоуровневых подзапросов.)

LATERAL подзапросы (PostgreSQL)

PostgreSQL поддерживает специальный синтаксис LATERAL, который позволяет подзапросу в FROM ссылаться на колонки других таблиц в том же FROM. Это PostgreSQL-расширение:

SELECT c.name, recent.product, recent.amount
FROM   customers c,
LATERAL (
    SELECT product, amount
    FROM   orders
    WHERE  customer_id = c.id   -- ссылка на внешнюю строку
    ORDER BY created_at DESC
    LIMIT 1
) recent;

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

Практический пример: рейтинг отделов

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

SELECT department,
       avg_salary,
       headcount,
       ROUND(avg_salary * 100.0 / max_avg, 1) AS pct_of_max
FROM (
    SELECT department,
           ROUND(AVG(salary), 0) AS avg_salary,
           COUNT(*)              AS headcount
    FROM   employees
    GROUP BY department
) dept_stats
CROSS JOIN (
    SELECT MAX(AVG(salary)) AS max_avg
    FROM   employees
    GROUP BY department
) top
ORDER BY avg_salary DESC;

Производная таблица dept_stats — статистика по отделам. CROSS JOIN с ещё одной производной таблицей, содержащей одно значение max_avg, позволяет вычислить процент в SELECT. Это нетривиально без подзапросов.

Типичные ошибки с производными таблицами

1. Забыть псевдоним:

-- Ошибка
SELECT * FROM (SELECT department, COUNT(*) FROM employees GROUP BY department);
-- ERROR: subquery in FROM must have an alias

2. Ссылка на псевдоним из внутреннего запроса по неверному имени:

SELECT department, cnt   -- ошибка: нет таблицы, нужен алиас.cnt или просто cnt
FROM (SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department) stats;

-- Правильно:
SELECT department, cnt
FROM (SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department) stats;
-- или явно:
SELECT stats.department, stats.cnt FROM (...) stats;

3. Производная таблица с несколькими колонками там, где ожидается одна:

-- Ошибка: подзапрос в WHERE должен возвращать одну колонку для IN
WHERE id IN (SELECT id, name FROM customers);   -- два столбца — ошибка

Когда использовать подзапрос в FROM вместо JOIN

Производная таблица удобна:

Когда нужно предагрегировать данные перед соединением
Когда нужно «закрепить» псевдоним для дальнейшей фильтрации
Когда логика разбивается на ясные шаги (вычислить → отфильтровать)

Альтернатива — CTE (WITH-выражения): следующий урок разберёт их подробно. CTE делают то же самое, но синтаксически чище — каждый шаг именован и вынесен наверх, а не вложен.

Читаемость: производные таблицы vs плоский запрос

Большинство запросов с производными таблицами можно переписать без них — с HAVING или дополнительным JOIN. Выбор в пользу производной таблицы делается ради читаемости:

-- С производной таблицей: логика разбита на шаги
SELECT name, annual_salary
FROM (SELECT name, salary * 12 AS annual_salary FROM employees) sub
WHERE annual_salary > 1200000;

-- Плоский запрос: выражение повторяется
SELECT name, salary * 12 AS annual_salary
FROM   employees
WHERE  salary * 12 > 1200000;

Оба варианта корректны. Второй короче, но при сложном выражении (например, длинная формула вместо salary * 12) первый предпочтительнее — не нужно повторять вычисление в двух местах.

Краткий итог

Скалярный подзапрос в SELECT добавляет вычисляемое значение к каждой строке
Производная таблица в FROM — подзапрос с псевдонимом, работает как временная таблица
Производная таблица решает проблему недоступности псевдонимов в WHERE
Производные таблицы можно соединять через JOIN — полезно для предагрегации
LATERAL в PostgreSQL позволяет подзапросу в FROM ссылаться на другие таблицы в FROM

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

Скалярный подзапрос в SELECT, возвращающий одинаковое значение для всех строк (например, SELECT AVG(salary) FROM employees), PostgreSQL обычно вычисляет один раз и кэширует. Это эффективно.

Но коррелированный подзапрос в SELECT (ссылается на внешнюю строку) выполняется для каждой строки результата. При 10 000 строк — 10 000 выполнений. Это медленно.

-- Медленно: подзапрос выполняется для каждой из N строк
SELECT e.name,
       (SELECT MAX(salary) FROM employees WHERE department = e.department) AS dept_max
FROM   employees e;

-- Быстрее: JOIN с предагрегированной производной таблицей
SELECT e.name, dept.dept_max
FROM   employees e
JOIN   (SELECT department, MAX(salary) AS dept_max FROM employees GROUP BY department) dept
       ON e.department = dept.department;

Второй вариант: агрегирование происходит один раз, потом результат соединяется с основной таблицей. Это значительно эффективнее при большом числе строк.

Что дальше

Вы умеете вкладывать запросы в WHERE, SELECT и FROM. Следующий урок — операторы EXISTS, ANY и ALL: специализированные инструменты для работы с подзапросами, которые часто выразительнее IN/NOT IN.

Подзапросы в SELECT и FROM