SQL CROSS JOIN: все комбинации строк

Урок 5.6 · Время чтения: ~5 мин · Задачи для практики ↓

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

Все соединения до сих пор связывали строки по условию. У CROSS JOIN условия нет: каждая строка первой таблицы встречается с каждой строкой второй. Для связанных данных это редко нужно, но именно так получают все комбинации: каждый магазин с каждой категорией, каждый товар с каждым размером, каждый день с каждым сотрудником.

Что возвращает CROSS JOIN

Если в первой таблице m строк, а во второй n, в результате будет m × n строк. Предложения ON нет.

Базовый синтаксис

SELECT table1.column1, table2.column2
FROM table1
CROSS JOIN table2;

Старый синтаксис через запятую, FROM table1, table2 без условия, означает то же самое. CROSS JOIN явно выражает намерение, поэтому предпочтительнее.

Пример: каждый магазин с каждой категорией

SELECT s.store_id, c.name AS category
FROM store AS s
CROSS JOIN category AS c
ORDER BY s.store_id, c.name;

Результат: 32 строки - 2 магазина × 16 категорий, каждый магазин с каждой категорией.

Построение сетки отчёта

Самое практичное применение CROSS JOIN - «скелет» отчёта. Допустим, нужно количество фильмов по магазинам и категориям, включая комбинации с нулём фильмов. Обычное соединение с GROUP BY вернёт только комбинации, которые есть в данных. CROSS JOIN сначала создаёт все комбинации, а LEFT JOIN затем добавляет к ним данные:

SELECT s.store_id,
       c.name AS category,
       COUNT(i.inventory_id) AS copies
FROM store AS s
CROSS JOIN category AS c
LEFT JOIN film_category AS fc ON fc.category_id = c.category_id
LEFT JOIN inventory AS i ON i.film_id = fc.film_id
                        AND i.store_id = s.store_id
GROUP BY s.store_id, c.name
ORDER BY s.store_id, c.name;

Результат: 32 строки с количеством копий фильмов каждой категории в каждом магазине. Комбинация без копий показала бы 0, а не исчезла бы.

COUNT(i.inventory_id) считает только найденные копии, поэтому комбинация без копий получает 0. Тот же приём заполняет пропуски во временных рядах: перекрёстное соединение списка дат со списком товаров даёт строку для каждого товара на каждый день.

Случайное декартово произведение

Декартово произведение часто возникает по ошибке - когда забыто условие соединения:

-- Нет условия соединения: каждый актёр сочетается с каждым фильмом
SELECT COUNT(*) AS row_count
FROM actor, film;

Результат: 200 000 строк - 200 актёров × 1000 фильмов.

Если запрос вдруг возвращает слишком много строк или строки повторяются в странных сочетаниях, проверьте, что у каждой присоединённой таблицы есть условие соединения. Две таблицы по 100 000 строк дают 10 миллиардов комбинаций, и это может остановить базу данных.

Ключевые выводы:

• CROSS JOIN сочетает каждую строку одной таблицы с каждой строкой другой.
• В результате m × n строк, условия ON нет.
• FROM a, b без условия - тоже декартово произведение, часто случайное.
• CROSS JOIN вместе с LEFT JOIN строит полную сетку отчёта с нулями для отсутствующих комбинаций.
• Оценивайте размер результата, прежде чем перекрёстно соединять большие таблицы.

Часто задаваемые вопросы

Что такое декартово произведение в SQL?

Множество всех комбинаций строк двух таблиц. Если в одной таблице 2 строки, а в другой 16, декартово произведение содержит 32 строки. CROSS JOIN возвращает его явно.

Чем отличаются CROSS JOIN и INNER JOIN?

INNER JOIN оставляет только комбинации, удовлетворяющие условию ON. CROSS JOIN оставляет все комбинации. INNER JOIN с условием, которое всегда истинно, ведёт себя как CROSS JOIN.

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

Когда нужны все комбинации: сетки отчётов, варианты товаров (цвета × размеры), календари (даты × сотрудники) или заполнение пропусков во временных рядах вместе с LEFT JOIN.