SELF JOIN и CROSS JOIN

Мира · маркетолог магазина настолок «Кубик»

Хочу собрать готовые наборы «игра плюс дополнение» к Новому году. Что у нас покупают вместе? И ещё: директор просит отчёт по возвратам, где каждый месяц и каждый жанр. Пустые клетки он не любит.

До сих пор мы соединяли разные таблицы: заказы с клиентами, позиции с играми. Сегодня два необычных соединения. В первом таблица встречается сама с собой. Во втором соединяются таблицы, у которых вообще нет общего ключа. Звучит странно, но на них держатся оба вопроса Миры.

Таблица в зеркале

В каталоге «Кубика» есть дополнения: «Крепость Подземелья: Новые земли» не играется без самой «Крепости Подземелья». Связь хранится в той же таблице products: у дополнения заполнен столбец base_id, и в нём лежит id базовой игры.

Представь, что каталог распечатан дважды. На левой распечатке ты ищешь дополнение, смотришь его base_id, а потом на правой распечатке находишь строку с таким id. Две распечатки одной таблицы и есть SELF JOIN. Отдельного слова в SQL для него нет: это обычный JOIN, у которого справа и слева одна и та же таблица. Различать копии помогают псевдонимы.

SELECT e.title AS expansion, b.title AS base, e.price, b.price
FROM products e
JOIN products b ON b.id = e.base_id
LIMIT 4;

 expansion                         | base                | price   | price
-----------------------------------+---------------------+---------+---------
 Крепость Подземелья: Новые земли  | Крепость Подземелья | 1970.00 | 4960.00
 Хроники Ледника: Пятый игрок      | Хроники Ледника     | 1110.00 | 2440.00
 Купцы Пустыни: Тёмные времена     | Купцы Пустыни       |  350.00 |  630.00
 Острова Империи: Пятый игрок      | Острова Империи     |  970.00 | 2220.00

e это копия «дополнения», b копия «базы». Без псевдонимов база не поймёт, о каком title речь. Игры без base_id отпали сами: внутреннее соединение отбрасывает строки без пары, как ты видел в уроке про INNER JOIN. Всего дополнений в каталоге 14.

Что лежит в одной корзине

Второй SELF JOIN ищет игры, купленные вместе. Соединим order_items саму с собой по номеру заказа, и рядом встанут две позиции одного заказа.

SELECT a.title AS game_a, b.title AS game_b, count(*) AS orders
FROM order_items x
JOIN order_items y ON y.order_id = x.order_id AND x.product_id < y.product_id
JOIN orders o ON o.id = x.order_id
JOIN products a ON a.id = x.product_id
JOIN products b ON b.id = y.product_id
WHERE o.status <> 'cancelled'
GROUP BY a.title, b.title
ORDER BY orders DESC
LIMIT 3;

 game_a              | game_b                           | orders
---------------------+----------------------------------+--------
 Крепость Подземелья | Крепость Подземелья: Новые земли |    120
 Купцы Пустыни       | Купцы Пустыни: Тёмные времена    |    100
 Острова Ледника     | Острова Ледника: Ярмарка         |    100

Посмотри на условие x.product_id < y.product_id. Без него в заказе из игр A и B получилось бы четыре сочетания: A с A, A с B, B с A и B с B. Пары с самой собой нам не нужны, а «A с B» и «B с A» это одна и та же пара. Знак «меньше» оставляет ровно одно сочетание: то, где слева игра с меньшим id.

Все семь пар, которые встретились в 60 и больше заказах, это игра и её дополнение. Наборы для Миры подсказывают сами покупатели.