Self и cross join

Self-join: таблица, соединённая с собой

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

Обход иерархии (adjacency list). Частый паттерн хранит дерево так, что каждая строка указывает на родителя: у users может быть колонка referred_by, а таблица в стиле employees имеет manager_id, ссылающийся на id той же таблицы. Чтобы спарить каждого сотрудника с именем его менеджера, соедини таблицу с собой:

-- Каждый сотрудник рядом с именем своего менеджера (один уровень иерархии)
SELECT e.id, e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- LEFT JOIN, чтобы CEO (manager_id IS NULL) тоже появился, manager = NULL

LEFT JOIN важен: у вершины дерева нет родителя, поэтому inner self-join молча выбросил бы корень. (Один self-join поднимается ровно на один уровень; предки произвольной глубины требуют рекурсивного CTE — раздел 05.)

Попарное сравнение. Другое применение — сравнение строк таблицы с другими строками той же таблицы: найти дубли товаров, пользователей из одного города, заказы в пределах часа друг от друга. Здесь и живёт баг из хука.

-- Пары товаров с одинаковым именем — дедуплены и без самосовпадений
SELECT a.id AS id_a, b.id AS id_b, a.name
FROM products a
JOIN products b ON a.name = b.name
WHERE a.id < b.id;     -- критическая строка

Без WHERE a.id < b.id ты получаешь три вида мусора: каждая строка совпадает сама с собой (a.id = b.id), и каждая настоящая пара появляется дважды в обоих порядках (A–B и B–A). Предикат a.id < b.id решает оба разом — он исключает самосовпадения (id никогда не меньше себя) и оставляет лишь один порядок каждой пары. Используй < для неупорядоченных пар; используй <> лишь если тебе действительно нужны оба направления.

Cross join: осознанное произведение

Прошлый урок подал декартово произведение как грабли. Но иногда произведение — ровно тот результат, что нужен, и ты пишешь CROSS JOIN, чтобы сказать это намеренно. Канонический случай — плотная сетка — каждая комбинация двух измерений, включая пустые ячейки.

-- Каждая ячейка (день, товар) для 7-дневного отчёта, даже дни с нулём продаж
SELECT d.day, p.id AS product_id, COALESCE(s.qty, 0) AS qty
FROM generate_series(DATE '2026-06-01', DATE '2026-06-07', INTERVAL '1 day') AS d(day)
CROSS JOIN products p
LEFT JOIN sales s ON s.product_id = p.id AND s.sold_on = d.day;

Здесь generate_series производит 7 дней, CROSS JOIN products делает каждую комбинацию день-товар (каркас), а LEFT JOIN к реальным продажам заполняет количества, дефолтя в 0 для пустых ячеек. Без cross join дни без продаж были бы вовсе отсутствующими строками — классический пробел в отчёте. Другие осознанные применения: матрица вариантов «все размеры × все цвета» или спаривание каждой строки с фиксированным набором корзин.

Различие, на которое указывает заголовок урока: явный CROSS JOIN читается как намерение — «да, мне нужно произведение». Произведение, прокравшееся через join по запятой (FROM a, b без WHERE, связывающего их) или слишком слабый ON, — это случайность и авария из урока 1. Пиши CROSS JOIN, когда имеешь это в виду; никогда не позволяй произведению случиться молча.