In a JPQL query with a LEFT JOIN, what is the difference between putting an extra restriction in the JOIN ... ON clause and putting it in the WHERE clause?

## Two different moments in query evaluation A join builds a row set; the WHERE clause then filters it. Conceptually the order is: 1. FROM/JOIN — produce combined rows, honouring the join type. 2. ON — decides which pairs match. For an outer join, unmatched left rows are still preserved, padded with nulls. 3. WHERE — filters the rows that came out of step 1–2. Nulls fail comparisons. Because the outer join's row-preservation happens in step 2 and the WHERE runs in step 3, a predicate on the optional side behaves completely differently depending on where you put it. ## The canonical example ``` -- A: restriction inside the join select a, b from Author a left join a.books b on b.price > 30 -- B: restriction after the join select a, b from Author a left join a.books b where b.price > 30 ``` Query A returns **every** author. Authors with no books at all, and authors whose books are all cheap, come back with `b = null`. Query B returns only authors who have at least one book over 30, each repeated once per such book — identical to an inner join. This is exactly the difference between "list all authors and show their expensive books, if any" and "list authors who have an expensive book". Both are legitimate requirements; the bug is writing one and meaning the other. ## The third variant: null-tolerant WHERE ``` select a, b from Author a left join a.books b where b is null or b.price > 30 ``` This keeps bookless authors, but it does **not** keep an author who has only cheap books — because for that author `b` is not null and the price fails. So it sits between A and B, and is almost never what you want. When in doubt, use ON. ## Syntax and history - JPQL: `LEFT JOIN o.items i ON i.status = :status` — standardised in JPA 2.1. - HQL before that used `WITH`: `left join o.items i with i.status = :status`. Hibernate still accepts `with`, and treats `on` as the modern spelling. - The Criteria API expresses the same thing with `join.on(predicate)`. A related restriction worth knowing: in strict JPQL an ON clause on an *association* join may only reference the joined alias and already-visible aliases; you cannot use it to re-root the query. Hibernate is more permissive, especially for entity joins with an arbitrary ON condition. ## Inner joins: no semantic difference For `INNER JOIN`, `on b.price > 30` and `where b.price > 30` produce the same rows. Databases push predicates around freely, so there is normally no performance difference either. Style-wise, many teams still prefer ON for conditions that are *about the relationship* ("only active memberships") and WHERE for conditions about the result ("only customers in Germany"), because it reads better and survives a later change of the join type to LEFT. ## Where it bites in real code The most common production bug is a dashboard or export that is supposed to show every parent with an optional aggregate — every account with its last failed payment, every product with its current discount — written as a LEFT JOIN and then filtered in WHERE. It looks right in test data where every parent happens to have a child, and quietly drops rows in production. Because the JPQL is one line and the join type is present and correct, code review often misses it; the reliable check is a test case with a parent that has no matching child. ## Rule of thumb - Condition selects *which related rows participate* → ON. - Condition selects *which result rows survive* → WHERE. - Any predicate on the alias of a LEFT JOIN that appears in WHERE is a defect candidate until proven intentional.