SQL CASE WHEN returns custom values based on row-level conditions in a single query. It works like an IF-THEN-ELSE statement, letting you label, bucket, or transform results without multiple queries or joins. Use CASE WHEN inside SELECT, ORDER BY, GROUP BY, or HAVING to create dynamic, readable logic that runs efficiently on the database engine.
SQL CASE WHEN returns a value based on conditional logic, similar to IF-THEN-ELSE statements in programming, all within one query.
CASE WHEN evaluates conditions sequentially and returns the first matching result, enabling row-level conditional logic without procedural code.
Use CASE WHEN to bucket values, create categorical labels, replace NULLs, pivot flags, or drive conditional aggregations without extra joins or subqueries.
The searched form is CASE WHEN condition THEN value […] ELSE value END. The simple form is CASE expression WHEN value THEN result […] END.
Basic CASE compares an expression to static values. Example: CASE status WHEN 'A' THEN 'Active' ELSE 'Inactive' END.
Searched CASE tests Boolean conditions. Example: CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END.
Place CASE WHEN in the SELECT list to create calculated columns on the fly, keeping the query set-based and performant.
Yes. Wrap conditions inside SUM or COUNT to perform conditional aggregation, enabling pivot-style metrics in one pass over the data.
Include CASE WHEN directly in ORDER BY or refer to its alias to sort or group results based on derived categories.
Nest CASE WHEN expressions inside each other for multi-level logic, but keep depth shallow for readability.
Keep conditions mutually exclusive, add an ELSE for clarity, cast results to a single data type, and document complex logic with comments.
CASE WHEN is SQL’s built-in conditional powerhouse. Master it to write cleaner, faster, and more maintainable queries while letting the database do the heavy lifting.
Technically yes, but it hurts readability. Prefer simple predicates or use derived tables and reference the CASE result outside WHERE.
Yes. ANSI-SQL defines CASE WHEN, and major engines (PostgreSQL, MySQL, SQL Server, Snowflake, BigQuery, Oracle) implement it.
CASE WHEN is evaluated per row during query execution and is usually fast. Indexes aren’t used inside CASE, so test complex logic.
Yes. Bind variables or session parameters can be compared inside CASE WHEN just like literals, keeping queries reusable and safe.
.png)
.avif){kind=logo}
