CASE Expressions

Prev Next

A CASE expression lets you write conditional logic directly inside a query — turning raw numbers or codes into readable labels, without pulling every row back into application code first. SQLite's CASE works exactly like standard SQL and comes in two forms: the simple form, which compares one expression against several exact values, and the searched form, which evaluates independent boolean conditions in order.

Searched CASE — WHEN with Conditions

The searched form checks each WHEN condition top to bottom and returns the result of the first one that's true. RecipeBox uses this to label a recipe's numeric difficulty column (1–5) with a friendly name:

SELECT
  title,
  difficulty,
  CASE
    WHEN difficulty <= 2 THEN 'Beginner'
    WHEN difficulty <= 4 THEN 'Intermediate'
    ELSE 'Advanced'
  END AS difficulty_label
FROM recipes
ORDER BY difficulty;

Conditions are checked in order and the first match wins, so a recipe with difficulty = 2 gets 'Beginner' and never even reaches the second WHEN. If no condition matches and there's no ELSE, the expression returns NULL.

Simple CASE — Comparing One Expression

The simple form compares a single expression against a list of exact values — useful when you're matching specific values rather than ranges or conditions:

SELECT
  title,
  CASE difficulty
    WHEN 1 THEN 'Very Easy'
    WHEN 5 THEN 'Expert Only'
    ELSE 'Standard'
  END AS note
FROM recipes;

CASE difficulty WHEN 1 THEN ... is shorthand for CASE WHEN difficulty = 1 THEN ... — the simple form only supports equality checks against the leading expression, so reach for the searched form whenever you need ranges, AND/OR logic, or comparisons across multiple columns.

Worked Example: Labeling Review Sentiment

RecipeBox's reviews table stores a numeric rating from 1 to 5. A dashboard query buckets ratings into sentiment labels and counts how many reviews fall into each bucket:

SELECT
  CASE
    WHEN rating >= 4 THEN 'Positive'
    WHEN rating = 3 THEN 'Neutral'
    ELSE 'Negative'
  END AS sentiment,
  COUNT(*) AS review_count
FROM reviews
GROUP BY sentiment
ORDER BY review_count DESC;

Because a CASE expression is just another expression, it can appear anywhere one is allowed — in the select list as shown above, but also inside WHERE, ORDER BY, or as the argument to an aggregate function like SUM for conditional counting.

Note: A CASE expression always evaluates to a single value per row — it doesn't run separate statements or produce side effects. For genuinely different actions based on data changes (like keeping a summary table in sync), you'd reach for a trigger instead, covered later in this track.

Up next: Head to JSON Functions to see how SQLite can query structured data — like a recipe's ingredient list — stored directly inside a single column.

Prev Next