JOIN: как соединять строки разных таблиц

Данные разделены по таблицам, поэтому для ответа часто нужно собрать их снова. Например, заказ хранит user_id, а почта пользователя находится в app_users. Операция JOIN соединяет строки по указанному условию.

Перед запросом проговорите смысл одной строки каждой таблицы и количество возможных совпадений. Это важнее знания синтаксиса.

#Обычное внутреннее соединение

SELECT o.id, u.email
FROM orders AS o
JOIN app_users AS u
  ON u.id = o.user_id
 AND u.org_id = o.org_id;

JOIN без уточнения означает INNER JOIN, или внутреннее соединение. В результате остаются только пары строк, для которых условие после ON истинно. Заказ без подходящего пользователя исчезнет из результата.

Условие проверяет и пользователя, и организацию. Так запрос не смешает данные разных клиентов, даже если одного user_id недостаточно для однозначной связи.

#LEFT JOIN сохраняет левую сторону

Иногда нужны все пользователи, включая тех, у кого нет платежа. Тогда подходит левое внешнее соединение:

SELECT u.id, p.status
FROM app_users AS u
LEFT JOIN payments AS p
  ON p.user_id = u.id
 AND p.status = 'captured';

LEFT JOIN сохраняет каждую строку таблицы слева. Если платёж не найден, столбцы p получают NULL.

Условие о статусе намеренно находится в ON. Если перенести p.status = 'captured' в WHERE, строки без платежа получат UNKNOWN и исчезнут. По этому условию запрос станет работать как внутреннее соединение.

#Почему строки неожиданно размножаются

У одного заказа может быть несколько позиций и несколько платежей. Если сразу присоединить обе таблицы, каждая позиция соединится с каждым платежом.

Например, 3 позиции и 2 платежа дадут 6 строк. Такое размножение называют раздуванием соединения (join fan-out). Сумма позиций в следующем запросе будет завышена:

SELECT o.id, sum(oi.quantity * oi.unit_price)
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
JOIN payments AS p ON p.order_id = o.id
GROUP BY o.id;

Сначала сведём каждую связь «один ко многим» к одной строке на заказ, затем соединим результаты:

WITH item_totals AS (
    SELECT order_id,
           sum(quantity * unit_price) AS item_total
    FROM order_items
    GROUP BY order_id
), payment_totals AS (
    SELECT order_id,
           sum(amount) FILTER (WHERE status = 'captured') AS captured_total
    FROM payments
    GROUP BY order_id
)
SELECT o.id, i.item_total, p.captured_total
FROM orders AS o
LEFT JOIN item_totals AS i ON i.order_id = o.id
LEFT JOIN payment_totals AS p ON p.order_id = o.id;

WITH создаёт именованные промежуточные результаты. В каждом из них уже одна строка на заказ. DISTINCT после ошибочного соединения не спасает вычисление: размноженные значения к этому моменту уже попали в sum.

#Нужен факт наличия, а не все пары

Если нужны заказы, у которых существует оплаченный платёж, не обязательно соединять и возвращать сами платежи. EXISTS проверяет наличие хотя бы одной подходящей строки:

SELECT o.id
FROM orders AS o
WHERE EXISTS (
    SELECT 1
    FROM payments AS p
    WHERE p.order_id = o.id
      AND p.status = 'captured'
);

Такую операцию называют полусоединением (semi join): возвращаются только строки левой стороны, не по одной копии на каждое совпадение.

Обратная задача — найти пользователей без заказов:

SELECT u.id, u.email
FROM app_users AS u
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o WHERE o.user_id = u.id
);

Это антисоединение (anti join). NOT EXISTS надёжнее NOT IN, когда в подзапросе возможен NULL.

#Последняя связанная строка через LATERAL

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

SELECT o.id, latest.status, latest.created_at
FROM orders AS o
LEFT JOIN LATERAL (
    SELECT p.status, p.created_at
    FROM payments AS p
    WHERE p.order_id = o.id
    ORDER BY p.created_at DESC, p.id DESC
    LIMIT 1
) AS latest ON true;

Для очередного заказа внутренний запрос получает его o.id. LEFT JOIN сохраняет заказы без платежей. Второй столбец сортировки p.id однозначно выбирает строку при одинаковом времени.

Подходящий индекс (order_id, created_at DESC, id DESC) позволяет быстро найти первый платёж каждого заказа. Но подзапрос может выполняться много раз, поэтому реальную стоимость проверяют через EXPLAIN и число loops.

#Соединение таблицы с самой собой

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

SELECT child.name, parent.name AS parent_name
FROM categories AS child
LEFT JOIN categories AS parent ON parent.id = child.parent_id;

Это самосоединение (self join). Оно показывает один уровень. Для дерева произвольной глубины понадобится рекурсивный запрос из следующего урока.

#Как сервер выполняет соединение

SQL описывает результат, но не закрепляет алгоритм. Планировщик может выбрать:

вложенный цикл (Nested Loop): для каждой строки слева ищет строки справа;
хеш-соединение (Hash Join): строит в памяти таблицу по ключу равенства;
соединение слиянием (Merge Join): идёт по двум совместимо упорядоченным наборам.

Ни один алгоритм не плох сам по себе. Выбор зависит от числа строк, индексов и доступной памяти. Сначала сравнивают ожидаемое и фактическое количество строк в плане, а не пытаются запретить конкретный вид соединения.

#Практика

В лаборатории 09 вы соберёте одну строку на заказ: заранее посчитаете сумму позиций и через LATERAL выберете последний платёж. Проверка обнаружит размножение строк и потерю заказа без платежа.

Подробнее: табличные выражения, выражения с подзапросами.