A join combines rows according to a condition. The important decision is whether your result should contain only matches or should also preserve rows without a match.
State the relationship before joining
Identify the key on each side and the intended meaning of one result row. Joining a learner to every enrolment can legitimately return that learner several times. That is not automatically a duplicate-data bug; the result now represents an enrolment rather than one learner. Counting the result without understanding that change can produce a wrong metric.
Choose inner or outer behaviour deliberately
An inner join keeps matched combinations. A left join also preserves each left-side row when no right-side match exists, filling the missing right-side fields with NULL. This is useful when you need to show learners who have not enrolled yet. The choice expresses a reporting requirement, not merely a preferred SQL style.
Be careful with filters on the optional side
A WHERE condition on a right-side column can remove the null-filled rows of a left join. Sometimes that is intended; sometimes the condition belongs in the ON clause. Test the query with at least one unmatched record and one parent with several children so both missing rows and repeated matches become visible.
Worked example
Ava has two enrolments and Noah has none. The left join returns three rows: two for Ava’s courses and one for Noah with a missing course. Replacing LEFT JOIN with INNER JOIN would remove Noah from this report.
CREATE TABLE learners (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE enrolments (learner_id INTEGER, course TEXT);
INSERT INTO learners VALUES (1,'Ava'), (2,'Noah');
INSERT INTO enrolments VALUES (1,'Python'), (1,'SQL');
SELECT l.name, e.course FROM learners l LEFT JOIN enrolments e ON e.learner_id = l.id ORDER BY l.id, e.course;
Expected output
Ava | Python
Ava | SQL
Noah | NULLCheck your understanding
Try each question before opening its explanation. These are free practice prompts; they do not use game chances.
Why does Ava appear twice?
There are two matching enrolment rows. The join produces one row for each match, so the result’s row meaning is learner-course, not just learner.
Does a left join invent a real enrolment for Noah?
No. It preserves the learner row and fills the absent right-side columns with NULL. That indicates no matching enrolment in this query.
How could a WHERE condition accidentally hide Noah?
A condition such as WHERE e.course = 'SQL' excludes the null-filled row. If preserving all learners is required, consider an appropriate ON-clause condition and verify the intended result.
Sources and further reading
These original examples explain foundational concepts. Use the official references for complete, current details and version-specific behaviour.
About this guide: AI-assisted educational content with original examples and linked primary references. Found an issue? Tell us what to improve.