Полное объединение данных с FULL OUTER JOIN

Сегодня мы разберёмся с самой демократичной формой объединения данных — FULL OUTER JOIN. Это объединение, где все желающие попадают в результат, даже если у них нет пары.

FULL OUTER JOIN — это вид объединения данных, где возвращаются все строки из обеих таблиц. Если для строки в одной таблице нет соответствующей строки в другой, вместо отсутствующих значений в результирующий набор записываются NULL. Это как вести учет всех людей, которые пришли на две разные вечеринки: даже если кто-то пришёл только на одну из них, он всё равно будет учтён.

Визуально это можно представить так:

Таблица A                     Таблица B
+----+----------+             +----+----------+
| id | name     |             | id | course   |
+----+----------+             +----+----------+
| 1  | Alice    |             | 2  | Math     |
| 2  | Bob      |             | 3  | Physics  |
| 4  | Charlie  |             | 5  | History  |
+----+----------+             +----+----------+

FULL OUTER JOIN РЕЗУЛЬТАТ:
+----+----------+----------+
| id | name     | course   |
+----+----------+----------+
| 1  | Alice    | NULL     |
| 2  | Bob      | Math     |
| 3  | NULL     | Physics  |
| 4  | Charlie  | NULL     |
| 5  | NULL     | History  |
+----+----------+----------+

Строки без соответствия сохраняются, но данные для отсутствующих колонок будут заполнены значением NULL.

Синтаксис FULL OUTER JOIN

Синтаксис выглядит просто, но мощь его велика:

SELECT
    столбцы
FROM 
    таблица1
FULL OUTER JOIN 
    таблица2
ON таблица1.общий_столбец = таблица2.общий_столбец;

Ключевая часть здесь — FULL OUTER JOIN, которая заставляет PostgreSQL взять все строки из обеих таблиц. Если для какой-то строки не хватает пары по условию ON, значения заменяются на NULL.

Примеры использования

Давайте разберем реальные примеры на основе хорошо знакомой базы данных university с таблицами students и enrollments.

Пример 1: список всех студентов и курсов

Представьте, что у нас есть две таблицы:

Таблица students:

Таблица enrollments:

Наша задача — составить полный список студентов и курсов, включая тех студентов, которые не записаны ни на один курс, и курсы без студентов.

Вот запрос:

SELECT
    s.student_id, 
    s.name, 
    e.course
FROM 
    students s
FULL OUTER JOIN 
    enrollments e
ON 
    s.student_id = e.student_id;

Результат:

Как видите, в результат попали все студенты и все курсы. Студент Charlie не записан на курсы, поэтому для него поле course — NULL. А курс History не имеет студента, поэтому его student_id и name равны NULL.

Пример 2: Анализ продаж и товаров

Теперь подумаем о магазине. У нас есть две таблицы:

Таблица products:

Таблица sales:

Мы хотим получить полный список всех продуктов и продаж, включая те продукты, которые не были проданы, и продажи с неверными идентификаторами product_id.

Запрос:

SELECT
    p.product_id,
    p.name AS product_name,
    s.quantity
FROM 
    products p
FULL OUTER JOIN 
    sales s
ON 
    p.product_id = s.product_id;

Результат:

Здесь мы видим, что у Smartphone продаж не было (quantity = NULL), а продажа с product_id = 4 не соответствует ни одному продукту.

Практическое задание

Попробуйте написать запрос для таблиц departments и employees:

Таблица departments:

Таблица employees:

Напишите FULL OUTER JOIN, чтобы получить полный список департаментов и сотрудников. Заполните отсутствующие данные строками с NULL.

Как обработать NULL значения

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

Пример:

SELECT
    COALESCE(s.name, 'No Student') AS student_name,
    COALESCE(e.course, 'No Course') AS course_name
FROM 
    students s
FULL OUTER JOIN 
    enrollments e
ON 
    s.student_id = e.student_id;

Результат:

Теперь вместо NULL мы видим понятные значения, которые делают отчеты более читаемыми.

Когда использовать FULL OUTER JOIN

FULL OUTER JOIN полезен в ситуациях, когда вам необходимо видеть все данные из обеих таблиц, даже если они не полностью связаны. Примеры:

• Отчеты по продажам и товарам — чтобы увидеть как проданные, так и непроданные товары.
• Анализ студентов и курсов — чтобы проверить, есть ли неучтенные данные.
• Сравнение списков — например, для выявления несоответствий между двумя наборами данных.

Надеюсь эта лекция дала вам хорошее представление о FULL OUTER JOIN. Теперь вас ждёт увлекательный мир более сложных объединений и обработки данных!

2

Задача

SQL SELF, 11 уровень, 4 лекция

Недоступна

Использование FULL OUTER JOIN для объединения данных

Использование FULL OUTER JOIN для объединения данных

Открыть

2

Задача

SQL SELF, 11 уровень, 4 лекция

Недоступна

Сравнение студентов и курсов используя FULL OUTER JOIN

Сравнение студентов и курсов используя FULL OUTER JOIN

Открыть

1

Опрос

Объединение данных, 11 уровень, 4 лекция

Недоступен

Объединение данных

Объединение данных

Открыть