fullstack.org.ua

Self Join · fullstack.org.ua

Середній12 хв· Fullstack Team

Self Join

Зв’яжете таблицю саму з собою для роботи з ієрархіями, колегами та іншими рекурсивними структурами.

Що таке self join

Self join — це об’єднання таблиці з нею самою. Для SQL це дві логічні копії однієї таблиці, тому кожній копії потрібно надати окремий псевдонім.

Self join застосовують, коли рядки однієї таблиці пов’язані між собою:

працівник має керівника;
категорія має батьківську категорію;
коментар є відповіддю на інший коментар;
користувач запросив іншого користувача;
міста або організаційні підрозділи утворюють ієрархію.

Найпоширеніша модель ієрархії має зовнішній ключ, який посилається на первинний ключ тієї самої таблиці:

text

employees.manager_id → employees.employee_id

Приклад ієрархії працівників

manager_id

NULL

sql

CREATE TEMP TABLE employees (
    employee_id integer PRIMARY KEY,
    full_name text NOT NULL,
    department text NOT NULL,
    manager_id integer REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_id, full_name, department, manager_id)
VALUES
    (1, 'Олена Коваль', 'Керівництво', NULL),
    (2, 'Андрій Мельник', 'Розробка', 1),
    (3, 'Марія Бондар', 'Розробка', 2),
    (4, 'Ігор Шевченко', 'Розробка', 2),
    (5, 'Наталія Литвин', 'Тестування', 1),
    (6, 'Павло Романюк', 'Тестування', 5);

text

 employee_name       | manager_name
---------------------+----------------
 Андрій Мельник      | Олена Коваль
 Марія Бондар        | Андрій Мельник
 Ігор Шевченко       | Андрій Мельник
 Наталія Литвин      | Олена Коваль
 Павло Романюк       | Наталія Литвин

sql

SELECT
    employee.full_name AS employee_name,
    employee.department,
    manager.full_name AS manager_name
FROM employees AS employee
LEFT JOIN employees AS manager
    ON manager.employee_id = employee.manager_id
ORDER BY employee.employee_id;

sql

SELECT
    manager.full_name AS manager_name,
    employee.full_name AS employee_name
FROM employees AS manager
JOIN employees AS employee
    ON employee.manager_id = manager.employee_id
WHERE manager.full_name = 'Андрій Мельник'
ORDER BY employee.full_name;

sql

SELECT
    employee.full_name AS employee_name,
    manager.full_name AS manager_name,
    top_manager.full_name AS top_manager_name
FROM employees AS employee
LEFT JOIN employees AS manager
    ON manager.employee_id = employee.manager_id
LEFT JOIN employees AS top_manager
    ON top_manager.employee_id = manager.manager_id
ORDER BY employee.employee_id;

text

employee_name    | manager_name       | top_manager_name
-----------------+--------------------+-----------------
Павло Романюк    | Наталія Литвин     | Олена Коваль

sql

SELECT
    employee.full_name AS employee_name,
    colleague.full_name AS colleague_name,
    employee.manager_id
FROM employees AS employee
JOIN employees AS colleague
    ON colleague.manager_id = employee.manager_id
   AND colleague.employee_id > employee.employee_id
WHERE employee.manager_id IS NOT NULL
ORDER BY employee.manager_id, employee.full_name;

sql

CREATE TEMP TABLE categories (
    category_id integer PRIMARY KEY,
    category_name text NOT NULL,
    parent_category_id integer REFERENCES categories(category_id)
);

INSERT INTO categories (category_id, category_name, parent_category_id)
VALUES
    (1, 'Електроніка', NULL),
    (2, 'Комп’ютери', 1),
    (3, 'Ноутбуки', 2),
    (4, 'Телефони', 1);

SELECT
    child.category_name AS category,
    parent.category_name AS parent_category
FROM categories AS child
LEFT JOIN categories AS parent
    ON parent.category_id = child.parent_category_id
ORDER BY child.category_id;

sql

SELECT
    employee.full_name AS employee_name,
    manager.full_name AS manager_name,
    manager.department AS manager_department
FROM employees AS employee
LEFT JOIN employees AS manager
    ON manager.employee_id = employee.manager_id
WHERE manager.department = 'Розробка';

sql

SELECT
    employee.full_name AS employee_name,
    manager.full_name AS manager_name,
    manager.department AS manager_department
FROM employees AS employee
LEFT JOIN employees AS manager
    ON manager.employee_id = employee.manager_id
   AND manager.department = 'Розробка';

sql

SELECT
    employee.full_name AS employee_name,
    manager.full_name AS manager_name,
    COUNT(colleague.employee_id) AS manager_subordinates
FROM employees AS employee
LEFT JOIN employees AS manager
    ON manager.employee_id = employee.manager_id
LEFT JOIN employees AS colleague
    ON colleague.manager_id = manager.employee_id
GROUP BY
    employee.employee_id,
    employee.full_name,
    manager.employee_id,
    manager.full_name
ORDER BY employee.employee_id;