Lesson 12 of 20

In progress

Course progress

SQL

LEFT JOIN: keeping unmatched rows

Find rows with no match, place filters in the right clause, and avoid the WHERE condition that turns a left join back into an inner one.

INNER JOIN answers "which rows match". LEFT JOIN answers "show me everything on the left, and whatever matches on the right" — which is how you find the missing ones.

What you will learn

  • Read a left join result with NULLs.
  • Find rows with no match using IS NULL.
  • Place filters in ON or WHERE depending on what should survive.

The result shape

SQL
Editable. Runs in your browser.





Press Run to see the output.

Four rows. Ana has two books, Ravi one, and Meera has a row with NULL in the title because no book matched. The left table is complete; the right side fills with NULLs where nothing exists.

That NULL is data, not an error. It is the signal that something is absent.

Counting with left joins

SQL
Editable. Runs in your browser.





Press Run to see the output.

COUNT(b.id) returns 0 for Meera, because COUNT(column) ignores NULL. COUNT(*) would return 1 for her, counting the placeholder row instead of a real book. This is the single most common bug in left-join reports.

The same applies to SUM: a sum over no matched rows is NULL, so wrap it in COALESCE(SUM(b.something), 0) when a number is required.

Finding rows with no match

SQL
Editable. Runs in your browser.





Press Run to see the output.

Read it as: keep every author, then keep only the rows where the right side failed to match. The result is Meera.

Any non-nullable column of the right table works as the test; b.id is conventional. Using a nullable column such as b.year would also return authors whose book exists but has no year, which is a different question.

The pattern generalises to "orphans" — rows whose parent is missing, found from the other direction:

SQL
Editable. Runs in your browser.





Press Run to see the output.

No rows here, because every book has an author. Run it after an import and it will find the rows that arrived before their parent.

The WHERE trap

SQL
Editable. Runs in your browser.





Press Run to see the output.

The result contains only Ana’s 2021 and 2024 books. Meera and Ravi are gone, because their b.year is NULL and NULL > 2020 is unknown. A condition on the right table in WHERE silently removes the unmatched rows, turning the left join into an inner join.

To keep every author and still filter the books:

SQL
Editable. Runs in your browser.





Press Run to see the output.

Now the condition is part of matching. Books that fail it are treated as absent, so Ravi and Meera appear with NULL titles. The choice between the two forms is a choice about which rows must survive, and it is worth stating in a comment when the query is not obvious.

Chaining left joins

SQL
Editable. Runs in your browser.





Press Run to see the output.

Each left join preserves everything to its left. Meera keeps a row with no book and no reviews, and the counts stay zero rather than vanishing. Grouping by b.id as well as a.id keeps each book’s reviews separate, which is what the detail column claims.

When LEFT JOIN is the wrong tool

If the question is "authors who have books", an inner join is the honest query: it says what it means and returns fewer rows for the planner to discard. Reaching for LEFT JOIN plus IS NOT NULL gives the same answer with more work and more ambiguity.

Use a left join when the absence matters: counting zero, listing everyone including the inactive, showing a profile with no orders yet.

Counting COUNT(*) after a left join to mean "how many related rows". It counts the placeholder row as one, so authors with no books report 1 instead of 0.

List every author with the number of books and the number of reviews, then list authors who have no reviews at all.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

COUNT(DISTINCT ...) is needed in the first query because the second join repeats each book once per review. The second query returns Ravi and Meera, since neither has a review; a review-free author with several books appears once because the WHERE filter removes every row with a review.

Recap

  • LEFT JOIN keeps every left row and fills the right side with NULLs when nothing matches.
  • Use COUNT(right.id) and COALESCE so zeros are real zeros.
  • A right-table condition in WHERE deletes unmatched rows; put it in ON to keep them.

Next, subqueries let one query use the result of another, including inside a join.

Is LEFT JOIN slower than INNER JOIN?
Usually, because it must preserve rows. On small tables the difference is negligible; check the plan on large ones.
What is a FULL OUTER JOIN?
It keeps unmatched rows from both sides. MySQL lacks it; emulate with a union of two left joins.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.