Многотабличные JOIN и алиасы

Вы освоили все виды JOIN: INNER, LEFT, RIGHT, FULL OUTER, CROSS и SELF. В реальных запросах редко соединяют только две таблицы — обычно три, четыре и более. В этом уроке разберём, как правильно строить сложные многотабличные запросы, зачем нужны алиасы и как не запутаться в десятках колонок.

Синтаксис многотабличного JOIN

Каждый следующий JOIN добавляется к уже полученному результату:

SELECT a.col1, b.col2, c.col3
FROM   table_a a
JOIN   table_b b ON a.id = b.a_id
JOIN   table_c c ON b.id = c.b_id;

СУБД выполняет JOIN последовательно: сначала соединяет a и b, потом к результату добавляет c. Оптимизатор PostgreSQL может изменить порядок соединений для производительности, но результат всегда одинаковый.

Практический пример: интернет-магазин

Рассмотрим схему из четырёх таблиц:

customers(id, name, email, city)
orders(id, customer_id, created_at, status)
order_items(id, order_id, product_id, quantity, unit_price)
products(id, title, category, supplier_id)

Запрос: для каждого клиента — список заказов с деталями товаров:

SELECT c.name        AS customer,
       o.id          AS order_id,
       o.created_at,
       p.title       AS product,
       oi.quantity,
       oi.unit_price,
       oi.quantity * oi.unit_price AS line_total
FROM   customers   c
JOIN   orders      o  ON o.customer_id  = c.id
JOIN   order_items oi ON oi.order_id    = o.id
JOIN   products    p  ON oi.product_id  = p.id
WHERE  o.status = 'completed'
ORDER BY c.name, o.id, p.title;

Четыре таблицы, три JOIN, фильтрация и сортировка — стандартная аналитика для e-commerce.

Алиасы таблиц: зачем и как

Алиасы (псевдонимы) таблиц — не просто сокращение. Они несут три функции:

1. Краткость. order_items → oi, customers → c. Запрос на 30 строк без алиасов превращается в нечитаемую стену текста с полными именами таблиц.

2. Разграничение одинаковых имён колонок. Колонка id есть в каждой таблице. Без уточнения PostgreSQL выдаёт ошибку «column reference is ambiguous». С алиасами: c.id, o.id, oi.id, p.id.

3. SELF JOIN. Без алиасов соединить таблицу с собой невозможно синтаксически — нужно два разных имени для одного объекта.

Алиасы назначаются в FROM и JOIN и действуют во всём запросе:

FROM customers AS c   -- ключевое слово AS необязательно
-- или просто:
FROM customers c

Соглашения об алиасах

Нет единого стандарта, но распространённые подходы:

Первая буква: c для customers, o для orders. Просто, но конфликтует если две таблицы начинаются с одной буквы.

Аббревиатура: oi для order_items, ep для employee_projects. Читаемее при множестве таблиц.

Осмысленный псевдоним: emp для employees, mgr для второго экземпляра employees в SELF JOIN. Выразительнее, но длиннее.

Главное — последовательность. Если в проекте принято c для customers, используйте c везде.

Порядок JOIN: принципы

Технически порядок JOIN чаще всего не влияет на результат — оптимизатор сам переставляет. Но для читаемости полезно следовать логике:

Главная таблица первой. Таблица, вокруг которой строится запрос, идёт в FROM. Остальные добавляются как расширения.

Последовательная цепочка. Если A → B → C (каждая следующая зависит от предыдущей), пишите в этом порядке.

LEFT JOIN'ы в конце. Если одни таблицы обязательны (INNER), а другие опциональны (LEFT), сначала INNER, потом LEFT — это подчёркивает разницу.

FROM   orders      o
JOIN   customers   c  ON o.customer_id  = c.id    -- обязательно
JOIN   products    p  ON oi.product_id  = p.id    -- обязательно
LEFT JOIN coupons  cp ON o.coupon_id    = cp.id   -- опционально

Конфликт имён колонок в SELECT *

При SELECT * с несколькими таблицами PostgreSQL вернёт все колонки из всех таблиц. Если несколько таблиц имеют колонку с одинаковым именем — они обе появятся в результате, что запутывает:

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
-- Результат содержит: o.id, o.customer_id, ..., c.id, c.name, ...
-- Два поля с именем "id" — путаница при чтении результата

В production-коде избегайте SELECT * с JOIN. Явно перечисляйте нужные колонки с алиасами:

SELECT o.id AS order_id, c.id AS customer_id, c.name, o.amount
FROM orders o JOIN customers c ON o.customer_id = c.id;

