Searched vs Simple CASE
Two ways to write CASE.
Two Ways to Write CASE
SQL gives you two flavors of the CASE expression: the Simple CASE and the Searched CASE. Both return a value based on conditions, but they differ in how those conditions are written.
By the end of this lesson you will know when to reach for each form and how to avoid common mistakes when mixing them up.
Simple CASE — The Idea
A Simple CASE compares one expression to a list of values. Think of it like a lookup table: you pick one column or value on the left, then list what that value equals in each WHEN branch.
Syntax: CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE default END
SELECT
product_name,
category_id,
CASE category_id
WHEN 1 THEN 'Electronics'
WHEN 2 THEN 'Clothing'
WHEN 3 THEN 'Books'
ELSE 'Other'
END AS category_name
FROM products;All lessons in this course
- The CASE Expression
- Searched vs Simple CASE
- Bucketing and Labeling Data
- CASE in ORDER BY and Aggregates