Соединения и деление

Соединение — главная рабочая операция реляционной алгебры. Разберём его виды и самую загадочную операцию — деление.

Соединение R ⋈_{условие} S — это декартово произведение R × S, к которому применили выборку по условию соединения. Соединение связывает кортежи разных отношений по совпадению значений.

Зачем нужно соединение

Нормализованная база разносит данные по разным таблицам: заказы отдельно, клиенты отдельно, товары отдельно. Чтобы собрать осмысленный ответ («какой клиент сделал этот заказ»), их нужно снова свести вместе по ключам. Это и делает соединение. Без него реляционная модель была бы бесполезна: данные разбиты на таблицы именно потому, что есть чем их снова связать.

Почему соединение — центральная операция

Из всех операций алгебры соединение заслуживает особого внимания, потому что именно оно делает реляционную модель работоспособной. Логика проектирования такова: чтобы избежать избыточности, мы разносим данные о разных сущностях по разным таблицам (клиенты отдельно, заказы отдельно). Но пользователю нужен целостный ответ — «заказ Анны на 1500 рублей», где данные из обеих таблиц. Соединение и есть мост, восстанавливающий целое из частей. Без него нормализованная база была бы набором несвязанных островов. Поэтому соединение — самая частая операция в реальных запросах и одновременно самая дорогая: именно его стоимость чаще всего определяет скорость запроса, и именно ради него строят индексы по ключам. Эту экономику мы подробно разберём в разделе про оптимизацию, а пока освоим виды соединений.

Тета-соединение и эквисоединение

Самое общее — тета-соединение R ⋈_θ S: произведение, отфильтрованное произвольным предикатом θ (это может быть <, >, =). Когда условие — это равенство атрибутов (R.a = S.b), соединение называют эквисоединением. Именно эквисоединение по ключам встречается чаще всего. Строго оно определяется так: σ_{R.client_id = S.id}(R × S).

CREATE TABLE orders (id INTEGER, client_id INTEGER, summa INTEGER);
CREATE TABLE clients (id INTEGER, name TEXT);
INSERT INTO clients VALUES (1,'Анна'),(2,'Борис');
INSERT INTO orders VALUES (10,1,1500),(11,2,2300),(12,1,800);

-- эквисоединение: orders ⋈(orders.client_id = clients.id) clients
SELECT o.id AS zakaz, c.name, o.summa
FROM orders o JOIN clients c ON o.client_id = c.id;

Вывод: три строки — заказ 10 (Анна, 1500), заказ 11 (Борис, 2300), заказ 12 (Анна, 800). Каждый заказ сопоставлен своему клиенту.

Соединение как восстановление целого

Полезно увидеть соединение под другим углом. В разделе про нормализацию мы будем разбивать одну широкую таблицу на несколько узких, чтобы убрать избыточность. Соединение — обратная операция: оно собирает данные обратно. Эти две операции — декомпозиция и соединение — связаны важным свойством: декомпозиция должна быть такой, чтобы соединение частей в точности восстанавливало исходное отношение, без потери и без появления лишних строк (это называют соединением без потерь, мы вернёмся к нему в нормализации). Так соединение оказывается не просто «способом склеить таблицы», а фундаментом всего проектирования: мы смело разносим данные по таблицам именно потому, что уверены — соединение соберёт их верно.

Естественное соединение ⋈ (natural join)

Естественное соединение — частный случай эквисоединения: оно автоматически соединяет по всем атрибутам с одинаковыми именами и оставляет такой общий столбец в результате один раз (а не два, как в эквисоединении). Это удобно, когда внешний и первичный ключ названы одинаково. Минус — неявность: если случайно совпали имена ненужных столбцов, соединение пойдёт и по ним. Поэтому на практике чаще пишут явное условие ON.

Внешние соединения (outer join)

Обычное (внутреннее) соединение теряет «непарные» строки: клиент без заказов в результат не попадёт. Иногда это нежелательно — нам нужны все клиенты, в том числе без заказов. Для этого есть внешние соединения:

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

CREATE TABLE clients (id INTEGER, name TEXT);
CREATE TABLE orders (id INTEGER, client_id INTEGER);
INSERT INTO clients VALUES (1,'Анна'),(2,'Борис'),(3,'Вера');
INSERT INTO orders VALUES (10,1),(11,1),(12,2);

