Використання підзапитів у 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.