Множественные 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 = 'Иван Петров';

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 могут быть сложными, но они позволяют создавать мощные отчёты и получать ценную информацию. Если правильно их оптимизировать и структурировать, они станут вашим инструментом для работы с большими базами данных.

2

Задача

SQL SELF, 12 уровень, 1 лекция

Недоступна

Базовый запрос с множественным JOIN

Базовый запрос с множественным JOIN

Открыть

2

Задача

SQL SELF, 12 уровень, 1 лекция

Недоступна

Фильтрация данных с использованием WHERE и множественных JOIN

Фильтрация данных с использованием WHERE и множественных JOIN

Открыть