-- LEFT JOIN: показать ВСЕХ клиентов, даже без заказов
SELECT c.name, COUNT(o.id) AS zakazov
FROM clients c LEFT JOIN orders o ON o.client_id = c.id
GROUP BY c.name
ORDER BY c.name;

Вывод: «Анна — 2», «Борис — 1», «Вера — 0». Вера попала в результат благодаря LEFT JOIN, хотя заказов у неё нет; COUNT(o.id) по NULL даёт 0. Внутреннее соединение Веру бы потеряло.

Деление ÷ (division)

Самая редкая и самая концептуально красивая операция. Деление R ÷ S отвечает на вопросы вида «найти X, связанные со всеми Y». Классика: «клиенты, которые купили все товары из списка», «студенты, сдавшие все экзамены».

Строго: пусть R(X, Y) содержит пары, а S(Y) — множество значений. Тогда R ÷ S — это множество значений X, которые в R связаны с каждым значением из S. Если S = {книга, кружка}, то в результат попадут те клиенты, у которых в R есть пара и с книгой, и с кружкой.

В SQL прямого оператора деления нет — его выражают через двойное отрицание («нет такого товара из S, который клиент НЕ купил») или через подсчёт. Покажем способ через подсчёт: клиент подходит, если число купленных им товаров из списка равно размеру списка.

CREATE TABLE purchases (client TEXT, tovar TEXT);
INSERT INTO purchases VALUES
  ('Анна','книга'),('Анна','кружка'),('Анна','лампа'),
  ('Борис','книга'),
  ('Вера','книга'),('Вера','кружка');

-- деление: кто купил ВСЕ товары из списка {книга, кружка}?
SELECT client
FROM purchases
WHERE tovar IN ('книга','кружка')
GROUP BY client
HAVING COUNT(DISTINCT tovar) = 2;

Вывод: «Анна» и «Вера». Борис купил только книгу, поэтому не подходит. Анна купила ещё и лампу — это не мешает, важно лишь, что обе позиции из списка у неё есть. Так деление отвечает на запрос «все из множества».

Почему деление так трудно даётся новичкам? Потому что в нём спрятан квантор всеобщности («для всех»), а человеческая интуиция и SQL заточены под «существует хотя бы один». «Купил хотя бы один товар из списка» — это IN или простое соединение, легко. «Купил все товары из списка» — это уже утверждение про всё множество, и его нельзя проверить, глядя на одну строку: нужно убедиться, что ни один элемент списка не пропущен. Отсюда и два способа реализации: через подсчёт (число совпадений равно размеру списка, как в примере) или через двойное отрицание («не существует товара из списка, которого этот клиент не купил»). Умение распознать в требовании скрытое «для всех» и перевести его в деление — признак зрелого владения реляционным мышлением.

Связь алгебры с реляционным исчислением

В завершение — взгляд за горизонт. Реляционная алгебра процедурна: она описывает результат как последовательность операций (сделай произведение, потом выборку, потом проекцию). Существует и второй формализм — реляционное исчисление, которое декларативно: оно описывает результат логической формулой «все такие кортежи, что выполняется условие», вообще не указывая, как их получить. Кодд доказал важнейшую теорему: алгебра и исчисление эквивалентны по выразительной силе — что выразимо в одном, выразимо и в другом. Это фундамент SQL: его синтаксис ближе к исчислению (вы описываете желаемое), а выполнение идёт через алгебру (СУБД строит дерево операций). Так два теоретических языка встречаются в одном практическом инструменте, которым вы пользуетесь каждый день.

Типичные ошибки

Внутреннее соединение там, где нужно внешнее. Хотели «всех клиентов с числом заказов» — внутреннее соединение молча выкинет клиентов без заказов.
Опасное естественное соединение. Оно соединяет по всем одноимённым столбцам; случайное совпадение имён даёт неверный результат.
Подмена деления на простое IN. «Купил хотя бы один из списка» (IN) и «купил все из списка» (деление) — разные запросы; деление требует проверки полноты.
Забыли DISTINCT в подсчёте для деления. Если позиция куплена дважды, COUNT без DISTINCT завысит число и впустит лишних.

Итог

Соединение — это произведение плюс выборка по условию; эквисоединение соединяет по равенству ключей.
Естественное соединение неявно работает по одноимённым столбцам — удобно, но рискованно.
Внешние соединения сохраняют непарные строки, заполняя недостающее NULL.
Деление отвечает на запросы «связан со всеми из множества»; в SQL его выражают через двойное отрицание или подсчёт.