In a JPQL query, how do you write an inner join and a left outer join between two entities, and how do the results differ?
answer
- JOIN a.books b — path, not table
- No ON needed: mapping supplies it
- INNER drops parents, LEFT keeps with null
- WHERE on the outer alias re-inners the query
- JOIN ≠ JOIN FETCH: no initialization
basics
~20 sYou join an association path, not a table: SELECT a FROM Author a JOIN a.books b. Inner join drops authors with no books; LEFT JOIN keeps them with b as null. The mapping supplies the join columns, so no ON clause is required.
solid answer
~50 sJPQL joins traverse a **mapped association**, so you write `JOIN a.books b` — the alias `a` on the left, an association path, and a new alias. Hibernate reads the mapping (`@OneToMany`/`@ManyToOne`, join column or join table) and emits the correct ON condition for you; you never spell out foreign-key columns. `JOIN` (or `INNER JOIN`) keeps only rows where the association resolves: an author with zero books disappears entirely. `LEFT JOIN` (`LEFT OUTER JOIN`) keeps every author, with the joined alias bound to `null` for the missing side, so any condition you then put on `b` in the WHERE clause will silently turn it back into an inner join unless you allow for null. One caveat: a plain `JOIN` only changes the SQL FROM clause and lets you filter or select on the other side. It does **not** initialize the association on the returned entities — that is what `JOIN FETCH` is for.
code
java · 7 linesList<Author> withBooks = em.createQuery(
"select distinct a from Author a join a.books b where b.price > 30",
Author.class).getResultList();
List<Object[]> allAuthors = em.createQuery(
"select a, b from Author a left join a.books b",
Object[].class).getResultList();go deeper
Recall the syntax (JOIN a.books b), that the join is on an association path, and that LEFT JOIN keeps parents without children while INNER drops them.
Explain duplicate parent rows, why a WHERE condition on the outer alias cancels the outer join, and that a plain join does not initialize the association.
Reason about the emitted SQL, choose between ON-clause filtering, null-tolerant WHERE, and EXISTS subqueries, and note when duplicate rows matter for downstream code.
Frame it as query shape versus fetch strategy: which joins exist to filter and which to load, and how that split affects row multiplication, pagination and cache behaviour across a service.
## Joins in JPQL are association-driven SQL joins two tables and you state the ON condition yourself. JPQL works one level up: entities are connected by **mapped associations**, and a join names the association path rather than the tables. The persistence provider (Hibernate) already knows, from `@ManyToOne`/`@OneToMany`/`@ManyToMany` plus `@JoinColumn` or `@JoinTable`, which columns connect the rows, so it generates the ON condition. ``` SELECT a FROM Author a JOIN a.books b WHERE b.price > 30 ``` Here `a` is the *root* (the entity the query starts from), `a.books` is the association path, and `b` is a new alias bound to the elements of that collection. Downstream clauses can use `b` like any other alias: `WHERE b.price > 30`, `ORDER BY b.title`, `SELECT b.title`. ## INNER JOIN `JOIN` and `INNER JOIN` are the same thing; `INNER` is optional. Semantically, the query produces one row per (author, book) pair that actually exists. Two consequences trip people up: 1. **Rows disappear.** An author with no books contributes nothing to the result, even if you only select `a`. If your intent was "list all authors, and by the way filter by book price when there is a book", inner join is the wrong tool. 2. **Rows duplicate.** Because the SQL result has one row per pair, `SELECT a FROM Author a JOIN a.books b` returns the same `Author` instance once per book. The entity identity is the same object (the persistence context guarantees one instance per identifier within a session), but the list has repeats. `SELECT DISTINCT a` removes them. ## LEFT (OUTER) JOIN `LEFT JOIN a.books b` keeps every author regardless of whether books exist. For an author with no books the row still appears, with `b` and every path off it evaluating to `null`. `LEFT OUTER JOIN` is a synonym; `OUTER` is optional. `RIGHT JOIN` exists in HQL and in Jakarta Persistence, but is rarely used because you can just swap the sides. The classic mistake is undoing the outer join in the WHERE clause: ``` SELECT a FROM Author a LEFT JOIN a.books b WHERE b.price > 30 ``` A null price is not `> 30`, so authors with no books are filtered out again and you have effectively written an inner join. To keep them, either move the condition into the ON clause (`LEFT JOIN a.books b ON b.price > 30`) or make the WHERE clause null-tolerant (`WHERE b IS NULL OR b.price > 30`). Those two are not equivalent: ON filters *before* the outer join preserves rows, WHERE filters *after*. ## Joining a to-one association The same syntax works for singular associations: `SELECT b FROM Book b JOIN b.publisher p WHERE p.country = 'DE'`. For a to-one, an explicit `JOIN` is often interchangeable with simply navigating (`WHERE b.publisher.country = 'DE'`), but naming the alias makes the join type explicit, which matters when the association is optional. ## What a join does not do A plain join affects the SQL FROM clause and gives you an alias to filter and project on. It does **not** load the joined entities into the returned objects' association fields — the collection on the returned `Author` is still whatever the mapping's fetch strategy says. Initializing associations in the same query is a separate feature (`JOIN FETCH`/entity graphs), which changes the SELECT list, not just the FROM clause. Also worth knowing: you cannot dereference a collection path directly. `WHERE a.books.price > 30` is illegal because `a.books` is collection-valued; you must introduce an alias with an explicit join first. Singular paths (`b.publisher.name`) can be navigated without an explicit join, which produces an *implicit* join in the generated SQL. ## Generated SQL, roughly `SELECT a FROM Author a JOIN a.books b` becomes something like ``` select a.id, a.name from author a inner join book b on b.author_id = a.id ``` and with `LEFT JOIN`, `left outer join`. Reading the emitted SQL (Hibernate's SQL logging) is the fastest way to confirm what a JPQL join actually did — especially when the answer is "more joins than I wrote".
- Does writing JOIN a.books b mean the books collection is loaded on the returned Author objects?No. A plain join only widens the SQL FROM clause so you can filter or project on the other side; the returned entities keep whatever fetch strategy the mapping declares, so a lazy collection is still an uninitialized proxy. Loading the association in the same round trip requires a fetch join or an entity graph, which adds the joined columns to the SELECT list.
- Why does SELECT a FROM Author a JOIN a.books b return the same author several times, and how do you stop it?The underlying SQL produces one row per author/book pair, and the provider maps each row to an entity, so an author with three books appears three times — all three references point to the same managed instance. Adding DISTINCT to the JPQL select collapses them; alternatively restructure the query as an EXISTS subquery so the join never multiplies rows.
An SQL join is like giving directions by street coordinates; a JPQL join is like saying "follow the hallway from this room" — the building plan (the mapping) already knows where the door is.
saying these in an interview costs you the question
- Thinking you must write an ON clause with foreign-key columns, as in raw SQL
- Believing JOIN initializes the association so no extra queries will fire later
- Adding a condition on the outer-joined alias in WHERE and still expecting parent rows without children
- Claiming LEFT JOIN and JOIN return the same rows when every parent 'usually' has children
- Writing a.books.title as a path expression instead of introducing an alias