JOIN і групування даних

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

Цілі лекції

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

пояснити, чому нормалізовані дані доводиться з'єднувати під час читання;
записувати з'єднання в синтаксисі ANSI SQL-92;
обирати INNER, LEFT, RIGHT, FULL, CROSS або self join відповідно до задачі;
формулювати точну умову з'єднання та розпізнавати причини дублікатів;
застосовувати COUNT, SUM, AVG, MIN, MAX;
групувати результати за допомогою GROUP BY;
відрізняти фільтрацію рядків у WHERE від початкової фільтрації груп у HAVING.

Передумови

Потрібно знати призначення первинного й зовнішнього ключів, уміти виконувати CREATE TABLE, INSERT і простий SELECT, а також використовувати WHERE та ORDER BY.

1. Навчальна схема PostgreSQL

Усі приклади нижче використовують одну невелику базу коледжу. Скопіюйте скрипт у pgAdmin або DBeaver і виконайте його цілком. DROP TABLE розміщено у зворотному порядку залежностей, тому скрипт можна запускати повторно.

DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS teachers;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
    department_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE teachers (
    teacher_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name text NOT NULL,
    department_id integer REFERENCES departments(department_id),
    mentor_id integer REFERENCES teachers(teacher_id)
);

CREATE TABLE students (
    student_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name text NOT NULL,
    department_id integer NOT NULL REFERENCES departments(department_id)
);

CREATE TABLE courses (
    course_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    teacher_id integer REFERENCES teachers(teacher_id),
    hours integer NOT NULL CHECK (hours > 0)
);

