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

Хотелось бы еще раз вернуться к теме подзапросов в SELECT. Особенно с упором на факт, что внутренний запрос может ссылаться на данные внешнего запроса. Вроде бы и все просто, но не очень. Давайте еще раз углубимся в эту тему...

Подзапросы в SELECT позволяют добавлять дополнительные столбцы с вычисляемыми значениями или данными, которые зависят от других записей или таблиц. Например, вы можете вывести список студентов с указанием их среднего балла, количества курсов, на которые они записаны, или текущего максимального балла в группе. Это полезно, когда нужно проанализировать данные "на ходу", создавая сводные колонки без предварительной обработки данных.

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

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

SELECT column1,
       column2,
       (SELECT агрегация_или_условие FROM другая_таблица WHERE условие) AS новое_имя_столбца
FROM основная_таблица;

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

Пример 1: Добавление среднего балла студента

Давайте начнем с простого и полезного запроса: у нас есть таблица students и таблица grades, в которой хранятся оценки студентов.

Таблица students:

Таблица grades:

Теперь мы хотим получить список студентов с их именами и средней оценкой. Для этого используем подзапрос в SELECT:

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade) 
     FROM grades g 
     WHERE g.student_id = s.id) AS average_grade
FROM students s;

Результат:

Здесь подзапрос (SELECT AVG(g.grade) FROM grades g WHERE g.student_id = s.id) вычисляет среднюю оценку для каждого студента. Он возвращает по одному значению для каждой строки из таблицы students, и это удобно, когда не хочется делать JOIN или предварительные представления.

Пример 2: Подсчет количества курсов для каждого студента

Теперь давайте добавим данные о студентах: сколько курсов они посещают. Для этого у нас есть дополнительные таблицы:

Таблица enrollments:

Выводим список студентов с количеством курсов, на которые они записаны:

SELECT
    s.id,
    s.name,
    (SELECT COUNT(*)
     FROM enrollments e
     WHERE e.student_id = s.id) AS course_count -- отсылка на таблицу students внешнего запроса
FROM students s;

Результат:

Подзапрос (SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.id) подсчитывает количество записей в таблице enrollments для каждого студента.

Агрегация данных в подзапросах

Часто подзапросы в SELECT используются для вычисления агрегированных данных. Такие функции, как AVG, SUM, COUNT, MAX, MIN, позволяют обрабатывать данные прямо внутри других запросов.

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

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

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Результат:

Этот подзапрос (SELECT SUM(g.grade) FROM grades g WHERE g.student_id = s.id) суммирует оценки каждого студента. Если у студента нет оценок, то результат будет NULL, так как SUM возвращает NULL при отсутствии значений.

Ограничения и рекомендации

1. Производительность. Подзапросы в SELECT выполняются отдельно для каждой строки основной таблицы. Это может привести к значительным задержкам на больших наборах данных. Если возможно, замените их на JOIN или используйте заранее подготовленные агрегированные данные. Например:

SELECT
    s.id,
    s.name,
    g.total_grade
FROM students s
LEFT JOIN (
    SELECT student_id, SUM(grade) AS total_grade
    FROM grades
    GROUP BY student_id
) g ON s.id = g.student_id;

Этот подход с JOIN более оптимален, так как группировка и подсчет выполняются один раз.

2. Проблемы с NULL.

Если в подзапросе отсутствуют данные, результат будет NULL. Это может быть неожиданным. Пример:

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Если для студента нет записей в grades, результат total_grade будет NULL. Чтобы заменить NULL на 0, используйте функцию COALESCE:

SELECT
    s.id,
    s.name,
    COALESCE((SELECT SUM(g.grade)
              FROM grades g
              WHERE g.student_id = s.id), 0) AS total_grade
FROM students s;

Да, тут в качестве первого параметра функции COALESCE мы передаем

(
    SELECT SUM(g.grade)
    FROM grades g
    WHERE g.student_id = s.id
)

Оптимизация подзапросов в SELECT

Чтобы избежать избыточных вычислений и улучшить производительность:

1. Используйте индексы на столбцах, которые участвуют в подзапросах. Например, индексация student_id в таблице grades ускорит выполнение фильтрации.
2. Заменяйте подзапросы на предварительно подготовленные агрегированные данные с помощью JOIN, если это возможно.
3. Ограничьте объем данных, обрабатываемых подзапросами, используя фильтрацию (WHERE).

Заключительный пример: объединение подзапросов

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

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS average_grade,
    (SELECT COUNT(*) 
     FROM enrollments e 
     WHERE e.student_id = s.id) AS course_count,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Этот запрос возвращает полный профиль студента, собранный с помощью мощи подзапросов. Мы видим среднюю и суммарную оценку, а также количество курсов, на которые записан каждый студент. Подобная конструкция — отличный способ быстро получить агрегированную информацию без создания отдельных VIEW или объединений через JOIN.

2

Задача

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

Недоступна

Найти средний балл студента

Найти средний балл студента

Открыть

2

Задача

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

Недоступна

Подсчитать количество курсов для каждого студента

Подсчитать количество курсов для каждого студента

Открыть