CASE is SQL's conditional expression — it returns different values based on conditions, like an if/else within a query. It's used in SELECT (computed/conditional columns), ORDER BY, aggregates, and more — adding conditional logic to queries.
name,
age,
age
age
age_group
users;
The searched CASE evaluates conditions in order, returning the value of the first matching WHEN. ELSE provides a default (without it, unmatched rows get NULL).
SELECT name,
CASE status -- simple form — compares `status` to each value
WHEN 'A' THEN 'Active'
WHEN 'I' THEN 'Inactive'
ELSE 'Unknown'
END AS status_label
FROM users;
-- count/sum conditionally (a very useful pattern)
SELECT
COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count,
SUM(CASE WHEN type = 'sale' THEN amount ELSE 0 END) AS sales_total
FROM orders;
-- → multiple conditional counts/sums in ONE query (pivoting-like)
Using CASE inside aggregates lets you compute multiple conditional counts or sums in a single query — a powerful technique for reporting (effectively pivoting data).
ORDER BY CASE status
WHEN 'urgent' THEN 1
WHEN 'normal' THEN 2
ELSE 3
END; -- custom priority sort
The CASE expression is a versatile, commonly-needed SQL feature for adding conditional logic to queries — transforming, categorizing, and conditionally computing values directly in SQL, so understanding it is valuable for real-world querying and reporting.
Knowing how to use CASE in SELECT (creating conditional/computed columns — like categorizing ages into groups, mapping codes to labels) is a frequent need for shaping query output.
Particularly powerful is using CASE inside aggregates (conditional counting and summing — like counting active vs inactive in one query, or summing only certain types) — a key technique for reporting and analytics that effectively pivots data, computing multiple conditional metrics in a single efficient query. CASE in ORDER BY enables custom sort orders (priority-based sorting).
Understanding both the searched form (evaluating conditions) and simple form (matching a value), and the various contexts where CASE applies, covers the practical uses.
Since adding conditional logic — categorizing data, conditional aggregation for reports, custom sorting — is a common need in querying and especially in reporting/analytics, and since CASE is the standard, flexible way to express this logic directly in SQL (avoiding pulling data into application code for simple transformations), mastering the CASE expression is useful, frequently-applied knowledge that adds expressive power to queries, particularly valuable for the conditional aggregation patterns central to SQL-based reporting and data analysis.