Соединения таблиц в SQL: INNER, LEFT и влияние на результат

Прямой ответ

Соединение таблиц объединяет строки по условию соответствия, поэтому тип соединения определяет, попадают ли в результат строки без соответствия.

SELECT u.user_id, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE u.is_active = true;

LEFT JOIN сохраняет строки левой таблицы без соответствия, поэтому пользователь без заказов остаётся в результате с пустым значением заказа. Об агрегации — в статье про GROUP BY в SQL.

Тип соединения и состав результата

Тип соединения определяет, попадают ли в результат строки без соответствия, поэтому состав результата зависит от выбранного типа.

Внутреннее соединение. В результат попадают только строки с соответствием в обеих таблицах, поэтому строки без пары исключаются.

Левое соединение. Все строки левой таблицы попадают в результат, поэтому строка без соответствия получает пустое значение столбцов правой таблицы.

Полное соединение. Все строки обеих таблиц попадают в результат, поэтому строки без соответствия сохраняются с обеих сторон.

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

Отдельный аспект — соединение с условием в фильтре. Условие на столбец правой таблицы, записанное в фильтре WHERE, исключает строки с пустым значением, поэтому левое соединение превращается во внутреннее.

Дубли строк при соединении

Соединение по неключевому столбцу может дать несколько соответствий на строку, поэтому число строк результата растёт.

Несколько соответствий. Строка одной таблицы может соответствовать нескольким строкам другой, поэтому результат содержит произведение соответствий.

Дубли в агрегате. Дубли строк увеличивают сумму и счёт, поэтому агрегат считается по повторённым значениям.

Уникальность ключа. Соединение по уникальному ключу даёт одно соответствие на строку, поэтому дубли не возникают.

Практическое следствие: соединение по неключевому столбцу завышает сумму и счёт, поскольку строка учитывается число раз, равное числу соответствий.

Влияние соединения на агрегаты

Агрегат вычисляется по строкам результата соединения, поэтому дубли и пустые значения влияют на значение агрегата.

Сумма по дублям. Строка, повторённая из-за нескольких соответствий, учитывается в сумме несколько раз, поэтому значение завышается.

Счёт с пустыми значениями. Счёт по столбцу правой таблицы не учитывает строки с пустым значением, поэтому число соответствий отличается от числа строк левой таблицы.

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

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

О подзапросах для предварительной агрегации — в статье про CTE в SQL; об индексах под условие соединения — в статье про индексы PostgreSQL.

Практика: применение соединений

1. Выбирайте тип соединения по требованию к строкам без соответствия. Левое соединение сохраняет строки без пары, внутреннее исключает их.

2. Проверяйте число строк результата. Рост числа строк относительно левой таблицы указывает на несколько соответствий.

3. Соединяйте по уникальному ключу. Одно соответствие на строку исключает дубли результата.

4. Выносите условие на правую таблицу в условие соединения. Условие в фильтре WHERE превращает левое соединение во внутреннее.

5. Агрегируйте до соединения при нескольких соответствиях. Предварительная группировка исключает завышение суммы.

6. Используйте счёт по столбцу для числа соответствий. Счёт со звёздочкой считает строки результата, включая пустые соответствия.

7. Проверяйте план запроса с соединением. Индекс под условие соединения сокращает объём работы.

Рекомендации:

1. Не оставляйте условие на правую таблицу в WHERE при левом соединении. Строки с пустым значением исключаются, поэтому соединение становится внутренним.
2. Учитывайте дубли при агрегации после соединения. Строка учитывается число раз, равное числу соответствий.
3. Сопоставляйте тип соединения с вопросом анализа. Строки без соответствия нужны не всегда.
4. Фиксируйте длительность запроса в метриках. Рост длительности указывает на отсутствие индекса под условие соединения.
5. Пересматриваем соединения при изменении структуры данных. Новая связь между таблицами требует условия соединения.

Практический сценарий: сумма заказов завышалась после соединения

Контекст: отчёт показывал сумму заказов выше фактической, при этом число пользователей соответствовало ожидаемому.

1. Диагностика. Проверка показала, что таблица пользователей соединялась с таблицей платежей, содержащей несколько строк на заказ. Каждый заказ повторялся в результате число раз, равное числу платежей, поэтому сумма учитывала заказ повторно.
2. Причина. Соединение выполнялось по неключевому столбцу, поэтому одна строка заказа соответствовала нескольким строкам платежей и дублировалась в результате.
3. Действия. (а) Вынесли агрегацию платежей по заказу в блок CTE, чтобы на каждый заказ приходилась одна строка; (б) соединили пользователей с агрегированным блоком по заказу, чтобы дубли исключались; (в) сравнили сумму до и после изменения и убедились в соответствии фактическому значению; (г) добавили проверку числа строк результата относительно числа заказов, чтобы дубли обнаруживались; (д) проверили план и убедились в использовании индекса под условие соединения.
4. Результат. Сумма заказов соответствует фактической, поскольку каждый заказ учитывается один раз.

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

FAQ

Чем LEFT JOIN отличается от INNER JOIN? LEFT JOIN сохраняет все строки левой таблицы, включая строки без соответствия, INNER JOIN оставляет только строки с соответствием в обеих таблицах.

Почему число строк результата растёт при соединении? Строка одной таблицы может соответствовать нескольким строкам другой, поэтому результат содержит произведение соответствий.

Как условие в WHERE влияет на LEFT JOIN? Условие на столбец правой таблицы исключает строки с пустым значением, поэтому левое соединение превращается во внутреннее.

Как избежать завышения агрегата после соединения? Агрегировать до соединения. Предварительная группировка даёт одну строку на ключ, поэтому дубли не возникают.

Источники: PostgreSQL JOIN tutorial, table expressions, unique indexes — ссылки в шапке.