What does Hibernate's `@Fetch(FetchMode.SUBSELECT)` do on a collection, and what conditions must hold for it to actually take effect?

## What subselect fetching is Subselect fetching answers the same question as batch fetching — "how do I avoid one query per parent when initialising lazy collections?" — with a different trick. Instead of collecting pending identifiers into an `IN (?, ?, ?)` list, Hibernate **replays the query that produced the parents** as a subquery. When a query loads parent entities and any of them has a collection mapped `@Fetch(FetchMode.SUBSELECT)`, Hibernate keeps the original SQL and its parameters associated with the session. The first time any one of those collections is initialised, it emits: ```sql select line.* from order_line line where line.order_id in (select o.id from orders o where o.status = ?) ``` and initialises the collection for **every** parent from the original result, not just the one you touched. Total cost: exactly two queries for the whole traversal, however many parents there were. ## Declaring it ```java @OneToMany(mappedBy = "order") @Fetch(FetchMode.SUBSELECT) private Set<OrderLine> lines; ``` `@Fetch` is a Hibernate annotation, not JPA; there is no standard equivalent. It applies per collection role and is a property of the mapping, not of the query — you cannot turn it on for one query and off for another, which is one of its real drawbacks. ## The conditions **It applies to collections only.** A `@ManyToOne` proxy cannot be subselect-fetched; there is no collection role to replay for. For the to-one direction, batch fetching is the tool. **The parents must come from a query.** The optimisation exists because Hibernate has SQL to replay. If the parent was loaded by `em.find`, or came from the persistence context or the second-level cache, there is no query to re-run — and with one parent there would be nothing to gain anyway. **Same persistence context.** The remembered query lives with the session. Clear the persistence context, detach the entities, or move to a new transaction, and the collection initialises with an ordinary single-parent select. This is a common reason people report "SUBSELECT doesn't work": the initialisation happened after a `clear()` or in a different session from the load. **It re-executes work on the database.** The subquery is the original query again. If the parent query was expensive — several joins, an unindexed filter, a sort — you pay that cost a second time. Batch fetching, by contrast, sends already-known primary keys, which is trivially cheap for the database to satisfy. This is the main reason to prefer batching as a default and reach for subselect deliberately. ## The pagination trap This one deserves its own paragraph because it is a genuine correctness-of-workload problem. Suppose the parent query is paginated with `setMaxResults(20)`. The outer query returns twenty rows, but the subselect that Hibernate replays is the query **without** the limit, so the collection query loads lines for every order matching the filter — potentially hundreds of thousands of rows into a session that only displays twenty parents. The memory and latency profile is catastrophic and the page still renders correctly, so it can go unnoticed until the table grows. With batch fetching, pagination is safe: the IN list contains only the identifiers actually in the page. ## Choosing subselect over batch Subselect wins when: the parent query is cheap and selective, you know you will traverse all the parents' collections, and there are many parents (so a fixed one-extra-query beats N/batchSize queries). Reporting jobs and bulk exports fit well. Batch wins when: results are paginated, only some parents' collections are touched, the parent query is expensive, or you want a single global setting that helps everywhere. That is most of an application, which is why `hibernate.default_batch_fetch_size` is the more common production choice and `@Fetch(FetchMode.SUBSELECT)` is applied surgically. ## Interaction with other strategies Subselect is orthogonal to fetch joins: a query that already fetch-joins the collection initialises it eagerly, and the subselect never fires. It is also independent of `@BatchSize` on the same role — declaring both means the subselect strategy takes over for collections loaded via a replayable query. ## How to answer Lead with the mechanism ("the original query is replayed as a subquery in the IN predicate"), then the payoff ("one extra query for all parents, no duplicate rows"), then the three conditions (collections, query-loaded parents, same session), then the pagination trap. That last point is what distinguishes someone who has used it from someone who has read about it.