Как работает cross-join в SQL и когда его использовать?

CROSS JOIN в SQL

CROSS JOIN (декартово произведение) — это вид соединения, которое создаёт все возможные комбинации строк из двух таблиц. Это мощный инструмент, но его нужно использовать осторожно, потому что он может создать огромные промежуточные таблицы.

Что такое CROSS JOIN

Формула: Если таблица A имеет N строк, таблица B имеет M строк, то CROSS JOIN вернёт N × M строк.

-- Самый простой синтаксис
SELECT *
FROM table_a
CROSS JOIN table_b;

-- Эквивалент (тоже самое)
SELECT *
FROM table_a, table_b;

Пример визуально:

Таблица A:        Таблица B:
id  name          product_id  name
1   Alice         100         Laptop
2   Bob           200         Phone

CROSS JOIN результат (2 × 2 = 4 строки):
id  name   product_id  name
1   Alice  100         Laptop
1   Alice  200         Phone
2   Bob    100         Laptop
2   Bob    200         Phone

Когда CROSS JOIN полезен

1. Генерирование всех комбинаций (e.g., матрица)

-- Найти все комбинации пользователей и продуктов
-- (например, для подготовки рекомендаций)
SELECT
  u.user_id,
  p.product_id,
  p.name as product_name
FROM users u
CROSS JOIN products p
WHERE u.active = TRUE
  AND p.active = TRUE
LIMIT 10;

-- Результат: каждый пользователь спарен с каждым продуктом
-- Потом можно добавить колонку "has_purchased" и анализировать, 
-- какие комбинации отсутствуют (= возможности для кросс-селла)

2. Генерирование временных рядов (календари, даты)

-- Создать календарь всех дней текущего года
WITH dates AS (
  SELECT GENERATE_SERIES('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day'::INTERVAL)::DATE as date
),
months AS (
  SELECT DISTINCT DATE_TRUNC('month', date)::DATE as month_start
  FROM dates
)
SELECT
  d.date,
  m.month_start,
  EXTRACT(DAY FROM d.date) as day_of_month,
  EXTRACT(DOW FROM d.date) as day_of_week
FROM dates d
CROSS JOIN (SELECT DISTINCT DATE_TRUNC('month', date)::DATE as month_start FROM dates) m
WHERE d.date >= m.month_start
  AND d.date < m.month_start + INTERVAL '1 month';

3. Создание всех возможных пар (друзья, матчи)

-- Найти всех потенциальных соответствий в приложении знакомств
-- (люди одного возраста, интересов, города)

WITH male_users AS (
  SELECT user_id, name, age, city, interests
  FROM users
  WHERE gender = 'M' AND active = TRUE
),
female_users AS (
  SELECT user_id, name, age, city, interests
  FROM users
  WHERE gender = 'F' AND active = TRUE
)
SELECT
  m.user_id as male_user_id,
  f.user_id as female_user_id,
  m.name as male_name,
  f.name as female_name,
  ABS(m.age - f.age) as age_difference
FROM male_users m
CROSS JOIN female_users f
WHERE m.city = f.city                      -- живут в одном городе
  AND ABS(m.age - f.age) <= 5              -- возраст близок
  AND m.interests && f.interests            -- есть общие интересы (массивы)
LIMIT 100;

4. Анализ с фиксированными значениями (сценарии, варианты)

-- Рассчитать выручку при разных ценовых стратегиях
WITH products AS (
  SELECT product_id, name, current_price, monthly_sales_quantity
  FROM products
  WHERE category = 'premium'
),
price_scenarios AS (
  SELECT 0.85 as price_multiplier UNION ALL
  SELECT 0.90 UNION ALL
  SELECT 1.00 UNION ALL
  SELECT 1.10 UNION ALL
  SELECT 1.20
)
SELECT
  p.product_id,
  p.name,
  ps.price_multiplier,
  p.current_price * ps.price_multiplier as new_price,
  -- Предположим elasticity: спрос снижается на 2% за каждый 1% роста цены
  p.monthly_sales_quantity * POWER(1 - (ps.price_multiplier - 1) * 0.02, 1) as estimated_quantity,
  (p.current_price * ps.price_multiplier) * (p.monthly_sales_quantity * POWER(1 - (ps.price_multiplier - 1) * 0.02, 1)) as estimated_monthly_revenue
FROM products p
CROSS JOIN price_scenarios ps
ORDER BY p.product_id, estimated_monthly_revenue DESC;

5. Создание полной матрицы для проверки пропусков

-- Найти пользователей, у которых отсутствуют данные за определённые месяцы
WITH all_users AS (
  SELECT DISTINCT user_id FROM users
),
all_months AS (
  SELECT DATE_TRUNC('month', DATE_SERIES)::DATE as month
  FROM GENERATE_SERIES('2024-01-01'::DATE, '2024-12-31'::DATE, '1 month'::INTERVAL) as DATE_SERIES
),
expected_records AS (
  SELECT u.user_id, m.month
  FROM all_users u
  CROSS JOIN all_months m
),
actual_records AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', activity_date)::DATE as month
  FROM user_activity
  WHERE activity_date >= '2024-01-01'
)
SELECT
  e.user_id,
  e.month,
  CASE WHEN a.user_id IS NULL THEN 'missing_data' ELSE 'present' END as status
