CASE Statements (Conditional Logic)
CASE is SQL's if-else expression. It evaluates conditions and returns a value based on the first match. Use CASE in SELECT to create computed columns, in ORDER BY for custom sort order, and in aggregate functions for conditional counts.
CASE Syntax Types
- Searched CASE: CASE WHEN condition THEN result ... ELSE default END
- Simple CASE: CASE col WHEN value THEN result ... ELSE default END
- ELSE is optional — returns NULL if no match and ELSE is omitted
- CASE can be used anywhere an expression is valid
- Nested CASE is possible but keep it simple for readability
CASE Examples
-- Price tier label using searched CASE
SELECT
name,
price,
CASE
WHEN price < 100 THEN 'Budget'
WHEN price < 600 THEN 'Mid-Range'
WHEN price < 1000 THEN 'Premium'
ELSE 'Luxury'
END AS price_tier
FROM products;
-- Result:
-- Headphones | 89.99 | Budget
-- Phone | 599.99 | Mid-Range
-- Laptop | 999.99 | Premium
-- Simple CASE (like switch statement)
SELECT
id,
status,
CASE status
WHEN 'pending' THEN '⏳ Waiting'
WHEN 'shipped' THEN '🚚 In Transit'
WHEN 'delivered' THEN '✅ Complete'
ELSE '❓ Unknown'
END AS status_label
FROM orders;
-- CASE in ORDER BY (custom sort order)
SELECT name, status FROM orders
ORDER BY CASE status
WHEN 'pending' THEN 1
WHEN 'shipped' THEN 2
WHEN 'delivered' THEN 3
ELSE 4
END;
-- CASE in aggregate (conditional count)
SELECT
COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending,
COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped
FROM orders;Quick Quiz
Tip
Tip
Practice CASE Statements Conditional Logic in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Relationships define how tables connect through foreign keys
Common Mistake
Warning
A common mistake with CASE Statements Conditional Logic is skipping edge case testing — empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task — (1) Write a working example of CASE Statements Conditional Logic from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- CASE is SQL's if-else expression.
- Searched CASE: CASE WHEN condition THEN result ... ELSE default END
- Simple CASE: CASE col WHEN value THEN result ... ELSE default END
- ELSE is optional — returns NULL if no match and ELSE is omitted