LEFT and RIGHT OUTER JOIN

## What "outer" adds to a join An inner join returns only the row combinations that satisfy the `ON` predicate; a row on either side with no partner simply vanishes from the result. An **outer** join adds a guarantee: one side is *preserved*, meaning every one of its rows reaches the output whether or not the predicate found a partner. `LEFT OUTER JOIN` preserves the table written to the left of the keyword. `RIGHT OUTER JOIN` preserves the one written to the right. The keyword `OUTER` carries no meaning of its own — `LEFT JOIN` and `LEFT OUTER JOIN` are the same operator, just as `JOIN` and `INNER JOIN` are. ## How the result is built A useful mental model, ignoring how any engine actually executes it: 1. Form every pairing of a left row with a right row. 2. Keep the pairings for which the `ON` predicate evaluates to true. (Predicates that evaluate to false *or unknown* are discarded — `ON` keeps only true.) 3. Then, for each left row that produced no surviving pairing, add one output row consisting of that left row plus a `NULL` in every column drawn from the right table. Step 3 is the whole of the outer join. Steps 1–2 alone are an inner join, so the identity worth memorising is: **left outer join = inner join result + the unmatched left rows, NULL-extended.** ```sql SELECT c.name, o.id AS order_id FROM customers c LEFT OUTER JOIN orders o ON o.customer_id = c.id; -- Ada, 10 -- Grace, NULL <- Grace has no orders, but survives ``` ## NULL-extension is manufactured, not stored The `NULL` in `order_id` above does not exist anywhere in `orders`. The join invented it to fill columns for which there is no row. That matters when reading a result: a `NULL` in a right-side column can mean either "this left row matched nothing" or "it matched a row whose value happens to be NULL". If you need to distinguish those, test a right-side column that is never `NULL` in the source table, typically its primary key or the join key itself. It also means the right-side columns of a preserved row become nullable *expressions* even if the underlying columns are declared `NOT NULL`. Any downstream arithmetic, concatenation or comparison on them inherits `NULL` semantics: `o.total * 2` is `NULL`, and `o.total > 0` is unknown, not false. ## What LEFT JOIN does not promise It promises *at least* one output row per left row, not *exactly* one. If a left row matches three right rows, it appears three times. Candidates who assume "the result has as many rows as the left table" get burned the moment the right table is not unique per join key. It also does not promise anything about the final result of the whole statement. `LEFT JOIN` describes what that one join operator produces; clauses evaluated later can still remove those preserved rows. ## The RIGHT mirror `a RIGHT OUTER JOIN b ON …` preserves `b`. It is exactly the same operator with the sides swapped, which is why `FROM a RIGHT JOIN b` and `FROM b LEFT JOIN a` return the same rows. Most teams write only `LEFT JOIN` so the preserved, driving table is always the one you have already read. ## Presenting the NULLs When a report should show a placeholder rather than an empty cell, wrap the right-side expression: ```sql SELECT c.name, COALESCE(o.status, 'no orders') AS status FROM customers c LEFT JOIN orders o ON o.customer_id = c.id; ``` `COALESCE` returns its first non-NULL argument, so unmatched customers read `no orders`. Note this changes the *display*, not the join: the row was already preserved. ## Why interviewers ask it Because almost every reporting requirement contains the phrase "including those with none" — every customer including those without orders, every department including empty ones, every day including days with no events. Recognising that sentence as "the left side is preserved" is the single most useful reflex in day-to-day SQL writing, and it is the setup for the classic follow-up: find the rows that matched nothing at all.