Java Interview Questions

Home / All Answers / Hibernate JPA

Question 28 · Section 16

How to Use JOIN FETCH to Solve the N+1 Problem

JOIN FETCH is the most common way to solve the N+1 problem in JPA. It allows loading related entities in a single SQL query instead of N separate queries.

English Hibernate JPA Source Markdown
Language versions: English Russian Ukrainian

Overview

JOIN FETCH is the most common way to solve the N+1 problem in JPA. It allows loading related entities in a single SQL query instead of N separate queries.

Junior Level

What is JOIN FETCH

JOIN FETCH - loads related entities in one query instead of N+1.

// N+1 problem:
List<Order> orders = em.createQuery("SELECT o FROM Order o", Order.class)
    .getResultList();  // 1 query
for (Order o : orders) {
    o.getItems().size();  // N queries
}
// 100 orders -> 101 queries

// Solution with JOIN FETCH:
List<Order> orders = em.createQuery(
    "SELECT DISTINCT o FROM Order o JOIN FETCH o.items", Order.class)
    .getResultList();  // 1 query with JOIN!
// 100 orders -> 1 query

Syntax

// One JOIN FETCH
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items")
List<Order> findAllWithItems();

// Multiple JOIN FETCH
@Query("SELECT DISTINCT o FROM Order o " +
       "JOIN FETCH o.items i " +
       "JOIN FETCH o.user u")
List<Order> findAllWithItemsAndUser();

Why DISTINCT

// JOIN can duplicate Order (one per OrderItem)
// DISTINCT eliminates duplicates in memory
SELECT DISTINCT o FROM Order o JOIN FETCH o.items

// Without DISTINCT:
// Order(id=1) - duplicated 5 times (by number of items)
// With DISTINCT:
// Order(id=1) - once

Middle Level

One JOIN FETCH per Query

// One JOIN FETCH
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.id = :id")
Order findByIdWithItems(@Param("id") Long id);

// Multiple JOIN FETCH - Cartesian Product
@Query("SELECT o FROM Order o " +
       "JOIN FETCH o.items " +
       "JOIN FETCH o.payments " +
       "JOIN FETCH o.shippings")
// 10 orders x 5 items x 3 payments x 2 shippings = 300 rows!

When Multiple JOIN FETCH is Acceptable

// Only when result set is small
@Query("SELECT DISTINCT o FROM Order o " +
       "JOIN FETCH o.items " +
       "JOIN FETCH o.user " +
       "WHERE o.id = :id")
// One order -> Cartesian product not a problem

Alternative - @BatchSize

// Instead of multiple JOIN FETCH:
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.user")
List<Order> findAllWithUser();

// + @BatchSize for items
@OneToMany(mappedBy = "order")
@BatchSize(size = 25)
private List<OrderItem> items;

// Result:
// 1 query for Order + User
// N/25 queries for items

Common Mistakes

// Without DISTINCT - duplicates
@Query("SELECT o FROM Order o JOIN FETCH o.items")
List<Order> findAll();  // duplicate Orders!

// JOIN FETCH with pagination
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items")
Page<Order> findAll(Pageable pageable);  // may not work correctly

// With separate count query
@Query(value = "SELECT DISTINCT o FROM Order o JOIN FETCH o.items",
       countQuery = "SELECT COUNT(o) FROM Order o")
Page<Order> findAll(Pageable pageable);

Senior Level

Hibernate 6 Improvements

Hibernate 6.2+ added some query-level improvements, but the Cartesian
product problem with multiple JOIN FETCH still requires manual resolution.

Advanced Patterns

// Pattern 1: EntityGraph for dynamic loading
@NamedEntityGraph(
    name = "Order.withItems",
    attributeNodes = @NamedAttributeNode("items")
)
@Entity
public class Order { }

// Usage
EntityGraph<Order> graph = entityManager.getEntityGraph("Order.withItems");
List<Order> orders = entityManager.createQuery("SELECT o FROM Order o", Order.class)
    .setHint("jakarta.persistence.fetchgraph", graph)
    .getResultList();

