Why does a join chain that starts with LEFT JOIN lose the preserved rows once an INNER JOIN follows?

## A FROM clause is a chain, not a set SQL evaluates a sequence of joins left to right. `FROM a LEFT JOIN b ON p1 JOIN c ON p2` means `((a LEFT JOIN b ON p1) INNER JOIN c ON p2)`. The second join's left input is not table `a` — it is the *result* of the first join, rows and NULL-extensions included. Reading the clause as "three tables joined together" is what makes the bug invisible. ## The mechanism of the loss ```sql SELECT c.name AS customer, p.name AS product FROM customers c LEFT JOIN orders o ON o.customer_id = c.id JOIN products p ON p.id = o.product_id; -- INNER ``` A customer with no orders reaches the second join as a row where every `orders` column is `NULL`. The inner join's predicate is then `p.id = NULL`, which evaluates to **unknown**. A join keeps only pairings whose predicate is true, so no pairing survives for that row, and an inner join has no preservation rule to fall back on. The row disappears. Repeat for every orderless customer and the `LEFT JOIN` on line two has been reduced to an inner join by a line written after it. Note the precise trigger: the inner join's predicate references a column from the NULL-extended side. If the predicate referenced only `customers`, the row could still survive — provided `products` had a match for it. But an inner join anywhere downstream can always remove preserved rows, because "must match" beats "was preserved". ## Fix 1 — make the rest of the chain outer If `products` is optional from the report's point of view: ```sql FROM customers c LEFT JOIN orders o ON o.customer_id = c.id LEFT JOIN products p ON p.id = o.product_id; ``` Now the orderless customer survives with both `orders` and `products` columns `NULL`. This is worth understanding rather than cargo-culting: the second `LEFT JOIN` still finds no match (its predicate is still unknown), but the outer join's preservation rule NULL-extends instead of discarding. The rule of thumb — "once you go outer, stay outer down the chain" — falls out of this. ## Fix 2 — group the inner relationship first Sometimes `orders` and `products` genuinely belong together: an order without a product is a data error, and you do not want a half-populated row. Then join them as a unit and attach the unit: ```sql FROM customers c LEFT JOIN ( SELECT o.customer_id, p.name AS product FROM orders o JOIN products p ON p.id = o.product_id ) op ON op.customer_id = c.id; ``` The inner join happens entirely inside the derived table; the outer join sees a single optional input. Standard SQL also allows a parenthesised join expression in the `FROM` clause for the same effect. ## Why the parentheses matter Inner joins are associative and commutative, so their order is free — an optimizer reorders them at will. Outer joins are not. `(a LEFT JOIN b) LEFT JOIN c` and `a LEFT JOIN (b LEFT JOIN c)` can return different results, and moving an inner join across an outer join changes the answer, as this whole question demonstrates. When you write a mixed chain, the textual order **is** the specification. ## Diagnosing it in the wild The symptom is a report that used to include "all customers" and now silently includes fewer, usually after someone added a lookup table to enrich the output. The fastest confirmation is a row count: count the driving table alone, then count the joined query's distinct driving-table keys. If the second number is smaller, some join below the first outer join is filtering. Comment out the joins from the bottom up until the count recovers; the line that restores it is the culprit. A good habit when writing these: after the first `LEFT JOIN`, treat every later join as a decision that must be justified out loud — "is this table required, or optional?" Required means you accept losing preserved rows, and if you do not accept that, the required table belongs inside a derived table instead. ## The interview answer State the grouping — the chain is `(a LEFT JOIN b) JOIN c` — explain that the inner join's predicate evaluates to unknown against NULL-extended columns so those rows are discarded, then give both fixes and say which one you would pick based on whether the third table is optional or part of an inseparable pair.