JOIN FETCH & Entity Graphs

## The problem JOIN FETCH solves An ORM maps object graphs onto tables. When you load an `Order`, its `items` collection is usually declared lazy, meaning the ORM hands you a placeholder and only issues SQL for the children when you actually touch them. That is fine for one order and terrible for a hundred: one query for the orders plus one per order for its items — the classic per-row query storm. `JOIN FETCH` is the JPQL clause that lets a *specific query* override the mapping's fetch plan and pull the association eagerly, in the same SQL statement. ## Plain JOIN vs JOIN FETCH Both produce a SQL join. The difference is what the ORM does with the joined columns. - **Plain `JOIN o.items i`** — the ORM adds the join so you can write `where i.sku = ...` or `order by i.price`. It does **not** select the child columns into the persistence context. The `items` collection on each returned `Order` remains uninitialized. - **`JOIN FETCH o.items`** — the ORM adds the join *and* selects the child columns, hydrates `OrderItem` entities, and wires them into each `Order`'s collection. After the query the collection is initialized; touching it issues no further SQL. A useful mental rule: plain JOIN is about the **where clause**; JOIN FETCH is about the **result graph**. `LEFT JOIN FETCH` matters when the association may be empty or null. An inner `JOIN FETCH` on a collection silently drops roots that have no children — a common source of "rows disappeared" bugs. ## Duplicate roots and DISTINCT Joining a to-many association multiplies rows: an order with three items produces three result rows. Without intervention the query returns the same `Order` reference three times (the same object, because the persistence context guarantees one instance per identity within a session, but three entries in the list). The fix is a de-duplication step. Historically people wrote `select distinct o from Order o join fetch o.items`, and Hibernate translated that into two things at once: a SQL `DISTINCT` keyword and an in-memory pass that collapses repeated roots. The SQL `DISTINCT` is useless work for the database on a query whose rows differ only by child columns. Modern Hibernate (6.x) performs the in-memory de-duplication of fetched-collection roots **automatically**, so `distinct` is no longer required for that purpose; in Hibernate 5.2+ the hint `hibernate.query.passDistinctThrough=false` was the way to keep the Java-side de-dup while suppressing the SQL keyword. Knowing this history is a good senior signal. ## Pagination is the sharp edge If you combine a collection `JOIN FETCH` with `setFirstResult`/`setMaxResults`, the row limit applies to the *joined* rows, not to root entities — so a limit of 10 might return three orders with partial-looking item sets. Hibernate detects this, refuses to trust the database limit, and instead fetches **all** matching rows and paginates in memory, logging a warning (historically `HHH000104: firstResult/maxResults specified with collection fetch; applying in memory`). On a large table that is an out-of-memory waiting to happen. The standard workaround is a two-step approach: page the root IDs with a plain query, then fetch the graph with `where o.id in :ids`. Hibernate 6 can also use window functions for some of these cases. ## Filtering a fetched collection Aliasing a fetched association and filtering on it is dangerous: ``` select o from Order o join fetch o.items i where i.status = 'SHIPPED' ``` The returned `Order.items` collections now contain only shipped items, yet those entities live in the persistence context and can be flushed. With `orphanRemoval` or a cascading delete you can lose data; at minimum you have an object that lies about the database. The JPA spec explicitly leaves this undefined. Fetch the whole collection and filter in memory, or query the child entity directly and project what you need. ## Multiple collections You can `JOIN FETCH` several to-one associations freely — they don't multiply rows. Fetching **two** `List` collections in one query throws `MultipleBagFetchException`, because the ORM cannot tell which cartesian row belongs to which list position. (That specific exception is a separate topic; the takeaway here is that two collection fetches in one query is a design smell — split into two queries and let the persistence context join them in memory.) ## When to reach for it Use `JOIN FETCH` when *this particular use case* needs the association: a detail screen, an export, a message payload. Do not fix per-query fetch problems by switching the mapping to `EAGER` — that penalises every other query in the application. `JOIN FETCH` is the per-query tool; the mapping should stay lazy.