What Is SQL CASE WHEN?

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.

1
minute read
Learning
June 10, 2025
Galaxy Team
Sign up for the latest notes from our team!
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.
SQL CASE WHEN lets you return different values per row by evaluating conditions in order and outputting the first match, with an optional ELSE for unmatched rows.

Table of Contents

What Is SQL CASE WHEN?

SQL CASE WHEN returns a value based on conditional logic, similar to IF-THEN-ELSE statements in programming, all within one query.

What Does CASE WHEN Do in SQL?

CASE WHEN evaluates conditions sequentially and returns the first matching result, enabling row-level conditional logic without procedural code.

When Should I Use CASE WHEN?

Use CASE WHEN to bucket values, create categorical labels, replace NULLs, pivot flags, or drive conditional aggregations without extra joins or subqueries.

How Does CASE WHEN Syntax Work?

The searched form is CASE WHEN condition THEN value […] ELSE value END. The simple form is CASE expression WHEN value THEN result […] END.

What Is a Basic CASE Expression?

Basic CASE compares an expression to static values. Example: CASE status WHEN 'A' THEN 'Active' ELSE 'Inactive' END.

What Is a Searched CASE Expression?

Searched CASE tests Boolean conditions. Example: CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END.

How Do I Use CASE WHEN in SELECT?

Place CASE WHEN in the SELECT list to create calculated columns on the fly, keeping the query set-based and performant.

Can I Combine CASE WHEN with Aggregations?

Yes. Wrap conditions inside SUM or COUNT to perform conditional aggregation, enabling pivot-style metrics in one pass over the data.

How Do I Use CASE WHEN in ORDER BY and GROUP BY?

Include CASE WHEN directly in ORDER BY or refer to its alias to sort or group results based on derived categories.

How Do I Nest CASE WHEN?

Nest CASE WHEN expressions inside each other for multi-level logic, but keep depth shallow for readability.

What Are Best Practices for CASE WHEN?

Keep conditions mutually exclusive, add an ELSE for clarity, cast results to a single data type, and document complex logic with comments.

Conclusion: Key Takeaways on CASE WHEN

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.

Frequently Asked Questions (FAQs)

Can CASE WHEN appear in WHERE?

Technically yes, but it hurts readability. Prefer simple predicates or use derived tables and reference the CASE result outside WHERE.

Is CASE WHEN supported in all SQL dialects?

Yes. ANSI-SQL defines CASE WHEN, and major engines (PostgreSQL, MySQL, SQL Server, Snowflake, BigQuery, Oracle) implement it.

Does CASE WHEN impact performance?

CASE WHEN is evaluated per row during query execution and is usually fast. Indexes aren’t used inside CASE, so test complex logic.

Can I use parameters inside CASE WHEN?

Yes. Bind variables or session parameters can be compared inside CASE WHEN just like literals, keeping queries reusable and safe.

Start Vibe Querying with Galaxy Today!
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.

Check out our other posts!

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo