Множинні JOIN в одному запиті

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

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

• students — дані про студентів.
• enrollments — інформація про записи студентів на курси.
• courses — інформація про курси.

Якщо ми хочемо отримати повний список студентів, їх курсів і викладачів, треба об'єднати три таблиці, використовуючи JOIN.

Порядок виконання JOIN

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

Приклад:

SELECT *
FROM students
    INNER JOIN enrollments ON students.id = enrollments.student_id
    INNER JOIN courses ON enrollments.course_id = courses.id;

1. Спочатку об'єднуються таблиці students і enrollments по стовпцю students.id = enrollments.student_id.
2. Результат першого об'єднання використовується для об'єднання з таблицею courses по стовпцю enrollments.course_id = courses.id.

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

Приклад: Список студентів, їх курсів і викладачів

Припустимо, у нас є такі таблиці з даними:

Таблиця students:

Таблиця courses:

Таблиця enrollments:

Запит:

SELECT
    students.name AS student_name,
    courses.name AS course_name,
    courses.teacher AS teacher_name
FROM students
    INNER JOIN enrollments ON students.id = enrollments.student_id
    INNER JOIN courses ON enrollments.course_id = courses.id;

Результат:

Фільтрація у запитах з множинними JOIN

Ти можеш застосовувати умови фільтрації до запитів з JOIN, щоб обмежити кількість повернутих даних і прискорити виконання запиту. Наприклад, якщо треба вивести тільки тих студентів, які навчаються на курсі "Mathematics":

SELECT
    students.name AS student_name,
    courses.name AS course_name
FROM students
    INNER JOIN enrollments ON students.id = enrollments.student_id
    INNER JOIN courses ON enrollments.course_id = courses.id
WHERE courses.name = 'Mathematics';

Результат:

Оптимізація запитів з множинними JOIN

Для роботи з великими таблицями оптимізація запитів стає дуже важливою. Ось кілька порад:

1. Використовуй індекси

Індекси дозволяють PostgreSQL працювати швидше, особливо при об'єднанні по ключових полях. Переконайся, що на стовпцях student_id і course_id у таблиці enrollments є індекси.

Приклад створення індексу:

CREATE INDEX idx_enrollments_student_id ON enrollments(student_id);
CREATE INDEX idx_enrollments_course_id ON enrollments(course_id);

Більше про індекси ти дізнаєшся на наступних рівнях, але все ж вирішив тут про них згадати. Вони дуже часто пов'язані з JOIN.

2. Фільтруй дані на ранніх етапах

Використовуй умови WHERE, щоб зменшити кількість оброблюваних рядків до виконання JOIN. Наприклад:

SELECT
    students.name AS student_name,
    courses.name AS course_name
FROM students
    INNER JOIN enrollments ON students.id = enrollments.student_id
    INNER JOIN courses ON enrollments.course_id = courses.id
WHERE 
    courses.teacher = 'Ivan Petrov';

3. Мінімізуй кількість рядків для об'єднання

Замість об'єднання всіх записів з двох таблиць, спробуй спочатку відфільтрувати їх підзапитами:

SELECT
    students.name AS student_name,
    courses.name AS course_name
FROM 
    (SELECT * FROM students WHERE id IN (1, 2)) sub_students
INNER JOIN enrollments ON sub_students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;

Більше про вкладені SELECT ти дізнаєшся буквально на наступному рівні :P

Приклад складного об'єднання: студенти, курси і факультети

Припустимо, у нас додається ще одна таблиця faculties:

Таблиця faculties:

Таблиця courses оновлюється:

Щоб отримати список студентів, курсів і факультетів, ми додаємо ще один JOIN:

SELECT
    students.name AS student_name,
    courses.name AS course_name,
    faculties.name AS faculty_name
FROM students
    INNER JOIN enrollments ON students.id = enrollments.student_id
    INNER JOIN courses ON enrollments.course_id = courses.id
    INNER JOIN faculties ON courses.faculty_id = faculties.id;

Результат:

SQL-запити з множинними JOIN можуть бути складними, але вони дозволяють створювати потужні звіти і отримувати цінну інформацію. Якщо правильно їх оптимізувати і структурувати, вони стануть твоїм інструментом для роботи з великими базами даних.