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

← назад к разделу

В таблице заказов нет имени клиента — только customer_id. Это сделано нарочно: имя хранится один раз в customer, заказы лишь ссылаются на него. Но отчёт «заказ — имя — сумма» требует данных из обеих таблиц сразу. Операция, которая склеивает таблицы по ссылке, называется JOIN — и это самый важный навык после SELECT.

INNER JOIN: только совпавшиеспросят на собеседовании

живой пример

SELECT orders.id, customer.first_name, orders.total_amount
FROM orders
JOIN customer ON orders.customer_id = customer.id;

Читается так: возьми заказы, к каждому подбери клиента, у которого customer.id равен orders.customer_id, и выдай строки парами. Условие после ON — сердце соединения: оно говорит, по каким столбцам таблицы связаны (почти всегда это внешний ключ и первичный ключ).

JOIN без уточнения — это INNER JOIN, «внутреннее» соединение: в результат попадают только строки, у которых нашлась пара. Заказ с несуществующим клиентом или клиент без заказов из результата молча выпадут.

В обеих таблицах есть id и created_at, и на голое id база ответит column reference "id" is ambiguous: колонку приходится писать с именем таблицы. А чтобы не писать его целиком, таблицам дают короткие алиасы:

живой пример

SELECT o.id, c.first_name, o.total_amount
FROM orders o
JOIN customer c ON o.customer_id = c.id
WHERE o.status = 'PAID';

Всё, что вы знаете про WHERE, ORDER BY и LIMIT, работает и здесь — фильтр применяется уже к склеенным строкам.

Отработать здесь: Состав заказа ord-02

Вопрос, который встаёт в незнакомой базе: а по каким колонкам соединять? Первый ответ даёт схема — внешние ключи видны в \d orders или в дереве клиента, и строка FOREIGN KEY (customer_id) REFERENCES customer(id) это готовое условие для ON. Но внешний ключ есть не всегда: в нашей песочнице его нет ни у order_items.order_id, ни у payments.order_id — связь существует только в головах разработчиков. Тогда работают три признака. Имя: суффикс _id и есть след связи, customer_id почти наверняка смотрит на customer.id. Форма значений: по обе стороны VARCHAR(36) со значениями вида ord-04. И проверка на данных:

живой пример

SELECT count(*) AS items, count(o.id) AS matched
FROM order_items i
LEFT JOIN orders o ON o.id = i.order_id;

Двадцать пять позиций и двадцать пять совпадений — колонки выбраны верно. Если matched меньше, чем items, либо соединяете не по тем колонкам, либо в данных есть сироты; и то и другое стоит выяснить до того, как строить на этом соединении отчёт.

LEFT JOIN: сохранить всех слеваспросят на собеседовании

А если нужен список всех клиентов, включая тех, кто ещё ничего не заказал? INNER JOIN их выбросит. Нужен LEFT JOIN — «сохрани все строки левой таблицы, даже без пары»:

живой пример

SELECT c.first_name, o.id, o.total_amount
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id;

В учебной базе таких двое: cus-07 Olga Fedorova и cus-08 Pavel Titov — аккаунт завели, а заказ пока ни одного. Они всё равно попадут в результат, а в столбцах заказа у них будет пусто — это и есть NULL: не ноль и не пустая строка, а отметка «значения тут нет». Про его повадки — отдельная статья цикла, пока достаточно понимать, что пустоту так обозначают. Этим же приёмом ищут «сирот»: клиентов без заказов —

живой пример

SELECT c.first_name FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Логика: LEFT JOIN оставил всех клиентов, а у тех, кому пары не нашлось, поля заказа — NULL; фильтр оставляет только их. Это классический запрос для проверок целостности данных.

Одна деталь, на которой LEFT JOIN ломается чаще всего, — где написано условие на правую таблицу. Сравните два запроса:

живой пример

SELECT c.first_name, o.id
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'PAID';

живой пример

SELECT c.first_name, o.id
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'PAID';

Первый отвечает на вопрос «все покупатели, и у каждого его оплаченные заказы, если они есть» — девять строк, все покупатели на месте, у кого нет оплаченных заказов, колонки заказа пустые. Второй отвечает «покупатели, у которых оплаченный заказ есть» — четыре строки. Причина: WHERE проверяется уже после соединения, а у непарных строк o.status равен NULL, условие для них не истинно, и они отсеиваются. LEFT JOIN тихо превратился в обычный.

Правило: условие на правую таблицу пишут в ON, условие на левую — в WHERE. Исключение ровно одно и намеренное — WHERE o.id IS NULL для поиска сирот, как в примере выше: там отсев непарных и есть цель.

«Левая» таблица — та, что написана до JOIN. Есть и RIGHT JOIN (сохраняет правую), но на практике все пишут LEFT, просто меняя таблицы местами. Есть и FULL OUTER JOIN — он оставляет непарные строки сразу с двух сторон; нужен редко, но знать имя полезно, чтобы узнать его в чужом запросе.

