Разница между CROSS JOIN с WHERE и обычным JOIN ON

Разница между CROSS JOIN с WHERE и обычным JOIN ON

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

На первый взгляд результат одинаков: оба синтаксиса возвращают одно множество строк. Но различия в производительности критичны:

• CROSS JOIN + WHERE сначала создаёт полный декартов произведение, потом фильтрует
• JOIN ON фильтрует уже при соединении (эффективнее)

Работая Data Engineer'ом, нужно понимать, когда использовать какой вариант.

Теория: Что происходит

CROSS JOIN + WHERE

SELECT u.id, o.order_id
FROM users u
CROSS JOIN orders o
WHERE u.id = o.user_id;

Порядок выполнения:

1. CROSS JOIN создаёт декартово произведение: 1000 users × 100 000 orders = 100 млн строк в памяти
2. WHERE фильтрует: оставляет только строки, где u.id = o.user_id

Проблема: 100 млн строк требуют огромной памяти!

JOIN ON

SELECT u.id, o.order_id
FROM users u
JOIN orders o ON u.id = o.user_id;

Порядок выполнения:

1. БД оптимизирует: использует индекс на o.user_id
2. Для каждого пользователя находит только его заказы (например, 100 заказов)
3. Итого: 1000 × 100 = 100 000 строк (только нужные)

Практический пример

-- Таблицы
-- users: 1000 строк
-- orders: 100,000 строк

-- Вариант 1: CROSS JOIN + WHERE (ПЛОХО)
EXPLAIN ANALYZE
SELECT u.id, o.order_id, o.amount
FROM users u
CROSS JOIN orders o
WHERE u.id = o.user_id;

-- Вывод примерно:
-- Seq Scan on users u  (cost=0.00..1000000.00 rows=1000)
-- Seq Scan on orders o  (cost=0.00..10000.00 rows=100000)
-- Filter: u.id = o.user_id
-- Execution time: 5000ms

-- Вариант 2: JOIN ON (ХОРОШО)
EXPLAIN ANALYZE
SELECT u.id, o.order_id, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Вывод примерно:
-- Nested Loop
--   -> Seq Scan on users u
--   -> Index Scan using orders_user_id_idx on orders o
-- Execution time: 50ms

Разница в 100 раз!

Когда результаты совпадают?

Результаты совпадают только если:

1. Условие в WHERE эквивалентно условию соединения
2. Нет других фильтров

-- Совпадают:
SELECT * FROM users u CROSS JOIN orders o WHERE u.id = o.user_id;
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

-- НЕ совпадают (WHERE имеет доп условие):
SELECT * FROM users u CROSS JOIN orders o 
WHERE u.id = o.user_id AND o.amount > 100;

SELECT * FROM users u JOIN orders o ON u.id = o.user_id 
WHERE o.amount > 100;

-- В первом случае БД сначала создаёт 100 млн строк, потом фильтрует по amount
-- Во втором БД фильтрует по u.id при соединении, потом по amount

В ClickHouse (колоночная БД)

В колоночных БД различие ещё более критично:

-- ПЛОХО: CROSS JOIN требует broadcast всей таблицы
SELECT u.id, COUNT(*) as orders_cnt
FROM users u
CROSS JOIN orders o
WHERE u.id = o.user_id
GROUP BY u.id;

-- ХОРОШО: JOIN использует распределённый алгоритм
SELECT u.id, COUNT(*) as orders_cnt
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

Почему? Потому что broadcast большой таблицы дорогой, а JOIN-оптимизация (hash join, sort-merge join) куда эффективнее.

Разница в производительности: детально

Для маленьких таблиц (< 10K rows)

# users: 100 rows
# orders: 1000 rows
# CROSS JOIN: 100 * 1000 = 100K rows временной таблицы

# Вариант 1: CROSS JOIN + WHERE
# Время: ~10-50ms (компьютер могёт вместить 100K строк в памяти)

# Вариант 2: JOIN ON
# Время: ~5-20ms (немного быстрее, но разница не критична)

Для больших таблиц (100M rows)

# users: 1 млн rows
# orders: 100 млн rows
# CROSS JOIN: 1M * 100M = 100 млрд rows!!! (невозможно вместить в памяти)

