Объединение нескольких таблиц с помощью JOIN в PostgreSQL

Цели:

  1. Понять необходимость использования JOIN в реляционных базах данных.
  2. Объяснить различные типы JOIN: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN и CROSS JOIN.
  3. Написать SQL-запросы, используя каждый тип JOIN в PostgreSQL.
  4. Выполнить JOIN-запросы с помощью pgAdmin 4.
  5. Интерпретировать результаты JOIN-запросов и понять, как данные объединяются из нескольких таблиц.

1. Настройка демонстрационной базы данных

Создадим простую базу данных с двумя таблицами, чтобы проиллюстрировать JOIN. Мы будем использовать таблицы с именами students (студенты) и courses (курсы).

Создание таблицы students. Выполните следующую SQL-команду в редакторе запросов:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    major VARCHAR(50)
);

Создание таблицы courses. Выполните следующую SQL-команду в редакторе запросов:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credits INTEGER
);

Заполнение таблиц демонстрационными данными. Выполните следующие операторы INSERT, чтобы добавить данные в наши таблицы:

INSERT INTO students (student_name, major) VALUES
('KOMILJONOV AZIMJON HAMIDBEKOVICH', 'Computer Science'),
('OTAJONOV SHOHZODBEK QAXRAMON O‘G‘LI', 'Mathematics'),
('OTANAZAROV SULTONBEK O‘KTAM O‘G‘LI', 'Physics'),
('QURAMBOYEV DIYORBEK RASHID O‘G‘LI', 'Chemistry'),
('RAXMONBERGANOV XAYRULLO MAXMUD O‘G‘LI', 'Chemistry'),
('SHONAZAROV AZIZBEK XUSHNUDBEK O‘G‘LI', 'Biology');

INSERT INTO courses (course_name, credits) VALUES
('Introduction to Programming', 3),
('Calculus I', 4),
('Classical Mechanics', 3),
('Organic Chemistry', 4),
('Genetics', 3),
('Data Structures', 3);

Добавление связующего столбца для представления зачисления студентов. Выполните следующую команду ALTER TABLE:

ALTER TABLE courses
ADD COLUMN student_id INTEGER REFERENCES students(student_id);

Эта команда добавляет новый столбец student_id в таблицу courses. Он имеет тип INTEGER и устанавливает связь по внешнему ключу со столбцом student_id в таблице students. Это означает, что student_id в таблице courses должен быть либо NULL, либо соответствовать существующему student_id в таблице students.

Обновление таблицы courses для привязки курсов к студентам.

Теперь обновим таблицу courses, чтобы указать, какой студент зачислен на каждый курс. Обратите внимание, что в этом примере не все курсы будут связаны со студентом, что продемонстрирует различное поведение JOIN позже.

UPDATE courses SET student_id = 1 WHERE course_id IN (1, 6);
UPDATE courses SET student_id = 2 WHERE course_id IN (2);
UPDATE courses SET student_id = 3 WHERE course_id IN (3);
UPDATE courses SET student_id = 4 WHERE course_id IN (4);

Теперь у нас есть две таблицы, students и courses, со связью, установленной через столбец student_id в таблице courses. Давайте рассмотрим различные типы JOIN для объединения данных из этих таблиц.

2. Типы JOIN

В SQL существует несколько типов JOIN, каждый из которых предназначен для различных сценариев объединения данных. Мы рассмотрим наиболее распространенные из них:

  • INNER JOIN: Возвращает строки только тогда, когда есть совпадение в обеих таблицах на основе условия соединения.
  • LEFT JOIN (или LEFT OUTER JOIN): Возвращает все строки из левой таблицы и совпадающие строки из правой таблицы. Если в правой таблице нет совпадения, он возвращает значения NULL для столбцов из правой таблицы.
  • RIGHT JOIN (или RIGHT OUTER JOIN): Возвращает все строки из правой таблицы и совпадающие строки из левой таблицы. Если в левой таблице нет совпадения, он возвращает значения NULL для столбцов из левой таблицы.
  • FULL OUTER JOIN: Возвращает все строки из обеих таблиц. Если есть совпадение, строки объединяются. Если в одной таблице нет совпадения, для столбцов из другой таблицы используются значения NULL.
  • CROSS JOIN: Возвращает декартово произведение всех строк из обеих таблиц. Он объединяет каждую строку из первой таблицы с каждой строкой из второй таблицы.

