Создание сложных запросов. Объединение нескольких таблиц и работа с соединениями (Joins).

1. Настройка среды (pgAdmin 4)

  1. Запустите pgAdmin 4: Откройте приложение pgAdmin 4.
  2. Подключитесь к серверу: Подключитесь к вашему экземпляру сервера PostgreSQL. Это подключение должно быть уже настроено (согласно P1).
  3. Выберите базу данных: Перейдите к базе данных, предназначенной для этого курса, по пути Servers -> [Имя вашего сервера] -> Databases. Щелкните правой кнопкой мыши по имени базы данных и выберите “Инструмент запросов” (Query Tool).
  4. Пример схемы: Для сегодняшних примеров мы будем использовать гипотетическую схему базы данных университета. Выполните следующий SQL-код в Инструменте запросов (Query Tool) (нажмите F5 или кнопку “Выполнить/Обновить” (значок треугольника/воспроизведения)), чтобы создать и заполнить необходимые таблицы:
-- Удаляем таблицы, если они существуют (чтобы начать с чистого листа)
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS instructors;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS departments;

-- Создаем таблицу Departments (Кафедры)
CREATE TABLE departments (
	dept_id SERIAL PRIMARY KEY,
	dept_name VARCHAR(100) NOT NULL UNIQUE,
	building VARCHAR(50)
);

-- Создаем таблицу Instructors (Преподаватели)
CREATE TABLE instructors (
	instructor_id SERIAL PRIMARY KEY,
	instructor_name VARCHAR(100) NOT NULL,
	dept_id INT REFERENCES departments(dept_id)
);

-- Создаем таблицу Courses (Курсы)
CREATE TABLE courses (
	course_id SERIAL PRIMARY KEY,
	course_name VARCHAR(100) NOT NULL,
	credits INT CHECK (credits > 0),
	dept_id INT REFERENCES departments(dept_id),
	instructor_id INT REFERENCES instructors(instructor_id)
);

-- Создаем таблицу Students (Студенты)
CREATE TABLE students (
	student_id SERIAL PRIMARY KEY,
	student_name VARCHAR(100) NOT NULL,
	major_dept_id INT REFERENCES departments(dept_id),
	enrollment_year INT
);

-- Создаем таблицу Enrollments (Зачисления) (Связующая таблица)
CREATE TABLE enrollments (
	enrollment_id SERIAL PRIMARY KEY,
	student_id INT REFERENCES students(student_id),
	course_id INT REFERENCES courses(course_id),
	grade CHAR(1) CHECK (grade IN ('A', 'B', 'C', 'D', 'F')),
	UNIQUE(student_id, course_id) -- Студент может записаться на курс только один раз
);

-- Вставляем пример данных
INSERT INTO departments (dept_name, building) VALUES
('Computer Science', 'Building A'),
('Mathematics', 'Building B'),
('Physics', 'Building A'),
('History', 'Building C');

INSERT INTO instructors (instructor_name, dept_id) VALUES
('Dr. Alan Turing', 1),
('Dr. Ada Lovelace', 1),
('Dr. Carl Gauss', 2),
('Dr. Marie Curie', 3),
('Dr. Herodotus', 4),
('Dr. Isaac Newton', NULL); -- Преподаватель еще не назначен на кафедру

INSERT INTO courses (course_name, credits, dept_id, instructor_id) VALUES
('Introduction to Programming', 4, 1, 1),
('Data Structures', 4, 1, 2),
('Calculus I', 4, 2, 3),
('Linear Algebra', 3, 2, 3),
('Quantum Mechanics', 4, 3, 4),
('Classical Mechanics', 4, 3, 6), -- Преподает Ньютон
('World History I', 3, 4, 5),
('Database Systems', 3, 1, 2); -- Преподает Лавлейс

INSERT INTO students (student_name, major_dept_id, enrollment_year) VALUES
('Alice Smith', 1, 2022),
('Bob Johnson', 2, 2021),
('Charlie Brown', 1, 2022),
('Diana Prince', 3, 2023),
('Eve Adams', NULL, 2021); -- Специальность не выбрана

