CASE & UNION: one report, many sources

READ & UNDERSTAND

A missing rating is not a zero-star review. Comparing NULL to a number produces an unknown result, so it does not select a WHEN branch. Without an explicit IS NULL branch, the row reaches ELSE.

For a review queue, use Awaiting review for NULL, Recommend for ratings at least 4.4, and Review notes for the remaining ratings. These labels describe this practice queue, not the official shelf-label question. Test both new arrivals and a known low-rated game.

Different columns give NULL different meanings: games.rating is not yet rated, sessions.score is absent for cooperative play, members.phone is not on file, and cafe_orders.member_id marks a walk-in. COALESCE can provide a display label, but replacing all of these with zero would erase their meanings.

SELECT id, title, rating, CASE WHEN rating IS NULL THEN 'Awaiting review' WHEN rating >= 4.4 THEN 'Recommend' ELSE 'Review notes' END AS review_state FROM games WHERE id >= 10 ORDER BY id;

Your question

Return id, title and review_state for all games using the three review-queue rules above, ordered by id.

Explain your evidence

Would COALESCE(rating, 0) preserve the meaning of a rating? Show the two kinds of evidence it would combine.

Try a query to unlock the worked-answer discussion.