Сколько записей возвращает CROSS JOIN?

Сколько записей возвращает CROSS JOIN?

Короткий ответ

CROSS JOIN возвращает M × N записей, где:

• M = количество строк в левой таблице
• N = количество строк в правой таблице

Это декартов (картезианский) произведение — каждая строка левой таблицы соединяется с каждой строкой правой таблицы.

Пример

-- Таблица 1: products (3 строки)
SELECT * FROM products;
-- product_id | name
-- 1           | Laptop
-- 2           | Mouse
-- 3           | Keyboard

-- Таблица 2: colors (2 строки)
SELECT * FROM colors;
-- color_id | color_name
-- 1         | Black
-- 2         | Silver

-- CROSS JOIN: 3 × 2 = 6 строк
SELECT p.product_id, p.name, c.color_id, c.color_name
FROM products p
CROSS JOIN colors c;

-- Результат:
-- product_id | name     | color_id | color_name
-- 1           | Laptop   | 1        | Black
-- 1           | Laptop   | 2        | Silver
-- 2           | Mouse    | 1        | Black
-- 2           | Mouse    | 2        | Silver
-- 3           | Keyboard | 1        | Black
-- 3           | Keyboard | 2        | Silver

Синтаксис CROSS JOIN

Существует несколько способов написать CROSS JOIN:

-- Способ 1: Явный CROSS JOIN (рекомендуется)
SELECT *
FROM products p
CROSS JOIN colors c;

-- Способ 2: Неявный (через запятую в FROM)
-- ⚠️ Опасный способ, потому что не видно, что это CROSS JOIN
SELECT *
FROM products p, colors c;

-- Способ 3: ON 1=1 (старый стиль, избегай)
SELECT *
FROM products p
INNER JOIN colors c ON 1=1;

Когда использовать CROSS JOIN

1. Генерация всех комбинаций

-- Задача: сделать полный каталог (все продукты во всех цветах)
SELECT 
  p.product_id,
  p.name,
  c.color_name,
  CONCAT(p.name, ' - ', c.color_name) as product_variant
FROM products p
CROSS JOIN colors c
ORDER BY p.product_id, c.color_id;

-- Полезно для e-commerce: из 100 продуктов × 5 цветов = 500 вариантов SKU

2. Генерация дат

-- Задача: создать календарь (все комбинации дат × справочников)
WITH dates AS (
  SELECT CURRENT_DATE - INTERVAL '90 days' + INTERVAL '1 day' * day as date
  FROM GENERATE_SERIES(0, 90) as day_series(day)
),
stores AS (
  SELECT store_id FROM stores WHERE status = 'active'
)
SELECT d.date, s.store_id
FROM dates d
CROSS JOIN stores s;

-- Результат: для каждого дня × каждый магазин = 90 × 50 = 4500 строк
-- Это основа для fill down логики (заполнение нулей в продажах)

3. Матрица повторений

-- Задача: у тебя есть пользователи и месяцы
-- Нужна матрица: каждый пользователь × каждый месяц

WITH users AS (
  SELECT DISTINCT user_id FROM orders
),
months AS (
  SELECT DISTINCT DATE_TRUNC('month', order_date) as month FROM orders
)
SELECT u.user_id, m.month
FROM users u
CROSS JOIN months m
LEFT JOIN orders o ON u.user_id = o.user_id AND DATE_TRUNC('month', o.order_date) = m.month
ORDER BY u.user_id, m.month;

-- Результат: even if user didn't order in month X, все равно показываем row
-- Это основа для вычисления retention metrics

Осторожно: CROSS JOIN может быть опасен!

Проблема 1: Экспоненциальный рост

Левая таблица: 1,000 строк
Правая таблица: 1,000 строк
CROSS JOIN: 1,000,000 строк ✓ OK

Левая таблица: 10,000 строк
Правая таблица: 100,000 строк
CROSS JOIN: 1,000,000,000 строк ⚠️ Может упасть!

Левая таблица: 1,000,000 строк
Правая таблица: 1,000 строк
CROSS JOIN: 1,000,000,000 строк 🔴 Почти наверняка упадёт!

Решение: Проверяй количество строк перед CROSS JOIN

-- ДО выполнения большого CROSS JOIN проверь размеры
SELECT 
  (SELECT COUNT(*) FROM table1) as table1_count,
  (SELECT COUNT(*) FROM table2) as table2_count,
  (SELECT COUNT(*) FROM table1) * (SELECT COUNT(*) FROM table2) as result_count;

-- Если result_count > 100 млн — пересмотри логику

Проблема 2: Случайный CROSS JOIN (скрытый баг)

