Складні SQL-запити

У попередній лекції ми поєднували таблиці через JOIN, формували групи та обчислювали агрегати. Тепер використаємо ці інструменти як основу для складніших запитань: відфільтруємо групові результати, вкладемо один запит в інший, перевіримо існування пов'язаних рядків і виконаємо операції над множинами результатів.

Цілі лекції

Після опрацювання матеріалу ви зможете:

розрізняти призначення WHERE і HAVING та поєднувати їх;
використовувати скалярні, багаторядкові й корельовані підзапити;
застосовувати IN, ANY, ALL, EXISTS і NOT EXISTS;
розміщувати підзапити в FROM і SELECT;
поєднувати сумісні результати через UNION, UNION ALL, INTERSECT, EXCEPT;
враховувати NULL і тризначну логіку SQL;
складати підсумкові запити з JOIN, агрегацією та підзапитами.

Передумови

Потрібні знання SELECT, WHERE, JOIN, агрегатних функцій і GROUP BY. Приклади розраховані на PostgreSQL.

Навчальний стенд

Виконайте цей блок один раз у порожній схемі PostgreSQL. Він створює невеликий журнал курсів. В оцінках навмисно є NULL: роботу призначено, але ще не перевірено.

DROP TABLE IF EXISTS grades, enrollments, courses, students CASCADE;

CREATE TABLE students (
    student_id integer PRIMARY KEY,
    full_name text NOT NULL,
    group_code text NOT NULL,
    city text
);

CREATE TABLE courses (
    course_id integer PRIMARY KEY,
    title text NOT NULL,
    semester integer NOT NULL
);

