0Pricing
SQL Interview Prep · Урок

Подзапросы в разделе FROM (производные таблицы)

Оборачивайте запрос во временную виртуальную таблицу и узнайте, почему псевдонимы обязательны.

«Подзапросы в разделе FROM (производные таблицы)» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Что такое производная таблица

Подзапрос в предложении FROM называется производной таблицей (или встроенным представлением). Вместо одного значения он возвращает целый набор результатов, с которым внешний запрос работает так, будто это настоящая таблица.

  • В нём может быть много строк и много столбцов.
  • С ним можно выполнять запросы, соединять его и фильтровать его так же, как любую таблицу.

Интервьюеры используют производные таблицы, чтобы проверить, умеете ли Вы разбивать задачу на этапы.

Псевдонимы обязательны

Главная ловушка: производная таблица обязательно должна иметь псевдоним. Без него большинство СУБД отклоняет запрос.

  • MySQL: каждая производная таблица должна иметь собственный псевдоним.
  • PostgreSQL: подзапрос в FROM должен иметь псевдоним.

Дайте ей имя — здесь dept_avg — и сможете обращаться к её столбцам по этому имени.

SELECT dept_avg.dept_id, dept_avg.avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS dept_avg;

Зачем предварительно агрегировать данные в производной таблице

Частая задача на собеседовании: показать каждого сотрудника рядом со средней зарплатой в его отделе. Нельзя напрямую объединить строку с деталями и агрегат без проблем с группировкой.

Чистое решение — вычислить среднюю зарплату по каждому отделу в производной таблице, а затем присоединить её обратно к строкам с деталями. Сначала производная таблица сворачивается до одной строки на отдел.

SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d ON e.dept_id = d.dept_id;

Фильтрация результата агрегирования

Производные таблицы позволяют фильтровать вычисленный агрегат без сложных конструкций с HAVING во внешнем запросе. Предположим, нам нужны только отделы, в которых средняя зарплата превышает 60000.

Сначала мы выполняем агрегирование внутри, а затем снаружи применяем обычное WHERE к производному столбцу. Внешний запрос видит avg_salary как обычный столбец.

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d
WHERE avg_salary > 60000;

Два уровня агрегирования

Производные таблицы особенно полезны, когда нужен агрегат от агрегата — классическая задача на собеседовании: каково среднее значение средних зарплат по отделам?

Нельзя напрямую вложить AVG(AVG(...)). Внутренний запрос выдаёт одно среднее значение на отдел, а внешний запрос вычисляет среднее этих значений.

SELECT AVG(avg_salary) AS avg_of_dept_avgs
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d;

Именование вычисляемых столбцов

Любому выражению в производной таблице нужен псевдоним, если Вы хотите ссылаться на него снаружи. В противном случае внутреннее salary * 12 получит имя, назначенное СУБД, на которое нельзя надёжно полагаться.

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

SELECT name, annual_salary
FROM (
  SELECT name, salary * 12 AS annual_salary
  FROM employees
) AS yearly
WHERE annual_salary > 100000;

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

Можно соединять несколько производных таблиц. Здесь мы сравниваем численность сотрудников каждого отдела с его общим фондом оплаты труда, соединяя два предварительно агрегированных подзапроса.

Каждая производная таблица отвечает на один дополнительный вопрос, а соединение объединяет их в итоговый отчёт. Именно такой поэтапный подход ценится на собеседованиях для специалистов среднего уровня.

SELECT c.dept_id, c.headcount, p.payroll
FROM (
  SELECT dept_id, COUNT(*) AS headcount
  FROM employees GROUP BY dept_id
) AS c
JOIN (
  SELECT dept_id, SUM(salary) AS payroll
  FROM employees GROUP BY dept_id
) AS p ON c.dept_id = p.dept_id;

Область видимости: внешний запрос не видит внутреннее содержимое

Важное правило: внешний запрос может обращаться только к тем столбцам, которые производная таблица предоставляет в своём списке SELECT. Столбцы, используемые только внутри подзапроса, снаружи невидимы.

Если внутренний запрос выбирает dept_id и avg_salary, то salary или name недоступны снаружи — они были использованы при агрегировании. Интервьюеры проверяют понимание этой границы области видимости.

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
) AS d;

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

Производная таблица и общее табличное выражение (CTE) часто приводят к одному плану выполнения. Интервьюер может спросить, почему Вы выбрали бы один вариант, а не другой:

  • Производная таблица: встроенная, подходит для разового использования.
  • CTE (WITH): объявляется в начале, удобна для чтения и может использоваться повторно, если на неё ссылаются несколько раз.

Для глубоко вложенной логики конвейер CTE читается сверху вниз, а производная таблица — изнутри наружу.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
)
SELECT * FROM dept_avg WHERE avg_salary > 60000;

LATERAL / Коррелированный подзапрос в FROM

Обычно подзапрос в FROM не может обращаться к строкам внешнего запроса. LATERAL (PostgreSQL) или CROSS APPLY (SQL Server) снимает это ограничение и позволяет производной таблице выполняться для каждой строки внешнего запроса.

Это позволяет получать первые N результатов для каждой строки. Знание этого ключевого слова показывает глубокое понимание SQL даже на собеседовании для специалиста среднего уровня.

SELECT d.dept_name, top_emp.name, top_emp.salary
FROM departments d
CROSS JOIN LATERAL (
  SELECT name, salary FROM employees e
  WHERE e.dept_id = d.id
  ORDER BY salary DESC LIMIT 1
) AS top_emp;

Короткий ответ для собеседования

Если Вас спросят о подзапросах в предложении FROM, скажите: «Производная таблица — это подзапрос в FROM, который возвращает набор результатов, используемый внешним запросом как таблица. У него обязательно должен быть псевдоним; внешний запрос видит только выбранные им столбцы. Это идеальный вариант для предварительного агрегирования перед соединением или для агрегирования уже агрегированного результата».

Добавьте, что LATERAL позволяет обращаться к строкам внешнего запроса, — и Вы рассмотрели все важные аспекты.

Быстрая проверка

Выберите утверждение, которое всегда необходимо для подзапроса в предложении FROM.

Итоги

Производные таблицы — закрепим:

  • Подзапрос в FROM возвращает виртуальную таблицу — множество строк и столбцов.
  • У него обязательно должен быть псевдоним; внешний запрос видит только выбранные им столбцы.
  • Используйте его для предварительного агрегирования перед соединением, фильтрации по агрегатам или агрегирования агрегированного результата.
  • CTE — это удобная именованная альтернатива; LATERAL/CROSS APPLY позволяют обращаться к строкам внешнего запроса.

Далее: подзапросы для проверки принадлежности множеству с помощью IN, ANY и ALL.

Часто задаваемые вопросы

Урок «Подзапросы в разделе FROM (производные таблицы)» бесплатный?

Да — полный текст урока «Подзапросы в разделе FROM (производные таблицы)» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Подзапросы в разделе FROM (производные таблицы)»?

Оборачивайте запрос во временную виртуальную таблицу и узнайте, почему псевдонимы обязательны. Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «Подзапросы в разделе FROM (производные таблицы)»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Скалярные подзапросы в SELECT и WHERE
  2. Подзапросы в разделе FROM (производные таблицы)
  3. Подзапросы IN, ANY и ALL
  4. Производительность EXISTS и IN
← Назад к SQL Interview Prep