CASE Expressions

So far every column we've selected has come straight from a table or a function. But sometimes you want a value that depends on a condition - "call this review Positive if the rating is high, Negative if it's low." That's exactly what SQL's CASE expression is for: an if/else-style expression you can drop directly into a SELECT list, an aggregate, or an ORDER BY clause. This lesson works through it using StreamHub's reviews and watchlist tables.

The Searched CASE - CASE WHEN ... THEN ... ELSE ... END

The most common form of CASE checks a series of conditions in order and returns the result tied to the first one that's true, falling back to ELSE if none match. Here we label each StreamHub review by its sentiment based on its rating:

SELECT id, rating,
  CASE
    WHEN rating >= 4 THEN 'Positive'
    WHEN rating = 3 THEN 'Neutral'
    ELSE 'Negative'
  END AS sentiment
FROM reviews;

Each WHEN condition is checked in the order it's written, and the first one that evaluates true "wins" - so once rating >= 4 matches, MySQL never even checks the remaining conditions for that row.

The Simple CASE - Matching Exact Values

When every branch is just checking a single column against a fixed value, there's a shorter "simple" form: CASE value WHEN x THEN ... WHEN y THEN ... END. Compare the two syntaxes side by side:

-- Searched CASE: each WHEN can be any condition
CASE
  WHEN rating = 5 THEN 'Five Stars'
  WHEN rating = 1 THEN 'One Star'
END

-- Simple CASE: only checks one value for exact matches, more compact
CASE rating
  WHEN 5 THEN 'Five Stars'
  WHEN 1 THEN 'One Star'
END

The simple form is more compact but far less flexible - it can only test the one value given right after CASE for equality against each WHEN. The moment you need a range check like rating >= 4, an AND/OR combination, or a comparison across two different columns, you need the searched form used in the sentiment example above.

CASE Inside an Aggregate - Conditional Counting

One of the most genuinely useful patterns is putting a CASE expression inside an aggregate function like SUM(). Each row contributes either a 1 or a 0 based on the condition, and SUM() adds those up - effectively counting only the rows that match, without a separate query or a WHERE clause that would exclude the other rows entirely:

SELECT movie_id,
  COUNT(*) AS total_reviews,
  SUM(CASE WHEN rating >= 4 THEN 1 ELSE 0 END) AS positive_reviews
FROM reviews
GROUP BY movie_id;

This one query now shows both the total review count and how many of them were positive, per movie, in a single pass - without needing a second query filtered down to just rating >= 4 and then joining the two results back together.

IFNULL() and COALESCE() - Simpler Alternatives for NULL Substitution

CASE can technically handle "substitute a default value if this is NULL" too, but for that one specific job, IFNULL() and COALESCE() are simpler and more direct. IFNULL(value, default) takes exactly two arguments - the value to check and what to fall back to if it's NULL. COALESCE() does the same thing but accepts any number of arguments, checking each one in order and returning the first that isn't NULL, which makes it more flexible when you have several possible fallbacks to try.

SELECT id, COALESCE(comment, 'No comment left') AS comment_display
FROM reviews;

A review left with a blank comment now displays a friendly placeholder instead of an empty cell. If StreamHub later added a second fallback source - say, a short auto-generated summary - COALESCE(comment, auto_summary, 'No comment left') would check each in turn, which IFNULL() couldn't express in one call since it only accepts two arguments.

CASE in ORDER BY - Custom Sort Priority

CASE isn't limited to the SELECT list - it can also drive an ORDER BY clause, which is useful whenever "sorted order" isn't simply alphabetical or chronological. Suppose StreamHub wants a user's watchlist sorted with movies "Currently Watching" first, then "Plan to Watch," then everything else - an order that plain alphabetical sorting on a status column could never produce on its own:

SELECT movie_id, status
FROM watchlist
WHERE user_id = 14
ORDER BY
  CASE status
    WHEN 'Currently Watching' THEN 1
    WHEN 'Plan to Watch' THEN 2
    ELSE 3
  END;

Each row's CASE expression evaluates to a small priority number, and ORDER BY sorts on that number rather than on the raw status text - so the rows come back in exactly the custom priority order you defined, not simple A-to-Z order.