CREATE TABLE enrollments (
    student_id integer REFERENCES students(student_id),
    course_id integer REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

CREATE TABLE grades (
    grade_id integer PRIMARY KEY,
    student_id integer NOT NULL REFERENCES students(student_id),
    course_id integer NOT NULL REFERENCES courses(course_id),
    score integer CHECK (score BETWEEN 0 AND 100),
    graded_at date
);

INSERT INTO students VALUES
    (1, 'Анна Коваль', 'ІПЗ-31', 'Львів'),
    (2, 'Богдан Левчук', 'ІПЗ-31', 'Луцьк'),
    (3, 'Віра Мельник', 'ІПЗ-32', 'Львів'),
    (4, 'Ганна Сорока', 'ІПЗ-32', NULL),
    (5, 'Денис Ткач', 'ІПЗ-31', 'Рівне'),
    (6, 'Олена Яремчук', 'ІПЗ-32', 'Луцьк');

INSERT INTO courses VALUES
    (10, 'Бази даних', 7),
    (20, 'Вебпрограмування', 7),
    (30, 'Тестування ПЗ', 7),
    (40, 'Комп''ютерні мережі', 8);

INSERT INTO enrollments VALUES
    (1, 10), (1, 20), (1, 30),
    (2, 10), (2, 20),
    (3, 10), (3, 30),
    (4, 10), (4, 20),
    (5, 20), (5, 30),
    (6, 10), (6, 30);

INSERT INTO grades VALUES
    (1, 1, 10, 92, '2026-12-10'),
    (2, 1, 10, 84, '2026-12-17'),
    (3, 1, 20, 76, '2026-12-12'),
    (4, 2, 10, 65, '2026-12-10'),
    (5, 2, 20, 73, '2026-12-12'),
    (6, 3, 10, 88, '2026-12-10'),
    (7, 3, 30, 91, '2026-12-15'),
    (8, 4, 10, NULL, NULL),
    (9, 4, 20, 58, '2026-12-12'),
    (10, 5, 20, 81, '2026-12-12'),
    (11, 5, 30, 81, '2026-12-15'),
    (12, 6, 10, 95, '2026-12-10'),
    (13, 6, 30, NULL, NULL);

1. `WHERE` і `HAVING`: два різні етапи

WHERE відбирає вхідні рядки до групування. HAVING відбирає готові групи після GROUP BY. Тому агрегатну умову зазвичай не можна перенести у WHERE.

Знайдемо курси 7-го семестру, на яких є щонайменше три перевірені оцінки, а середня оцінка вища за 75:

SELECT c.title,
       COUNT(g.score) AS graded_count,
       ROUND(AVG(g.score), 2) AS avg_score
FROM courses AS c
JOIN grades AS g ON g.course_id = c.course_id
WHERE c.semester = 7
GROUP BY c.course_id, c.title
HAVING COUNT(g.score) >= 3
   AND AVG(g.score) > 75
ORDER BY avg_score DESC;

Логічна послідовність тут така:

FROM і JOIN утворюють набір рядків;
WHERE залишає рядки 7-го семестру;
GROUP BY створює групу для кожного курсу;
агрегати обчислюються для груп;
HAVING відкидає групи, що не виконують умови;
SELECT формує стовпці результату;
ORDER BY упорядковує результат.

Порівняйте дві різні вимоги:

-- Середнє лише серед оцінок не нижче 60.
SELECT course_id, AVG(score)
FROM grades
WHERE score >= 60
GROUP BY course_id;

-- Середнє серед усіх перевірених оцінок,
-- але показано лише групи із середнім не нижче 60.
SELECT course_id, AVG(score)
FROM grades
GROUP BY course_id
HAVING AVG(score) >= 60;

У першому запиті оцінка 58 не бере участі в середньому. У другому бере участь, але може вплинути на те, чи пройде вся група. Це не взаємозамінні запити.

HAVING технічно може містити умову без агрегату, але умову над звичайними рядками краще ставити у WHERE: так точніше передано намір і раніше зменшено набір даних.

`NULL` в агрегатах

COUNT(score), AVG(score), SUM(score), MIN(score) і MAX(score) ігнорують NULL. COUNT(*) рахує рядки незалежно від NULL.

SELECT course_id,
       COUNT(*) AS all_grade_rows,
       COUNT(score) AS checked_rows
FROM grades
GROUP BY course_id
HAVING COUNT(*) > COUNT(score);

Запит знаходить курси, де є хоча б одна неперевірена робота.

2. Підзапит як частина іншого запиту

Підзапит - це SELECT, вкладений в іншу SQL-команду. Його форма має відповідати місцю використання.

Скалярний підзапит

Скалярний підзапит повертає одне значення: один стовпець і не більш ніж один рядок. Знайдемо перевірені оцінки, вищі за загальне середнє:

SELECT s.full_name, c.title, g.score
FROM grades AS g
JOIN students AS s ON s.student_id = g.student_id
JOIN courses AS c ON c.course_id = g.course_id
WHERE g.score > (
    SELECT AVG(score)
    FROM grades
)
ORDER BY g.score DESC;

Якщо скалярний підзапит поверне кілька рядків, PostgreSQL повідомить про помилку. Якщо не поверне жодного, скалярним результатом буде NULL, а порівняння з ним дасть UNKNOWN.

Багаторядкові підзапити: `IN`, `ANY`, `ALL`

IN перевіряє належність значення до набору. Знайдемо студентів, записаних на курс «Бази даних»:

SELECT full_name
FROM students
WHERE student_id IN (
    SELECT student_id
    FROM enrollments
    WHERE course_id = 10
)
ORDER BY full_name;

comparison ANY (subquery) означає, що порівняння має бути істинним хоча б для одного значення. = ANY (...) еквівалентне IN (...).

SELECT full_name
FROM students
WHERE student_id = ANY (
    SELECT student_id
    FROM grades
    WHERE score >= 90
);

comparison ALL (subquery) вимагагає істинності порівняння для кожного повернутого значення. Наприклад, оцінки, вищі за всі перевірені оцінки Богдана:

SELECT grade_id, student_id, score
FROM grades
WHERE score > ALL (
    SELECT score
    FROM grades
    WHERE student_id = 2
      AND score IS NOT NULL
);

Порожній набір має важливу особливість: умова з ANY над ним є FALSE, а умова з ALL - TRUE. Тому завжди перевіряйте, чи відповідає така поведінка змісту задачі.

3. Корельовані підзапити

Звичайний некорельований підзапит можна виконати незалежно від зовнішнього запиту. Корельований підзапит посилається на поточний рядок зовнішнього запиту, тому логічно перевіряється для кожного такого рядка.

Знайдемо оцінки, вищі за середню оцінку того самого курсу:

SELECT s.full_name, c.title, g.score
FROM grades AS g
JOIN students AS s ON s.student_id = g.student_id
JOIN courses AS c ON c.course_id = g.course_id
WHERE g.score > (
    SELECT AVG(g2.score)
    FROM grades AS g2
    WHERE g2.course_id = g.course_id
)
ORDER BY c.title, g.score DESC;

Посилання g2.course_id = g.course_id створює кореляцію: g належить зовнішньому запиту. Аліаси тут обов'язкові для зрозумілості.

Корельований підзапит не завжди є найшвидшим способом. PostgreSQL може оптимізувати його, але для великих даних варто також розглянути попередню агрегацію в FROM і перевірити план виконання. Критерій вибору зараз - правильність і читабельність, а оптимізацію планів детально вивчатимемо пізніше.

4. `EXISTS` і `NOT EXISTS`

EXISTS (subquery) перевіряє, чи повернув підзапит хоча б один рядок. Значення стовпців неважливі, тому в ньому прийнято писати SELECT 1.

Студенти, які мають хоча б одну оцінку нижчу за 60:

SELECT s.student_id, s.full_name
FROM students AS s
WHERE EXISTS (
    SELECT 1
    FROM grades AS g
    WHERE g.student_id = s.student_id
      AND g.score < 60
);

Студенти, які не мають жодної неперевіреної роботи:

SELECT s.student_id, s.full_name
FROM students AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM grades AS g
    WHERE g.student_id = s.student_id
      AND g.score IS NULL
)
ORDER BY s.student_id;

