How to use LEFT JOIN

Preserve the left table

A LEFT JOIN returns every row from the left table. Unmatched right-side columns contain NULL.

SELECT c.title, e.student_id
FROM courses AS c
LEFT JOIN enrollments AS e
  ON e.course_id = c.id;

Find missing relationships

Test a non-nullable right-side key for NULL to find left rows with no match.

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

Place filters carefully

A condition on the right table in WHERE can discard unmatched rows and act like an inner join. Put match-specific conditions in ON when unmatched left rows must remain.

Know the names

LEFT JOIN and LEFT OUTER JOIN mean the same thing in SQLite, PostgreSQL, and MySQL.