Does LEFT JOIN orders o ON o.customer_id = c.id AND c.region = 'EU' remove non-EU customers?
answer
- which side can ever be NULL-extended?
- ON governs pairing, not membership
- the customer stays; the orders vanish
- preserved-side filters belong in WHERE
basics
~10 sNo. The ON clause only decides which orders count as matches, so a non-EU customer is kept and NULL-extended rather than removed. To exclude those customers, put c.region = 'EU' in WHERE instead.
solid answer
~50 sAn `ON` predicate never removes rows from the preserved side of an outer join — it only decides which right-hand rows a left row may pair with. Because `c.region = 'EU'` is FALSE for a Japanese customer, none of that customer's orders qualify as matches, so the customer is NULL-extended and still appears once, with every order column NULL. The set of customers in the result is unchanged; what changed is that non-EU customers lost their orders. This is the mirror image of the classic trap, and it is almost always a bug: someone meant to restrict the customers and instead blanked their orders. The rule of thumb is placement by side — a predicate on the **preserved** side goes in `WHERE`, where it genuinely removes rows; a predicate on the **optional** side goes in `ON` when you want unmatched rows kept.
code
sql · 14 lines-- Preserved-side predicate in ON: keeps ALL customers,
-- but a non-EU customer's orders are suppressed (NULL columns)
SELECT c.name, c.region, o.id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND c.region = 'EU';
-- Preserved-side predicate in WHERE: keeps ONLY EU customers,
-- each with all of their orders
SELECT c.name, c.region, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.region = 'EU';go deeper
Learn the direction of the rule: filters on the table you want to keep every row of go in WHERE. An ON clause in an outer join decides matching only, so it cannot delete rows from the preserved side.
Explain why the two sides behave differently: NULL-extension nulls only the optional side's columns, so predicates on that side interact with three-valued logic while predicates on the preserved side do not.
Catch it in review by asking what the author expected. Compare distinct preserved-side keys in the result with the base table; if a filter was meant to reduce customers and the counts match, the predicate is in the wrong clause.
Push for queries that state their intent unambiguously — placement by side, an explicit INNER JOIN when the collapse is wanted, and a comment on the rare deliberate preserved-side condition in ON, so future edits do not silently change a report.
## What the ON clause can and cannot do The `ON` clause of a join answers exactly one question: *given a row from the left and a row from the right, do these two belong together?* Its verdict controls pairing. In an inner join, a left row with no surviving pair simply produces no output — so `ON` can indirectly remove left rows. In an outer join it cannot, because the NULL-extension step exists precisely to add such rows back. That is the whole of it: **in an outer join, `ON` can never reduce the preserved side.** So consider: ```sql SELECT c.name, c.region, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND c.region = 'EU'; ``` For a customer in Japan the conjunction is FALSE for every candidate order, so no pair survives. Step two of the join then adds that customer back with `o.id` NULL. The customer is still in the result — the query threw away their orders, not them. ## Working an example Take three customers: A in the EU with two orders, B in Japan with one order, C in Japan with none. - A pairs with both orders → 2 rows with real order data. - B fails the region test on every candidate → 0 surviving pairs → 1 NULL-extended row. - C has no candidate orders at all → 1 NULL-extended row. Three customers, four rows. Compare with the same predicate in `WHERE`: ```sql SELECT c.name, c.region, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE c.region = 'EU'; ``` Here the join runs normally, then `WHERE` tests `c.region` — a column of the preserved side, never NULL-extended, so the test is a genuine TRUE/FALSE. B and C are removed. One customer, two rows. That is what "only EU customers, with all their orders" means, and only the `WHERE` version expresses it. ## Why the preserved side behaves differently The asymmetry follows directly from which columns can become NULL. NULL-extension nulls the **optional** side's columns only. A `WHERE` predicate on the optional side therefore meets NULLs and returns UNKNOWN, discarding preserved rows as a side effect — the classic collapse. A `WHERE` predicate on the preserved side meets the real values it was written for, and does the ordinary thing. Conversely, an `ON` predicate on the optional side shapes the match set usefully, while an `ON` predicate on the preserved side merely suppresses matches for the rows it dislikes, which is rarely a meaningful operation. Summarised as a placement table for a `LEFT JOIN a ... b`: - filter on `a` (preserved), want those rows gone → `WHERE`. - filter on `b` (optional), want unmatched `a` rows kept → `ON`. - filter on `b` (optional), want only matched rows → `WHERE`, and write `INNER JOIN` so the intent is legible. - filter on `a` (preserved) in `ON` → almost certainly a mistake. ## The one case where it is deliberate Occasionally a preserved-side condition in `ON` is intended: "show every customer, but only attach orders for the customers we are actively reviewing." A compact way to say that is exactly the `ON ... AND c.region = 'EU'` form — all customers listed, order columns populated only for EU ones. It is a legitimate result, and rare enough that it deserves a comment in the SQL, because most readers will otherwise assume it is the bug rather than the feature. ## Spotting it in review The give-away is an alias from the preserved side appearing inside the `ON` clause of an outer join in anything other than the join key. Ask the author what they expected: if they say "only EU customers", the predicate is in the wrong clause. A quick check is to compare the number of distinct preserved-side keys in the result with the number in the base table — if the predicate was supposed to filter customers and the counts match, it did not filter anything. ## What interviewers listen for Strong candidates answer "no" immediately and explain *why*: `ON` governs matching, and NULL-extension guarantees the preserved side survives regardless. They then state the correct placement rule by side and note the visible symptom — the non-EU customers are still there, but their order columns are blank. Weak candidates confuse this with the classic `WHERE`-collapses-the-join case and answer that the customers disappear.
- How many rows come back for a non-EU customer that has five orders under this ON clause?One. The region test is FALSE for every candidate pair, so none of the five orders qualifies as a match and the customer is NULL-extended exactly once. All five orders are absent from the result, and the customer's order columns are NULL.
- Where should the region filter go if you want only EU customers, each with all of their orders?In `WHERE`. `c.region` belongs to the preserved side, so it is never NULL-extended and the predicate behaves normally: non-EU customers are removed, and the EU customers keep every order the `ON` key matched. Filtering the preserved side in `WHERE` does not collapse the outer join.
- Is a preserved-side predicate in ON ever legitimate?Yes, when you deliberately want every preserved row listed but attachments only for a subset — "all customers, orders shown only for EU ones". It is uncommon enough that it should carry a comment, since most reviewers will read it as the well-known misplacement bug.
ON is the rule for pairing dancers; WHERE is the guest list at the door. Changing the pairing rule cannot uninvite anyone — it only leaves them standing alone on the floor.
saying these in an interview costs you the question
- Says non-EU customers disappear from the result
- Believes ON can filter either table symmetrically in an outer join
- Thinks the query returns one row per matching order for every customer
- Claims moving the predicate between ON and WHERE never matters here
- Confuses this with the WHERE-collapses-LEFT-JOIN case