NOT EXISTS особливо корисний для вимог «немає жодного». Він безпечніший за NOT IN, якщо підзапит може повертати NULL.

-- Небезпечний шаблон: один NULL у наборі може зробити результат UNKNOWN.
WHERE student_id NOT IN (SELECT student_id FROM some_table)

-- Надійний антизапит із явним зв'язком.
WHERE NOT EXISTS (
    SELECT 1
    FROM some_table AS x
    WHERE x.student_id = s.student_id
)

5. Підзапити у `FROM` і `SELECT`

Підзапит у FROM створює похідну таблицю. У PostgreSQL вона повинна мати аліас. Це зручно, коли спочатку треба отримати проміжний підсумок, а потім фільтрувати або поєднати його.

SELECT course_stats.title,
       course_stats.checked_count,
       course_stats.avg_score
FROM (
    SELECT c.course_id,
           c.title,
           COUNT(g.score) AS checked_count,
           ROUND(AVG(g.score), 2) AS avg_score
    FROM courses AS c
    LEFT JOIN grades AS g ON g.course_id = c.course_id
    GROUP BY c.course_id, c.title
) AS course_stats
WHERE course_stats.checked_count >= 2
ORDER BY course_stats.avg_score DESC NULLS LAST;

Підзапит у SELECT має бути скалярним для кожного зовнішнього рядка. Покажемо кожен курс і кількість записаних студентів:

SELECT c.title,
       (
           SELECT COUNT(*)
           FROM enrollments AS e
           WHERE e.course_id = c.course_id
       ) AS enrolled_count
FROM courses AS c
ORDER BY c.course_id;

Цей запис компактний, але кілька подібних підзапитів можуть приховати складність. Якщо потрібні численні показники з одних даних, часто зрозуміліше виконати один JOIN і групування або приєднати одну агреговану похідну таблицю.

6. Операції над множинами результатів

Операції над множинами додають рядки вертикально. Це відрізняється від JOIN, який поєднує пов'язані стовпці горизонтально.

UNION поєднує результати й усуває дублікати;
UNION ALL поєднує результати та зберігає дублікати;
INTERSECT залишає рядки, присутні в обох результатах;
EXCEPT залишає рядки першого результату, яких немає в другому.

Для сумісності кожен запит повинен повертати однакову кількість стовпців, а типи у відповідних позиціях мають бути сумісними. Назви стовпців результату беруться з першого SELECT.

-- Міста студентів груп ІПЗ-31 та ІПЗ-32 без повторів.
SELECT city
FROM students
WHERE group_code = 'ІПЗ-31' AND city IS NOT NULL
UNION
SELECT city
FROM students
WHERE group_code = 'ІПЗ-32' AND city IS NOT NULL
ORDER BY city;

Замініть UNION на UNION ALL, і повторювані міста збережуться. Якщо усунення дублікатів не потрібне, UNION ALL точніше передає вимогу й зазвичай потребує менше роботи.

-- Студенти, записані і на БД, і на тестування.
SELECT student_id FROM enrollments WHERE course_id = 10
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 30;

-- Студенти БД, які не записані на вебпрограмування.
SELECT student_id FROM enrollments WHERE course_id = 10
EXCEPT
SELECT student_id FROM enrollments WHERE course_id = 20;

INTERSECT має вищий пріоритет, ніж UNION та EXCEPT. У складних виразах використовуйте дужки, щоб порядок був очевидним. ORDER BY для всього складеного результату ставлять наприкінці.

7. `NULL` і тризначна логіка

SQL має три логічні результати: TRUE, FALSE та UNKNOWN. NULL означає відсутнє або невідоме значення, тому NULL = NULL, score = NULL і score <> NULL дають UNKNOWN, а не TRUE чи FALSE. WHERE та HAVING залишають лише рядки або групи, для яких умова дорівнює TRUE.

SELECT grade_id, score
FROM grades
WHERE score IS NULL;       -- правильно

SELECT grade_id, score
FROM grades
WHERE score = NULL;        -- не поверне очікувані рядки