INSERT INTO enrollments (student_id, course_id, grade) VALUES
(1, 1, 'A'), -- Алиса, Введение в программирование
(1, 2, 'B'), -- Алиса, Структуры данных
(1, 8, 'A'), -- Алиса, Системы баз данных
(2, 3, 'A'), -- Боб, Математический анализ I
(2, 4, 'B'), -- Боб, Линейная алгебра
(3, 1, 'B'), -- Чарли, Введение в программирование
(3, 8, 'C'), -- Чарли, Системы баз данных
(4, 5, 'A'); -- Диана, Квантовая механика
-- Примечание: Студентка Ева Адамс еще не записана ни на один курс.
-- Примечание: На курс 'World History I' еще нет записей.

2. Объединение нескольких таблиц (трех и более)

Основной принцип остается тем же, что и при соединении двух таблиц: вы последовательно связываете операторы JOIN. Результат первого соединения (логически, промежуточная таблица) становится левой таблицей для второго соединения, и так далее.

Шаблон синтаксиса:

SELECT
    t1.column1,
    t2.column2,
    t3.column3,
    -- ... другие столбцы
FROM
    table1 AS t1
JOIN
    table2 AS t2 ON t1.joining_column = t2.joining_column
JOIN
    table3 AS t3 ON t2.another_joining_column = t3.another_joining_column
-- Дополнительные операторы JOIN могут быть добавлены здесь
WHERE
    -- Необязательные условия
ORDER BY
    -- Необязательная сортировка;

Ключевые моменты:

  • Псевдонимы крайне важны: Используйте псевдонимы таблиц (t1, t2, t3 или более описательные, например s, c, d), чтобы ваш запрос был читаемым и однозначным, особенно когда таблицы содержат столбцы с одинаковыми именами (например, dept_id).
  • Порядок соединения важен (логически): Убедитесь, что условие ON правильно связывает таблицы, соединяемые на данном шаге. Обычно таблицы соединяют на основе связей по внешним ключам.
  • По умолчанию используется INNER JOIN: Если вы просто пишете JOIN, PostgreSQL предполагает INNER JOIN.

Пример 1: Вывести список студентов, курсов, на которые они записаны, и преподавателей, ведущих эти курсы.

Это требует соединения таблиц students, enrollments, courses и instructors.

  • students связывается с enrollments через student_id.
  • enrollments связывается с courses через course_id.
  • courses связывается с instructors через instructor_id.
SELECT
    s.student_name,
    c.course_name,
    i.instructor_name
FROM
    students AS s
INNER JOIN
    enrollments AS e ON s.student_id = e.student_id
INNER JOIN
    courses AS c ON e.course_id = c.course_id
INNER JOIN
    instructors AS i ON c.instructor_id = i.instructor_id
ORDER BY
    s.student_name,
    c.course_name;

Выполнение в pgAdmin 4:

  1. Введите запрос в Инструмент запросов (Query Tool).
  2. Нажмите F5 или щелкните по кнопке Execute (Выполнить).
  3. Просмотрите результаты на панели вывода данных (Data Output). Вы должны увидеть имена студентов в паре с курсами, которые они проходят, и именами соответствующих преподавателей.

3. Сочетание различных типов соединений

Иногда для достижения желаемого результата вам нужно сочетать INNER JOIN с LEFT JOIN (или RIGHT JOIN, FULL OUTER JOIN). Например, вы можете захотеть вывести все записи из одной таблицы, но только совпадающие записи из последующих соединений.

Пример 2: Вывести всех студентов и, если они записаны на какие-либо курсы, вывести эти курсы и их факультеты.

Нам нужны все студенты, поэтому мы начинаем с таблицы students и используем LEFT JOIN для таблиц enrollments и courses. Мы также хотим получить факультет курса.

  • Начать с students.
  • LEFT JOIN с enrollments (чтобы сохранить всех студентов, даже тех, кто не записан).
  • LEFT JOIN с courses (чтобы сохранить студентов, записанных на курсы, информация о которых может отсутствовать, хотя это менее вероятно при использовании внешних ключей. Концептуально это также поддерживает фокус на “всех студентах”).
  • LEFT JOIN с departments (чтобы получить название факультета для курса).
SELECT
    s.student_name,
    c.course_name,
    d.dept_name AS course_department
FROM
    students AS s
LEFT JOIN
    enrollments AS e ON s.student_id = e.student_id
LEFT JOIN
    courses AS c ON e.course_id = c.course_id
