Что происходит при LEFT JOIN, если в правой таблице несколько соответствующих значений

Что проверяют

Понимаете ли вы, что соединение размножает строки левой таблицы, и видите ли, к чему это приводит в приложении: неверные суммы, дубли в выдаче, сломанная пагинация. Следом обычно спрашивают, как посчитать агрегат без задвоения и что происходит в JPA, когда join fetch по коллекции приносит одного родителя несколько раз.

Как отвечать

LEFT JOIN возвращает каждую строку левой таблицы хотя бы один раз. Нашлось справа три совпадения, левая строка появится трижды, в паре с каждым из них. Не нашлось ни одного, она появится один раз, а колонки правой таблицы будут NULL. Итого на одну левую запись приходится max(1, число совпадений) строк.

Практические следствия:

• агрегаты: SUM(o.amount) после соединения заказов с позициями посчитает сумму заказа столько раз, сколько в нём позиций. Агрегируйте правую таблицу в подзапросе или CTE до соединения;
• пагинация: LIMIT 20 отрезает строки результата, а не родительские записи, и на странице окажется меньше двадцати пользователей;
• фильтр по правой таблице: условие WHERE b.status = 'active' выкинет строки с NULL и молча превратит соединение во внутреннее. Условие, которое должно лишь сузить совпадения, ставится в ON;
• Hibernate: запрос select a from Author a left join fetch a.books раньше возвращал автора по разу на каждую книгу, и приходилось писать distinct. С Hibernate 6 корневые сущности дедуплицируются сами, но SQL по-прежнему приносит размноженные строки, а с setMaxResults страница режется уже в памяти с предупреждением HHH90003004.

Если нужна ровно одна строка на левую запись, решайте это явно: ROW_NUMBER() в подзапросе, LATERAL с LIMIT 1 или DISTINCT ON в PostgreSQL.

Где спотыкаются

Отвечают, что LEFT JOIN берёт первое совпадение справа. Никакого первого нет: соединение возвращает все пары, а порядок строк без ORDER BY не определён.

Лечат дубли через DISTINCT на весь SELECT, не разобравшись, откуда они. Сумма от этого правильной не станет, а на большой выборке добавится сортировка.

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