SQL Challenger
All SQL guides

SQL LEFT JOIN with unmatched rows

Use LEFT JOIN to calculate total copies sold for every album, including albums with zero sales.

Sample query

SELECT a.title, COALESCE(SUM(s.quantity), 0) AS copies_sold
FROM albums AS a
LEFT JOIN sales AS s ON s.album_id = a.id
GROUP BY a.id, a.title
ORDER BY copies_sold DESC, a.title;

Start with the list that must stay complete

The shop wants a sales report that includes albums that have never sold. Put albums on the left of the join and match sales.album_id to albums.id. For an album with no matching sale, the join still returns a row for that album, with NULL in the sales columns.

Add up quantities at the album level

One album can have several sales, and one sale can include several copies. Group by the album ID and title to produce one line per album. SUM(s.quantity) counts copies rather than transactions. When an album has no sales, SUM returns NULL. COALESCE gives that total a value of zero, which is the number the report needs.

Spot the albums a join can lose

Night Bus sold two copies in one transaction and three in another, so its total is five. Low Tide and Soft Circuit have no sales and both appear with zero. Try swapping LEFT JOIN for JOIN. Those two albums disappear because an inner join returns only matching rows.

Query result

titlecopies_sold
Night Bus5
Static Bloom3
Blue Hour2
Coastline1
Low Tide0
Soft Circuit0
Try a related challenge