JPQL, Criteria API & Queries

JPQL Joins & Fetch Joins

18 min Lesson 2 of 13

JPQL Joins & Fetch Joins

In SQL you join tables. In JPQL you join entity relationships. The shift is subtle but profound: instead of naming columns and foreign keys you navigate the object graph that JPA already knows about, and Hibernate translates that navigation into whatever SQL the underlying database needs. This lesson covers the full join vocabulary of JPQL — inner, outer, implicit, explicit, and the critically important fetch join — and explains the performance trade-offs every production developer must understand.

The Domain Model

All examples in this lesson use a small e-commerce model. An Order has many OrderItems, each linked to a Product; an Order also belongs to one Customer.

// Order.java
@Entity
@Table(name = "orders")
public class Order {

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id")
    private Customer customer;

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<OrderItem> items = new ArrayList<>();

    private LocalDateTime createdAt;
    // getters / setters omitted
}

// OrderItem.java
@Entity
@Table(name = "order_items")
public class OrderItem {

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id")
    private Order order;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "product_id")
    private Product product;

    private int quantity;
    private BigDecimal unitPrice;
}

Notice that every @ManyToOne and @OneToMany uses FetchType.LAZY. That is the right default for production — associations are only loaded when explicitly accessed. It is also what makes understanding joins so important: lazy loading without a proper join leads directly to the N+1 problem.

Implicit vs Explicit Joins

JPQL supports two syntactic styles for joining related entities.

Implicit join — dot-notation path traversal. Hibernate generates the required SQL JOIN automatically:

// "Give me all orders placed by customers in Berlin"
TypedQuery<Order> q = em.createQuery(
    "SELECT o FROM Order o WHERE o.customer.city = :city",
    Order.class);
q.setParameter("city", "Berlin");
List<Order> orders = q.getResultList();

Explicit join — the JOIN keyword with an alias, exactly like SQL:

TypedQuery<Order> q = em.createQuery(
    "SELECT o FROM Order o JOIN o.customer c WHERE c.city = :city",
    Order.class);
q.setParameter("city", "Berlin");

Both produce the same SQL INNER JOIN. Prefer explicit joins as soon as you need to reference the joined entity more than once in the query — it avoids redundant path traversals and makes the intent clear.

Inner Join, Left Join, and Cross Join

JPQL mirrors standard SQL join types, but targets relationships rather than tables:

JOIN / INNER JOIN — returns only rows where the relationship exists (non-null FK, non-empty collection).
LEFT JOIN / LEFT OUTER JOIN — returns the owning entity even when the related entity is absent (NULL on the right side).
CROSS JOIN (rarely needed) — Cartesian product of two entity ranges.

// LEFT JOIN: also return customers who have no orders yet
TypedQuery<Customer> q = em.createQuery(
    "SELECT DISTINCT c FROM Customer c LEFT JOIN c.orders o WHERE o IS NULL",
    Customer.class);
List<Customer> noOrders = q.getResultList();

DISTINCT in JPQL does two jobs: it adds DISTINCT to the SQL, and it de-duplicates the Java result list. Without it, a Customer with three orders would appear three times in the list because the SQL produces three rows.

The N+1 Problem — Why Fetch Joins Exist

Consider loading 50 orders and then printing each order's customer name:

List<Order> orders = em.createQuery("SELECT o FROM Order o", Order.class)
                        .getResultList();

for (Order o : orders) {
    // Each call to o.getCustomer().getName() fires a separate SELECT
    System.out.println(o.getCustomer().getName());
}

This fires 1 query to fetch the orders plus 50 additional queries to fetch each customer one by one — 51 round trips in total. With hundreds of rows this becomes a serious bottleneck. This is the classic N+1 select problem.

FETCH JOIN — Loading the Graph in One Query

A fetch join tells Hibernate to load the associated entity eagerly in the same SQL statement, overriding the lazy default for this specific query:

// Load orders AND their customers in a single SQL JOIN — no lazy selects
List<Order> orders = em.createQuery(
    "SELECT o FROM Order o JOIN FETCH o.customer",
    Order.class).getResultList();

for (Order o : orders) {
    // o.getCustomer() is already initialised — zero extra queries
    System.out.println(o.getCustomer().getName());
}

The generated SQL is roughly:

SELECT o.*, c.*
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id

One query instead of 51. The trade-off is a wider result set — more columns per row — but that is almost always preferable to dozens of extra round trips.

LEFT JOIN FETCH for Nullable Associations

If the association might be null (e.g., an order can exist without a customer in your domain), use LEFT JOIN FETCH so orders without a customer are still returned:

List<Order> orders = em.createQuery(
    "SELECT o FROM Order o LEFT JOIN FETCH o.customer",
    Order.class).getResultList();

Fetch Joining a Collection — The Duplicate Row Trap

Fetching a @OneToMany collection inflates the result at the SQL level. A single order with four items produces four SQL rows, all pointing at the same Order object. Without DISTINCT, your Java list will contain four copies of that order:

// BAD: duplicates in the list
List<Order> bad = em.createQuery(
    "SELECT o FROM Order o JOIN FETCH o.items",
    Order.class).getResultList();

// GOOD: de-duplicated at the Hibernate layer
List<Order> good = em.createQuery(
    "SELECT DISTINCT o FROM Order o JOIN FETCH o.items",
    Order.class).getResultList();

HibernateJpaDialect warning — "HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory!" Hibernate cannot apply SQL-level pagination (LIMIT/OFFSET) to a query that fetch-joins a collection, because the row count at the database layer does not equal the entity count. Hibernate falls back to fetching ALL rows into memory and then paginating there. For paginated list endpoints, fetch the IDs first with a plain query and pagination, then load the full graph in a second query using WHERE o.id IN :ids.

Chained Fetch Joins

You can fetch multiple associations in one query. Here we load orders with their items, and also initialise each item's product:

List<Order> orders = em.createQuery(
    "SELECT DISTINCT o FROM Order o " +
    "JOIN FETCH o.customer " +
    "JOIN FETCH o.items i " +
    "JOIN FETCH i.product",
    Order.class).getResultList();

Fetch at most one collection per query. Joining two collections simultaneously (e.g., o.items and o.tags) creates a Cartesian product of both collections. Use @BatchSize or separate queries for the second collection.

Using Fetch Joins in a Spring Data Repository

Spring Data JPA lets you place JPQL queries directly on repository methods with @Query:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // Fetch orders with their customer in one query
    @Query("SELECT o FROM Order o JOIN FETCH o.customer WHERE o.createdAt >= :since")
    List<Order> findRecentWithCustomer(@Param("since") LocalDateTime since);

    // Paginated orders without collection fetch (safe for pagination)
    @Query(
        value = "SELECT o FROM Order o JOIN FETCH o.customer WHERE o.customer.id = :customerId",
        countQuery = "SELECT COUNT(o) FROM Order o WHERE o.customer.id = :customerId"
    )
    Page<Order> findByCustomerId(@Param("customerId") Long customerId, Pageable pageable);
}

The separate countQuery is required when the main query contains a fetch join — Spring Data cannot derive the count query automatically from a fetch-join query.

Summary

JPQL joins follow the same semantic rules as SQL but operate on the object graph. Use explicit JOIN / LEFT JOIN when you need to filter or sort by a related entity. Use JOIN FETCH / LEFT JOIN FETCH when you need to initialise an association and avoid N+1 selects. Always add DISTINCT when fetch-joining a collection, and avoid mixing pagination with collection fetch joins. The next lesson covers JPQL aggregation functions and grouping.