9.6 JPQL JOIN and Advanced Queries

When you need to load related entities in one go or query across multiple tables, you use JPQL (Java Persistence Query Language) JOIN syntax. This page covers INNER/LEFT JOIN, FETCH JOIN, @EntityGraph, pagination pitfalls, and DTO projection—patterns used often in production.

Reference: Spring Data JPA, Hibernate (JPA 3.x / Jakarta Persistence)

---

1. JPQL JOIN Types

JPQL queries target entities (class names, not table names). JOINs combine entities that have an association.

JOIN type	JPQL keyword	Description	Resulting SQL
Inner join	JOIN or INNER JOIN	Only rows with matching related data	INNER JOIN
Outer join	LEFT JOIN or LEFT OUTER JOIN	All left-side rows; right side null when no match	LEFT OUTER JOIN
Fetch join	JOIN FETCH	Load associated entity in the same SELECT (avoids N+1)	INNER JOIN + single load

---

2. Basic JOIN (INNER / LEFT)

In @Query JPQL, you JOIN on association fields.

Sample entities

@Entity
public class Order {
    @Id @GeneratedValue
    private Long id;
    private String orderNo;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "member_id")
    private Member member;
}

@Entity
public class Member {
    @Id @GeneratedValue
    private Long id;
    private String name;
}

INNER JOIN

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT o FROM Order o JOIN o.member m WHERE m.name = :name")
    List<Order> findByMemberName(@Param("name") String name);
}

• JOIN o.member m: Inner join between Order and Member. Orders without a member are excluded.
• Generated SQL: SELECT o.* FROM orders o INNER JOIN members m ON o.member_id = m.id WHERE m.name = ?

LEFT JOIN (include orders even when member is missing)

@Query("SELECT o FROM Order o LEFT JOIN o.member m WHERE m.name = :name OR m.name IS NULL")
List<Order> findByMemberNameOrNoMember(@Param("name") String name);

• LEFT JOIN o.member m: All orders are returned; m is null when there is no related member.
• Note: Without FETCH, only Order is loaded in this query; member may be loaded later via Lazy (extra queries). To load the association in one go, use JOIN FETCH below.

---

3. JOIN FETCH — The Key to Fixing N+1

JOIN FETCH means “load the associated entity in this same query.” It is the main tool to eliminate N+1 from lazy loading.

Syntax

@Query("SELECT o FROM Order o JOIN FETCH o.member WHERE o.id = :id")
Optional<Order> findByIdWithMember(@Param("id") Long id);

• JOIN FETCH o.member: When loading Order, member is included in the same SELECT.
• Generated SQL: SELECT o.*, m.* FROM orders o INNER JOIN members m ON o.member_id = m.id WHERE o.id = ?
• After this, calling order.getMember().getName() in the service does not trigger an extra query.

DISTINCT with collection (OneToMany) FETCH

When you FETCH a one-to-many association (e.g. List<Comment>), the SQL result has one row per related row, so the parent entity can appear multiple times. Use distinct in JPQL so JPA returns each parent entity once.

@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.orderItems WHERE o.member.id = :memberId")
List<Order> findByMemberIdWithItems(@Param("memberId") Long memberId);

• DISTINCT: JPA deduplicates the same Order instance so you get a unique list of orders.
• SQL may also get a DISTINCT, but the main deduplication is at the entity level.

Fetching multiple associations in one query

You can FETCH multiple associations in one query (at the same level).

@Query("SELECT DISTINCT o FROM Order o " +
       "JOIN FETCH o.member " +
       "JOIN FETCH o.orderItems " +
       "WHERE o.id = :id")
Optional<Order> findByIdWithMemberAndItems(@Param("id") Long id);

• Caution: If orderItems is a collection, the result set can grow (Cartesian product). For large data, consider FETCHing only one association and using a separate query or @BatchSize for the rest.

---

4. @EntityGraph — Declarative FETCH

@EntityGraph lets you declare “load this association eagerly for this query” without writing JPQL. It gives the same effect as a FETCH JOIN for method-name or simple queries.

attributePaths