// Pattern 2: Different repositories for different use cases
public interface OrderRepository extends JpaRepository<Order, Long> {
    // For list - without items
    List<Order> findByStatus(String status);

    // For detailed view - with items
    @Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.id = :id")
    Optional<Order> findByIdWithItems(@Param("id") Long id);

    // For admin - with user and items
    @EntityGraph(attributePaths = {"user", "items"})
    Optional<Order> findAdminViewById(Long id);
}

JOIN FETCH and Pagination - The Problem

Why JOIN FETCH + pagination is problematic: Hibernate cannot correctly apply LIMIT/OFFSET to a query with JOIN - the result contains duplicates of root entities. Count query may also be wrong due to JOIN. Example: LIMIT 10 on a query with JOIN FETCH returns fewer than 10 distinct Orders.

// Problem: JOIN FETCH + Pageable may give wrong count
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items")
Page<Order> findAll(Pageable pageable);

// Solution 1: separate count query
@Query(value = "SELECT DISTINCT o FROM Order o JOIN FETCH o.items",
       countQuery = "SELECT COUNT(DISTINCT o) FROM Order o")
Page<Order> findAllWithPage(Pageable pageable);

// Solution 2: two queries
@Query("SELECT o.id FROM Order o")
Page<Long> findOrderIds(Pageable pageable);

@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.id IN :ids")
List<Order> findByIds(@Param("ids") List<Long> ids);

Performance Analysis

JOIN FETCH performance:
- 1 query instead of N+1
- But may produce large result (Cartesian product)
- DISTINCT handles duplicates in memory
- For large collections - @BatchSize is better
- JOIN FETCH may cause OutOfMemoryError when collection is very large. An order with 10,000 items loads all 10,000 into memory at once. For large collections, @BatchSize or sub-select is safer.

Rules:
- One JOIN FETCH per query
- @BatchSize for additional associations
- EntityGraph for dynamic loading

Best Practices

One JOIN FETCH per query
DISTINCT to eliminate duplicates
@BatchSize for additional associations
EntityGraph for dynamic loading
Different methods for different use cases

Multiple JOIN FETCH (Cartesian product)
Without DISTINCT
Ignoring Cartesian product
JOIN FETCH with pagination without countQuery

Interview Cheat Sheet

Must know:

  • JOIN FETCH loads related entities in one SQL query - N+1 solution
  • DISTINCT mandatory - eliminates duplicate root objects (one per child)
  • One JOIN FETCH per query - multiple -> Cartesian product (10 x 5 items x 3 payments = 150 rows)
  • For pagination with JOIN FETCH, separate countQuery needed - LIMIT/OFFSET incorrect with JOIN
  • Alternative for additional associations - @BatchSize instead of multiple JOIN FETCH
  • EntityGraph - dynamic loading when different fetch plans needed

Frequent follow-up questions:

  • Why is DISTINCT needed? JOIN returns one row per child, DISTINCT eliminates duplicate roots in memory
  • Why is pagination with JOIN FETCH problematic? Hibernate can’t correctly apply LIMIT - duplicate root objects
  • When are multiple JOIN FETCH acceptable? When result set is small (single order by ID), Cartesian product not an issue
  • JOIN FETCH vs @BatchSize? JOIN FETCH - 1 query when data always needed, @BatchSize - N/batchSize when sometimes

Red flags (DO NOT say):

  • “Multiple JOIN FETCH in one query” - Cartesian product, huge results
  • “Without DISTINCT” - duplicate Orders in results
  • “JOIN FETCH with pagination without countQuery” - wrong LIMIT, fewer distinct results
  • “I use JOIN FETCH for all associations always” - Cartesian product with multiple collections

Related topics:

  • [[1. What is the N+1 Problem and How to Solve It]]
  • [[6. What Does the @BatchSize Annotation Do]]
  • [[5. What Fetch Strategies Exist in Hibernate]]
  • [[29. What is Projection in JPA]]