Почему JOIN размножает строки: ключи, связи и нужный уровень детализации
Как найти причину повторяющихся строк после JOIN: посчитать совпадения ключей, проверить ограничения и выбрать детализацию результата.
В этом материале
Короткий ответ
JOIN возвращает пары подходящих строк, поэтому одна исходная запись может появиться несколько раз. Сначала определите, что означает одна строка вашего результата: заказ, позицию заказа или итог по заказу. Затем посчитайте повторения реальных ключей соединения в обеих таблицах с учётом фильтров. Для ненулевого ключа, встречающегося L раз слева и R раз справа, соединение по равенству даёт L × R пар. Отдельно проверьте ограничения уникальности: отсутствие повторений сегодня не гарантирует их отсутствия после следующих загрузок.
Что именно считает JOIN
При INNER JOIN каждая пара строк, удовлетворяющая условию ON, становится строкой результата. Внешнее соединение дополнительно может сохранить строки без пары, заполнив отсутствующие значения NULL. Объяснение через декартово произведение описывает логический смысл SQL, а не обязательный способ физического выполнения запроса. Поэтому сначала изучите условие соединения, а затем остальные части запроса. Последующий WHERE способен удалить строки, которые появились на этапе JOIN, и изменить итоговое количество записей.
Декартово произведение и повторяющиеся ключи
CROSS JOIN сопоставляет каждую левую строку с каждой правой: три записи с каждой стороны образуют девять пар. Соединение по равенству оставляет только пары с совпадающими ключами. Если одинаковый ключ повторяется в обеих таблицах, количество пар для него равно произведению числа повторений. Получается связь многие ко многим. Само по себе увеличение результата относительно родительской таблицы нормально, а превышение размера самой большой исходной таблицы не является универсальным признаком связи один ко многим.
WITH t1(num) AS (VALUES (1), (2), (3)),
t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num AS left_num, t2.num AS right_num
FROM t1 CROSS JOIN t2;
-- Nine pairs in this illustrative dataset.Пример связи один ко многим
В примере заказ 1 имеет две позиции, а заказ 2 - одну. Соединение возвращает три строки: идентификатор первого заказа встречается дважды, но рядом стоят разные позиции. Это полезные отношения между записями, которые нельзя автоматически удалить как ошибочные дубли. Если отчёт должен показывать позиции, результат уже имеет нужную детализацию. Если требуется одна строка на заказ, сначала надо свернуть позиции в агрегат или выбрать другой способ получения требуемых данных.
WITH orders(order_id) AS (VALUES (1), (2)),
order_items(item_id, order_id) AS (VALUES (10, 1), (11, 1), (12, 2))
SELECT o.order_id, i.item_id
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
ORDER BY o.order_id, i.item_id;
-- Three pairs: order 1 appears with two different items.LEFT JOIN и строки без совпадений
LEFT JOIN сохраняет левую строку без совпадения, подставляя NULL в правые столбцы. При нескольких совпадениях левая запись всё равно появляется несколько раз. В небольшом примере ключи 1 и 3 находят пары, а ключ 2 остаётся без правого значения: получаются три строки. Это описание результата самого соединения. Условие WHERE по правому столбцу может затем исключить строки с NULL, поэтому проверять нужно весь запрос, а не только название типа JOIN.
WITH t1(num) AS (VALUES (1), (2), (3)),
t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num AS left_num, t2.num AS right_num
FROM t1 LEFT JOIN t2 ON t1.num = t2.num
ORDER BY t1.num;
-- Three rows; the right value for left key 2 is NULL.Как проверить фактические ключи
Посчитайте число строк для каждого реального ключа соединения после применения нужных фильтров. Пример группирует позиции по order_id и показывает заказ с двумя позициями. Для обычного сравнения через равенство исключайте NULL: значение NULL не совпадает с другим NULL в таком условии. Если в текущих данных каждый ключ встречается один раз, это ещё не доказывает постоянную связь один к одному. Гарантию уникальности ищите в ограничениях схемы, отдельно от наблюдаемого распределения значений.
WITH order_items(item_id, order_id) AS (VALUES (10, 1), (11, 1), (12, 2))
SELECT order_id, COUNT(*) AS item_count
FROM order_items
WHERE order_id IS NOT NULL
GROUP BY order_id
HAVING COUNT(*) > 1;
-- Order 1 has two items in this illustrative dataset.Внешний ключ, уникальность и NULL
Внешний ключ связывает дочерний столбец с уникальным ключом родительской таблицы, но не делает дочерние значения уникальными. Несколько позиций могут относиться к одному заказу, если другое ограничение этого не запрещает. Внешний ключ сам по себе также не требует заполненного значения: допустимость NULL определяется отдельно. В учебной схеме order_id объявлен NOT NULL. Сопоставьте реальные столбцы ON с ограничениями, особенно если используется составной ключ или соединение включает дополнительные условия.
CREATE TABLE orders (
order_id integer PRIMARY KEY
);
CREATE TABLE order_items (
item_id integer PRIMARY KEY,
order_id integer NOT NULL REFERENCES orders(order_id)
);Выберите детализацию результата
При соединении нескольких таблиц детализации позиции одной таблицы могут повторяться вместе с позициями другой. Суммы после такого JOIN легко становятся неверными. Когда нужна одна строка на заказ, агрегируйте дочерние записи до этой детализации перед соединением. Пример сначала считает позиции, а затем присоединяет результат к заказам. Его INNER JOIN показывает только заказы с позициями. Для сохранения остальных заказов понадобится LEFT JOIN и явное решение о представлении отсутствующего количества, например нулём.
SELECT o.order_id, i.item_count
FROM orders AS o
JOIN (
SELECT order_id, COUNT(*) AS item_count
FROM order_items
GROUP BY order_id
) AS i ON i.order_id = o.order_id;Сравните соединения на небольших данных
Последний пример задаёт небольшие таблицы через VALUES, поэтому исходные ключи видны прямо в запросе. Общими являются только 1 и 3: INNER JOIN даёт две строки. Предыдущий пример LEFT JOIN сохраняет также ключ 2 и даёт три, а CROSS JOIN образует девять пар. Это расчёт для перечисленных учебных значений, а не результат измерения производительности. Добавьте мысленно ещё одну запись с совпадающим ключом и проследите, какие новые пары она должна создать.
WITH t1(num) AS (VALUES (1), (2), (3)),
t2(num) AS (VALUES (1), (3), (5))
SELECT t1.num
FROM t1 INNER JOIN t2 ON t1.num = t2.num
ORDER BY t1.num;
-- Two rows: matching keys 1 and 3.Что проверить
- Посчитайте повторения фактического ключа в обеих таблицах с учётом фильтров и обработки NULL.
- Проверяйте PRIMARY KEY, UNIQUE и FOREIGN KEY отдельно: текущие данные не заменяют ограничения схемы.
- При L повторениях ключа слева и R справа соединение по равенству даёт L × R подходящих пар.
- Используйте EXPLAIN для оценок, но проверяйте причину размножения по ключам: алгоритм соединения сам её не доказывает.
Границы применения
Примеры относятся к соединению по равенству и синтаксису PostgreSQL. Другие условия, составные ключи, WHERE, группировка и проекция могут изменить результат. Приведённые количества рассчитаны для учебных VALUES; выполнение запросов и скорость работы не измерялись.