CREATE TABLE enrollments (
    student_id integer REFERENCES students(student_id),
    course_id integer REFERENCES courses(course_id),
    grade numeric(4, 1) CHECK (grade BETWEEN 0 AND 100),
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO departments (name) VALUES
    ('Інженерія програмного забезпечення'),
    ('Комп''ютерні науки'),
    ('Кібербезпека');

INSERT INTO teachers (full_name, department_id, mentor_id) VALUES
    ('Олена Коваль', 1, NULL),
    ('Андрій Бондар', 2, NULL),
    ('Ірина Левченко', 1, 1),
    ('Максим Гнатюк', 3, 2),
    ('Наталія Сова', NULL, 1);

INSERT INTO students (full_name, department_id) VALUES
    ('Марія Іваненко', 1),
    ('Олег Петренко', 1),
    ('Софія Мельник', 2),
    ('Данило Шевчук', 3),
    ('Анна Кравець', 2);

INSERT INTO courses (title, teacher_id, hours) VALUES
    ('Бази даних', 1, 90),
    ('Алгоритми', 2, 72),
    ('Веброзробка', 3, 60),
    ('Основи безпеки', 4, 54),
    ('Хмарні технології', NULL, 36);

INSERT INTO enrollments (student_id, course_id, grade) VALUES
    (1, 1, 91),
    (1, 2, 84),
    (2, 1, 76),
    (3, 1, 88),
    (3, 2, 95),
    (3, 3, NULL),
    (4, 4, 82);

Схема нормалізована: назва дисципліни зберігається один раз у courses, ім'я студента - один раз у students, а таблиця enrollments фіксує зв'язок багато-до-багатьох і оцінку. Щоб показати «Марія Іваненко - Бази даних - 91», потрібно з'єднати три таблиці.

2. Навіщо нормалізованим даним JOIN

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

JOIN формує тимчасовий табличний результат: СКБД порівнює рядки за умовою та повертає потрібні комбінації. Вихідні таблиці при цьому не зливаються і не змінюються.

3. Синтаксис ANSI SQL-92

Старий стиль SQL-89 перелічував таблиці через кому, а умову додавав у WHERE:

SELECT s.full_name, d.name
FROM students s, departments d
WHERE s.department_id = d.department_id;

Такий запит працює, але умову зв'язку легко змішати зі звичайною фільтрацією або випадково пропустити. Стандарт SQL-92 зробив з'єднання явним:

SELECT s.full_name, d.name AS department_name
FROM students AS s
JOIN departments AS d
    ON d.department_id = s.department_id;

Загальна форма має вигляд:

SELECT список_стовпців
FROM ліва_таблиця AS l
[тип] JOIN права_таблиця AS r
    ON умова_з'єднання;

Псевдоніми s, d, e скорочують запис. Кваліфіковане ім'я s.full_name однозначно вказує, з якої таблиці походить стовпець.

4. INNER JOIN: лише відповідні рядки

INNER JOIN повертає лише ті комбінації, для яких умова ON істинна. Слово INNER можна опустити: звичайний JOIN означає INNER JOIN.

SELECT
    s.full_name AS student,
    c.title AS course,
    e.grade
FROM enrollments AS e
JOIN students AS s ON s.student_id = e.student_id
JOIN courses AS c ON c.course_id = e.course_id
ORDER BY s.full_name, c.title;

Кожен рядок enrollments знаходить одного студента й одну дисципліну. Анна Кравець не потрапить у результат, бо для неї немає рядка в enrollments. Дисципліна «Хмарні технології» також не потрапить, бо на неї ніхто не записаний.

5. Зовнішні з'єднання

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

LEFT JOIN

LEFT JOIN зберігає всі рядки таблиці ліворуч і додає відповідні рядки праворуч.

SELECT
    s.full_name AS student,
    c.title AS course,
    e.grade
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.student_id
LEFT JOIN courses AS c ON c.course_id = e.course_id
ORDER BY s.full_name, c.title;

Анна Кравець буде в результаті, але course і grade для неї дорівнюватимуть NULL. Порядок таблиць важливий: запит починається зі студентів, бо вимога звучить «показати всіх студентів».

RIGHT JOIN

RIGHT JOIN зберігає всі рядки таблиці праворуч.

SELECT
    t.full_name AS teacher,
    c.title AS course
FROM courses AS c
RIGHT JOIN teachers AS t ON t.teacher_id = c.teacher_id
ORDER BY t.full_name, c.title;

Наталія Сова з'явиться без дисципліни. Той самий результат часто легше прочитати як teachers LEFT JOIN courses, тому в командних проєктах LEFT JOIN трапляється частіше. Проте RIGHT JOIN є повноцінною операцією і корисний, коли потрібно зберегти саме праву сторону вже побудованого виразу.

FULL JOIN

FULL JOIN зберігає всі рядки обох сторін: відповідні поєднує, а невідповідні доповнює NULL.

SELECT
    t.full_name AS teacher,
    c.title AS course
FROM teachers AS t
FULL JOIN courses AS c ON c.teacher_id = t.teacher_id
ORDER BY t.full_name NULLS LAST, c.title;

У результаті видно і Наталію Сову без дисципліни, і «Хмарні технології» без призначеного викладача. Такий запит корисний для пошуку прогалин з обох боків.

6. CROSS JOIN: декартів добуток

CROSS JOIN створює всі можливі комбінації рядків. Умова ON не задається. Якщо є 3 кафедри й 2 формати навчання, результат міститиме 3 × 2 = 6 рядків.

SELECT d.name, f.format_name
FROM departments AS d
CROSS JOIN (
    VALUES ('очно'), ('дистанційно')
) AS f(format_name)
ORDER BY d.name, f.format_name;

CROSS JOIN доречний, коли всі комбінації справді потрібні, наприклад для створення сітки «кафедра × формат». Випадковий декартів добуток через пропущену умову може збільшити результат у сотні або мільйони разів.

7. Self join: таблиця з'єднується сама із собою

Self join не є окремим ключовим словом. Це звичайний JOIN, у якому та сама таблиця має два різні псевдоніми й виконує дві ролі.

SELECT
    employee.full_name AS teacher,
    mentor.full_name AS mentor
FROM teachers AS employee
LEFT JOIN teachers AS mentor
    ON mentor.teacher_id = employee.mentor_id
ORDER BY employee.full_name;

Псевдонім employee позначає викладача, а mentor - інший рядок тієї самої таблиці. LEFT JOIN зберігає також викладачів без наставника.

8. Умова з'єднання та дублікати

Умова ON повинна описувати реальний зв'язок. Зазвичай первинний ключ однієї таблиці порівнюють із відповідним зовнішнім ключем іншої:

ON c.course_id = e.course_id

Помилкова умова може дати неправильні пари:

-- Помилка: години дисципліни не ідентифікують викладача.
SELECT t.full_name, c.title
FROM teachers AS t
JOIN courses AS c ON t.teacher_id = c.hours;

Пропущена або надто широка умова створює зайві комбінації. Водночас повторення значення у результаті не завжди є помилкою. Марія з'являється двічі в запиті студентів і дисциплін, бо має два різні записи. Це два факти, а не випадкові дублікати.

Перед застосуванням DISTINCT поставте три запитання:

Яка очікувана деталізація одного рядка результату?
Чи є між таблицями зв'язок один-до-багатьох або багато-до-багатьох?
Чи повна умова ON, особливо якщо зв'язок визначається кількома стовпцями?

DISTINCT приховує однакові рядки у виведенні, але не виправляє неправильну логіку з'єднання.

Умова в ON і фільтр у WHERE

Для INNER JOIN перенесення деяких умов між ON і WHERE часто не змінює результат. Для зовнішнього з'єднання різниця критична.

-- Зберігає всіх студентів; приєднує лише оцінки від 90.
SELECT s.full_name, e.grade
FROM students AS s
LEFT JOIN enrollments AS e
    ON e.student_id = s.student_id
   AND e.grade >= 90;

-- WHERE відкидає рядки з NULL, тому студенти без такої оцінки зникають.
SELECT s.full_name, e.grade
FROM students AS s
LEFT JOIN enrollments AS e
    ON e.student_id = s.student_id
WHERE e.grade >= 90;

У ON задають, які рядки вважаються відповідними під час з'єднання. WHERE фільтрує вже сформовані рядки результату.

9. Агрегатні функції

Агрегатна функція отримує набір рядків і повертає одне підсумкове значення.

Функція	Результат
`COUNT(*)`	Кількість рядків
`COUNT(expression)`	Кількість ненульових значень виразу
`SUM(expression)`	Сума ненульових числових значень
`AVG(expression)`	Середнє ненульових числових значень
`MIN(expression)`	Мінімальне ненульове значення
`MAX(expression)`	Максимальне ненульове значення

SELECT
    COUNT(*) AS enrollment_rows,
    COUNT(grade) AS graded_rows,
    SUM(grade) AS grade_sum,
    ROUND(AVG(grade), 2) AS average_grade,
    MIN(grade) AS minimum_grade,
    MAX(grade) AS maximum_grade
FROM enrollments;

У таблиці є 7 записів, але одна оцінка NULL. Тому COUNT(*) поверне 7, а COUNT(grade) - 6. SUM, AVG, MIN і MAX також не враховують NULL. Якщо всі значення аргументу в наборі є NULL, ці функції повертають NULL.

10. GROUP BY: один підсумок для кожної групи

Без GROUP BY агрегат описує весь відібраний набір. GROUP BY ділить рядки за однаковими значеннями та обчислює агрегат окремо для кожної групи.

SELECT
    c.course_id,
    c.title,
    COUNT(e.grade) AS graded_count,
    ROUND(AVG(e.grade), 2) AS average_grade,
    MIN(e.grade) AS minimum_grade,
    MAX(e.grade) AS maximum_grade
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.course_id
GROUP BY c.course_id, c.title
ORDER BY c.title;

LEFT JOIN потрібний, щоб не втратити дисципліни без записів. Для них COUNT(e.grade) поверне 0, а середнє, мінімум і максимум - NULL.

У PostgreSQL кожен звичайний стовпець у SELECT, який не є аргументом агрегатної функції, повинен бути визначений групуванням. Надійний початковий запис:

SELECT c.course_id, c.title, COUNT(*)
FROM courses AS c
JOIN enrollments AS e ON e.course_id = c.course_id
GROUP BY c.course_id, c.title;

Важливо вибрати правильний об'єкт підрахунку після LEFT JOIN:

SELECT
    c.title,
    COUNT(*) AS result_rows,
    COUNT(e.student_id) AS enrolled_students
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.course_id
GROUP BY c.course_id, c.title
ORDER BY c.title;

Для дисципліни без студентів зовнішнє з'єднання все одно створює один результуючий рядок із NULL. Тому COUNT(*) дає 1, а COUNT(e.student_id) правильно дає 0.

11. WHERE і доступний вступ до HAVING

Порядок логічної обробки можна спрощено уявити так:

FROM / JOIN → WHERE → GROUP BY → агрегати → HAVING → SELECT → ORDER BY

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

SELECT
    c.title,
    COUNT(e.student_id) AS student_count,
    ROUND(AVG(e.grade), 2) AS average_grade
FROM courses AS c
JOIN enrollments AS e ON e.course_id = c.course_id
WHERE e.grade IS NOT NULL
GROUP BY c.course_id, c.title
HAVING COUNT(e.student_id) >= 2
ORDER BY c.title;

Спочатку WHERE прибирає незавершені оцінювання. Потім рядки групуються за дисциплінами. HAVING залишає дисципліни, де є щонайменше два оцінені студенти. Тут достатньо запам'ятати межу відповідальності; складні умови HAVING, підзапити та операції над множинами будуть темою лекції 15.

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

Завдання 1. Студент і його кафедра

Напишіть запит у синтаксисі ANSI SQL-92, який виводить student, department для всіх студентів. Відсортуйте результат за назвою кафедри та ім'ям студента.

Завдання 2. Усі дисципліни та кількість студентів

Виведіть course, student_count для кожної дисципліни, включно з дисциплінами без записаних студентів. Для них кількість має дорівнювати 0. Відсортуйте від найбільшої кількості до найменшої, а однакові значення - за назвою дисципліни.

Завдання 3. Звіт кафедр

Для кожної кафедри виведіть department, кількість різних студентів із ненульовою оцінкою graded_students і середню оцінку average_grade, округлену до двох знаків. Залиште лише кафедри, де оцінки мають щонайменше два різні студенти. Кафедру визначайте за належністю студента, а не викладача.

Підсумок

Нормалізація розділяє факти між таблицями, а JOIN відновлює потрібне представлення під час запиту.
ANSI SQL-92 явно відокремлює JOIN ... ON від фільтрації WHERE.
INNER JOIN залишає лише відповідності; LEFT, RIGHT і FULL JOIN також зберігають невідповідні рядки визначених сторін.
CROSS JOIN створює всі комбінації, а self join надає одній таблиці дві ролі.
Повторення рядків часто відображає зв'язок один-до-багатьох; DISTINCT не виправляє хибну умову ON.
COUNT, SUM, AVG, MIN, MAX узагальнюють набір рядків; більшість агрегатів ігнорує NULL.
GROUP BY формує окремі групи, а звичайні стовпці результату мають узгоджуватися з групуванням.
WHERE фільтрує рядки до групування, а HAVING - групи після нього.
Детальні підзапити, складніші умови HAVING та UNION, INTERSECT, EXCEPT розглядатимуться в лекції 15.