DataslopeOuter Joins
Learn how SQLite LEFT JOIN keeps unmatched rows and uses NULLs when related data is missing.
An inner join hides rows with no match. Sometimes those missing matches are exactly what you need to find:
- customers who have never ordered
- products that have never sold
- courses with no students yet
For those questions, beginners should reach first for LEFT JOIN.
LEFT JOIN keeps every row from the left table
A LEFT JOIN keeps all rows from the first table you name. If a matching row exists on the right, SQLite includes it. If not, the right-side columns become NULL.
The left table is preserved. The right table is optional.
Linus appears even though he has no order. His order columns are NULL, which means "no matching value exists here."
In the LEFT JOIN above, why does Linus appear?
Because every customer automatically gets a fake order.
Because customers is the left table, and LEFT JOIN keeps all left-table rows.
Because NULL means zero dollars.
Because ORDER BY adds missing rows.
NULLs are the honest placeholder
When SQLite cannot find a matching right-side row, it cannot show a real orders.id or orders.amount. So it shows NULL.
COALESCE is only for display here. The underlying missing value is still NULL.
Finding unmatched rows
A LEFT JOIN plus WHERE right_table.id IS NULL is the classic pattern for "show me the rows with no match."
Linus and Mae are returned because their joined order id is NULL.
This same pattern works whenever the question says "never," "missing," or "no related row."
Counting with a left join
COUNT(column) ignores NULL, which is useful with left joins. It lets you count matching right-side rows while still keeping left-side rows with zero matches.
Linus gets 0 because there are no non-NULL order ids to count.
A note about RIGHT and FULL OUTER JOIN in SQLite
For beginners, LEFT JOIN is the most important outer join. Recent SQLite versions also support RIGHT JOIN and FULL OUTER JOIN, but you can usually make your query clearer by arranging the table you want to preserve on the left and using LEFT JOIN.
How to choose
Ask: "Which table's rows must not disappear?" Put that table first, then use `LEFT JOIN`.
Check your understanding
What does a LEFT JOIN keep?
Only rows that match in both tables.
Every row from the left table, with NULLs for right-side columns when no match exists.
Every row from the right table only.
No rows with NULLs.
What do right-side NULLs mean in a LEFT JOIN result?
The matching row exists but all values are zero.
No matching right-side row was found for that left-side row.
The query sorted the row incorrectly.
The table was deleted.
Which pattern finds customers who have never ordered?
INNER JOIN customers to orders.
LEFT JOIN orders, then WHERE o.id IS NULL.
LEFT JOIN orders, then WHERE o.id IS NOT NULL.
Select only from the orders table.
Why is LEFT JOIN usually the first outer join to learn?
It is the only join SQLite can ever run.
It clearly expresses "keep this table's rows even when related rows are missing."
It never returns NULL.
It changes the database structure.