When a JPQL query filters on a nested path such as 'where o.customer.address.city = :city' on an Order entity, what SQL does the provider generate, and why should you care?
answer
- Dot across association = join
- Implicit joins are INNER
- Collection path cannot be dotted
- Repeated path may join twice
- o.customer.id often needs no join
basics
~20 sEach dot across a to-one association becomes a join in the SQL: two hops means two joins you never wrote. They are inner joins by default, they can silently multiply if repeated, and they are invisible in the JPQL text.
solid answer
~50 sNavigating a singular association with a dot creates an **implicit join**. `o.customer.address.city` walks Order → Customer → Address, so the emitted SQL contains two joins even though the JPQL mentions none. Only the last segment (`city`) is a column on the final table. Three things follow. First, the join type: an implicit join through a to-one is an **inner** join, so orders whose customer or address is null vanish from the result — usually not what a report intends. Second, cost: a nested path in several clauses can produce several joins, and older Hibernate versions would emit a separate join per occurrence rather than reusing one. Third, readability: reviewers see one innocent predicate and miss three tables' worth of work. The fix when it matters is to write the join explicitly — `left join o.customer c left join c.address a where a.city = :city` — which makes both the join count and the join type visible and controllable. Implicit joins are fine for one short, mandatory hop.
code
java · 10 lines// implicit: two joins hidden in one predicate, both inner
em.createQuery("select o from Order o where o.customer.address.city = :c", Order.class);
// explicit: join count and join type are visible and controllable
em.createQuery("""
select o from Order o
left join o.customer c
left join c.address a
where a.city = :c
""", Order.class);go deeper
Know that a dot across an association becomes a SQL join, and that collections cannot be navigated that way.
Explain that implicit joins are inner, that they can repeat, and when to replace them with an explicit alias.
Diagnose missing rows and surprising plans by reading emitted SQL, and set a team convention for when explicit joins are mandatory.
Weigh terseness against transparency across a codebase: dotted paths hide join cardinality and nullability decisions that determine report correctness and query-plan stability.
## What an implicit join is JPQL lets you navigate the object graph with dots. In `select o from Order o where o.customer.address.city = :city`, only `city` is a plain column; `customer` and `address` are associations. To evaluate the predicate the database must reach the address row, and the only way to do that in SQL is with joins. The provider inserts them for you — hence *implicit* join, as opposed to the *explicit* join you would write with `join o.customer c`. The rule of thumb: **every dot that crosses an association boundary is a join**; dots that reach a basic field or an embeddable's field are not (an `@Embeddable` lives in the same table, so `o.shippingAddress.city` costs nothing extra). ## Only singular paths can be implicit Implicit joins work for `@ManyToOne` and `@OneToOne` — single-valued paths. A collection path cannot be dereferenced: `o.lineItems.price` is a parse error, because there is no single value to continue from. Collections must be joined explicitly with an alias, or handled with `SIZE()`, `IS EMPTY`, `MEMBER OF`, or an EXISTS subquery. ## The join type is inner This is the sharpest edge. An implicit join is an inner join, so it filters. Consider `select o from Order o where o.customer.name like 'A%'` where `customer` is optional (`@ManyToOne(optional = false)` not declared, nullable FK). Orders with no customer disappear — which is arguably right for that predicate. But now consider a select-list or ORDER BY use: `select o.id, o.customer.name from Order o` also inner-joins, so orders without a customer vanish from a listing that was supposed to show all orders. Nothing in the JPQL text warns you. Writing `left join o.customer c` and selecting `c.name` restores them. (Some provider versions treat implicit joins in the SELECT list differently from the WHERE clause, and Hibernate 6 improved reuse and consistency, but the safe habit is version-independent: if you need outer semantics, say so explicitly.) ## Duplicate joins If the same path appears in several places — `where o.customer.address.city = :city and o.customer.address.zip = :zip` — the provider may reuse the joins or may emit them again, depending on version and on whether the occurrences are in different clauses. Historically this produced SQL with the same table joined three or four times; the query still returns correct rows for to-one joins, but the plan is worse and the SQL is baffling to read. Introducing one explicit alias and reusing it removes all ambiguity: ``` select o from Order o join o.customer c join c.address a where a.city = :city and a.zip = :zip ``` ## Where implicit joins are genuinely fine One mandatory hop on a `@ManyToOne(optional = false)` in a WHERE clause is idiomatic and readable: `where o.customer.id = :id`. In fact that particular case is special — for the *identifier* of a to-one association, the provider can read the foreign-key column already present on the owning table and emit **no join at all**, as long as the identifier is simple and the mapping is not an inverse one-to-one. That is why `o.customer.id` is cheap while `o.customer.name` is not, and why experienced developers reach for `o.customer.id = :id` instead of loading the customer. ## How to spot the problem Turn on SQL logging (`hibernate.show_sql` or, better, a statement-logging datasource proxy) and read the generated statement for any query that surprises you. Count the joins and compare with the joins you wrote. In code review, treat multi-dot paths as a signal: ask whether the association is optional, whether the path repeats, and whether the query is on a hot path. ## The judgment call Implicit joins optimize for terse, object-flavoured queries; explicit joins optimize for control and transparency. A reasonable team rule is: one hop implicit, two or more hops explicit; anything optional explicit and outer; anything in the SELECT list explicit. That keeps short queries readable without ever letting a report silently lose rows.
- Why does 'where o.customer.id = :id' usually generate no join at all?The foreign-key column holding the customer identifier already lives on the order table, so the provider can compare it directly without visiting the customer table. This holds for a simple identifier on the owning side of a to-one association; navigating to any non-identifier attribute, or across an inverse one-to-one, forces a real join.
- Does an implicit join through an @Embeddable field cost an extra join?No. An embeddable is mapped into the owning entity's own table, so o.shippingAddress.city is just another column on the orders table. Only paths that cross a real entity association turn into joins.
saying these in an interview costs you the question
- Assuming a dotted path is 'just a field access' with no SQL cost
- Expecting implicit joins to be outer joins so optional associations still yield rows
- Trying to navigate a collection with a dot, e.g. o.lineItems.price
- Believing the same path used twice is always collapsed into one join
- Thinking o.customer.name is as cheap as o.customer.id