Можно ли использовать join внутри подзапроса

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

Понимаете ли вы, что подзапрос это полноценный SELECT со всеми возможностями, и умеете ли выбирать между подзапросом и соединением. Java-разработчику следом почти всегда задают вопрос про JPQL: можно ли то же самое написать в репозитории Spring Data и что при этом сгенерирует Hibernate.

Как отвечать

Да. Внутри подзапроса допустимы JOIN, GROUP BY, агрегаты и оконные функции, а ограничения зависят от места, куда подзапрос вставлен:

• в WHERE через IN или EXISTS: подзапрос возвращает набор ключей или факт наличия строки. Дубли, которые даёт JOIN, здесь не мешают;
• в FROM как производная таблица: результат с JOIN и GROUP BY работает как временная таблица, ей нужен алиас;
• в SELECT как скалярный подзапрос: должен вернуть не больше одной строки, иначе ошибка выполнения;
• CTE через WITH: то же самое, но читается лучше, когда подзапрос большой.

Пример, заказы клиентов с золотым статусом:

SELECT o.id FROM orders o WHERE EXISTS (SELECT 1 FROM customers c JOIN memberships m ON m.id = c.membership_id WHERE c.id = o.customer_id AND m.level = 'GOLD')

В JPQL это тоже работает: WHERE o.customer.id IN (SELECT c.id FROM Customer c JOIN c.membership m WHERE m.level = :level). Подзапросы в WHERE и HAVING стандарт JPA поддерживает давно, в FROM их умеет HQL современного Hibernate. В Criteria API подзапрос создаётся через query.subquery(), и внутри него доступен join().

Про производительность скажите честно: PostgreSQL и MySQL 8 обычно превращают IN и EXISTS в полусоединение, и план получается как у JOIN. Проверять надо через EXPLAIN, а не на глаз.

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

Используют NOT IN с подзапросом, в котором встречается NULL. Условие тогда не выполняется ни для одной строки, и запрос молча возвращает пустоту. Надёжнее NOT EXISTS.

Делают JOIN в производной таблице и потом суммируют. Соединение один ко многим размножает строки, и SUM оказывается завышенным в разы.

Пишут коррелированный скалярный подзапрос в SELECT для каждой строки большой выборки. Если планировщик не сумеет его развернуть, получится тот же N+1, только внутри базы.