CROSS JOIN и SELF JOIN в SQL: когда нужно декартово произведение

CROSS JOIN и SELF JOIN — два специальных вида соединений, которые используются реже INNER и LEFT JOIN, но незаменимы в определённых задачах. Разберём когда и как их применять.

CROSS JOIN: декартово произведение

CROSS JOIN возвращает все возможные комбинации строк из двух таблиц:

-- Синтаксис
SELECT * FROM table_a CROSS JOIN table_b;

-- Эквивалентный старый синтаксис (избегайте — не очевидно что CROSS JOIN)
SELECT * FROM table_a, table_b;

Если table_a имеет 3 строки, а table_b — 4, результат будет 3 × 4 = 12 строк.

Практический пример: все комбинации

-- Таблица размеров
sizes: S, M, L
-- Таблица цветов
colors: красный, синий, зелёный

SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;
-- 9 строк: S-красный, S-синий, S-зелёный, M-красный, ...

Когда нужен CROSS JOIN

1. Генерация сетки дат × категорий

-- Убедиться что в отчёте есть строка для каждой даты и категории
-- даже если продаж не было

WITH dates AS (
    SELECT generate_series(
        '2026-03-01'::date,
        '2026-03-31'::date,
        '1 day'::interval
    )::date AS sale_date
),
categories AS (
    SELECT DISTINCT category FROM products
)
SELECT d.sale_date, c.category, COALESCE(SUM(s.amount), 0) AS revenue
FROM dates d
CROSS JOIN categories c
LEFT JOIN sales s ON s.sale_date = d.sale_date AND s.category = c.category
GROUP BY d.sale_date, c.category
ORDER BY d.sale_date, c.category;

2. Матрица сравнения всех пар

-- Все пары команд в турнире
SELECT t1.team AS home, t2.team AS away
FROM teams t1
CROSS JOIN teams t2
WHERE t1.team <> t2.team  -- исключить игру с собой
ORDER BY home, away;

3. Умножение строк

-- Повторить каждую строку N раз (для тестовых данных)
SELECT p.*, g.n
FROM products p
CROSS JOIN generate_series(1, 5) AS g(n);
-- Каждый продукт повторяется 5 раз

Внимание: случайный CROSS JOIN

Самая опасная ошибка — случайный CROSS JOIN из-за пропущенного условия:

-- Забыли ON — получили CROSS JOIN!
SELECT u.name, o.amount
FROM users u
JOIN orders o;  -- ошибка: нет условия

-- Правильно:
FROM users u
JOIN orders o ON o.user_id = u.id

PostgreSQL выдаст ошибку синтаксиса, но старый синтаксис через запятую молча создаст декартово произведение:

-- ОПАСНО: миллионы строк если таблицы большие
SELECT * FROM users, orders WHERE status = 'completed';
-- Нет условия соединения = CROSS JOIN + фильтр

SELF JOIN: таблица соединяется сама с собой

SELF JOIN — это обычный JOIN, но к одной и той же таблице с разными алиасами:

-- Нужно для работы с иерархическими данными в одной таблице
SELECT a.*, b.*
FROM employees a
JOIN employees b ON b.id = a.manager_id;
-- a — подчинённый, b — его менеджер

Таблица сотрудников с самоссылкой

-- employees: id | name | manager_id (NULL для CEO)
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY m.name, e.name;

Найти всех сотрудников одного менеджера

-- Коллеги Бориса (один менеджер)
SELECT e2.name AS colleague
FROM employees e1
JOIN employees e2 ON e2.manager_id = e1.manager_id
WHERE e1.name = 'Борис'
  AND e2.id <> e1.id;

Поиск дубликатов через SELF JOIN

-- Найти пользователей с одинаковым email
SELECT a.id, a.email
FROM users a
JOIN users b ON b.email = a.email AND b.id < a.id;
-- b.id < a.id — чтобы не дублировать пары

Поиск пар с условием

-- Все пары товаров в одной категории
SELECT
    a.name AS product_1,
    b.name AS product_2,
    a.category
FROM products a
JOIN products b ON b.category = a.category AND b.id > a.id
-- b.id > a.id — берём каждую пару только раз (a→b, но не b→a)
ORDER BY a.category, a.name;

SELF JOIN vs рекурсивный CTE

Для иерархий фиксированной глубины SELF JOIN проще:

-- 2 уровня: сотрудник → менеджер → директор
SELECT
    e.name AS employee,
    m.name AS manager,
    d.name AS director
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
LEFT JOIN employees d ON d.id = m.manager_id;

Для глубины N → рекурсивный CTE (см. статью «Рекурсивные CTE»).

CROSS JOIN LATERAL

LATERAL — мощное расширение: правая часть может ссылаться на строки левой:

-- Топ-3 заказа для каждого пользователя
SELECT u.id, u.name, top_orders.*
FROM users u
CROSS JOIN LATERAL (
    SELECT amount, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY amount DESC
    LIMIT 3
) top_orders;

Без LATERAL подзапрос не мог бы ссылаться на u.id. LATERAL превращает подзапрос в «коррелированный FROM».

Итог

CROSS JOIN — редкий, но мощный инструмент. SELF JOIN — стандартная техника для работы с деревьями и парами в одной таблице.