В прошлом уроке ты вернул в отчёт Карину — но «клиент и число заказов» это сводка. Стоит поддержке открыть конкретный заказ, чтобы ответить клиенту, и сводки мало: нужен чек — что заказали, по какой цене, на чьё имя. А сырая строка orders человеку бесполезна: customer_id лежит в ней строкой-идентификатором, а что внутри заказа — в orders вообще нет. Номер заказа в orders, имя клиента в customers, позиции в order_items, названия напитков в drinks. Чтобы напечатать «заказ #1, Алиса, капучино ×1», надо собрать строки из всех четырёх таблиц — одним запросом, а не четырьмя походами в базу.

И отдельный, поначалу неинтуитивный приём: таблицу можно соединить саму с собой. Звучит странно, пока не встретишь иерархию: у бариста есть менеджер, а менеджер — такой же сотрудник из той же таблицы staff. Чтобы рядом с именем сотрудника показать имя его руководителя, таблицу staff надо подключить дважды — это и есть self-join.

JOIN тянется через сколько угодно таблиц

JOIN не ограничен двумя таблицами: каждый следующий JOIN присоединяет ещё одну через свой ключ. Цепочка orders → customers → order_items → drinks связывает заказ с клиентом (c.id::text = o.customer_id), заказ с его позициями (oi.order_id = o.id) и позицию с напитком (d.id = oi.drink_id). Порядок JOIN на результат INNER-цепочки не влияет (планировщик сам выберет, как их соединять), но читать удобнее «по нити»: от заказа к его деталям.

orders тут — ствол: к нему по customer_id подвешивается клиент, по order_id — позиции, а к каждой позиции по drink_id — её напиток.

orders ──┬─▶ customers      c.id::text = o.customer_id   → имя клиента
(заказ)  │
         └─▶ order_items     oi.order_id = o.id           → что заказали
                  └─▶ drinks  d.id = oi.drink_id           → название напитка

Один заказ даёт столько строк чека, сколько в нём позиций: поэтому заказ #1 с двумя позициями развернётся в две строки.

Сумму строки (количество × цена) считаем прямо в SQL: oi.quantity * oi.unit_price. Цена — BIGINT (центы, см. 01-01), количество — INT; произведение приводим к ::bigint, чтобы и в Go это был int64 (без приведения sqlc вывел бы тип по первому операнду — int32, и на больших суммах это могло бы переполниться).

Self-join: одна таблица под двумя псевдонимами

Self-join — это обычный JOIN, где обе стороны — одна и та же таблица, но под разными псевдонимами. Псевдонимы обязательны: без них SELECT name FROM staff JOIN staff неоднозначен — непонятно, чьё name. Даём staff e («сотрудник») и staff m («руководитель») и связываем их по ссылке внутри строки:

FROM staff e
LEFT JOIN staff m ON m.id = e.manager_id

e.manager_id = m.id «разворачивает» ссылку: для строки сотрудника находим строку его руководителя в той же таблице и берём оттуда имя. Та же таблица staff читается дважды — слева как «сотрудники», справа как «руководители»:

одна таблица staff, прочитанная под двумя псевдонимами:
 
  сотрудник (e)              руководитель (m)
  e.name   e.manager_id  ─▶  m.id   m.name
  Борис         1             1      Анна
  Вера          1             1      Анна
  Глеб          1             1      Анна
  Анна        NULL            —      нет ссылки → LEFT JOIN даёт NULL

LEFT JOIN здесь важен: у самого старшего (Анна) manager_id — NULL, и INNER-вариант её бы выкинул, а LEFT оставляет с пустым руководителем.

Сотрудник Борис в этой иерархии — однофамилец клиента Бориса Петрова из справочника customers: это разные люди, один варит кофе, другой его покупает. Имена в Brew повторяются, как в жизни, — различает их таблица, в которой строка живёт.

Это разворот ровно на один уровень — «сотрудник и его прямой начальник». Чтобы пройти иерархию на любую глубину (начальник начальника и так далее), self-join не хватит — нужен рекурсивный CTE (юнит 08-04).

Что показывает наш код

Два запроса в query.sql. Многотабличный чек:

-- name: OrderReceipt :many
SELECT o.id AS order_id, c.name AS customer, d.name AS drink,
       oi.quantity, oi.unit_price, (oi.quantity * oi.unit_price)::bigint AS line_total
