SQL LEFT JOIN vs INNER JOIN
Compare SQL LEFT JOIN and INNER JOIN using learner enrollment examples, NULL handling, ON versus WHERE filters and row-count checks.
INNER JOIN and LEFT JOIN both combine rows from related tables, but they answer different questions. This guide uses a small learner-and-enrollment example to show which rows each join keeps, how NULLs appear, and why moving a filter from ON to WHERE can change a LEFT JOIN result.
Start with the result you need
Use an INNER JOIN when you want only rows with a match on both sides. Use a LEFT JOIN when every row from the left table must remain in the result, including rows without a match on the right.
Suppose learners contains one row per learner and enrollments contains course registrations. Some learners may not have enrolled in a course yet.
INNER JOIN returns matching pairs
SELECT l.id, l.name, e.course_name
FROM learners AS l
INNER JOIN enrollments AS e
ON e.learner_id = l.id;This returns rows where the join condition matches. A learner with no enrollment has no matching pair, so that learner is absent. A learner with multiple enrollments appears once for each matching enrollment.
LEFT JOIN keeps every left-side learner
SELECT l.id, l.name, e.course_name
FROM learners AS l
LEFT JOIN enrollments AS e
ON e.learner_id = l.id;Every learner row remains. When there is no matching enrollment, the selected enrollment columns are NULL. This is useful for questions such as “show all learners and any courses they have joined” or “find learners with no enrollment.”
SELECT l.id, l.name
FROM learners AS l
LEFT JOIN enrollments AS e
ON e.learner_id = l.id
WHERE e.learner_id IS NULL;The final filter keeps only the unmatched left-side rows.
The ON versus WHERE filter trap
Suppose you want every learner, but only want to display enrollments for a particular course. Put the course condition in the ON clause:
SELECT l.id, l.name, e.course_name
FROM learners AS l
LEFT JOIN enrollments AS e
ON e.learner_id = l.id
AND e.course_name = 'Python';This keeps each learner. Learners without a Python enrollment receive NULL for the enrollment columns.
If you instead write WHERE e.course_name = 'Python', rows with no matching enrollment have NULL in that column. The condition is not true for those rows, so they are filtered out. The result now contains only matching Python enrollments, which is often equivalent to the row-preserving behavior you expected from an inner join.
Compare the joins with a small matrix
| Left row | Right match? | INNER JOIN | LEFT JOIN |
|---|---|---|---|
| Learner A | One enrollment | Matching pair appears | Matching pair appears |
| Learner B | No enrollment | Row is absent | Learner appears with NULL enrollment fields |
| Learner C | Two enrollments | Two result rows | Two result rows |
Joins produce rows, not just a combined set of columns. If a left row matches several right rows, it repeats once for each match. This can affect counts and sums, so check the relationship and expected row count before adding aggregates.
Common mistakes to check
- Choosing LEFT JOIN without needing unmatched rows. If unmatched rows should be excluded, an INNER JOIN communicates that intent more clearly.
- Filtering a right-side column in WHERE by accident. This can remove the NULL-extended rows that the LEFT JOIN was meant to preserve.
- Assuming one row per learner. Multiple matching enrollments create multiple result rows.
- Using DISTINCT to hide an unexpected row count. First inspect the join keys and relationship cardinality; DISTINCT can mask a modeling or query issue.
- Testing only with fully matched sample data. Include a left-side row with no match so you can see whether the query preserves it.
A quick way to choose
- Identify which table's rows must always remain.
- If rows without a match should disappear, use INNER JOIN.
- If every row from the left table must remain, use LEFT JOIN.
- Place right-side matching criteria in ON when unmatched left rows must still show.
- Use WHERE to filter the final result, remembering that conditions on nullable right-side columns may remove unmatched rows.
- Check the number of result rows when one record can match multiple records.
Practice the difference
Write two queries over a small dataset: one that lists all learners with their enrollments, and one that finds learners with none. Add a learner without any enrollment and a learner with two enrollments. This exposes both unmatched-row and duplicate-pair behavior.
For structured SQL learning, review the SQL course details and the Jaipur Engineers course catalog.
Official reference
The PostgreSQL table expressions documentation defines inner and outer join behavior, including the NULL-extended rows produced by a left outer join. SQL syntax can vary in details across database systems, so consult the documentation for the database you use.
Want a structured Java learning path?
Explore Jaipur Engineers course options to connect Java fundamentals, debugging, backend development and project practice in a guided learning plan.