Самооб'єднання даних за допомогою SELF JOIN

SELF JOIN — це спосіб об'єднати таблицю саму з собою. Це може здатися дивним на перший погляд: навіщо об'єднувати таблицю саму з собою? Але в реальному житті такі задачі трапляються досить часто. Наприклад, уяви, що в тебе є таблиця співробітників, і кожен співробітник має менеджера. Менеджер теж є співробітником, а значить, його дані знаходяться в тій же таблиці. SELF JOIN допоможе нам зіставити співробітників з їхніми менеджерами.

Якщо говорити формально, SELF JOIN — це звичайний JOIN, але в ньому ми використовуємо одну й ту ж таблицю двічі, даючи їй різні псевдоніми (аліаси), щоб відрізняти її "версії".

SELF JOIN можна використовувати для:

• Ієрархічних зв'язків: встановлення відносин "батько-дочірній елемент", наприклад, співробітник і його менеджер.
• Аналізу даних всередині таблиці: порівняння записів у таблиці, наприклад, пошук схожих товарів або подій.
• Складних запитів до структур з дублюючою логікою.

Синтаксис SELF JOIN

Давай для більшої ясності одразу подивимось на спрощений синтаксис SELF JOIN:

SELECT
    A.column_name,
    B.column_name
FROM 
    table_name A
JOIN 
    table_name B
ON 
    A.common_column = B.common_column;

Тут:

• table_name A і table_name B — це одна й та сама таблиця, але з різними псевдонімами.
• A.common_column і B.common_column — стовпці, які використовуються для об'єднання записів.

Псевдоніми (A і B) потрібні для того, щоб СУБД "розуміла", з якою з "копій" таблиці ти працюєш.

Приклади використання SELF JOIN

Приклад 1: список співробітників і їхніх менеджерів

Отже, припустимо, у нас є таблиця employees, яка виглядає ось так:

У цій таблиці:

• employee_id — ідентифікатор співробітника.
• name — ім'я співробітника.
• manager_id — ідентифікатор менеджера, який також є employee_id іншого співробітника.

Задача: отримати список співробітників і їхніх менеджерів.

Ось так ця задача вирішується за допомогою SELF JOIN:

SELECT
    e.name AS employee_name,
    m.name AS manager_name
FROM 
    employees e
LEFT JOIN 
    employees m
ON 
    e.manager_id = m.employee_id;

Результат:

Тут:

• Таблиця e — це "співробітники".
• Таблиця m — це теж "співробітники", але в ролі "менеджерів".

У Alex Lin немає менеджера manager_id = NULL, тому в результаті поле manager_name буде порожнім.

Приклад 2: Пошук схожих товарів

Припустимо, у нас є таблиця products:

Задача: знайти пари товарів, які належать до однієї категорії.

Рішення з використанням SELF JOIN:

SELECT
    p1.product_name AS product_1,
    p2.product_name AS product_2
FROM 
    products p1
JOIN 
    products p2
ON 
    p1.category = p2.category
AND 
    p1.product_id < p2.product_id;

Результат:

Зверни увагу на умову p1.product_id < p2.product_id. Воно дозволяє уникнути дублювання пар, наприклад, щоб у результатах не з'являлись рядки типу Holodilnyk — Styralna mashyna і Styralna mashyna — Holodilnyk.

Приклад 3: Аналіз ієрархій (батьки та нащадки)

Розглянемо ще один приклад. Припустимо, у нас є таблиця categories:

Тут:

• category_id — ідентифікатор категорії.
• category_name — назва категорії.
• parent_id — ідентифікатор батьківської категорії.

Задача: зіставити категорії та їхні батьківські категорії.

Запит:

SELECT
    c1.category_name AS child_category,
    c2.category_name AS parent_category
FROM 
    categories c1
LEFT JOIN 
    categories c2
ON 
    c1.parent_id = c2.category_id;

Результат:

Типові помилки при використанні SELF JOIN

Забули вказати аліаси (псевдоніми): якщо не використовувати аліаси, стає неможливо відрізнити одну "копію" таблиці від іншої.

Циклічні посилання: якщо дані містять циклічні посилання (наприклад, співробітник сам собі менеджер), це може призвести до неочікуваних результатів.

Дублювання результатів: не забувай фільтрувати результати (наприклад, за допомогою p1.product_id < p2.product_id), щоб уникнути дублювання.

SELF JOIN — це потужний інструмент при роботі з даними, і його правильне використання дозволяє вирішувати складні задачі з ієрархіями, зв'язками всередині таблиць і аналізом даних. Сподіваюсь, тепер ти бачиш, наскільки корисний цей "селфі" у всесвіті SQL!