Using CriteriaBuilder, how do you express an inner join to a related entity and a correlated subquery, and what is the difference between calling join() and fetch() on a Root?
answer
- join() → Join node, filterable; fetch() → Fetch, not an Expression
- implicit path nav = inner join, no handle, may duplicate
- Join.on vs where changes LEFT-join semantics
- cq.subquery(T) + correlate(outerRoot) + cb.exists
- to-many filter: exists beats join+distinct
basics
~20 sroot.join("customer", JoinType.INNER) returns a Join you can navigate and filter on. root.fetch("lines") returns a Fetch — it loads the association eagerly but is not usable in WHERE, so you cannot filter through it (people cast Fetch to Join to reuse it). Subqueries: cq.subquery(Long.class), its own from(), correlate() for the outer root, then cb.exists / cb.in.
solid answer
~50 s**Joins.** `Join<Order, Customer> c = order.join("customer", JoinType.LEFT)` adds an explicit join and gives you a node to build paths and predicates on: `cb.equal(c.get("country"), "DE")`. Path navigation (`order.get("customer").get("country")`) also joins, but always inner and without a handle you can reuse. **Fetch.** `order.fetch("lines", JoinType.LEFT)` returns a `Fetch`, not a `Join`. It exists only to initialise the association in the same select; it is deliberately not an `Expression`, so it cannot appear in WHERE. The widely used trick `Join<Order, Line> l = (Join<Order, Line>) order.fetch("lines")` works on Hibernate but relies on the implementation implementing both interfaces, and filtering a fetched collection silently gives you partially populated collections. **Subquery.** ```java Subquery<Long> sq = cq.subquery(Long.class); Root<Line> l = sq.from(Line.class); sq.select(cb.count(l)).where(cb.equal(l.get("order"), order)); cq.where(cb.greaterThan(sq, 5L)); ``` `sq.correlate(order)` gives the subquery an explicit handle on an outer root when you need to join from it.
code
java · 13 linesCriteriaQuery<Order> cq = cb.createQuery(Order.class);
Root<Order> o = cq.from(Order.class);
Join<Order, Customer> c = o.join(Order_.customer, JoinType.INNER);
Subquery<Integer> big = cq.subquery(Integer.class);
Root<Order> oc = big.correlate(o);
Join<Order, Line> l = oc.join(Order_.lines);
big.select(cb.literal(1)).where(cb.gt(l.get(Line_.qty), 100));
cq.select(o).where(
cb.equal(c.get(Customer_.country), "DE"),
cb.exists(big));go deeper
Recognise root.join(...) as the way to reach a related entity's attributes, and that fetch is about loading rather than filtering.
Explain implicit versus explicit joins, join types, row multiplication on to-many joins, and the Fetch-is-not-Join rule.
Choose exists over join-plus-distinct with reasons, use Join.on correctly with outer joins, and build correlated subqueries with correlate().
Judge when a query has outgrown the Criteria API entirely and belongs in JPQL or native SQL projected to a DTO, and set that boundary for the team.
## Explicit joins `Root.join(attribute)` (or `join(attribute, JoinType.LEFT/INNER/RIGHT)`) adds a join to the query and returns a `Join<Z, X>` — which is itself a `From`, so you can chain: `order.join("customer").join("address")`. The returned node is the handle you use everywhere else: predicates (`cb.like(c.get("name"), "A%")`), ordering (`cb.asc(c.get("name"))`), selection (`cq.multiselect(order.get("id"), c.get("name"))`). Contrast with **implicit joins** through path navigation: `order.get("customer").get("country")` is legal and shorter, but it always renders an inner join (which silently drops rows whose association is null), and you get no node back — so two navigations of the same association can produce two joins in the SQL. Explicit joins, cached in a local variable or a map, are the disciplined choice in any query that touches an association more than once. Collection joins have a typed flavour when you use the metamodel: `order.join(Order_.lines)` returns `ListJoin<Order, Line>`; `SetJoin`, `MapJoin` exist likewise, with `key()`/`value()` on the map variant. Joining a to-many multiplies rows — one row per matching child — which is why filtering on a collection join usually needs `cq.distinct(true)` or, better, an `exists` subquery. `Join.on(...)` adds an extra condition to the join itself rather than the WHERE clause. That distinction matters for LEFT joins: a condition in `on` keeps the outer rows and nulls the inner side, whereas the same condition in WHERE turns the outer join back into an effective inner join. ## Fetch is not join `Fetch` and `Join` look parallel but serve different purposes. `join` shapes the **query**; `fetch` shapes what the **result graph** contains — it tells Hibernate to initialise that association in the same select instead of lazily later. `Fetch` deliberately does not extend `Expression`, so the API refuses to let you filter or select through it. That refusal encodes a real semantic hazard: if you filter a fetched to-many collection, the collections attached to the returned entities contain only the matching children, yet those entities are managed and indistinguishable from fully loaded ones — a partially populated collection can then be flushed or cached as if it were complete. Because Hibernate's implementation class implements both `Fetch` and `Join`, the cast `(Join<X,Y>) root.fetch("lines")` compiles and works, and is common in the wild for the legitimate case of fetching and joining *the same* association once rather than twice. Know the trick, know that it is provider-dependent and unsafe if you add a predicate on the fetched collection. ## Subqueries `CriteriaQuery.subquery(Class)` creates a `Subquery<T>`, which has its own `from()` and its own `select`/`where`. Two shapes matter: **Uncorrelated** — self-contained, typically feeding `in`: ```java Subquery<Long> ids = cq.subquery(Long.class); Root<Ban> b = ids.from(Ban.class); ids.select(b.get("customerId")).where(cb.isTrue(b.get("active"))); cq.where(cb.not(order.get("customerId").in(ids))); ``` **Correlated** — refers to the outer query's row. You may reference the outer `Root` directly in a predicate, or call `sq.correlate(order)` to get a correlated `From` you can join off inside the subquery: ```java Subquery<Integer> sq = cq.subquery(Integer.class); Root<Order> o2 = sq.correlate(order); Join<Order, Line> l = o2.join(Order_.lines); sq.select(cb.literal(1)).where(cb.gt(l.get("qty"), 100)); cq.where(cb.exists(sq)); ``` `cb.exists(sq)` / `cb.not(cb.exists(sq))` are usually the right tool for "has at least one child matching X", because unlike a to-many join they neither multiply rows nor require `distinct`. `cb.all(sq)`, `cb.any(sq)`, and `path.in(sq)` cover the comparison forms. ## Practical guidance - Filtering on a to-many? Prefer `exists` over join + distinct — it is usually both clearer and cheaper. - Need the children in the result too? That is a `fetch`, and a separate concern from filtering. - Reuse join nodes; never let path navigation duplicate a join. - Use `LEFT` deliberately: an inner join on an optional association is a silent row filter, and it is the most common cause of "rows disappeared when I added that filter". - Keep subquery construction in a helper if it is reused; a `Subquery` belongs to its parent `CriteriaQuery` and cannot be moved between queries, exactly like a `Root`. ## Where the API gets painful Deeply nested subqueries with aggregates are where criteria readability collapses. When the builder code needs a comment to explain what SQL it makes, that query has outgrown the API — write it as JPQL or native SQL and project into a DTO.
- Why does the JPA API make Fetch a separate type from Join instead of letting you filter a fetched association?Because filtering a fetched to-many collection would hand you managed entities whose collections are only partially populated, with no marker distinguishing them from complete ones — a correctness hazard once those entities are flushed or cached. Fetch therefore is not an Expression and cannot appear in WHERE. Hibernate's implementation happens to implement both interfaces, so the cast works, but the restriction exists for a real reason.
- You need orders that have at least one line over 100 units. Join with distinct, or an exists subquery?Exists, in most cases. A to-many join multiplies rows and forces distinct, which the database must satisfy with a sort or hash, and it interacts badly with row-limiting. Exists short-circuits on the first matching child and leaves the outer row count alone. A join is preferable when you also need columns from the child in the result.
saying these in an interview costs you the question
- Assuming root.fetch() returns something usable in a WHERE predicate
- Using implicit path navigation on an optional association and not realising it renders an inner join
- Filtering a fetched collection and treating the resulting entities as fully loaded
- Putting a condition in WHERE on a LEFT-joined table and expecting the outer rows to survive
- Trying to reuse a Subquery or Root across two different CriteriaQuery instances