Как писать SQL запросы с использованием join

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

Умеете ли вы предсказать, сколько строк вернёт запрос, и понимаете ли, откуда берутся дубли и потерянные строки. Java-разработчику следом почти всегда задают вопрос про JPA: как тот же JOIN выглядит в Hibernate и что такое проблема N+1.

Как отвечать

Коротко по типам, с акцентом на то, что остаётся в результате:

• INNER JOIN: только строки, у которых нашлась пара в обеих таблицах;
• LEFT JOIN: все строки левой таблицы, у несовпавших поля правой равны NULL. RIGHT JOIN зеркален, его обычно переписывают в LEFT, меняя таблицы местами;
• FULL OUTER JOIN: все строки обеих таблиц. В MySQL его нет, эмулируют через UNION двух внешних соединений;
• CROSS JOIN: декартово произведение, например сетка «все даты на все товары»;
• self join: таблица соединяется сама с собой, классика «сотрудник и его руководитель».

Соединение по связи «один ко многим» размножает строки родителя. Если после этого считать SUM по полю родителя, сумма окажется завышенной: дочернюю таблицу агрегируют в подзапросе или CTE и соединяют уже результат. NULL в ключе не равен ничему, даже другому NULL.

Колонки внешних ключей стоит индексировать, PostgreSQL сам этого не делает. Алгоритм соединения выбирает планировщик, смотреть через EXPLAIN ANALYZE.

В JPA соединения идут по связям сущностей: select o from Order o join fetch o.items. JOIN FETCH или @EntityGraph грузят связь одним запросом и убирают N+1, когда на каждую сущность летит отдельный SELECT.

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

Фильтруют правую таблицу в WHERE после LEFT JOIN. Условие WHERE d.active = true отбрасывает строки с NULL, и соединение становится внутренним. Такое условие переносят в ON.

Лечат дубли через DISTINCT, не разобравшись, откуда они. Ошибка в условии соединения остаётся, а запрос становится медленнее.

Делают JOIN FETCH коллекции вместе с пагинацией. Hibernate выгружает все строки и режет страницу в памяти, о чём предупреждает в логе.