re:re:actable
Урок 062 минуты чтения

Что такое self join

На этой странице
Кратко

Self join — соединение таблицы с самой собой. Таблица участвует в запросе в двух ролях, которые различают алиасами (e — сотрудник, m — руководитель). Применения: иерархии через ссылку на себя (manager_id, parent_id), сравнение строк внутри таблицы (товары одной категории, пары пользователей из одного города), поиск дублей, соседние события. Для иерархий произвольной глубины self join не хватает — нужен рекурсивный CTE; для сравнения с соседней строкой по порядку удобнее LAG/LEAD.

Вопрос

Что такое self join и где он применяется?

Сотрудник и руководитель

CREATE TABLE employees (
  id bigint PRIMARY KEY,
  name text NOT NULL,
  manager_id bigint REFERENCES employees (id)
);
 
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;    -- LEFT: у CEO руководителя нет

Сравнение строк внутри таблицы

-- сотрудники, получающие больше своего руководителя
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

Пары без повторов

-- пары пользователей из одного города, каждая пара один раз
SELECT a.id AS user_a, b.id AS user_b, a.city
FROM users a
JOIN users b ON b.city = a.city AND a.id < b.id;

a.id < b.id убирает пары «сам с собой» и зеркальные (1-2 и 2-1).

Поиск дублей

SELECT a.id, b.id AS duplicate_id, a.email
FROM users a
JOIN users b ON lower(a.email) = lower(b.email) AND a.id < b.id;

Ограничения

ЗадачаЛучше
Иерархия произвольной глубиныWITH RECURSIVE
Предыдущее/следующее событиеLAG / LEAD
Самая последняя запись в группеDISTINCT ON или ROW_NUMBER
-- ✗ self join для «предыдущего заказа» — сложно и медленно
-- ✓
SELECT id, created_at, lag(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_at
FROM orders;

Частые ошибки

  • Одинаковые алиасы или запросы без алиасов — непонятно, где какая роль.
  • INNER JOIN в иерархии теряет корневые элементы.
  • Пары без a.id < b.id — дубли и сравнение строки с собой.
  • Self join на больших таблицах без индекса по столбцу соединения.

Что важно помнить

  • Self join — одна таблица в двух ролях, роли задают алиасы.
  • Для пар используйте a.id < b.id.
  • Глубокие иерархии — рекурсия, соседние строки — оконные функции.

Мини-шпаргалка

  • FROM t a JOIN t b ON ....
  • LEFT JOIN для опционального родителя.
  • a.id < b.id — уникальные пары.
  • WITH RECURSIVE, LAG/LEAD — альтернативы.

Документация

PostgreSQL Tutorial: Joins Between Tables.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам