JPQL Fetch Joins

A JPQL fetch join loads a related association as part of the query so accessing it does not require a separate lazy-loading query. It can reduce N+1 queries, but collection fetch joins may duplicate root results and are problematic with pagination.

A normal JPQL join can use an association for filtering or joining without necessarily initializing that association on the returned entity. Adding `fetch` tells the JPA provider to retrieve the association together with the owning entity and initialize it in the resulting persistence context.

For example, `select o from Order o join fetch o.customer` returns `Order` entities while eagerly loading each associated `Customer`. This is particularly useful when application code will immediately traverse a lazy association and otherwise trigger one query per entity.

Collection fetch joins require more care. If one `Order` has several `OrderLine` entries, a SQL join produces one row for each matching line, so the same order can appear multiple times in the SQL result. JPQL commonly uses `distinct` to remove duplicate entity references from the returned result, although the underlying SQL still has to process the joined rows.

Fetch joins also interact poorly with pagination when a collection is fetched. Applying `setMaxResults` or `setFirstResult` to a query whose SQL result contains multiple rows per root entity can make database-level pagination operate on joined rows rather than the intended number of distinct root entities. Providers may handle this differently, including performing pagination in memory or warning about the query.

A fetch join is therefore a query-level loading decision rather than a replacement for every eager-loading strategy. For multiple collections, large collections, or paginated screens, alternatives such as batch fetching, entity graphs, separate queries, or two-step ID pagination may be more appropriate.

Key points

• `JOIN FETCH` initializes the joined association while loading the root entity.
• Fetch joins are commonly used to avoid N+1 queries for associations needed by the use case.
• Collection fetch joins can multiply SQL rows because one root can have many associated rows.
• `DISTINCT` can remove duplicate root entities from the JPQL result, but does not eliminate the underlying join expansion.
• Collection fetch joins and database pagination are a risky combination.

Interview questions

What problem does `JOIN FETCH` solve in JPQL?

`JOIN FETCH` is primarily used to load an association together with the root entities returned by a query. It is useful when a normally lazy relationship will definitely be accessed by the current use case. Without it, accessing that relationship later can cause additional database queries. This makes fetch joins a common technique for avoiding N+1 query behavior.

Why can a collection fetch join produce duplicate root entities?

A collection association has potentially several rows for each root entity. Joining `Order` to `OrderLine`, for example, produces one SQL row for each matching line, so an order with five lines contributes five joined rows. The ORM must reconstruct the object graph from those rows. JPQL `distinct` is often used when the application wants each root entity only once in the returned result.

Why is pagination with a collection fetch join problematic?

Database pagination is normally applied to SQL rows, while a collection fetch join can produce multiple rows for one root entity. Consequently, a row limit may not correspond to the requested number of distinct root entities. A provider may need special handling, and behavior can become inefficient or surprising. For reliable pagination, a common approach is to page root IDs first and then fetch the required associations separately.

When would you choose a fetch join versus batch fetching?

A fetch join is attractive when a particular query always needs a related association and the resulting join remains reasonably sized. Batch fetching can be preferable when the association should remain lazy or when joining it would substantially expand the result set. The choice depends on access patterns, cardinality, query shape, and whether pagination is required. I would verify the generated SQL and query counts rather than choosing solely from the object model.

What risks arise from fetch-joining multiple collection associations?

Each collection can multiply the number of SQL rows associated with a root entity. Fetching two collections in one SQL query can therefore produce combinations of their elements, potentially causing a large result set even when the number of root entities is modest. It can also make pagination unsuitable and increase database and application-side processing. In such cases, separate queries or other fetch strategies can be safer.