Давайте рассмотрим каждый тип с примерами, используя наши таблицы students и courses.

INNER JOIN Назначение: Получение строк, для которых есть совпадение в обеих таблицах на основе указанного условия. Синтаксис:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON join_condition;
  • table1 и table2 - это таблицы, которые вы хотите объединить.
  • join_condition указывает, как таблицы должны быть связаны. Обычно это условие основывается на общем столбце между таблицами. В нашем случае это будет students.student_id = courses.student_id.

Пример: Давайте найдем имена студентов и курсы, на которые они зачислены. Выполните следующий запрос в pgAdmin 4:

SELECT students.student_name, courses.course_name
FROM students
INNER JOIN courses ON students.student_id = courses.student_id;

Пояснение:

  • Мы выбираем student_name из таблицы students и course_name из таблицы courses.
  • FROM students INNER JOIN courses: Мы указываем, что хотим объединить таблицы students и courses с помощью INNER JOIN.
  • ON students.student_id = courses.student_id: Это условие соединения. Оно указывает, что мы хотим сопоставить строки, где student_id в таблице students равен student_id в таблице courses.

Результат: Вы увидите таблицу, подобную этой, в панели “Data output” pgAdmin 4:

Интерпретация: INNER JOIN вернул только те строки, где был совпадающий student_id как в таблице students, так и в таблице courses. Курсы, такие как ‘Genetics’, которые не связаны ни с одним студентом (т.е. student_id равен NULL или не совпадает), и студенты, такие как ‘RAXMONBERGANOV XAYRULLO MAXMUD O‘G‘LI’, которые явно не связаны с курсами в нашей текущей настройке, не включены в результат.

LEFT JOIN (LEFT OUTER JOIN) Назначение: Получение всех строк из левой таблицы и совпадающих строк из правой таблицы. Если в правой таблице нет совпадения, он все равно включает строку из левой таблицы, но со значениями NULL для столбцов из правой таблицы. Синтаксис:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON join_condition;
  • table1 считается левой таблицей, а table2 - правой таблицей в LEFT JOIN.

Пример: Давайте перечислим всех студентов и, если они зачислены на курс, покажем название курса. Выполните следующий запрос в pgAdmin 4:

SELECT students.student_name, courses.course_name
FROM students
LEFT JOIN courses ON students.student_id = courses.student_id;

Пояснение:

  • Мы используем LEFT JOIN с таблицей students в качестве левой таблицы и таблицей courses в качестве правой таблицы.
  • Запрос вернет все строки из таблицы students.
  • Для каждого студента, если есть совпадающий student_id в таблице courses, он также извлечет course_name.
  • Если студент не зачислен ни на один курс (согласно нашим данным), course_name будет NULL.

Результат:

Интерпретация: Обратите внимание, что ‘RAXMONBERGANOV XAYRULLO MAXMUD O‘G‘LI’ и ‘SHONAZAROV AZIZBEK XUSHNUDBEK O‘G‘LI’ включены в результат, хотя они не связаны ни с одним курсом в нашей таблице courses (нет совпадающего student_id). course_name имеет значение NULL, указывая на то, что для этого студента нет связанного курса на основе условия соединения.