Отработать здесь: Заказы Анны и их платежи — включая неоплаченные · Сколько заказов у каждого покупателя

Три таблицы и соединение с самой собой

Два JOIN подряд это просто два JOIN: каждый следующий присоединяется к тому, что уже собрано. Покупатель, его заказы и позиции заказов:

живой пример

SELECT c.first_name, o.id AS order_id, i.product_id, i.quantity
FROM customer c
JOIN orders o ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
WHERE c.id = 'cus-01'
ORDER BY o.id;

Читают сверху вниз: взяли покупателя, к нему заказы, к заказам позиции. Правило про потери работает на каждом шаге: если у заказа нет позиций, JOIN его выбросит, LEFT JOIN оставит с NULL в колонках позиций. Псевдонимы c, o, i здесь не украшение: в трёх таблицах есть колонка id, и без префикса база ответит «column reference is ambiguous».

Отдельный случай, когда таблица соединяется сама с собой. В песочнице у заказа есть parent_order_id: заказ-замена ссылается на исходный. Чтобы показать замену рядом с оригиналом, таблицу orders берут дважды под разными именами:

живой пример

SELECT o.id AS replacement, p.id AS original, p.status AS original_status
FROM orders o
JOIN orders p ON p.id = o.parent_order_id;

Для базы это два разных источника, которые случайно оказались одной таблицей. Так же ищут сотрудника и его руководителя в одной таблице или сравнивают заказы одного покупателя между собой. Признак, что нужен self-join: колонка ссылается на первичный ключ своей же таблицы.

Две ловушки: потери и задвоениеспросят на собеседовании

Сделали INNER JOIN, и в отчёте меньше заказов, чем в таблице. Значит, у части заказов не нашлось пары, например customer_id пустой: INNER JOIN теряет непарные строки молча, и если терять нельзя, берите LEFT JOIN.

Вторая ловушка обратная: строк стало больше. JOIN выдаёт строку на каждую пару, и если у Anna семь заказов, она встретится в результате семь раз. Для «списка заказов с именами» это норма, а для подсчётов беда: возьмите после такого соединения сумму чего-нибудь со стороны клиента, скажем бонусный баланс, и у Anna он окажется всемеро больше настоящего, потому что её строка повторилась семь раз. Правило: увидели в результате больше строк, чем ожидали, проверьте, не соединяете ли «один-ко-многим».

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

Где это применяется

Реальные вопросы к базе почти всегда межтабличные: «покажи заказы клиента с этой почтой», «какие товары лежат в заказе 101», «есть ли платежи без заказа». Одна привычная связка: найти id по-человечески читаемого поля в одной таблице и через JOIN вытащить связанное из другой — заменяет десятки кликов по интерфейсу. А LEFT JOIN с IS NULL — главный инструмент поиска потерянных данных: заказы без позиций, оплаты без заказов, пользователи без профилей.

Где спотыкаются начинающие:

• Забывают условие ON или пишут его неверно. JOIN без правильного ON склеивает каждую строку с каждой — миллион строк из двух маленьких таблиц и бессмысленный результат. У этого склеивания есть имя — декартово произведение; когда его хотят нарочно, пишут CROSS JOIN, и тогда это не ошибка, а осознанный приём.
• Не замечают, что INNER JOIN выбросил строки. Отчёт «сходится», но в нём молча нет заказов с пустым клиентом. Сомневаетесь — начните с LEFT JOIN и посмотрите на NULL-строки.
• Считают суммы по задвоенным строкам. После соединения «один-ко-многим» агрегаты со стороны «одного» умножаются на число пар.

Коротко

• INNER JOIN оставляет только совпавшие пары, LEFT JOIN сохраняет все строки левой таблицы и ставит NULL там, где пары нет.
• Условие на правую таблицу в WHERE превращает LEFT JOIN обратно в INNER; фильтр правой таблицы пишут в ON.
• Связь «один ко многим» задваивает строки левой стороны: считать после JOIN можно только с DISTINCT или по подзапросу.
• Три таблицы это два JOIN подряд с псевдонимами; таблица соединяется сама с собой, когда колонка ссылается на её же ключ.
• По каким колонкам соединять, подсказывают внешний ключ в схеме, суффикс _id в имени и проверка count(*) против count(правая.id) после LEFT JOIN.
• Колонка соединения почти всегда должна быть проиндексирована: под внешний ключ PostgreSQL индекс не создаёт.

Что почитать дальше

• Агрегаты: COUNT, GROUP BY и HAVING — посчитать по собранным строкам.
• Подзапросы — когда соединение задваивает, а нужен факт «есть ли».
• Почему запрос медленный — индексы на колонках соединения.