Зв’язки, JOIN та коректні підсумки

Що ви опануєте

Навчитеся реалізовувати зв’язки один-до-багатьох і багато-до-багатьох, читати INNER та LEFT JOIN, пояснювати кількість рядків після поєднання й уникати неправильних підсумків. Приклад — студенти, навчальні гуртки та членство. Студент може бути у кількох гуртках, гурток може мати багатьох студентів.

Зв’язок та кратність

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

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

Виконуваний приклад

CREATE SCHEMA lecture_join;
SET search_path TO lecture_join;
CREATE TABLE student (id integer PRIMARY KEY, name text NOT NULL);
CREATE TABLE club (id integer PRIMARY KEY, title text NOT NULL);
CREATE TABLE membership (
  student_id integer REFERENCES student(id),
  club_id integer REFERENCES club(id),
  joined_on date NOT NULL,
  PRIMARY KEY (student_id, club_id)
);
INSERT INTO student VALUES (1,'Олена'),(2,'Андрій'),(3,'Марія');
INSERT INTO club VALUES (10,'Робототехніка'),(20,'SQL'),(30,'Дизайн');
INSERT INTO membership VALUES
  (1,10,'2027-02-01'),(1,20,'2027-02-02'),(2,20,'2027-02-03');
SELECT s.name, c.title
FROM student AS s
JOIN membership AS m ON m.student_id = s.id
JOIN club AS c ON c.id = m.club_id
ORDER BY s.id, c.id;
SELECT c.id, c.title, COUNT(m.student_id) AS members
FROM club AS c LEFT JOIN membership AS m ON m.club_id = c.id
GROUP BY c.id, c.title ORDER BY c.id;

Перший запит повертає три пари: Олена–Робототехніка, Олена–SQL, Андрій–SQL. Марія не входить у результат, бо членство відсутнє. Другий запит повертає всі три гуртки з кількостями 1, 2, 0. COUNT(m.student_id) ігнорує NULL у рядку гуртка без учасників. COUNT(*) для такого LEFT JOIN дав би 1, оскільки доповнений рядок існує.

Membership є таблицею зв’язку. Складений ключ не дозволяє повторну пару student_id,club_id. Атрибут joined_on належить членству, оскільки дата залежить від обох сутностей. Зберігання її лише у student не пояснило б різні дати вступу до різних гуртків.

Умови ON та WHERE

Для підрахунку лише членств із дати 2027-02-02 зберігаючи всі гуртки пишіть:

SELECT c.title, COUNT(m.student_id) AS recent_members
FROM club AS c
LEFT JOIN membership AS m
  ON m.club_id = c.id AND m.joined_on >= DATE '2027-02-02'
GROUP BY c.id, c.title ORDER BY c.id;

Фільтр ON обмежує праві збіги. Результат містить Робототехніку з 0, SQL з 2 та Дизайн з 0. Якщо перенести m.joined_on >= … у WHERE, доповнені NULL рядки не пройдуть умову. Тоді гуртки без потрібних членств зникнуть. Розташування фільтра має відповідати питанню: які пов’язані записи рахувати чи які кінцеві рядки залишити.

Множення рядків

Припустімо, студент має два членства й три заявки на події. Якщо приєднати обидві таблиці без попередніх підсумків, для студента утвориться шість комбінацій. Сума вартостей заявок повториться двічі. DISTINCT у кінці може приховати повторені рядки, але не виправляє вже обчислену неправильну суму.

Рішення починається з одиниці результату. Якщо потрібен один рядок на студента, спершу згрупуйте членства до student_id та окремо заявки до student_id, а потім поєднайте підсумки. Common Table Expression, або CTE, називає проміжний запит через WITH і допомагає показати ці кроки. CTE сам по собі не гарантує конкретний спосіб фізичного виконання.

WITH member_counts AS (
  SELECT student_id, COUNT(*) AS clubs
  FROM membership GROUP BY student_id
)
SELECT s.name, COALESCE(mc.clubs, 0) AS clubs
FROM student AS s
LEFT JOIN member_counts AS mc ON mc.student_id = s.id
ORDER BY s.id;

Тут Олена має 2, Андрій 1, Марія 0. COALESCE виправданий: відсутність членств означає нуль клубів. Для іншого показника NULL може означати невідоме значення, яке не слід замінювати нулем автоматично.

Пошук відсутніх зв’язків

EXISTS перевіряє, чи підзапит має хоча б один рядок. Для студентів без членств:

SELECT s.id, s.name FROM student AS s
WHERE NOT EXISTS (
  SELECT 1 FROM membership AS m WHERE m.student_id = s.id
)
ORDER BY s.id;

Очікується Марія. Підзапит пов’язаний із поточним студентом через s.id. SELECT 1 показує, що значення полів не потрібні; важлива наявність рядка. NOT IN із набором, який містить NULL, може давати unknown і несподівано виключати записи. NOT EXISTS добре виражає саме відсутність відповідного зв’язку.

Практичний сценарій та професійні практики

Керівник просить звіт «усі гуртки та кількість учасників, включно з порожніми». Перед запитом запишіть одиницю результату: один гурток. Визначте, чи членство історичне, активне або будь-яке. Якщо потрібно лише активне, модель повинна мати стан або дату завершення, а фільтр — погоджене правило часу.

Перевіряйте звіт на наборі з порожнім гуртком, студентом у двох гуртках і однаковими іменами. GROUP BY name об’єднав би різних людей з однаковим іменем. Групуйте за ідентичністю, а ім’я виводьте як атрибут. Під час перевірки кількості рядків порівнюйте очікувану кратність на кожному JOIN.

Не поєднуйте таблиці за схожістю текстових назв, якщо існують ключі. Однакові назви не гарантують тотожності. Для індексів спочатку визначайте реальні фільтри та поєднання; PostgreSQL не створює автоматично індекс на кожному стовпці зовнішнього ключа. Первинний ключ membership починається зі student_id, тому запити переважно за club_id можуть потребувати окремого індексу після вимірювання.

Ручний підрахунок кардинальності

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

Візьміть одного студента з двома членствами. INNER JOIN student–membership дає два рядки. Приєднання club за первинним ключем зберігає їхню кількість, бо кожне членство має один клуб. Якщо club.title не унікальне й поєднати за title, число збігів може зрости. Помилка ключа змінює зміст звіту навіть коли таблиця виглядає правдоподібною.

Для підсумку «кількість студентів, що мають хоча б одне членство» EXISTS повертає один результат на студента без повторення. JOIN дає рядок на членство й потребує іншого способу підрахунку унікальних студентів. Обирайте структуру запиту за одиницею питання. DISTINCT має конкретний зміст усунення однакових проекцій, а його застосування слід пояснювати.

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

Схема процесу

Самоперевірка

Підсумок

Коректний JOIN залежить від ключів, кратності та питання звіту. LEFT JOIN зберігає непов’язані рядки, а умови WHERE можуть їх відфільтрувати. Підсумки потрібно перевіряти на порожніх зв’язках і множенні рядків.

Джерела

• JOIN
• Нормалізація і таблиці