Повне об'єднання даних з 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, 'Немає студента') AS student_name,
    COALESCE(e.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. Тепер на тебе чекає захопливий світ більш складних об'єднань і обробки даних!