🔎 What is subselect fetching?
Subselect fetching is a Hibernate-specific fetch strategy for collections (e.g., @OneToMany, @ManyToMany) that reduces the N+1 select problem in a different way than batch or join fetches.
Instead of:
- executing N separate queries (1 per parent) for each collection,
- or doing a single join query that risks Cartesian products,
Hibernate with subselect will:
1️⃣ Load your list of parent entities (e.g., 20 authors).
2️⃣ When you access the collection on any one of those parents, Hibernate triggers one secondary query with a WHERE parent_id IN (...) subselect, fetching collections for all the parents in the original list.
🔎 Example:
1️⃣ Load authors:
SELECT * FROM authors LIMIT 20;
2️⃣ Later, you access author.getBooks() on any of those 20 authors → Hibernate issues a single subselect:
SELECT * FROM books WHERE author_id IN (1, 2, 3, ..., 20);
That one query populates the collections for all 20 authors → solving the N+1 problem.
✅ How do you enable subselect fetching?
Use Hibernate’s @Fetch(FetchMode.SUBSELECT) annotation on your collection mapping:
@Entity
public class Author {
@OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
@Fetch(FetchMode.SUBSELECT) // Hibernate-specific
private List<Book> books;
}
✅ How is it different from batch fetching?
- Batch fetching groups proxies in fixed-size batches (e.g., 10), issuing multiple small queries as needed.
- Subselect fetching triggers a single query with a subselect covering all parents loaded in the original query, loading all collections at once.
✅ How is it different from join fetching?
- Join fetch loads parents and collections in one big SQL query with a JOIN, but can cause duplicate parent rows (Cartesian explosion).
- Subselect fetch loads collections in a separate single query, avoiding duplication and keeping parent query and collection query separate.
🚨 Caveats:
- Subselect fetching only works if you load a collection of parents (e.g.,
List<Author>), not a single parent entity. - Large
IN (...)lists (hundreds or thousands of IDs) may hurt performance or exceed database limits.
🔥 Summary:
Subselect fetching is a Hibernate strategy that issues one additional query with a
WHERE parent_id IN (...)subselect, loading all collections for a set of parents in a single query and avoiding the classic N+1 problem.