Проверь себя: почему при четырёх таблицах с JOIN и SELECT * можно получить до 4 колонок с именем id?

Многотабличный запрос с агрегатами

Агрегаты с несколькими таблицами — мощный инструмент:

SELECT c.name,
       c.city,
       COUNT(DISTINCT o.id)       AS orders_count,
       SUM(oi.quantity)           AS units_bought,
       SUM(oi.quantity * oi.unit_price) AS total_spent
FROM   customers   c
LEFT JOIN orders      o  ON o.customer_id  = c.id
LEFT JOIN order_items oi ON oi.order_id    = o.id
GROUP BY c.id, c.name, c.city
ORDER BY total_spent DESC NULLS LAST;

COUNT(DISTINCT o.id) — количество уникальных заказов (без DISTINCT строки из order_items дублировали бы подсчёт). LEFT JOIN — чтобы клиенты без заказов тоже попали в отчёт с нулями.

Смешивание типов JOIN

В одном запросе можно смешивать разные виды JOIN. Это нормально и часто нужно. Важно понимать, что происходит:

SELECT c.name,
       o.id         AS order_id,
       cp.code      AS coupon_code,
       p.title      AS product
FROM   customers   c
JOIN   orders      o  ON o.customer_id = c.id      -- INNER: только клиенты с заказами
LEFT JOIN coupons  cp ON o.coupon_id   = cp.id     -- LEFT: заказы без купонов тоже включены
JOIN   order_items oi ON oi.order_id   = o.id      -- INNER: только заказы с позициями
JOIN   products    p  ON oi.product_id = p.id;     -- INNER: только позиции с товарами

Здесь INNER JOIN для основного пути данных (клиент → заказ → позиция → товар), и LEFT JOIN для опционального атрибута (купон). Заказы без купонов появятся в результате с NULL в coupon_code.

Чтение чужих многотабличных запросов

Когда вы видите незнакомый сложный запрос, читайте его по шагам:

FROM — с чего начинаем (главная сущность)
Каждый JOIN — что добавляем и как связываем
WHERE — какие строки фильтруем
GROUP BY — по каким измерениям агрегируем
SELECT — что берём из всего получившегося

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

Типичные ошибки в многотабличных запросах

1. Неверное условие ON — соединение не по тем колонкам:

-- Ошибка: перепутаны колонки
JOIN order_items oi ON oi.order_id = c.id   -- нужно o.id, не c.id

Результат — либо пустой, либо декартово произведение.

2. Забытый алиас вызывает двусмысленность:

SELECT id, name FROM customers c JOIN orders o ON c.id = o.customer_id;
-- ERROR: column reference "id" is ambiguous

3. Неправильный тип JOIN для опциональных связей:

-- INNER JOIN отфильтрует заказы без купонов
JOIN coupons cp ON o.coupon_id = cp.id   -- нужен LEFT JOIN

4. GROUP BY без всех не-агрегированных колонок:

SELECT c.name, c.city, COUNT(*) FROM customers c JOIN orders o ON ...
GROUP BY c.name;  -- ошибка: c.city не в GROUP BY

Производительность многотабличных JOIN

Каждый JOIN добавляет нагрузку на СУБД: нужно найти совпадения между всё большим набором строк. PostgreSQL автоматически выбирает оптимальный план (nested loop, hash join, merge join) в зависимости от размеров таблиц и наличия индексов.

Для разработчика важны два правила:

Соединяйте по индексированным колонкам. Первичные ключи всегда индексированы. Внешние ключи — часто нет, но добавить индекс на FK-колонку обычно ускоряет JOIN в разы.
Фильтруйте до JOIN: WHERE на таблице с большим количеством строк лучше добавить перед JOIN, чтобы СУБД работала с меньшим набором данных.

На небольших таблицах (тысячи строк) JOIN работает мгновенно. На миллионах строк — нужны индексы. Детали оптимизации — в модуле 10.

Краткий итог

Каждый следующий JOIN добавляется к уже соединённому результату
Алиасы необходимы: краткость, разграничение одинаковых имён, SELF JOIN
Конвенция алиасов: первая буква или осмысленная аббревиатура; главное — последовательность
В SELECT * с JOIN — риск дублирующихся имён колонок; предпочитайте явный список
COUNT(DISTINCT o.id) при LEFT JOIN предотвращает двойной подсчёт из-за строк дочерней таблицы

Что дальше

Вы завершили модуль 6 — все виды JOIN. Следующий модуль — подзапросы и UNION. Вы научитесь вкладывать один запрос внутрь другого и объединять результаты нескольких запросов в один набор данных.

Многотабличные JOIN и алиасы