FROM orders o
JOIN customers c    ON c.id::text = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN drinks d       ON d.id = oi.drink_id
ORDER BY o.id, oi.id;

И self-join иерархии (StaffWithManager, см. выше). В main.go обе выборки тонкие: чек печатаем построчно и складываем line_total в общий итог; иерархию — «сотрудник → руководитель», подставляя «— (старший)» там, где руководителя нет.

Запуск

Подними песочницу (из корня репозитория) и накати схему Brew:

docker compose up -d
make lecture L=04-querying-across-tables/04-02-multi-table-and-self-joins T=db-reset
make lecture L=04-querying-across-tables/04-02-multi-table-and-self-joins

(T=run — значение по умолчанию. Изнутри каталога юнита это make db-reset, make run.)

Вывод:

1) Чек заказа — JOIN по 4 таблицам (orders→customers→order_items→drinks):
   заказ клиент           напиток      кол     цена    сумма
   #1    Алиса Иванова    Капучино       1     4.50     4.50
   #1    Алиса Иванова    Колд брю       1     5.20     5.20
   #2    Борис Петров     Эспрессо       1     3.00     3.00
   #3    Алиса Иванова    Латте          2     4.80     9.60
   итого по всем позициям: 22.30
 
2) Иерархия персонала — self-join staff (e=сотрудник, m=руководитель):
   сотрудник роль         руководитель
   Анна     manager      — (старший)
   Борис    barista      Анна
   Вера     barista      Анна
   Глеб     shift-lead   Анна
   → у Анны руководителя нет (manager = NULL) — LEFT JOIN её не выкинул.

Прочитаем вывод. Чек развернул заказы в строки-позиции: заказ #1 дал две строки (капучино 4.50 и колд брю 5.20), заказ #3 — латте 4.80 ×2 = 9.60. JOIN к drinks подставил названия напитков вместо drink_id, JOIN к customers — имя вместо строкового customer_id; итог 22.30 — сумма всех line_total. Второй запрос соединил staff саму с собой: у Бориса, Веры и Глеба в колонке руководителя стоит Анна, а у самой Анны — «— (старший)», потому что её manager_id пуст и LEFT JOIN подставил NULL, не выкинув строку.

Заборчик

Что мы упростили.

• Многотабличный JOIN идёт по нескольким ключам сразу, и каждый на больших данных хочет индекс — иначе планировщик соединяет таблицы перебором. Под FK order_items.order_id индекс в базовой схеме уже есть (order_items_order_id_idx); а c.id::text = o.customer_id — это JOIN по выражению, и обычный индекс по customer_id его не ускорит (как индексировать выражение и как сервер выбирает способ соединения — модуль 06).
• Цепочку из четырёх INNER JOIN мы держим короткой намеренно. В проде такие отчёты быстро отрастают в десяток таблиц, и тогда «в каком порядке и каким способом соединять» решает уже не читаемость, а план запроса.
• Self-join разворачивает иерархию ровно на один уровень. Для произвольной глубины нужен рекурсивный CTE (08-04); складывать N self-join'ов «на всякий случай» — антипаттерн.

Что забрать с собой

• JOIN не ограничен двумя таблицами: цепочка JOIN'ов связывает сколько угодно таблиц, каждый — по своему ключу.
• Считать производные значения (количество × цена) можно прямо в SELECT; приведение типа (::bigint) убирает сюрпризы с шириной результата.
• Self-join — это JOIN таблицы с собой под разными псевдонимами; псевдонимы обязательны, иначе колонки неоднозначны.
• Иерархию «сотрудник → руководитель» self-join разворачивает на один уровень; LEFT JOIN сохраняет вершину (того, у кого руководителя нет).
• Произвольная глубина иерархии — это уже рекурсивный CTE (08-04), а не стопка self-join'ов.

Чек развернул заказ в отдельные строки — по позиции на напиток. Но бизнес чаще спрашивает обратное: не «что в каждом заказе», а «сколько у клиента заказов», «на какую сумму», «сколько в среднем». Это значит схлопнуть строки в сводку — и тут поджидает ловушка: count(*) и count(колонка) считают разное, и на LEFT JOIN разница тихо искажает отчёт (та самая Карина без заказов снова окажется на грани). Этим займётся 04-03 «Агрегация, GROUP BY/HAVING».