LEFT JOIN: Keeping Rows That Don't Match

Overview

A LEFT JOIN returns every row from the left (first) table, and the matching row(s) from the right table when one exists, or NULL in every right-table column when it doesn't. This is the tool you reach for whenever "no match" is itself meaningful information, rather than something you want to discard.

A classic case: not every movie in a catalog has been rated by every source. An INNER JOIN between movies and ratings would silently hide movies with no rating at all. A LEFT JOIN keeps them in the result, with NULL standing in for "we don't have that rating."

The Query

Step-by-Step Breakdown

1. FROM movies m: the left table. Every row from here is guaranteed to appear in the output at least once.
2. LEFT JOIN ratings r ON m.id = r.movie_id AND r.source = 'Audience': attach the matching ratings row, but only the one where source = 'Audience'. Notice the source filter lives inside the ON clause, not in a WHERE clause: that distinction matters for outer joins (see Common Mistakes).
3. SELECT m.title, m.genre, r.score AS audience_score: if no audience rating exists for a movie, r.score (and every other ratings column) comes back as NULL for that row, but the movie row itself is still included.
4. ORDER BY m.title: alphabetical for readability.

Several movies in this dataset only have a "Critics" rating and no "Audience" rating: those rows will show NULL for audience_score instead of disappearing.

Variations

Find exactly which movies have no audience rating, using the "anti-join" pattern:

This is a common and useful idiom: do a LEFT JOIN, then filter with WHERE <right_table>.<any_not_null_column> IS NULL to isolate rows that didn't find a match. It effectively answers "which movies exist that ratings doesn't have an audience score for?"

Common Mistakes

• Putting join-specific filters in WHERE instead of ON. As shown above, this quietly discards the unmatched rows a LEFT JOIN was meant to preserve.
• Checking the wrong column for NULL. Use a column from the right table that can never be NULL when a match does exist, typically its primary key (r.id), not an arbitrary column that might legitimately be NULL.
• Forgetting which table is "left." A LEFT JOIN B keeps all of A; swapping the table order changes the result. If you want all rows from ratings instead, either reorder the join or use RIGHT JOIN.
• Expecting aggregate functions to ignore the extra NULL rows. COUNT(*) over a left-joined result counts every row, including the ones with NULL in every right-table column. Use COUNT(r.id) if you want to count only actual matches.