SQLzoo音乐教程第4题：LEFT JOIN与INNER JOIN选择困惑

Hey there! Let's clear up the confusion around JOIN types for this problem—you're totally right to question the "correct" solution if it misses albums without tracks.

The Core Requirement #

显示每张专辑的标题及其曲目总数

The key word here is 每张 (every album). That means we need to include all albums from the album table, even those that have no matching tracks in the track table.

Why INNER JOIN Falls Short #

If you use an INNER JOIN, it will only return rows where there's a matching record in both the album and track tables. Any album with no tracks will be excluded from the result set—this doesn't meet the requirement of showing every album.

Here's what an INNER JOIN version might look like (and why it's wrong for your case):

SELECT a.title, COUNT(t.id) AS track_count
FROM album a
INNER JOIN track t ON a.id = t.album_id
GROUP BY a.title;

Why LEFT JOIN Is the Right Choice #

A LEFT JOIN (or LEFT OUTER JOIN) preserves all rows from the left table (in this case, the album table), even when there's no matching row in the right table (track table). For albums with no tracks, the track columns will be NULL, and we can count those correctly as 0.

Here's the correct query:

SELECT a.title, COUNT(t.id) AS track_count
FROM album a
LEFT JOIN track t ON a.id = t.album_id
GROUP BY a.title;

Important Note About COUNT()

Notice we use COUNT(t.id) instead of COUNT(*). That's because COUNT(*) counts every row, including those with NULL values from the LEFT JOIN. COUNT(t.id) ignores NULL values (since t.id will be NULL for albums with no tracks), so it returns 0 for those albums—exactly what we want.

Why Some "Correct Solutions" Might Use INNER JOIN #

It's possible that some solutions assume every album has at least one track, but since you've observed that there are albums without track records, those solutions don't account for the full dataset. Your instinct to use LEFT JOIN is correct here.

内容的提问来源于stack exchange，提问作者KevinKim