Handle a missing value
Your task
Return each title and each matching sale ID, using zero when an album has no sale.
COALESCE returns the first non-NULL argument. A missing joined value can therefore fall back to a label or number.
Sample data
| id | title | genre | price | stock |
|---|---|---|---|---|
| 1 | Night Bus | Jazz | 18 | 7 |
| 2 | Static Bloom | Indie | 22 | 4 |
| 3 | Soft Circuit | Electronic | 24 | 0 |
| 4 | Blue Hour | Jazz | 16 | 3 |
| 5 | Coastline | Indie | 19 | 8 |
| 6 | Low Tide | Ambient | 21 | 2 |
Expected output
| title | COALESCE(sales.id, 0) |
|---|---|
| Night Bus | 101 |
| Night Bus | 103 |
| Static Bloom | 102 |
| Static Bloom | 106 |
| Soft Circuit | 0 |
| Blue Hour | 104 |
| Coastline | 105 |
| Low Tide | 0 |
Show a hint
Use a LEFT JOIN and COALESCE(sales.id, 0).
Expressions and missing values guide
Calculate a value for each row
Expressions can combine columns, constants, and functions. This query estimates how much stock is worth at the listed price. AS gives the calculation a name in the result.
SELECT title, price * stock AS stock_value
FROM albums;Build a category from conditions
CASE tests branches from top to bottom. Put the most specific test first: zero would also satisfy stock < 5. ELSE catches the remaining rows.
SELECT title,
CASE
WHEN stock = 0 THEN 'out of stock'
WHEN stock < 5 THEN 'low stock'
ELSE 'stocked'
END AS availability
FROM albums;Account for missing values
NULL represents an unknown or missing value. Compare it with IS NULL rather than equality. NULLIF can also turn a known sentinel into NULL. AVG ignores NULL, so these two averages use different sets of albums.
SELECT AVG(stock) AS all_albums,
AVG(NULLIF(stock, 0)) AS in_stock_only
FROM albums;Worked solution
SELECT albums.title, COALESCE(sales.id, 0) FROM albums LEFT JOIN sales ON sales.album_id = albums.id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
