For an INNER JOIN, does it matter whether a filter goes in the ON clause or in WHERE?

## Two clauses that look interchangeable `ON` and `WHERE` both take a boolean expression and both cause rows to disappear, which is why so many people treat them as two spellings of the same idea. For inner joins that instinct happens to be right. For outer joins it is wrong, and the reason is worth understanding precisely rather than memorising as a rule. ## The logical model of a joined table SQL defines the result of `FROM a JOIN b ON p` in steps. Reading them literally answers every question in this area: 1. Form the Cartesian product of `a` and `b` — every row of `a` paired with every row of `b`. 2. Keep the pairs for which the join condition `p` evaluates to TRUE. A predicate that evaluates to FALSE **or** to UNKNOWN (the value a comparison takes when an operand is NULL) is not TRUE, so those pairs are dropped. 3. **Outer joins only.** For each row of the preserved side that produced no surviving pair in step 2, emit one extra row containing that row's columns and NULL in every column of the other side. This is called NULL-extension. For `LEFT JOIN` the preserved side is the left table, for `RIGHT JOIN` the right, for `FULL OUTER JOIN` both. 4. The rest of the query runs against whatever step 3 produced: `WHERE`, then `GROUP BY`, `HAVING`, `SELECT`, `ORDER BY`. An inner join simply stops after step 2 — there is no step 3, because no unmatched row is ever preserved. ## Why inner joins make the two placements equivalent With no step 3, the pairs that reach `WHERE` are exactly the pairs that satisfied `ON`. Writing `ON p AND q` keeps the pairs where `p AND q` is TRUE. Writing `ON p ... WHERE q` keeps the pairs where `p` is TRUE and then, of those, the ones where `q` is TRUE — the same set. Conjunction is commutative and associative, and nothing between the two steps modifies a row, so the multisets are identical. Column values are untouched too, so the projected output matches row for row. ```sql -- identical results SELECT c.name, o.id FROM customers c INNER JOIN orders o ON o.customer_id = c.id AND o.status = 'PAID'; SELECT c.name, o.id FROM customers c INNER JOIN orders o ON o.customer_id = c.id WHERE o.status = 'PAID'; ``` ## Where the equivalence ends Add step 3 and the two placements stop describing the same thing. A predicate in `ON` participates in deciding what counts as a match, so failing it produces a NULL-extended row rather than no row. A predicate in `WHERE` runs after the NULL-extended rows already exist, and it is applied to them like any other row — where it usually evaluates to UNKNOWN, because the columns it names are NULL. UNKNOWN is not TRUE, so those rows are discarded and the outer join collapses into an inner join. ## The convention, and when to break it Most teams write join conditions in `ON` and row filters in `WHERE`, because it reads as "how these tables relate" followed by "which rows I want". That is a good default for inner joins, where it costs nothing. For outer joins the placement is a semantic decision: a filter on the optional side belongs in `ON` when you want to keep unmatched preserved-side rows, and in `WHERE` when you deliberately want to keep only rows that matched. Deliberately is the key word — the collapse is a legitimate result, just rarely the one someone intended when they typed `LEFT JOIN`. ## Two details worth knowing **One WHERE for the whole FROM clause.** A query may chain several joins, but there is a single `WHERE`, evaluated once against the fully joined row. In `a LEFT JOIN b ON ... INNER JOIN c ON ...`, predicates in `WHERE` see the row after every join has been applied, not after the join they happen to be written next to. **This is not a performance question.** Since the two forms are logically equivalent for inner joins, both describe the same result and an engine is free to evaluate either one however it likes. Choose the placement for meaning and readability; leave the execution strategy to the optimizer. ## What interviewers listen for A weak answer is "they're the same" with no qualification, or "`ON` is for join keys, `WHERE` is for filters" recited as a syntax rule with no model behind it. A strong answer names the step that only outer joins have — NULL-extension between the `ON` test and the `WHERE` test — and can immediately produce the `LEFT JOIN` example where moving one predicate changes the answer.