Использование подзапросов в FROM

Давайте еще сильнее углубимся в кроличью нору: рассмотрим, как использовать подзапросы в конструкции FROM. Это один из самых частых подходов разработчиков SQL, поскольку позволяет создавать мощные временные таблицы прямо "на месте" и переиспользовать их, как будто они существуют в базе данных.

Представьте, что вы собираете отчёт, который требует расчётов, группировок или фильтрации данных, но при этом вы не хотите создавать дополнительные временные таблицы на сервере. Что делать? Вот тут и приходят на помощь подзапросы в FROM. Они позволяют:

• Временно объединять данные или агрегировать их перед основным запросом.
• Создавать структурированные наборы данных "на лету".
• Сократить количество операций, требуя от базы данных минимального сохранения промежуточных данных.

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

Основы подзапросов в FROM

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

1. Написать подзапрос в конструкцию FROM в круглых скобках.
2. Назначить алиас (псевдоним) для подзапроса.
3. Использовать этот псевдоним, как если бы это была полноценная таблица.

Синтаксис

SELECT колонки
FROM (
    SELECT колонки
    FROM таблица
    WHERE условие
) AS псевдоним
WHERE внешнее_условие;

Звучит страшновато? Давайте перейдём к примерам.

Пример: Студенты и средний балл

Предположим, у нас есть две таблицы:

students (данные о студентах — их имя и ID):

grades (данные об оценках студентов):

Теперь задача: получить список студентов и их средний балл.

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

SELECT s.student_name, g.avg_grade
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS g
JOIN students AS s ON s.student_id = g.student_id;

Результат:

Временные таблицы "на лету"

Подзапросы в FROM особенно полезны, когда вам нужно делать более одного уровня обработки данных. Например, если вы хотите не просто получить средний балл, но и рассчитать максимально возможный балл для каждого студента — всё в одном запросе.

SELECT g.student_id, g.avg_grade, g.max_grade
FROM (
    SELECT student_id, 
           AVG(grade) AS avg_grade, 
           MAX(grade) AS max_grade
    FROM grades
    GROUP BY student_id
) AS g;

Результат:

Обратите внимание, что это работает, как полноценная временная таблица со своими аналогами столбцов: avg_grade и max_grade.

Когда подзапросы в FROM лучше всего использовать?

Для агрегированных данных. Если вы хотите сначала выполнить подсчёты (например, посчитать среднее, суммы или максимумы), а затем объединить результаты с другими таблицами.

Для фильтрации данных. Когда нужно отфильтровать данные до объединения с основной таблицей.

Для упрощения сложных запросов. Разделение сложных задач на этапы помогает избежать путаницы.

Пример: Отчёт о студентах с двумя уровнями обработки

Давайте представим, что мы хотим найти студентов, у которых средний балл выше 80. Мы начнём с подзапроса, который считает средние баллы, и затем используем его в фильтре.

SELECT s.student_name, g.avg_grade
FROM students AS s
JOIN (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS g ON s.student_id = g.student_id
WHERE g.avg_grade > 80;

Результат:

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

Обязательный псевдоним. Всегда присваивайте алиас подзапросу (например, AS g), иначе PostgreSQL не поймёт, как обращаться к этой "временной таблице".

Оптимизация. Подзапросы в FROM могут быть медленнее объединений таблиц (JOIN), особенно если вы фильтруете данные внутри подзапроса.

Индексация. Убедитесь, что поля, используемые для объединения, индексы и фильтры оптимизированы — это сильно влияет на производительность.

Пример сложного запроса: курсы и количество студентов

Теперь давайте сделаем реальную задачу посложнее. Представьте, что у нас есть такая таблица:

courses (список курсов):

И enrollments (записи студентов на курсы):

Теперь мы хотим узнать, сколько студентов записано на каждый курс.

SELECT c.course_name, e.students_count
FROM courses AS c
JOIN (
    SELECT course_id, COUNT(student_id) AS students_count
    FROM enrollments
    GROUP BY course_id
) AS e ON c.course_id = e.course_id;

Результат:

Надеюсь лекция вам понравилась... но следующая будет еще интереснее :)

2

Задача

SQL SELF, 14 уровень, 1 лекция

Недоступна

Подсчет числа студентов с оценками

Подсчет числа студентов с оценками

Открыть

2

Задача

SQL SELF, 14 уровень, 1 лекция

Недоступна

Количество студентов на каждом курсе

Количество студентов на каждом курсе

Открыть