RIGHT JOIN (RIGHT OUTER JOIN) Назначение: Получение всех строк из правой таблицы и совпадающих строк из левой таблицы. Если в левой таблице нет совпадения, он все равно включает строку из правой таблицы, но со значениями NULL для столбцов из левой таблицы. Синтаксис:

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON join_condition;
  • table1 - это левая таблица, а table2 - правая таблица в RIGHT JOIN.

Пример: Давайте перечислим все курсы и, если студент зачислен, покажем имя студента. Выполните следующий запрос в pgAdmin 4:

SELECT students.student_name, courses.course_name
FROM students
RIGHT JOIN courses ON students.student_id = courses.student_id;

Пояснение:

  • Мы используем RIGHT JOIN с таблицей students в качестве левой таблицы и таблицей courses в качестве правой таблицы.
  • Запрос вернет все строки из таблицы courses.
  • Для каждого курса, если есть совпадающий student_id в таблице students, он также извлечет student_name.
  • Если курс не связан ни с одним студентом (хотя в нашем примере все курсы связаны каким-либо образом, давайте представим, что ‘Genetics’ не был связан), student_name будет NULL.

Результат:

Интерпретация: ‘Genetics’ включен в результат, хотя мы явно не связывали его со студентом в нашей таблице students для этой демонстрации. Поскольку наши текущие данные связывают все курсы с каким-либо студентом, практическая разница между RIGHT JOIN и INNER JOIN в этом конкретном наборе данных заключается в том, что RIGHT JOIN будет включать курсы, даже если они гипотетически не были связаны ни с одним студентом (чего нет в нашей текущей настройке данных, но концептуально важно). Если бы у ‘Genetics’ student_id был NULL или идентификатор, отсутствующий в students, и мы хотели бы видеть его в результате, RIGHT JOIN обеспечил бы его появление.

FULL OUTER JOIN Назначение: Получение всех строк как из левой, так и из правой таблиц. Для совпадающих строк столбцы объединяются. Для несовпадающих строк из левой таблицы столбцы правой таблицы будут NULL, а для несовпадающих строк из правой таблицы столбцы левой таблицы будут NULL. Синтаксис:

SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2 ON join_condition;

Пример: Давайте посмотрим всех студентов и все курсы, показывая совпадения там, где они есть, и NULL там, где их нет. Выполните следующий запрос в pgAdmin 4:

SELECT students.student_name, courses.course_name
FROM students
FULL OUTER JOIN courses ON students.student_id = courses.student_id;

Пояснение:

  • FULL OUTER JOIN объединяет эффекты LEFT JOIN и RIGHT JOIN.
  • Он включит всех студентов и все курсы.
  • Там, где есть совпадение на основе student_id, строки будут объединены.
  • Для студентов без соответствующего курса (в нашей текущей настройке все студенты связаны хотя бы с одним курсом) и для курсов без соответствующего студента (опять же, в нашей настройке все курсы связаны) вы увидите значения NULL в столбцах из таблицы без совпадения.

Результат:

Интерпретация: Этот результат показывает всех студентов и все курсы. ‘SHONAZAROV AZIZBEK XUSHNUDBEK O‘G‘LI’, который не связан с курсом, отображается с course_name равным NULL. ‘Genetics’, который, как мы можем себе представить, может быть не связан со студентом в некоторых сценариях, отображается с student_name равным NULL. FULL OUTER JOIN гарантирует, что ни одна строка из любой таблицы не будет потеряна в результате.

CROSS JOIN Назначение: Выполнение декартова произведения двух таблиц. Это означает, что каждая строка из первой таблицы объединяется с каждой строкой из второй таблицы. Обычно используется реже, чем другие типы JOIN, и может привести к очень большим результирующим наборам, если таблицы большие. Синтаксис:

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;

Примечание: CROSS JOIN не использует условие ON, потому что он объединяет каждую строку с каждой другой строкой, независимо от каких-либо отношений. Пример: Давайте посмотрим все возможные комбинации студентов и курсов. Выполните следующий запрос в pgAdmin 4:

