Stockertim Dictionary · Technology
Left join
Also called left outer join, SQL left join, LEFT OUTER JOIN in SQL
In short
A join that keeps every row of the first table and adds matching columns from the second, filling those columns with NULL where no match exists.
- Beginner
- Concept
- Checked
In detail
A left join treats the first table, the one written before the word JOIN, as the one that must be kept whole. Each of its rows appears in the result at least once. Where the second table has a matching row, its columns are filled in; where it has none, they come back as NULL. LEFT JOIN and LEFT OUTER JOIN mean the same thing.
The usual purpose is completeness. A report of all branches with their sales this month should show a branch with no sales as a row with a blank total, not leave it out. Writing branches LEFT JOIN sales does that, and COALESCE(SUM(s.amount), 0) turns the blank into a zero where zero is the right reading.
The second purpose is finding gaps. Adding WHERE second_table.key IS NULL after a left join keeps only the rows that found no partner: customers who never ordered, invoices without a payment, employees missing from the payroll file. This pattern, sometimes called an anti-join, is one of the most useful checks in data cleaning.
Two details catch people out. A condition on the second table placed in WHERE, such as WHERE s.year = 2026, removes the NULL rows and turns the left join back into an inner one; the condition belongs in the ON clause instead. And a left join can still add rows: if the second table has duplicate keys, each left row repeats once per match.
Worked example
A training provider in Birmingham has 120 enrolled learners and a submissions table, and wants learners who have not handed in their first assignment.
- Write:
SELECT l.learner_id, l.name FROM learners AS l LEFT JOIN submissions AS s ON l.learner_id = s.learner_id AND s.assignment = 1 WHERE s.learner_id IS NULL; - The query returns 15 learners: 15 / 120 = 12.5% of the cohort.
Tutors contact the 15 learners; an inner join would have shown only the 105 who submitted.
In practice
Analysts use left joins for complete lists (every product, every month, every member) and to find records missing from a second source, a routine step in reconciliations. Pandas offers the same with merge(how='left'), and a spreadsheet lookup returning #N/A is the hand-made version.
Watch out
- A WHERE condition on the right-hand table removes the unmatched rows and quietly makes the join inner; put it in ON.
- Duplicate keys on the right still multiply rows, so a left join can return more rows than the left table holds.
Learn it properly
Official sources
- The Government Data Quality Framework · UK Government Data Quality Hub
- The AQuA Book: guidance on producing quality analysis for government · HM Treasury
Facts last checked on 9 November 2026.
Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.