DataslopeOuter Joins
Keeping unmatched rows on purpose — LEFT, RIGHT, and FULL joins — and why NULLs appear where a match is missing.
An inner join silently drops rows that have no match. But sometimes the unmatched rows are exactly what you want to see: "customers who have never ordered," "products that have never sold." For those questions you need an outer join, which deliberately keeps unmatched rows.
LEFT JOIN: keep all rows from the left table
A LEFT JOIN keeps every row from the left (first) table,
matched or not. Where a match exists, the right table's columns are
filled in. Where none exists, those columns come back as NULL.
The key insight: the left table is fully preserved. The right
table is "optional" — it contributes data when it can and NULLs
when it cannot.
Linus appears, with NULL for order_id and amount — the join's
honest way of saying "this customer matched nothing." An inner join
would have hidden him entirely.
Finding the unmatched rows
A LEFT JOIN plus WHERE ... IS NULL is the classic recipe for
"find the rows with no match." Keep everyone, then keep only the
ones whose match came back empty:
Linus and Mae have no orders, so their joined o.id is NULL, and
the filter keeps exactly them. This pattern — outer join, then test
for NULL — answers a whole family of "who is missing?" questions.
RIGHT and FULL joins
Once LEFT JOIN makes sense, its siblings are easy:
RIGHT JOINkeeps every row from the right table instead of the left. It is just aLEFT JOINwith the tables mentally swapped — most people simply reorder the tables and useLEFT.FULL JOINkeeps every row from both tables, fillingNULLs on whichever side lacks a match.
A simple way to choose
Ask: "whose rows must I keep even without a match?" Keep the left
table → LEFT JOIN. Keep both → FULL JOIN. Keep only matched rows
→ plain INNER JOIN. Naming the table you refuse to lose tells you
the join.
Why NULLs appear — and that it is correct
The NULLs in an outer join are not a glitch; they are the truth.
There genuinely is no order for Linus, so "his order amount" is
genuinely unknown/absent — exactly what NULL means (recall the
Working with NULL page). Outer joins and NULL are natural
partners: the join preserves a row, and NULL faithfully marks the
information that the missing side could not supply.
Check your understanding
What does a LEFT JOIN keep that an INNER JOIN does not?
Nothing; they are identical.
Every row from the left table, even those with no match in the right table (filling NULLs for the missing side).
Only rows that match in both tables.
Every row from the right table only.
In a LEFT JOIN of customers to orders, a customer with no orders appears with what in the order columns?
Zeros.
Empty strings.
NULLs, marking that there was no matching order to supply those values.
The previous customer's order values.
Which pattern finds "customers who have never placed an order"?
An inner join between customers and orders.
A LEFT JOIN from customers to orders, then WHERE o.id IS NULL.
A LEFT JOIN then WHERE o.id IS NOT NULL.
SELECT DISTINCT on the orders table.
A FULL JOIN between two tables returns which rows?
Only the rows that match in both tables.
Only the left table's rows.
Every row from both tables, with NULLs wherever a row on one side has no match on the other.
No rows, because both sides must match.