Join fetch for optimization in Spring Boot - Step-by-Step Execution

Concept Flow - Join fetch for optimization

Start Query

↓

Identify Entities to Fetch

↓

Add JOIN FETCH Clause

↓

Execute Single Query

↓

Load Parent and Child Entities Together

↓

Return Optimized Result

The flow shows how adding a JOIN FETCH clause in a query loads related entities in one go, avoiding multiple database hits.

Execution Sample

Spring Boot

String jpql = "SELECT p FROM Parent p JOIN FETCH p.children WHERE p.id = :id";
Parent parent = entityManager.createQuery(jpql, Parent.class)
  .setParameter("id", 1L)
  .getSingleResult();

This code fetches a Parent entity and its children in one query to optimize performance.

Execution Table

Step	Action	JPQL Query	Database Query Executed	Entities Loaded	Result
1	Prepare JPQL with JOIN FETCH	SELECT p FROM Parent p JOIN FETCH p.children WHERE p.id = :id	No query yet	None	JPQL ready
2	Set parameter id=1	Same as above	No query yet	None	Parameter set
3	Execute query	Same as above	SELECT p.*, c.* FROM Parent p JOIN Child c ON p.id = c.parent_id WHERE p.id = 1	Parent with children	Single query returns parent and children
4	Return result	Same as above	Query done	Parent entity with children loaded	Parent object with children accessible

💡 Query executed once with JOIN FETCH, loading parent and children together, avoiding multiple queries.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
jpql		SELECT p FROM Parent p JOIN FETCH p.children WHERE p.id = :id	Same	Same	Same
parameter id	unset	unset	1	1	1
parent	null	null	null	Parent entity with children loaded	Parent entity with children loaded

Key Moments - 2 Insights

Why do we add JOIN FETCH instead of just JOIN?

JOIN FETCH tells JPA to load the related entities eagerly in the same query, avoiding lazy loading multiple queries. See execution_table step 3 where one query loads both parent and children.

What happens if we omit JOIN FETCH and just use JOIN?

Without JOIN FETCH, JPA loads the parent first, then loads children lazily in separate queries, causing N+1 query problem. The execution_table shows only one query with JOIN FETCH.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the database query actually executed?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Check the 'Database Query Executed' column in execution_table rows.

According to variable_tracker, what is the value of 'parent' after Step 3?

A Parent entity with children loaded

B null

C JPQL query string

D Parameter id value

💡 Hint

Look at the 'parent' row and 'After Step 3' column in variable_tracker.

If we remove JOIN FETCH from the JPQL, what changes in the execution flow?

A One query loads parent and children together

B Multiple queries load children lazily after parent

C No query is executed

D Parameter setting fails

💡 Hint

Refer to key_moments explanation about JOIN vs JOIN FETCH.

Concept Snapshot

Join fetch in JPQL loads related entities eagerly in one query.
Syntax: SELECT p FROM Parent p JOIN FETCH p.children WHERE ...
Avoids multiple queries and N+1 problem.
Improves performance by fetching parent and children together.
Use when you know you need related data immediately.

Full Transcript

Join fetch is a way to optimize database queries in Spring Boot JPA. Instead of loading a parent entity and then lazily loading its children in separate queries, join fetch loads both parent and children in one query. The flow starts by preparing a JPQL query with JOIN FETCH, setting parameters, executing the query which runs a single SQL join query, and returning the parent entity with children loaded. Variables like the JPQL string, parameters, and the parent entity change state step by step. Key points include that JOIN FETCH triggers eager loading, avoiding multiple queries. The visual quiz tests understanding of when the query runs, variable states, and the effect of removing JOIN FETCH. This technique improves performance by reducing database hits.

Practice

1. What is the main purpose of using JOIN FETCH in Spring Boot JPA queries?

easy

A. To create a new table for the joined entities

B. To delete related entities automatically when the parent is deleted

C. To load related entities eagerly in a single query and avoid multiple database hits

D. To update related entities in batch

Solution

1. Step 1: Understand what JOIN FETCH does

   JOIN FETCH tells JPA to load related entities eagerly in the same query instead of lazy loading them later.

2. Step 2: Recognize the performance benefit

   This reduces the number of database queries, improving performance by avoiding the N+1 select problem.

3. Final Answer:

   To load related entities eagerly in a single query and avoid multiple database hits -> Option C

4. Quick Check:

   Join fetch = eager load related data [OK]

Hint: Join fetch loads related data in one query to boost speed [OK]

Common Mistakes:

• Thinking join fetch deletes or updates data
• Confusing join fetch with creating new tables
• Assuming join fetch delays loading entities

