CASE Expressions

Overview

CASE is SQL's if/then/else. It can evaluate conditions top-to-bottom and returns the value of the first WHEN branch that is TRUE. If no branch matches, it returns the ELSE value (or NULL if ELSE is omitted). Searched CASE (the most common form) tests arbitrary conditions: SELECT name, price, CASE WHEN price >= 100 THEN 'expensive' WHEN price >= 20 THEN 'moderate' ELSE 'cheap' END AS price_tier FROM products ORDER BY price DESC; Returns 6 rows: Monitor → expensive, Desk → expensive, Chair → moderate, Keyboard → moderate, Mouse → cheap, Notebook → cheap. Simple CASE compares one expression against multiple values: SELECT name, status, CASE status WHEN 'paid' THEN 'Completed' WHEN 'pending' THEN 'Awaiting payment' WHEN 'cancelled' THEN 'Cancelled' END AS display_status FROM orders ORDER BY id; Returns 8 rows, each with a human-readable display_status. CASE works in SELECT, WHERE, ORDER BY, and even inside aggregate functions. It is an expression, not a statement — it produces a value, so you can use it anywhere a value is expected. Critical rule: CASE will evaluate branches top-to-bottom and stops at the first TRUE match. Order matters! If you put WHEN price >= 20 before WHEN price >= 100, Monitor (199.99) would match 'moderate' first and never reach 'expensive'. If no WHEN matches and there is no ELSE, CASE returns NULL: SELECT CASE 'x' WHEN 'a' THEN 1 END; Returns NULL.

CASE lets you transform raw data into meaningful categories directly in SQL, without post-processing in application code. Bucketing prices, mapping status codes to labels, and conditional aggregation all depend on CASE.

Where used: mapping status codes to display labels in API responses, bucketing numeric values for analytics reports, conditional aggregation (COUNT with CASE)

Why learn this

• Conditional aggregation (e.g., COUNT paid vs. cancelled orders in one query) requires CASE inside aggregate functions
• Mapping raw database values to user-friendly labels avoids doing the transformation in application code

Query walkthrough

SELECT name, price,
  CASE
    WHEN price >= 100 THEN 'expensive'
    WHEN price >= 20  THEN 'moderate'
    ELSE 'cheap'
  END AS price_tier
FROM products
ORDER BY price DESC;

Focus: Watch the CASE expression evaluate top-to-bottom for each row — Monitor hits >= 100 first ('expensive'), Keyboard hits >= 20 ('moderate'), Notebook falls through to ELSE ('cheap').

Common mistakes

• Wrong WHEN order — first match wins: CASE stops at the first TRUE branch. If you write WHEN price > 0 before WHEN price > 100, every product matches the first branch. Put the most restrictive condition first.
• Omitting ELSE and getting unexpected NULLs: If no WHEN matches and ELSE is missing, CASE returns NULL. Always include ELSE unless you explicitly want NULL for unmatched rows.
• Forgetting END: Every CASE must close with END. Omitting it is a syntax error. When using an alias, write END AS alias_name.

Glossary

evaluate

To calculate or determine the value or outcome of an expression or condition.

branch

A specific path or option in a decision-making process, like one of the possible choices in an if/then structure.

alias

A temporary name given to a table or column in a query to make it easier to read or reference.

Recall questions

• What happens when multiple WHEN conditions are TRUE for the same row?
• What does CASE return if no WHEN matches and there is no ELSE clause?
• What is the difference between searched CASE and simple CASE?

Understanding checks

What tier does Monitor (price 199.99) get, and why not 'premium'?

Monitor gets 'mid-or-above'.

First-match-wins: price >= 20 is TRUE for Monitor (199.99 >= 20), so CASE returns 'mid-or-above' and never evaluates the >= 100 branch. The fix: put WHEN price >= 100 first.

What value does the Notebook row get for dept?

NULL.

Notebook's category is 'Stationery', which matches neither 'Electronics' nor 'Furniture'. With no ELSE clause, CASE returns NULL for unmatched rows.

Practice tasks

Add a size category for quantities

This query shows order items. Modify the SELECT to add a CASE expression that classifies quantity as 'bulk' (quantity >= 3), 'standard' (quantity >= 2), or 'single' (otherwise). Alias it as qty_category. Keep the existing columns and ordering.

Challenge

Label orders by date quarter

Write a query that returns the id and order_date from orders, plus a column called quarter that shows 'Q1' for Jan-Mar, 'Q2' for Apr-Jun, 'Q3' for Jul-Sep, or 'Q4' for Oct-Dec based on the order_date. Use EXTRACT(MONTH FROM order_date) in CASE conditions. Order by id.

Continue learning

Previous: WHERE & Operators

Next: Aggregate Functions

Return to SQL Roadmap