Звіти через JOIN: зв’язки та нульові результати

Мета та потрібні знання

Побудувати зв’язок багато-до-багатьох і звіти, які коректно враховують відсутні пов’язані записи. Потрібно знати первинні ключі, REFERENCES, INNER JOIN, LEFT JOIN, GROUP BY та COUNT. Результат — перевірені SQL-запити з поясненням одиниці рядка кожного звіту.

Теоретичний мінімум

Членство пов’язує студента з гуртком. Його ключ складається з обох id. Для звіту «кожен гурток і кількість учасників» одиниця результату — гурток. LEFT JOIN зберігає порожній гурток, а COUNT правого ключа повертає 0. Якщо рахувати COUNT(*), доповнений рядок буде враховано й результат стане 1.

Підготовка середовища

Використовуйте локальний PostgreSQL або окремий навчальний Supabase project. Для локального сервера підключіться через pgAdmin Query Tool або psql -d назва_навчальної_бази. У Supabase відкрийте SQL Editor власного навчального проєкту. Потрібні права створення схеми й таблиць. Перевірте з’єднання командою SELECT version(), current_database(), current_user;.

Скрипт нижче створює власну схему один раз і не видаляє наявних даних. Якщо схема вже існує після минулого запуску, використайте іншу навчальну назву та змініть SET search_path відповідно. Не виконуйте роботу у production-базі. Для окремих експериментів відкривайте новий запит і після помилки в явній транзакції виконуйте ROLLBACK.

Повний демонстраційний SQL

CREATE SCHEMA lab02_demo;
SET search_path TO lab02_demo;
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),
  PRIMARY KEY (student_id, club_id)
);
INSERT INTO student VALUES (1,'Олена'),(2,'Олена'),(3,'Ігор');
INSERT INTO club VALUES (10,'AI'),(20,'SQL'),(30,'Mobile');
INSERT INTO membership VALUES (1,10),(1,20),(2,20);
SELECT s.id, s.name, c.title
FROM student s
JOIN membership m ON m.student_id = s.id
JOIN club c ON c.id = m.club_id
ORDER BY s.id,c.id;
SELECT c.id, c.title, COUNT(m.student_id) AS member_count
FROM club c LEFT JOIN membership m ON m.club_id = c.id
GROUP BY c.id,c.title ORDER BY c.id;
SELECT s.id, s.name FROM student s
WHERE NOT EXISTS (
  SELECT 1 FROM membership m WHERE m.student_id = s.id
)
ORDER BY s.id;
WITH counts AS (
  SELECT student_id, COUNT(*) AS clubs FROM membership GROUP BY student_id
)
SELECT s.id, s.name, COALESCE(c.clubs,0) AS club_count
FROM student s LEFT JOIN counts c ON c.student_id = s.id
ORDER BY s.id;

Пояснення та очікуваний результат

Два студенти мають однакове ім’я, але різні id. Перший запит повертає три членства, другий — AI:1, SQL:2, Mobile:0. NOT EXISTS знаходить Ігоря з id3. Останній запит повертає кількості 2,1,0 для id1,2,3 відповідно. Групування лише за name злило б двох різних людей, тому використовуємо ключі.

WITH створює названий підсумок counts. До приєднання він має не більше одного рядка на student_id. Це явна гарантія одиниці результату на рівні логіки запиту. COALESCE перетворює відсутність підсумку на нуль членств. Таке трактування відповідає цьому звіту.

Покрокове виконання

1. Намалюйте зв’язки та позначте складений первинний ключ.
2. Виконайте демонстрацію й вручну звірте кожен рядок зі вставленими даними.
3. Тимчасово замініть COUNT(m.student_id) на COUNT(*) і поясніть різницю для Mobile.
4. Додайте четвертого студента й перевірте, що список без членств розширився.
5. Додайте членство нового студента в Mobile та перевірте обидва звіти.
6. Спробуйте вставити повторну пару ключів і зафіксуйте відмову.
7. Додайте у власній копії membership дату вступу. Побудуйте звіт за обраним періодом зі збереженням порожніх гуртків.

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

Самостійне завдання

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

Побудуйте чотири звіти: перелік пар автор–публікація, кількість публікацій кожного автора, публікації без авторів, публікації з щонайменше двома авторами. Для кожного вкажіть одиницю рядка, очікувану кількість і приклад граничного стану. Основне рішення та тестові дані створіть самостійно.

Додаткове дослідження: якщо приєднати авторів і ключові слова публікації, як зміниться число рядків? На невеликому наборі вручну випишіть комбінації й запропонуйте спосіб окремого агрегування. Не використовуйте DISTINCT як універсальну відповідь без пояснення, який саме факт повторюється.

Налагодження та типові помилки

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

Нульовий звіт із LEFT JOIN перевіряйте на COUNT правого ключа й умовах WHERE. Ambiguous column означає, що поле має однакову назву в кількох джерелах; використайте псевдонім таблиці. Групування за текстовою назвою може змішати окремі сутності з однаковими назвами.

Результат та контрольні питання

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

Додаткова предметна перевірка

Для кожного звіту додайте ручну перевірку на власних id. Перелік назв може приховати змішування двох авторів, тому ідентичність має бути видимою у перевірочній версії. Потім спробуйте додати ще одне авторство й перед запуском визначте, який саме підсумок збільшиться. Така локальна зміна дозволяє перевірити, чи звіт реагує на потрібний факт, зберігаючи інші результати.

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

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

Підсумок

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

Джерела

• PostgreSQL Tutorial
• SQL commands