# Вариант 1: CROSS JOIN + WHERE
# Время: КРАХ (out of memory, или spill to disk → очень медленно, >10 минут)

# Вариант 2: JOIN ON
# Время: ~1-5 сек (эффективное использование памяти, индексов, оптимизатора)

Правило оптимизации

Золотое правило: Всегда перемещай условия соединения в ON, а не в WHERE.

-- ПРАВИЛЬНО
SELECT a.id, b.value
FROM a
JOIN b ON a.id = b.a_id  -- Условие соединения
WHERE a.status = 'active'  -- Фильтр после соединения
;

-- НЕПРАВИЛЬНО
SELECT a.id, b.value
FROM a
CROSS JOIN b
WHERE a.id = b.a_id AND a.status = 'active'
;

Когда CROSS JOIN имеет смысл?

CROSS JOIN полезен только в редких случаях:

1. Справочные таблицы (маленькие, < 100 rows)

-- Получить все комбинации (year, month)
SELECT y.year, m.month
FROM years y
CROSS JOIN months m
WHERE y.year >= 2020;

-- Вполне нормально, потому что:
-- years: 10 rows
-- months: 12 rows
-- CROSS JOIN: 120 rows (ничего страшного)

2. Декартово произведение намеренное

-- Генерируем все возможные комбинации
SELECT p.product_id, c.category_id
FROM products p
CROSS JOIN categories c
WHERE p.price > 100;  -- Фильтруем после

-- Вполне нормально, если tables маленькие

3. Работа с рядами (time series)

-- Заполнить все дни месяца для каждого пользователя
SELECT u.user_id, d.date_day
FROM users u
CROSS JOIN (SELECT DISTINCT DATE_TRUNC('day', timestamp) as date_day FROM events) d
WHERE u.created_at <= d.date_day;

Когда БД может быть умной?

Некоторые БД (PostgreSQL, MySQL 8+, ClickHouse) автоматически переписывают CROSS JOIN + WHERE на JOIN ON:

-- PostgreSQL оптимизатор может переписать это:
SELECT *
FROM users u
CROSS JOIN orders o
WHERE u.id = o.user_id
AND o.amount > 100;

-- На это (примерно):
SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

Но не всегда! Особенно с более сложными условиями. Полагаться на это опасно.

Best Practices для Data Engineer'а

1. Всегда используй JOIN ON для соединений с условиями

   -- ✅ Правильно
   SELECT * FROM a JOIN b ON a.id = b.a_id
   
   -- ❌ Неправильно
   SELECT * FROM a CROSS JOIN b WHERE a.id = b.a_id
   

2. CROSS JOIN только для маленьких справочных таблиц

   -- ✅ ОК для справочников
   SELECT * FROM large_table CROSS JOIN small_reference
   
   -- Но лучше всё равно явно:
   SELECT * FROM large_table JOIN small_reference ON 1=1
   

3. Проверяй EXPLAIN ANALYZE

   EXPLAIN ANALYZE
   SELECT * FROM users u
   JOIN orders o ON u.id = o.user_id;
   
   -- Ищи "Nested Loop", "Hash Join", "Sort Merge Join"
   -- Избегай "Seq Scan on orders" без индекса
   

4. При распределённых системах (ClickHouse, Spark) это критично

   • CROSS JOIN требует broadcast всей таблицы
   • JOIN ON использует более эффективные алгоритмы

Пример: реальная ошибка

-- Студент написал:
SELECT p.product_id, p.name, c.customer_id, c.email
FROM products p
CROSS JOIN customers c
WHERE p.product_id = c.last_viewed_product_id;

-- На таблицах:
-- products: 10,000
-- customers: 1,000,000
-- CROSS JOIN: 10 млрд строк в памяти!
-- Query timeout после 30 минут

-- Правильное решение:
SELECT p.product_id, p.name, c.customer_id, c.email
FROM products p
JOIN customers c ON p.product_id = c.last_viewed_product_id;

-- Выполняется за 100ms

Итог

Золотое правило: CROSS JOIN + WHERE — это анти-паттерн. Используй JOIN ON.