JOIN на пальцах

Соединение — это подбор пар

Соединение (JOIN) берёт строку левой таблицы, ищет в правой таблице все строки, для которых выполняется условие ON, и для каждой найденной пары выдаёт одну строку результата — склейку колонок обеих. Как на школьной дискотеке двух классов: к каждому ученику из 7А подбирают всех из 7Б, с кем он договорился танцевать. Нашлось двое — будет две пары; не нашлось никого — всё решает тип соединения.

Кругов Эйлера здесь не будет: они рисуют множества, а соединение работает со строками и парами. Удобнее смотреть на маленький кусок живых данных. Возьмём три категории из categories и их товары из products (связь products.category_id → categories.id, см. статью «Как читать схему shop»):

• Электроника (id = 1) — корневая категория, товаров напрямую в ней нет, все они лежат в подкатегориях;
• Книги (id = 2) — два товара: «Роман «Тихий Дон»» (id = 27) и «Сборник рассказов» (id = 28);
• Смартфоны (id = 4) — один товар: «Смартфон Nova X» (id = 1).

INNER JOIN: только строки с парой

SELECT c.id, c.name AS category, p.id AS product_id, p.name AS productFROM categories cJOIN products p ON p.category_id = c.idWHERE c.id IN (1, 2, 4)ORDER BY c.id, p.id

 id | category  | product_id |      product
----+-----------+------------+-------------------
  2 | Книги     |         27 | Роман «Тихий Дон»
  2 | Книги     |         28 | Сборник рассказов
  4 | Смартфоны |          1 | Смартфон Nova X
(3 rows)

Посмотри на две вещи. «Книги» встречаются дважды: у категории две пары, значит, две строки результата — JOIN не «добавляет колонки к строке», а размножает её по числу пар. «Электроники» нет вовсе: пары не нашлось, и JOIN (он же INNER JOIN) такую строку выбрасывает. Подробнее — карточка join.

LEFT JOIN: все строки левой таблицы

SELECT c.id, c.name AS category, p.id AS product_id, p.name AS productFROM categories cLEFT JOIN products p ON p.category_id = c.idWHERE c.id IN (1, 2, 4)ORDER BY c.id, p.id

 id |  category   | product_id |      product
----+-------------+------------+-------------------
  1 | Электроника |            |
  2 | Книги       |         27 | Роман «Тихий Дон»
  2 | Книги       |         28 | Сборник рассказов
  4 | Смартфоны   |          1 | Смартфон Nova X
(4 rows)

Запрос тот же, изменилось одно слово. LEFT JOIN сохраняет каждую строку левой таблицы (той, что в FROM): если пар нет, она попадает в результат один раз, а колонки правой таблицы заполняются NULL. «Электроника» вернулась с пустыми product_id и product. RIGHT JOIN — то же самое, только сохраняется правая таблица; FULL JOIN сохраняет обе (карточка left-join).

Anti-join: строки, у которых нет пары

Пустые колонки правой таблицы после LEFT JOIN — признак «пары не нашлось». Оставь только такие строки, и получится ответ на вопрос «в каких категориях нет ни одного товара»:

SELECT c.id, c.nameFROM categories cLEFT JOIN products p ON p.category_id = c.idWHERE p.id IS NULLORDER BY c.id

 id |    name
----+-------------
  1 | Электроника
(1 row)

Здесь проверены уже все 10 категорий, и без товаров оказалась только «Электроника». Проверяют колонку, которая в настоящей паре не бывает пустой, — обычно id правой таблицы. Проверять колонку, где NULL бывает и сам по себе, нельзя: такие строки ошибочно попадут в результат. Тот же вопрос решает NOT EXISTS (карточка exists) — выбирай, что тебе легче читать.

Для QA anti-join — основной инструмент поиска «сирот» и дыр в данных: категории без товаров, заказы без позиций, позиции со ссылкой на товар, которого нет в каталоге.

Условие на правую таблицу: в ON или в WHERE

Хотим все три категории и в них только товары дешевле 1000 рублей. Первое, что приходит в голову, — дописать условие в WHERE:

SELECT c.id, c.name AS category, p.name AS product, p.priceFROM categories cLEFT JOIN products p ON p.category_id = c.idWHERE c.id IN (1, 2, 4) AND p.price < 1000ORDER BY c.id, p.id

 id | category |      product      | price
----+----------+-------------------+--------
  2 | Книги    | Роман «Тихий Дон» | 741.98
  2 | Книги    | Сборник рассказов | 485.23
(2 rows)

Остались только «Книги». Порядок шагов: сначала FROM с LEFT JOIN собрал четыре строки из примера выше, потом WHERE проверил p.price < 1000. У «Электроники» p.price — NULL, сравнение дало «неизвестно», и строка отброшена; у смартфона цена больше 1000. LEFT JOIN фактически стал обычным JOIN. Если перенести условие в ON (LEFT JOIN products p ON p.category_id = c.id AND p.price < 1000), оно станет частью подбора пар: «Электроника» и «Смартфоны» останутся в результате с пустыми колонками товара, а «Книги» — с двумя дешёвыми книгами. Правило: условие на правую таблицу LEFT JOIN — в ON, условие на левую — в WHERE; исключение — проверка IS NULL для поиска строк без пары. Почему NULL так себя ведёт — статья «NULL и три значения истины».

Размножение строк

Каждое соединение «один ко многим» умножает строки. Два таких соединения подряд от одной таблицы умножают их друг на друга. Посчитаем для книг отзывы (reviews) и заказы (order_items) одним запросом:

SELECT p.id, p.name,       COUNT(r.id) AS review_rows,       COUNT(DISTINCT r.id) AS reviews,       COUNT(DISTINCT oi.order_id) AS ordersFROM products pLEFT JOIN reviews r ON r.product_id = p.idLEFT JOIN order_items oi ON oi.product_id = p.idWHERE p.category_id = 2GROUP BY p.id, p.nameORDER BY p.id

 id |       name        | review_rows | reviews | orders
----+-------------------+-------------+---------+--------
 27 | Роман «Тихий Дон» |          63 |       3 |     21
 28 | Сборник рассказов |          76 |       4 |     19
(2 rows)

У «Тихого Дона» 3 отзыва и 21 заказ, но после двух соединений каждый отзыв склеился с каждой позицией заказа: 3 × 21 = 63 строки. COUNT(r.id) посчитал эти 63 копии, а не отзывы. COUNT(DISTINCT …) спасает счётчики, но не суммы: SUM по размноженным строкам завысит результат, и DISTINCT тут не поможет — две разные покупки на одинаковую сумму он склеит в одну. Надёжнее посчитать каждую сторону отдельно (в подзапросе или WITH, карточки subquery и with) и соединять уже готовые итоги — по одной строке на товар.

Как проверить себя

• Прежде чем группировать, посчитай строки соединения: SELECT COUNT(*) с теми же FROM и JOIN. Если строк больше, чем ожидал, где-то размножение.
• Сколько строк должен вернуть LEFT JOIN? Не меньше, чем строк в левой таблице после WHERE по её колонкам. Меньше — значит, условие на правую таблицу попало в WHERE.
• Сомневаешься в ON — выведи обе колонки связи рядом (c.id, p.category_id) и посмотри на пары глазами. Ошибки в ON разобраны в карточке join.