LEFT JOIN
    departments AS d ON c.dept_id = d.dept_id -- Соединение по факультету курса
ORDER BY
    s.student_name,
    c.course_name;

Пояснение:

  • students LEFT JOIN enrollments: Гарантирует, что все студенты (например, ‘Eve Adams’) будут отображены, даже если у них нет совпадающих строк в enrollments. Для таких студентов столбцы из enrollments (и последующих соединений) будут иметь значение NULL.
  • ... LEFT JOIN courses: Если у студента есть записи о зачислении (enrollments), мы пытаемся найти соответствующий курс.
  • ... LEFT JOIN departments: Если мы нашли курс, мы пытаемся найти его факультет.

Наблюдение: Вы увидите, что ‘Eve Adams’ указана со значениями NULL для course_name и course_department. Студенты, записанные на курсы, будут отображаться с названием курса и факультетом, который предлагает этот курс.

Пример 3: Вывести все факультеты и имена преподавателей на этих факультетах. Включить факультеты, даже если на них в настоящее время нет назначенных преподавателей.

  • Начать с departments.
  • LEFT JOIN с instructors.
SELECT
    d.dept_name,
    d.building,
    i.instructor_name
FROM
    departments AS d
LEFT JOIN
    instructors AS i ON d.dept_id = i.dept_id
ORDER BY
    d.dept_name,
    i.instructor_name;

Наблюдение: Выводятся все факультеты. Если на факультете есть преподаватели, их имена выводятся. Если факультету не назначены преподаватели в таблице instructors (или если у преподавателя dept_id = NULL, как у Dr. Isaac Newton), этот факультет все равно появится, возможно, со значением NULL для имени преподавателя, или такой преподаватель не будет связан ни с одним из отображаемых факультетов.

4. Самосоединения (Self-Joins)

Самосоединение используется для соединения таблицы с самой собой. Это полезно, когда строки в одной таблице имеют связь с другими строками в той же самой таблице. Классический пример — таблица сотрудников (employee), где один столбец хранит идентификатор (ID) менеджера сотрудника, который также является сотрудником в той же таблице.

Ключевое требование: Вы должны использовать псевдонимы таблиц (aliases), чтобы различать две концептуальные копии соединяемой таблицы.

Пример 4: Вывести список преподавателей и название кафедры, к которой они принадлежат. (Использование самосоединения для таблицы кафедр здесь нетипично, но давайте представим сценарий, где мы моделируем предварительные требования (prerequisites) в таблице courses).

Изменение для лучшего примера самосоединения: Давайте добавим столбец prerequisite_course_id в таблицу courses.

Сначала обновим таблицу и данные (Выполните это в инструменте запросов pgAdmin Query Tool):

-- Добавляем столбец для предварительного курса в таблицу courses
ALTER TABLE courses
ADD COLUMN prerequisite_course_id INT REFERENCES courses(course_id) NULL; -- Разрешаем NULL для курсов без предварительных требований

-- Обновляем некоторые курсы, чтобы у них появились предварительные требования
UPDATE courses SET prerequisite_course_id = 1 WHERE course_name = 'Data Structures'; -- Data Structures требует Intro to Prog
UPDATE courses SET prerequisite_course_id = 3 WHERE course_name = 'Linear Algebra'; -- Linear Algebra требует Calculus I
UPDATE courses SET prerequisite_course_id = 1 WHERE course_name = 'Database Systems'; -- Database Systems требует Intro to Prog

Теперь сам запрос с самосоединением: Вывести список курсов и их предварительных требований.

SELECT
    c1.course_name AS course,
    c2.course_name AS prerequisite
FROM
    courses AS c1 -- Основной курс
LEFT JOIN
    courses AS c2 ON c1.prerequisite_course_id = c2.course_id -- Соединяем обратно с таблицей courses для получения предварительного курса
ORDER BY
    c1.course_name;

Пояснение:

  • Мы обращаемся к таблице courses дважды, используя псевдонимы c1 (представляет основной курс) и c2 (представляет предварительный курс).
  • LEFT JOIN гарантирует, что курсы без предварительных требований (где prerequisite_course_id равен NULL) также будут включены в список, показывая NULL в столбце prerequisite.
  • Условие ON связывает prerequisite_course_id основного курса (c1) с course_id предварительного курса (c2).

