The SQL CASE WHEN statement is used to handle conditional logic in queries by returning different results based on specified conditions. It functions like an if-else statement and can be applied in SELECT, WHERE, ORDER BY, and HAVING clauses. For example, it can classify sales as “Low”, “Medium”, or “High” based on amount ranges. Additionally, CASE WHEN supports data transformation, summary reporting, and conditional aggregation when paired with functions like SUM() or COUNT(). Best practices include testing conditions in order, avoiding overly long blocks, ensuring no overlapping conditions accidentally skip matches, and simplifying with helper tables or functions where possible.

When writing SQL queries, sometimes you need to handle conditional logic — like categorizing data or transforming values based on specific conditions. That’s where the CASE WHEN statement comes in handy. It works a bit like an if-else statement in programming languages and lets you return different results depending on your data.

Basic Syntax of CASE WHEN

At its core, the CASE WHEN structure is straightforward. You define one or more conditions using WHEN, specify what should happen with THEN, and finish it off with END. Here's the general format:

SELECT column1,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE default_result
       END AS new_column_name
FROM table_name;

The ELSE part is optional but useful for catching any unmatched cases. If you don’t include it and no condition matches, the result will be NULL.

A quick example: say you have a sales table and want to label each sale as “Low”, “Medium”, or “High” based on the amount:

SELECT sale_amount,
       CASE
           WHEN sale_amount < 100 THEN 'Low'
           WHEN sale_amount BETWEEN 100 AND 500 THEN 'Medium'
           ELSE 'High'
       END AS sale_category
FROM sales;

This gives you a new column that classifies each row based on the value in sale_amount.

Using CASE in Different Parts of a Query

One nice thing about CASE WHEN is that you can use it in various parts of a SQL query — not just in the SELECT clause.

• In SELECT: As shown earlier, this is common for creating derived columns.
• In WHERE: Useful when filtering rows based on conditional logic.
• In ORDER BY: Helps sort data dynamically based on certain rules.
• In HAVING (for grouped data): Can help filter aggregated results conditionally.

Here’s an example using CASE inside ORDER BY to sort users by custom priority:

SELECT name, role
FROM users
ORDER BY 
    CASE
        WHEN role = 'Admin' THEN 1
        WHEN role = 'Editor' THEN 2
        ELSE 3
    END;

This way, admins come first, editors second, and everyone else last — without sorting alphabetically.

Common Use Cases and Tips

There are a few typical scenarios where CASE WHEN shines:

• Creating summary reports: For instance, grouping age ranges into buckets like “Under 18”, “18–30”, etc.
• Data transformation: Like converting NULLs to “N/A” or turning boolean flags into readable labels.
• Conditional aggregation: Combine CASE with aggregate functions like SUM() or COUNT() to count only matching rows.

A few tips to keep in mind:

• Always test your conditions in order — once a WHEN condition matches, the rest are skipped.
• Try to avoid overly long CASE blocks; they get hard to read.
• Be careful with overlapping conditions — make sure your logic doesn’t accidentally skip expected matches.

If you're dealing with many similar conditions, consider simplifying with helper tables or functions if your database supports them.

That’s how you work with CASE WHEN in SQL — flexible enough to handle basic labeling and complex logic alike. It’s a must-know tool for anyone doing data reporting or transformation directly in the database.

The above is the detailed content of How to use the CASE WHEN statement in a query?. For more information, please follow other related articles on the PHP Chinese website!