Глава 04 / Базы данных

JOIN: связи и число строк результата

Борис хочет видеть историю выдач с именами участников и названиями книг. В loans есть только member_id и copy_id; название связано с экземпляром через book_id. Если сделать три отдельных запроса на каждую выдачу, приложение начнёт тратить много обменов с базой. Соединение позволяет описать весь вопрос в SQL, но правильные поля ещё не гарантируют правильное число строк.

Каждая совпавшая пара образует строку

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

SELECT l.id, m.name, b.title, c.inventory_code
FROM club.loans AS l
JOIN club.members AS m ON m.id = l.member_id
JOIN club.copies AS c ON c.id = l.copy_id
JOIN club.books AS b ON b.id = c.book_id
ORDER BY l.id;

Ожидается три строки: 1000,Анна,Практика Go,A-100; 1001,Борис,Запросы SQL,B-102; 1002,Анна,Практика Go,A-101. Алиасы сокращают имена; они не меняют факты. Явная цепочка объясняет, почему нельзя соединить loans.copy_id напрямую с books.id: это ключи разных объектов, даже если случайно какие-то числа совпадут.

Первичные и внешние ключи помогают понять кардинальность — число строк на стороне связи. Одна выдача ссылается на одного участника, один участник имеет ноль или несколько выдач. Для одной книги есть несколько экземпляров. Точная оценка зависит от данных и ограничений. Временное совпадение «у каждого по одной выдаче» не доказывает one-to-one связь.

Когда нужен LEFT JOIN

Вера ещё не брала книгу, но общий список участников должен включать её. INNER JOIN с loans исключил бы строку без совпадения. LEFT JOIN сохраняет все строки левой стороны и добавляет NULL вместо недостающих полей правой. Эти NULL возникают из отсутствия связанной строки, даже если реальная колонка правой таблицы объявлена NOT NULL.

SELECT m.id, m.name, l.id AS loan_id
FROM club.members AS m
LEFT JOIN club.loans AS l ON l.member_id = m.id
ORDER BY m.id, l.id;

Получаем четыре строки: Анна с 1000, Анна с 1002, Борис с 1001, Вера с NULL. Четыре строки не означают четырёх участников или четырёх выдач. Перед COUNT нужно решить, что именно считать. Присутствие Веры — дополнительная строка представления отсутствия связи.

Условие правой таблицы в WHERE способно убрать сохранённое отсутствие. Например, фильтр WHERE l.due_on < DATE '2026-09-18' отбрасывает строку Веры: сравнение NULL даёт неизвестность. Если требуется оставить всех участников, а справа показать только подходящие выдачи, условие отбора правой стороны размещают в ON.

SELECT m.id, l.id AS active_loan
FROM club.members AS m
LEFT JOIN club.loans AS l
  ON l.member_id=m.id AND l.returned_on IS NULL
ORDER BY m.id,l.id;

Результат: 1,1000; 1,1002; 2,NULL; 3,NULL. У Бориса история есть, но активной выдачи нет. Это различие важно: отсутствие совпадения зависит от конкретного вопроса, а не только от физического существования строки loans.

Почему два независимых множества умножают друг друга

Допустим, приложение одновременно присоединяет к участнику выдачи и уведомления. У Анны две выдачи и три уведомления. Соединение только по member_id создаёт шесть комбинаций, если между конкретной выдачей и уведомлением другой связи нет. Сумма выдач после этого JOIN может оказаться втрое больше. Добавление DISTINCT на весь результат не обязательно исправит смысл: уведомления различаются, поэтому строки остаются разными.

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

SELECT l.id, n.notice_id
FROM club.loans AS l
JOIN (VALUES (1,10),(1,11),(1,12)) AS n(member_id,notice_id)
  ON n.member_id=l.member_id
WHERE l.member_id=1
ORDER BY l.id,n.notice_id;

Ожидаются шесть строк: 1000 с 10,11,12 и 1002 с 10,11,12. VALUES задаёт строки только для этого запроса; он не сохраняет уведомления. Исправление зависит от задачи: агрегировать каждую сторону до соединения, связать по нужной выдаче или проверять существование без разворачивания всех пар.

EXISTS отвечает «есть ли», а не «сколько пар»

Чтобы получить участников хотя бы с одной активной выдачей, не нужно возвращать все их выдачи и затем удалять повторяющиеся имена. EXISTS проверяет наличие подходящей строки. NOT EXISTS позволяет найти отсутствие без дополнительных NULL-ловушек внешнего соединения.

SELECT m.id,m.name FROM club.members m
WHERE EXISTS (
  SELECT 1 FROM club.loans l
  WHERE l.member_id=m.id AND l.returned_on IS NULL
)
ORDER BY m.id;

Результат: только Анна. Если заменить EXISTS на NOT EXISTS, получим Бориса и Веру. Условие подзапроса связано с текущим m.id; это называют коррелированным подзапросом. SQL описывает смысл, а оптимизатор вправе исполнить его иначе. Выбор JOIN или EXISTS должен сначала сохранять ответ, затем сравнивать планы.

Самостоятельная лаборатория

Отведите 30 минут. Найдите все книги вместе с экземплярами, участников без какой-либо истории и участников без активных выдач. Перед запуском отдельно выпишите количество строк каждого результата. Затем добавьте виртуальные уведомления из VALUES к запросу Анны и объясните шесть комбинаций без фразы «база ошиблась».

Критерии: все ON связывают осмысленные ключи; название участника не служит идентификатором; отсутствие истории отличается от отсутствия активности; сохранённые LEFT JOIN строки не отфильтровываются случайным WHERE. Назовите единицу каждой строки и причину любого увеличения числа строк.

Разбор

Книги с экземплярами дают четыре строки; без истории найдена только Вера; без активных выдач — Борис и Вера. У Анны две выдачи, поэтому три уведомления умножают их до шести комбинаций. При отчёте по участникам сначала надо свернуть выдачи до одной строки участника либо использовать EXISTS для булевого вопроса. В следующем уроке проведём группировку и проверим, что она действительно считает нужные факты.

Первичные источники

PostgreSQL: Table Expressions, Tutorial: Joins, Berkeley CS186: SQL joins.