2. Which of the following is the correct JPQL syntax to fetch a parent entity and its child entities using join fetch?

easy

A. SELECT p FROM Parent p JOIN FETCH p.children

B. SELECT p FROM Parent p JOIN p.children FETCH

C. SELECT p FROM Parent p FETCH JOIN p.children

D. SELECT p FROM Parent p LEFT JOIN p.children FETCH

Solution

1. Step 1: Recall correct JPQL join fetch syntax

   The correct syntax places JOIN FETCH before the association path: JOIN FETCH p.children.

2. Step 2: Check each option

   Only SELECT p FROM Parent p JOIN FETCH p.children matches the correct syntax. The others misuse the order of keywords or use incorrect join types.

3. Final Answer:

   SELECT p FROM Parent p JOIN FETCH p.children -> Option A

4. Quick Check:

   Join fetch syntax = JOIN FETCH association [OK]

Hint: Remember: 'JOIN FETCH' comes together before the association [OK]

Common Mistakes:

• Swapping FETCH and JOIN keywords
• Placing FETCH after the association path
• Using FETCH without JOIN keyword

3. Given the following JPQL query:

SELECT o FROM Order o JOIN FETCH o.items WHERE o.id = :id

What will happen when this query runs?

medium

A. It loads only the items without the Order

B. It loads the Order and all its items in one query, avoiding lazy loading

C. It throws a syntax error because JOIN FETCH cannot be used with WHERE

D. It loads only the Order, items are loaded lazily later

Solution

1. Step 1: Analyze the query structure

   The query uses JOIN FETCH to eagerly load the items collection along with the Order entity filtered by id.

2. Step 2: Understand the effect of join fetch with WHERE

   The WHERE clause filters the order, but the join fetch still loads the items eagerly in the same query.

3. Final Answer:

   It loads the Order and all its items in one query, avoiding lazy loading -> Option B

4. Quick Check:

   Join fetch + WHERE = eager load filtered data [OK]

Hint: Join fetch loads related data even with WHERE filters [OK]

Common Mistakes:

• Thinking join fetch causes syntax errors with WHERE
• Assuming items load lazily despite join fetch
• Confusing join fetch with separate queries

4. Consider this JPQL query:

SELECT c FROM Customer c JOIN FETCH c.orders o WHERE o.status = 'PENDING'

What is the likely problem with this query?

medium

A. It may return duplicate Customer entities due to multiple matching orders

B. It will fail because JOIN FETCH cannot have an alias

C. It will not fetch orders eagerly because of the WHERE clause

D. It will only fetch orders with status other than 'PENDING'

Solution

1. Step 1: Understand join fetch with filtering on collection

   Filtering on orders with WHERE o.status = 'PENDING' can cause multiple rows per customer if they have multiple pending orders.

2. Step 2: Recognize duplicate root entities issue

   This leads to duplicate Customer entities in the result list unless distinct is used.

3. Final Answer:

   It may return duplicate Customer entities due to multiple matching orders -> Option A

4. Quick Check:

   Join fetch + filtered collection = possible duplicates [OK]

Hint: Filtering join fetch collections can cause duplicates [OK]

Common Mistakes:

• Believing join fetch cannot have aliases
• Thinking WHERE disables eager loading
• Assuming only non-matching orders are fetched

5. You want to optimize loading a list of Author entities with their books and each book's publisher in one query. Which JPQL query correctly uses join fetch for this?

hard

A. SELECT a FROM Author a JOIN FETCH a.books, b.publisher

B. SELECT a FROM Author a JOIN a.books b JOIN FETCH b.publisher

C. SELECT a FROM Author a JOIN FETCH a.books JOIN b.publisher

D. SELECT a FROM Author a JOIN FETCH a.books b JOIN FETCH b.publisher

Solution

1. Step 1: Identify the need for nested join fetch

   To load authors with books and each book's publisher eagerly, use join fetch on both associations.

2. Step 2: Check the syntax for multiple join fetches

   SELECT a FROM Author a JOIN FETCH a.books b JOIN FETCH b.publisher correctly uses JOIN FETCH a.books b and then JOIN FETCH b.publisher to fetch nested associations.

3. Final Answer:

   SELECT a FROM Author a JOIN FETCH a.books b JOIN FETCH b.publisher -> Option D

4. Quick Check:

   Multiple join fetches = eager load nested relations [OK]

Hint: Use multiple JOIN FETCH for nested eager loading [OK]

Common Mistakes:

• Missing JOIN FETCH on nested association
• Using JOIN without FETCH for nested entities
• Incorrect syntax with commas or missing aliases