Когда лучше употреблять self join, а когда иерархический запрос?

Self join - это присоединение таблицу к самой себе. Это нужно, чтобы сравнить данные в одной таблице с другими данными в ней же. А иерархический запрос- это способ получить данные из таблицы, которая имеет ссылку на саму себя.

Вот наглядные примеры.

Self join: Вы хотите найти все пары сотрудников, которые работают в одной и той же должности. Запрос:

SELECT e1.name, e2.name FROM employees e1
JOIN employees e2
ON e1.position = e2.position AND e1.employee_id != e2.employee_id

Иерархический: Вы хотите найти всех подчиненных у директора компании. Запрос:

WITH RECURSIVE employee_hierarchy(name, manager) AS (
SELECT name, manager FROM employees
WHERE manager IS NULL
UNION
SELECT e.name, e.manager FROM employees e
JOIN employee_hierarchy eh ON e.manager = eh.name
)
SELECT * FROM employee_hierarchy WHERE manager = 'Director'

В 1 примере мы используем соединение таблицы с самой собой (self join) для поиска сотрудников с одинаковой должностью. Во 2 примере мы используем рекурсивный запрос для поиска подчиненных у директора компании.