FROM expected_records e
LEFT JOIN actual_records a ON e.user_id = a.user_id AND e.month = a.month
WHERE a.user_id IS NULL
ORDER BY e.user_id, e.month;

ПЕРЕПРОВЕРКА: CROSS JOIN vs INNER JOIN

-- CROSS JOIN: ВСЕ комбинации
SELECT u.user_id, p.product_id
FROM users u
CROSS JOIN products p
LIMIT 5;

-- Результат: все 5000 пользователей × 100 продуктов = 500,000 строк

-- INNER JOIN: ТОЛЬКО где есть связь
SELECT u.user_id, p.product_id
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
INNER JOIN products p ON o.product_id = p.product_id
LIMIT 5;

-- Результат: только где пользователь купил продукт (может быть 10,000 строк)

Предупреждения: опасности CROSS JOIN

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

-- ОПАСНО! Может упасть сервер
SELECT *
FROM users                -- 100K строк
CROSS JOIN products       -- 50K строк
CROSS JOIN orders         -- 10M строк

-- Результат: 100K × 50K × 10M = 50 триллионов строк!
-- Это заполнит всю память и упадёт

Защита: всегда добавляй WHERE условие

SELECT *
FROM users u
CROSS JOIN products p
WHERE u.active = TRUE
  AND p.active = TRUE
  AND u.created_at > NOW() - INTERVAL '30 days'  -- только свежие
LIMIT 1000;  -- и ограничи результат

Проблема 2: Забытый WHERE условие в CROSS JOIN

-- НЕПРАВИЛЬНО: хотел INNER JOIN, но написал CROSS JOIN
SELECT *
FROM users u
CROSS JOIN orders o  -- забыл ON условие!

-- Результат: каждый пользователь спарен с КАЖДЫМ заказом
-- Если 1000 пользователей и 100K заказов: 100M строк (!)

-- ПРАВИЛЬНО:
SELECT *
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;  -- добавил ON

Оптимизация CROSS JOIN запросов

Плохо: без индексов и фильтрации

SELECT *
FROM users u
CROSS JOIN products p;
-- Если нет WHERE, это 100% таблица сканирования

Хорошо: с фильтрацией и пределом

SELECT u.user_id, p.product_id
FROM (
  SELECT user_id FROM users WHERE active = TRUE LIMIT 1000
) u
CROSS JOIN (
  SELECT product_id FROM products WHERE category IN ('premium', 'bestseller') LIMIT 100
) p;

-- Результат: максимум 1000 × 100 = 100K строк (управляемо)

Практические примеры для Product Analyst

Пример 1: Находка неиспользованных функций

-- Какие функции никогда не использовал каждый пользователь?
WITH all_features AS (
  SELECT feature_id, feature_name FROM features
),
features_per_user AS (
  SELECT DISTINCT user_id, feature_id
  FROM user_events
  WHERE event_type = 'feature_used'
),
expected_usage AS (
  SELECT u.user_id, af.feature_id, af.feature_name
  FROM (SELECT DISTINCT user_id FROM users WHERE active = TRUE) u
  CROSS JOIN all_features af
)
SELECT
  eu.user_id,
  eu.feature_id,
  eu.feature_name
FROM expected_usage eu
LEFT JOIN features_per_user fpu 
  ON eu.user_id = fpu.user_id AND eu.feature_id = fpu.feature_id
WHERE fpu.user_id IS NULL  -- не использовал
ORDER BY eu.user_id;

Пример 2: Метрики по всем комбинациям

-- Проверить все комбинации страна × устройство × браузер
WITH dimensions AS (
  SELECT DISTINCT country FROM events UNION
  SELECT DISTINCT device FROM events UNION
  SELECT DISTINCT browser FROM events
)
SELECT
  country,
  device,
  browser,
  COUNT(*) as event_count,
  COUNT(DISTINCT user_id) as unique_users
FROM events
GROUP BY country, device, browser
HAVING COUNT(*) > 0  -- только где есть данные
ORDER BY event_count DESC;

Чеклист при использовании CROSS JOIN

• ☑ Понимаю, что N × M может быть ОГРОМНЫМ числом
• ☑ Всегда добавляю WHERE условие для фильтрации
• ☑ Использую LIMIT для ограничения результатов
• ☑ Проверяю EXPLAIN ANALYZE перед запуском
• ☑ Не забыл ON условие (случайно CROSS JOIN вместо INNER JOIN)
• ☑ Рассчитал примерный размер результата перед запуском
• ☑ Использую подзапросы, чтобы сначала отфильтровать данные

Главное: CROSS JOIN генерирует все комбинации, что мощно, но опасно. Всегда будь осторожен с размером результата.