Velamuvi AIAcademy

Lesson 12 of 17 · Combining Tables

LEFT JOIN

Keep every row from one table with SQL LEFT JOIN. Find records with no match, count zeros correctly and understand the difference from INNER JOIN.

  • 8 min read
  • Beginner
  • Reviewed
In this lesson you will learn
  • How a left join differs from an inner join
  • How to find rows that have no match in another table
  • How to count per group and still include zeros
  • A trap that silently turns a left join into an inner join

The rows an inner join hides

An inner join keeps only rows that match on both sides. In the last lesson, that made two students disappear, because they have no enrollments.

But questions about what is missing are often the most valuable ones. Which students have not started a course? Which products have never been sold? Which customers have never ordered?

A left join keeps every row from the first table, whether it has a match or not.

Try it yourselfSQL
SELECT s.name, e.course_id, e.score
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id
ORDER BY s.id;

Scroll to the bottom of the result. Omar Hassan and Sofia Rossi are there, with NULL in the enrollment columns. The database had nothing to put there, so it filled the gap with NULL.

Left means "the first table"

The table written before LEFT JOIN is the left table. All of its rows are kept. The table written after is the right table. Its rows appear only where they match.

  • FROM students LEFT JOIN enrollments keeps every student.
  • FROM enrollments LEFT JOIN students keeps every enrollment.

So with a left join, the order of the tables matters.

Finding rows with no match

Rows without a partner have NULL in the right table's columns. Filter for that, and you get exactly the unmatched rows.

Try it yourselfSQL
SELECT s.name
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id
WHERE e.id IS NULL;

These are the students who have not enrolled in anything. Test the right table's primary key for NULL, because a primary key can never be missing in a real row.

The same pattern works in the other direction: which courses have no students?

Try it yourselfSQL
SELECT c.title
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.id
WHERE e.id IS NULL;

Counting, including the zeros

How many courses is each student taking? With an inner join, students with no enrollments vanish from the answer. With a left join they stay, and show a count of zero.

Try it yourselfSQL
SELECT s.name, COUNT(e.id) AS courses
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id
GROUP BY s.id, s.name
ORDER BY courses DESC, s.name;

Look carefully at the aggregate: it is COUNT(e.id), not COUNT(*).

  • COUNT(*) counts rows. An unmatched student still has one row, so the count would be 1. That is wrong.
  • COUNT(e.id) counts rows where an enrollment exists. For an unmatched student that is 0. That is right.

Inner or left?

INNER JOINLEFT JOIN
Rows keptOnly those with a match on both sidesEvery row of the left table
Unmatched rowsDroppedKept, with NULL on the right
Use it whenYou only care about matched pairsThe left table must stay complete, or you want what is missing

The trap: filtering the right table in WHERE

Suppose you want every student, together with any scores of 90 or more.

Try it yourselfSQL
SELECT s.name, e.score
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id
WHERE e.score >= 90
ORDER BY s.name;

Most students have gone. WHERE runs after the join and removes every row where the score is NULL or below 90, including all the unmatched students. The left join has quietly become an inner join.

To keep every student, move the condition into ON. It then decides which enrollments may match, not which rows survive.

Try it yourselfSQL
SELECT s.name, e.score
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id AND e.score >= 90
ORDER BY s.name;

All twelve students are back. Those without a high score show NULL.

Common mistakes

Practice exercise

List every course with its number of enrolled students. A course with nobody enrolled must appear with a count of 0.

Your solutionSQL
-- Show EVERY course title with the number of students enrolled in it,
-- including courses with no students (they must show 0).
-- Columns: title, students
Knowledge check

In FROM students LEFT JOIN enrollments ... , what happens to a student with no enrollments?

Summary

  • A left join keeps every row of the first table.
  • Unmatched rows have NULL in the second table's columns.
  • WHERE right_table.id IS NULL finds rows with no match.
  • With a left join, count a column from the right table, not *.
  • A WHERE condition on the right table removes unmatched rows. Put it in ON to keep them.

Next: joining three tables and more.

Finished reading? Tick it off to track your progress.