Урок 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— альтернативы.
Документация
Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.