-- Плохо: забыл WHERE clause или JOIN условие
SELECT o.order_id, p.price
FROM orders o
INNER JOIN prices p ON 1=1;  -- 🔴 Это CROSS JOIN!
-- Результат: 1M заказов × 1000 цен = 1B строк

-- Хорошо: явное условие
SELECT o.order_id, p.price
FROM orders o
INNER JOIN prices p ON o.product_id = p.product_id
  AND o.order_date BETWEEN p.valid_from AND p.valid_to;

Проблема 3: CROSS JOIN в WHERE

-- Очень плохо
SELECT *
FROM orders o
WHERE o.order_id IN (
  SELECT p1.product_id
  FROM products p1
  CROSS JOIN products p2  -- Зачем?
);

-- Это создаст 1000 × 1000 = 1M комбинаций в subquery!

Практический пример: Retention Cohort Analysis

Это real-world сценарий, где CROSS JOIN незаменим:

-- Задача: посчитать retention по когортам (когда пользователь первый раз активен)
WITH cohorts AS (
  -- Когорта: первый месяц активности пользователя
  SELECT 
    user_id,
    DATE_TRUNC('month', MIN(order_date)) as cohort_month
  FROM orders
  GROUP BY user_id
),
activity_matrix AS (
  -- Матрица: каждый user × каждый месяц
  -- Потому что нужно показать даже месяцы без активности
  SELECT DISTINCT 
    c.user_id,
    DATE_TRUNC('month', o.order_date) as activity_month
  FROM cohorts c
  CROSS JOIN orders o  -- ← Вот он, CROSS JOIN!
),
retention AS (
  SELECT 
    c.cohort_month,
    EXTRACT(MONTH FROM a.activity_month - c.cohort_month) as month_offset,
    COUNT(DISTINCT c.user_id) as retained_users
  FROM cohorts c
  LEFT JOIN activity_matrix a ON c.user_id = a.user_id
  GROUP BY c.cohort_month, month_offset
  ORDER BY c.cohort_month, month_offset
)
SELECT * FROM retention;

Производительность

-- CROSS JOIN медленнее чем обычный JOIN
-- Потому что:
-- 1. Нет фильтрации по условию (ON clause)
-- 2. Все комбинации должны быть вычислены
-- 3. Результат большой

-- Бенчмарк (100k левая × 1k правая):
-- INNER JOIN с условием: 200ms
-- CROSS JOIN: 5-10s

-- Оптимизация:
-- 1. Предфильтруй таблицы перед CROSS JOIN
SELECT *
FROM (
  SELECT * FROM products WHERE category = 'Electronics'  -- 100 строк
) p
CROSS JOIN (
  SELECT * FROM colors WHERE is_active = true  -- 5 строк
) c;
-- Вместо: 1000 × 100 = 100k
-- Теперь: 100 × 5 = 500

-- 2. Используй LIMIT если не нужны все комбинации
SELECT TOP 1000 * FROM products p CROSS JOIN colors c;

Ошибки при CROSS JOIN

Ошибка 1: Дублирование из-за забытого JOIN условия

-- Плохо
SELECT o.order_id, od.product_id
FROM orders o
CROSS JOIN order_details od;  -- Каждый заказ × ВСЕ order details!

-- Хорошо
SELECT o.order_id, od.product_id
FROM orders o
INNER JOIN order_details od ON o.order_id = od.order_id;

Ошибка 2: Неправильный расчёт в итоговых метриках

-- Плохо: если использовать CROSS JOIN без DISTINCT
SELECT COUNT(*) as total_revenue
FROM orders o
CROSS JOIN order_details od
WHERE o.amount > 100;
-- Каждый заказ > 100 будет посчитан МНОГО раз!

-- Хорошо: используй GROUP BY
SELECT SUM(o.amount) as total_revenue
FROM orders o
INNER JOIN order_details od ON o.order_id = od.order_id
GROUP BY o.order_id;

Правило: Когда использовать CROSS JOIN

✅ Использовать CROSS JOIN когда:
  - Явно нужны ВСЕ комбинации
  - Обе таблицы маленькие (< 10k строк)
  - Пишешь матрицу для дальнейших расчётов
  - Генерируешь справочники (календари, комбинации)

❌ Избегать CROSS JOIN когда:
  - Забыл WHERE или ON условие (ошибка!)
  - Одна таблица большая (> 100k)
  - Нужен обычный JOIN (с условием)
  - Не уверен в результирующем размере

Вывод

CROSS JOIN возвращает M × N записей — декартово произведение. Это мощный инструмент для:

• Генерации комбинаций
• Создания матриц для анализа
• Cohort analysis и retention расчётов

Но нужна осторожность: легко создать 1 млрд строк по ошибке!