Наблюдение: В результате будет показан каждый курс и, если для него определено предварительное требование, название этого предварительного курса.

5. Сочетание соединений с агрегированием и группировкой

Сложные запросы часто включают сочетание соединений с агрегатными функциями (COUNT, AVG и т. д. - P10) и операторами GROUP BY / HAVING (P6).

Пример 5: Вывести список отделов и количество курсов, предлагаемых каждым. Включить только те отделы, которые предлагают более одного курса.

Это требует соединения таблиц departments и courses, затем группировки по отделу и фильтрации групп.

SELECT
    d.dept_name,
    COUNT(c.course_id) AS number_of_courses
FROM
    departments AS d
INNER JOIN -- Или LEFT JOIN, если вы хотите видеть отделы с 0 курсов (до фильтрации)
    courses AS c ON d.dept_id = c.dept_id
GROUP BY
    d.dept_id, d.dept_name -- Группируем по первичному ключу и любым другим выбранным неагрегированным столбцам
HAVING
    COUNT(c.course_id) > 1 -- Фильтруем группы на основе агрегированного количества
ORDER BY
    number_of_courses DESC,
    d.dept_name;

Пояснение:

  1. Таблица departments соединяется с таблицей courses по dept_id. INNER JOIN включает только те отделы, у которых есть хотя бы один курс. LEFT JOIN изначально включил бы все отделы.
  2. GROUP BY d.dept_id, d.dept_name: Группирует строки так, чтобы все курсы, принадлежащие одному и тому же отделу, оказались вместе. Мы группируем по dept_id (уникальный идентификатор) и включаем dept_name, потому что он указан в списке SELECT и не агрегируется.
  3. COUNT(c.course_id): Подсчитывает количество курсов внутри каждой группы отделов.
  4. HAVING COUNT(c.course_id) > 1: Отфильтровывает группы (отделы), в которых количество курсов не превышает 1 (т.е. оставляет только те, где количество > 1).
  5. ORDER BY: Сортирует итоговый результат.

Пример 6: Найдите средний балл для каждого курса.

Это требует объединения таблиц courses и enrollments с последующей группировкой. Примечание: Нам нужно обрабатывать буквенные оценки численно для усреднения.

-- Нам нужен способ преобразования оценок в числовые значения для AVG.
-- Для простоты здесь мы просто подсчитаем количество зачисленных студентов на каждый курс вместо вычисления средней оценки.
-- Настоящий средний балл потребовал бы оператора CASE или отдельной таблицы сопоставления (что может выходить за рамки простоты, принятой в этом контексте).

-- Давайте найдем количество студентов, зачисленных на каждый курс.
SELECT
    c.course_name,
    COUNT(e.student_id) AS number_of_students_enrolled
FROM
    courses AS c
LEFT JOIN -- Используйте LEFT JOIN, чтобы включить курсы с нулевым количеством зачисленных студентов
    enrollments AS e ON c.course_id = e.course_id
GROUP BY
    c.course_id, c.course_name -- Группируем по курсу
ORDER BY
    number_of_students_enrolled DESC,
    c.course_name;

Наблюдение: Этот запрос выводит список всех курсов и показывает, сколько студентов зачислено на каждый, включая курсы с 0 зачисленных студентов, такие как ‘World History I’.

6. Лучшие практики для сложных объединений (JOIN)

  • Используйте осмысленные псевдонимы (Aliases): e для employees, d для departments лучше, чем t1, t2.
  • Форматируйте ваш SQL: Используйте отступы и переносы строк, чтобы сделать сложные запросы читаемыми. Выравнивайте операторы JOIN, ON, WHERE.
  • Понимайте типы объединений (Join Types): Осознанно выбирайте INNER JOIN, LEFT JOIN, RIGHT JOIN или FULL OUTER JOIN в зависимости от того, нужно ли вам сохранять все строки из левой, правой или обеих таблиц, даже если совпадений нет.
  • Указывайте столбцы для объединения: Всегда используйте оператор ON с правильными столбцами для объединения. Избегайте старого синтаксиса объединения через запятую в операторе FROM.
  • Тестируйте поэтапно: Создавайте сложные запросы шаг за шагом. Начните с двух таблиц, проверьте результат, затем добавьте следующее объединение и так далее.