Логічні правила, важливі для запитів:

TRUE AND UNKNOWN дає UNKNOWN;
FALSE AND UNKNOWN дає FALSE;
TRUE OR UNKNOWN дає TRUE;
FALSE OR UNKNOWN дає UNKNOWN;
NOT UNKNOWN також дає UNKNOWN.

У підзапитах це пояснює небезпеку NOT IN. Якщо список містить NULL, жодне порівняння виду x <> NULL не стане TRUE. Для антиумови зазвичай використовуйте NOT EXISTS, а для явної перевірки відсутності значення - IS NULL або IS NOT NULL.

COALESCE(value, replacement) замінює NULL для виведення або визначеної бізнес-логіки, але не слід бездумно перетворювати невідому оцінку на нуль: «не перевірено» і «отримано 0» мають різний зміст.

8. Як будувати складний запит

Не намагайтеся одразу написати один великий блок. Використовуйте послідовність:

Сформулюйте один рядок очікуваного результату: що він означає?
Визначте початкові таблиці та зв'язки.
Відберіть рядки через WHERE.
Додайте групування й агрегати, якщо потрібен підсумок.
Відберіть групи через HAVING.
Додайте підзапит лише там, де його форма відповідає задачі.
Окремо запустіть кожен підзапит і перевірте його кардинальність.
Перевірте випадки з порожнім набором, дублями та NULL.

Практичні завдання

Завдання 1. Фільтрація груп

Для кожної групи студентів обчисліть кількість перевірених оцінок і середню оцінку. Враховуйте лише оцінки 7-го семестру не нижчі за 60. Покажіть лише групи, де враховано щонайменше три оцінки й середнє вище за 80. Виведіть group_code, checked_count, avg_score.

Завдання 2. Підзапити й відсутні роботи

Виведіть ідентифікатор та ім'я кожного студента, який:

має хоча б одну перевірену оцінку, вищу за загальну середню перевірену оцінку;
не має жодного рядка оцінки зі score IS NULL.

Розв'яжіть задачу за допомогою двох корельованих перевірок EXISTS / NOT EXISTS.

Завдання 3. Прикладний підсумковий звіт семестру

Адміністрації потрібен звіт про курси 7-го семестру. Для кожного курсу виведіть:

назву курсу;
кількість записаних студентів;
кількість перевірених оцінок;
середню перевірену оцінку, округлену до двох знаків;
кількість неперевірених робіт.

Залиште лише курси, де записано щонайменше чотири студенти. Додайте стовпець attention_reason: значення є неперевірені, якщо існує оцінка з NULL; низьке середнє, якщо неперевірених немає, але середня нижча за 75; інакше без зауважень. Побудуйте запит із похідними таблицями або скалярними підзапитами так, щоб не перемножити рядки enrollments та grades. Після основного звіту через UNION ALL додайте рядок УСЬОГО із загальними показниками для показаних курсів.

Підсумок семестру

За 7-й семестр ми пройшли повний шлях від змісту даних до запиту:

визначали предметну область, сутності, атрибути, зв'язки й кардинальності;
переходили від ER-моделі до таблиць, первинних і зовнішніх ключів;
забезпечували доменну, сутнісну та посилальну цілісність;
усували аномалії через нормалізацію та обґрунтовували компроміси;
створювали й змінювали схему через DDL та керували доступом;
виконували INSERT, UPDATE, DELETE, фільтрацію і сортування;
поєднували таблиці, групували дані та обчислювали агрегати;
сьогодні додали групову фільтрацію, підзапити й операції над множинами.

Складний SQL-запит не є набором випадкових ключових слів. Він відтворює точне питання до правильно спроєктованих даних. Перед семестровим заліком умійте пояснити не лише синтаксис, а й значення кожного рядка результату, вплив NULL, можливі дублікати та причину вибору JOIN, підзапиту або множинної операції.

Короткий підсумок

WHERE фільтрує рядки до групування, HAVING - групи після нього.
Скалярний підзапит дає одне значення; багаторядковий поєднують з IN, ANY, ALL.
Корельований підзапит посилається на поточний рядок зовнішнього запиту.
EXISTS і NOT EXISTS перевіряють наявність або відсутність рядків і коректно працюють у задачах з NULL.
Підзапит у FROM є похідною таблицею, а підзапит у SELECT має бути скалярним.
Множинні операції потребують однакової кількості сумісних стовпців; лише UNION ALL зберігає всі дублікати.
Через тризначну логіку порівняння з NULL дає UNKNOWN; використовуйте IS NULL, IS NOT NULL і обережно застосовуйте NOT IN.