SELECT students.student_name, courses.course_name
FROM students
CROSS JOIN courses;

Пояснение:

  • CROSS JOIN объединяет каждого студента с каждым курсом.
  • Если у вас 6 студентов и 6 курсов, результат будет содержать 6 * 6 = 36 строк. Результат: (Показан только частичный результат из-за длины)

Интерпретация: Результатом является список всех возможных пар студента и курса. CROSS JOIN полезен в определенных ситуациях, таких как генерация всех возможных комбинаций для тестирования или анализа, но важно понимать его поведение, чтобы избежать непреднамеренного создания чрезвычайно больших результирующих наборов.

3. Псевдонимы для таблиц

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

Пример (использование INNER JOIN с псевдонимами):

SELECT s.student_name, c.course_name
FROM students AS s
INNER JOIN courses AS c ON s.student_id = c.student_id;

В этом примере s - это псевдоним для students, а c - псевдоним для courses. Вы можете использовать эти псевдонимы во всем запросе вместо полных имен таблиц.

4. Предложение WHERE с JOIN

Вы можете комбинировать JOIN с предложениями WHERE для дальнейшей фильтрации результатов. Предложение WHERE применяется после выполнения операции JOIN.

Пример: Найдите имена студентов, обучающихся по специальности “Computer Science”, и курсы, на которые они зачислены.

SELECT s.student_name, c.course_name
FROM students AS s
INNER JOIN courses AS c ON s.student_id = c.student_id
WHERE s.major = 'Computer Science';

Пояснение:

  • Этот запрос сначала выполняет INNER JOIN между таблицами students и courses.
  • Затем предложение WHERE фильтрует результат, чтобы включить только те строки, где major в таблице students равен ‘Computer Science’.

5. Использование COALESCE() для обработки значений NULL в результатах JOIN

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

Назначение COALESCE(): Функция COALESCE() возвращает первое не-NULL выражение в списке выражений. Она принимает несколько аргументов и оценивает их по порядку, пока не найдет первый, который не равен NULL, а затем возвращает это значение. Если все аргументы равны NULL, COALESCE() вернет NULL.

Синтаксис:

COALESCE(expression1, expression2, expression3, ...);

Пример с LEFT JOIN: В нашем примере LEFT JOIN мы отобразили всех студентов и курсы, на которые они записаны. Для студентов, не записанных ни на один курс, course_name отображалось как NULL. Давайте используем COALESCE(), чтобы заменить это NULL текстом ‘Not enrolled in any course’ (Не записан ни на один курс).

Выполните следующий запрос в pgAdmin 4:

SELECT s.student_name, COALESCE(c.course_name, 'Not enrolled in any course') AS course_name
FROM students AS s
LEFT JOIN courses AS c ON s.student_id = c.student_id;

Пояснение:

  • COALESCE(c.course_name, 'Not enrolled in any course'): Эта часть запроса проверяет c.course_name (то есть courses.course_name, благодаря псевдониму c).
    • Если c.course_name не равно NULL (что означает, что студент записан на курс), COALESCE() вернет фактическое значение course_name.
    • Если c.course_name равно NULL (что означает, что студент не записан ни на один курс в нашей таблице courses), COALESCE() вернет второй аргумент, который является строкой 'Not enrolled in any course'.
  • AS course_name: Мы используем псевдоним course_name для результата функции COALESCE(), поэтому выходной столбец по-прежнему будет называться course_name.

Результат:

Интерпретация: Теперь вместо NULL для студентов, которые не записаны на курс, вы видите ‘Not enrolled in any course’. Это делает вывод более читабельным и информативным. COALESCE() - это мощная функция для обработки значений NULL и предоставления значений по умолчанию в результатах запроса, особенно при работе с JOIN, которые могут вводить значения NULL. Вы можете использовать COALESCE() с более чем двумя выражениями, чтобы проверить несколько потенциальных значений NULL и предоставить резервный вариант.