Многотабличные запросы: операция JOIN

JOIN связывает строки по условию и создаёт единый табличный результат. Чтобы запрос не превратился в случайное произведение таблиц, сначала рисуют путь по внешним ключам, затем добавляют фильтры.

Учебная схема

customer(id, name)
purchase(id, customer_id, status, created_at)
purchase_item(purchase_id, product_id, quantity, price)
product(id, name, category_id)
category(id, name)

Получим оплаченные позиции с покупателем и товаром:

SELECT
    p.id AS purchase_id,
    c.name AS customer_name,
    pr.name AS product_name,
    i.quantity,
    i.price * i.quantity AS line_total
FROM purchase AS p
JOIN customer AS c
    ON c.id = p.customer_id
JOIN purchase_item AS i
    ON i.purchase_id = p.id
JOIN product AS pr
    ON pr.id = i.product_id
WHERE p.status = 'PAID'
ORDER BY p.id, pr.name;

Каждый join имеет собственное условие связи. Удаление одного ON или связь по неуникальному полю может кратно увеличить результат.

Логический порядок обработки

Упрощённо SQL рассматривается так:

1. FROM и JOIN ... ON;
2. WHERE;
3. GROUP BY;
4. HAVING;
5. SELECT;
6. DISTINCT;
7. ORDER BY;
8. LIMIT/OFFSET.

Физический план может выполнять операции иначе, если результат эквивалентен. Логический порядок объясняет, почему alias из SELECT обычно нельзя использовать в WHERE.

Агрегация после join

SELECT
    c.id,
    c.name,
    COALESCE(SUM(i.quantity * i.price), 0) AS paid_total
FROM customer c
LEFT JOIN purchase p
  ON p.customer_id = c.id
 AND p.status = 'PAID'
LEFT JOIN purchase_item i
  ON i.purchase_id = p.id
GROUP BY c.id, c.name;

Условие статуса находится в ON, поэтому покупатели без оплаченных покупок сохраняются. COUNT(*) здесь вернул бы минимум один для строки покупателя; для числа позиций нужен COUNT(i.product_id).

Несколько независимых one-to-many

Если одновременно присоединить к покупателю покупки и адреса, каждая покупка скомбинируется с каждым адресом. Агрегаты будут завышены. Варианты:

• сначала агрегировать каждую дочернюю таблицу в CTE/подзапросе;
• выполнить независимые запросы;
• использовать correlated subquery для одного показателя;
• точно понимать требуемую гранулярность результата.

DISTINCT не заменяет правильную модель: он удаляет одинаковые итоговые строки, а не объясняет, почему они появились.

Производительность

Индексы полезны на внешних ключах и селективных фильтрах, но решение принимает оптимизатор. Проверяют план выполнения, статистику, число строк на каждом узле и фактические данные. См. SQL оценка производительности и Индексы SQL.

На собеседовании

Что произойдёт без условия join? Получится Cartesian product: каждая строка одной стороны соединится с каждой строкой другой.

Почему сумма после join завышена? До агрегации строки могли размножиться из-за другой связи one-to-many.

Связанные заметки: INNER JOIN, OUTER JOIN, Подзапросы, Что лучше подзапросы или JOIN.