SQL’s CASE WHEN expression lets you add conditional logic to SELECT, UPDATE, and ORDER BY clauses without writing multiple queries. It evaluates conditions in order and returns the first matching result, making dynamic labels, custom sorting, and data cleaning simple and efficient.
CASE WHEN is SQL’s built-in conditional expression. It works like an IF-THEN-ELSE statement, letting you return different values based on one or more boolean conditions inside a single query.
Using one CASE WHEN keeps logic in a single pass over the data, reducing query count, cutting I/O, and making maintenance easier than chaining UNIONs or subqueries.
Basic syntax starts with CASE, lists WHEN ... THEN pairs, adds an optional ELSE, and ends with END. You can place it anywhere an expression is allowed.
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END AS alias_name
Put CASE WHEN in a SELECT list to map raw codes into friendly text, e.g., 1 → ‘Active’, 0 → ‘Inactive’.
Wrap a CASE WHEN inside SUM, COUNT, or AVG to tally only the rows that meet each condition, enabling multi-condition dashboards without POST-processing.
Yes. CASE WHEN in ORDER BY assigns numeric sort buckets, letting you prioritize statuses like ‘critical’, ‘warning’, ‘ok’ without changing data.
CASE WHEN can coalesce messy values into standardized categories during SELECT INTO or INSERT SELECT steps, fixing data on the fly.
Searched CASE uses independent boolean tests (WHEN age > 65). Simple CASE compares one expression to literals (CASE status WHEN 'A' THEN ...). Choose searched for complex predicates.
Yes. You can embed a CASE inside another CASE to model multi-level logic, but excessive nesting hurts readability—favor CTEs when rules grow.
CASE is computed row-by-row and is usually cheap. Performance issues arise only if conditions include non-sargable functions or subqueries.
Evaluate most-common conditions first, keep ELSE explicit, alias clearly, and comment on complex logic. Test edge cases to avoid NULL surprises.
If logic belongs in the application layer or requires heavy procedural flow, use stored procedures or client code instead of huge CASE ladders.
ANSI CASE works the same in PostgreSQL, MySQL, SQL Server, and Snowflake. Only Oracle’s DECODE differs, but CASE is also supported.
Galaxy’s galaxy.io/features/ai" target="_blank" id="">AI copilot auto-completes CASE templates, suggests conditions from table metadata, and flags unreachable WHEN branches, cutting trial-and-error time.
CASE WHEN is your Swiss-army knife for conditional logic in SQL. Master its syntax, use it for labeling, aggregation, and sorting, and follow best practices to keep queries readable and fast.
CASE WHEN is ANSI standard and portable; IF is vendor-specific and usually limited to procedural blocks, not plain SELECTs.
Most engines allow hundreds. Practical readability, not the spec, sets the real limit—split logic into multiple CASEs or a lookup table when it grows.
You cannot index computed columns directly in the SELECT list, but some databases let you create indexed computed columns or materialized views.
Yes. Use CASE WHEN in the SET clause to update different rows with different values in one command.
.png)
.avif){kind=logo}
