INNER JOIN drops rows that have no match on either side. That works well when you only care about matched data, but sometimes you need to keep every row from one table regardless of whether a match exists.

LEFT JOIN does exactly that: it returns all rows from the left table and fills in NULLs for the right table's columns when there is no match.

Insert the following rows to follow along:

User 6 (frank_j) has no rows in streams. Users 2 (bob_jones), 4 (dave_kr), and 6 (frank_j) have no playlists. These gaps are deliberate and will drive the examples in this chapter.

LEFT JOIN starts the same way as INNER JOIN: it compares each row in the left table against every row in the right table using the ON condition. Rows that match are combined, just like INNER JOIN. The difference is what happens to left rows that have no match. Instead of being dropped, they are kept in the result with NULLs filling every column from the right table.

This query returns all 15 stream rows for the five users who have streams, plus one additional row for frank_j where stream_id and track_id are both NULL. With INNER JOIN, that same query would return only the 15 matched rows.

The following diagram shows the matching process. All left rows are preserved. Matched rows get combined with right-table data. Unmatched rows get NULLs:

The red node shows the key difference from INNER JOIN: frank_j has no matching stream, but LEFT JOIN keeps the row anyway with NULL values for the right side.

LEFT JOIN vs LEFT OUTER JOIN

LEFT JOIN and LEFT OUTER JOIN are identical. The word OUTER is optional. Most developers write LEFT JOIN because it is shorter. You will see both forms in documentation and interview questions, but they mean the same thing.

One-to-Many Behavior

LEFT JOIN has the same one-to-many behavior as INNER JOIN. When a left row matches multiple right rows, it appears once per match. alice_m has four streams, so she produces four rows. The only difference is that LEFT JOIN also includes left rows with zero matches, which INNER JOIN would drop.

One of the most common uses of LEFT JOIN is finding rows in the left table that have no corresponding row in the right table. The technique is straightforward: do a LEFT JOIN, then use WHERE to keep only the rows where the right table's key column is NULL.

This returns only frank_j, the one user who has never streamed a track.

The logic works in two steps:

This pattern is called an anti-join: "give me rows from the left table that have no match in the right table." It appears constantly in interviews and real-world queries.

Choosing the Right Column for IS NULL

Always check for NULL on a column that cannot be NULL when a match exists. The safest choice is the right table's primary key or the join key itself. Checking a nullable column could give false positives: a row that matched but happened to have a NULL value in that column would incorrectly pass the filter.

More Anti-Join Examples

Finding artists who have never been followed:

Finding users who have never made a payment:

The pattern is always the same: LEFT JOIN on the relationship, then WHERE right-side key IS NULL.

The previous chapter mentioned that putting a filter in ON vs WHERE produces identical results for INNER JOIN. That equivalence does not hold for LEFT JOIN, and understanding why is essential.

The rule is:

Here is a concrete example. We want all users, with their streams, but we only care about streams of track 7 (Bloom):

Filter in ON

This returns all six users. alice_m, carol_uk, and dave_kr have streams of track 7, so their rows show the stream data. The other three users (bob_jones, emma_dev, frank_j) have no streams of track 7, so their stream columns are NULL. The ON condition narrowed which streams counted as a "match," but LEFT JOIN still preserved every user.

Filter in WHERE

This returns only the three users who streamed track 7. The WHERE clause runs after the join completes, and it filters out every row where track_id is not 7, including all the NULL-padded rows from unmatched users. This effectively converts the LEFT JOIN into an INNER JOIN.

The following diagram illustrates how the two approaches produce different results:

This distinction is the single most important thing to understand about LEFT JOIN. When you want to narrow the match without losing unmatched left rows, put the condition in ON. When you want to filter the final result (and accept that unmatched rows will be removed), put the condition in WHERE.

Combining ON Filters with IS NULL

You can combine both techniques. For example, "find users who have never streamed track 7" requires an ON filter to restrict the match and a WHERE filter to find the unmatched:

This first finds streams of track 7 for each user (ON condition). Users with no stream of track 7 get NULLs. The WHERE clause then keeps only those NULL rows. The result is every user who has not streamed track 7: bob_jones, emma_dev, and frank_j.

Real queries often chain several tables. When every join in the chain is a LEFT JOIN, unmatched rows are preserved at each step. But mixing LEFT JOIN with INNER JOIN in the same query requires caution.

Chaining LEFT JOINs

To show every track with its stream count and the username of who streamed it (including tracks nobody has streamed), you might chain two LEFT JOINs:

Every track appears in the result. Track 10 (Pulse) has no streams in our sample data, so it shows up with NULLs for both the stream and user columns. The second LEFT JOIN preserves the NULL-padded rows from the first one.

Mixing LEFT JOIN and INNER JOIN

Watch what happens if you change the second join to INNER JOIN:

Track 10 (Pulse) disappears from the result. The LEFT JOIN preserved it with a NULL user_id, but the INNER JOIN on users requires a match on s.user_id. Since s.user_id is NULL for unstreamed tracks, the INNER JOIN drops them.

This is a common mistake: using LEFT JOIN early in the chain to preserve rows, then accidentally removing them with an INNER JOIN later. If you need to preserve all left rows through the entire chain, every subsequent join in that path must also be a LEFT JOIN.

Multi-Table Anti-Join

You can find unmatched rows through a chain of tables. For example, "find artists whose tracks have never been streamed" requires going through artists → albums → tracks → streams:

The first two joins are INNER JOINs because we only want artists who have albums and tracks (an artist with no albums has nothing to stream in the first place). The LEFT JOIN on streams preserves tracks with no streams, and the WHERE clause filters down to just those. If every track by an artist has been streamed, that artist does not appear.

In our sample data, every track has at least one stream except Pulse (track 10). So this query would return DJ Pulse, since one of their tracks has never been streamed. But the query finds tracks, not artists, that were never streamed. If you specifically want artists where none of their tracks have been streamed, you need a different approach using a subquery or a GROUP BY with HAVING. The query above gives you artists who have at least one unstreamed track.

Counting with Zeros

A frequent task is counting related rows for every item in a table, including items with a count of zero. INNER JOIN cannot do this because it drops the items with no related rows.

To get the stream count for every track, including tracks with zero streams:

Two details matter here:

This same pattern works for any "count per item including zeros" scenario: albums per artist, playlists per user, payments per user.

Optional Relationships

Some relationships in a database are optional. A user might not have a timezone set. A track might not have streams yet. When you need data from both sides of an optional relationship, LEFT JOIN keeps the rows where the optional side is missing.

For example, showing every user with their most recent payment date:

Users who have never made a payment show NULL for last_payment instead of being excluded from the result.

Default Values with COALESCE

When NULL values from unmatched rows make the output confusing, use COALESCE to provide a default:

In this particular case, COUNT(s.stream_id) already returns 0 (not NULL) for unstreamed tracks, so COALESCE is not strictly needed. But for aggregations like SUM, MAX, or MIN that return NULL when all input values are NULL, COALESCE is essential:

Without COALESCE, frank_j would show NULL for total_seconds. With it, frank_j shows 0.