@EntityGraph(attributePaths = {"member"})
@Query("SELECT o FROM Order o WHERE o.id = :id")
Optional<Order> findByIdWithMember(@Param("id") Long id);

• attributePaths = {"member"}: When loading Order, member is joined and loaded in the same query.
• With a derived query:

@EntityGraph(attributePaths = {"member", "orderItems"})
List<Order> findByMemberId(Long memberId);

• The SELECT generated for findByMemberId will include member and orderItems via JOIN FETCH.

Nested associations

@EntityGraph(attributePaths = {"member", "orderItems.product"})
@Query("SELECT o FROM Order o WHERE o.id = :id")
Optional<Order> findByIdWithMemberAndItemProducts(@Param("id") Long id);

• orderItems.product: Loads OrderItem and its Product in one go.

---

5. JOIN with ON conditions

Use the ON clause when you need extra join conditions (JPA 2.1+).

@Query("SELECT o FROM Order o LEFT JOIN o.member m ON m.status = 'ACTIVE' WHERE o.createdAt >= :since")
List<Order> findOrdersWithActiveMemberSince(@Param("since") LocalDateTime since);

• The mapping usually provides the default FK condition; ON adds conditions like member status or date ranges.

---

6. Pagination and JOIN FETCH — Pitfalls

Using Pageable with a query that uses JOIN FETCH (especially on a collection) can trigger Hibernate warnings or in-memory pagination. Collection FETCH and pagination together are limited.

Recommended patterns

1. No FETCH in the paged query; use @BatchSize or a separate query to load associations:

   Page<Order> findByMemberId(Long memberId, Pageable pageable);
   // In service, call findByIdWithItems(id) when needed

2. DTO projection: Select only the columns you need and page that query:

   @Query("SELECT new com.example.dto.OrderSummaryDto(o.id, o.orderNo, m.name) " +
          "FROM Order o JOIN o.member m WHERE o.member.id = :memberId")
   Page<OrderSummaryDto> findOrderSummariesByMember(@Param("memberId") Long memberId, Pageable pageable);

3. If you really need collection FETCH, consider no pagination (e.g. list + application-level slice or cursor).

---

7. DTO projection with JOIN

For API responses you often want a DTO with a subset of fields instead of full entities. JPQL constructor expression lets you map JOIN results directly into a DTO.

public record OrderSummaryDto(Long orderId, String orderNo, String memberName) {}

@Query("SELECT new com.example.dto.OrderSummaryDto(o.id, o.orderNo, m.name) " +
       "FROM Order o JOIN o.member m WHERE o.member.id = :memberId")
List<OrderSummaryDto> findOrderSummariesByMemberId(@Param("memberId") Long memberId);

• new package.Class(constructor args): Passes the join result into the DTO constructor.
• Benefits: No need to load full entities; only required columns are selected, reducing memory and query cost. Works well with pagination.

---

8. Native query with JOIN (reference)

Use nativeQuery = true only when you need DB-specific functions or syntax. Prefer JPQL and DTO projection for maintainability.

@Query(value = "SELECT o.*, m.name as member_name FROM orders o " +
               "LEFT JOIN members m ON o.member_id = m.id WHERE o.id = :id",
       nativeQuery = true)
Optional<Object[]> findOrderWithMemberNameNative(@Param("id") Long id);

• Return type becomes awkward; often used with Projection interfaces or SqlResultSetMapping.

---

9. Summary

Goal	Recommended approach	Note
Fix N+1 (single association)	JOIN FETCH or @EntityGraph(attributePaths = {"member"})	Load in one query
Fix N+1 (collection)	JOIN FETCH + DISTINCT or @BatchSize	Watch Cartesian size
Pagination + related data	DTO projection + normal JOIN, or separate batch load	Avoid collection FETCH + pagination
Minimal response data	SELECT new ... DTO(...) FROM ... JOIN ...	Less entity load and exposure
Extra join condition	LEFT JOIN o.member m ON m.status = 'ACTIVE'	Use ON clause

In practice: use @EntityGraph for a single association; use @Query with JPQL JOIN and constructor projection when conditions are complex or you need DTOs directly. For dynamic conditions and complex search, see 9.7 Querydsl for type-safe joins and projections.