When working with data in SQL, you often need to apply logic similar to “if-else” statements.

This is where the CASE WHEN statement comes in.

It allows you to create conditional logic directly inside your SQL queries making your analysis more flexible and powerful.

In this guide, you’ll learn how to use the CASE WHEN statement step by step with practical examples.

What Is CASE WHEN in SQL?

CASE WHEN is used to apply conditions in SQL queries.

It works like an IF-ELSE statement:

• If a condition is true → return a value
• If not → check the next condition
• If none match → return a default value

Basic Syntax

SELECT column_name,
       CASE 
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE result
       END AS new_column
FROM table_name;

Example 1: Categorizing Sales

SELECT order_id, revenue,
       CASE 
           WHEN revenue > 1000 THEN 'High'
           WHEN revenue BETWEEN 500 AND 1000 THEN 'Medium'
           ELSE 'Low'
       END AS sales_category
FROM orders;

Insight:

Categorizes orders into High, Medium, and Low sales.

Example 2: Creating a New Column

SELECT customer_id,
       CASE 
           WHEN total_orders > 10 THEN 'Loyal'
           ELSE 'New'
       END AS customer_type
FROM customers;

Insight:

Segments customers based on activity.

Example 3: Using CASE WHEN With Aggregation

SELECT 
    COUNT(CASE WHEN status = 'Completed' THEN 1 END) AS completed_orders,
    COUNT(CASE WHEN status = 'Cancelled' THEN 1 END) AS cancelled_orders
FROM orders;

Insight:

Counts different types of orders in one query.

Example 4: Conditional SUM

SELECT 
    SUM(CASE WHEN region = 'West' THEN revenue ELSE 0 END) AS west_revenue,
    SUM(CASE WHEN region = 'East' THEN revenue ELSE 0 END) AS east_revenue
FROM sales;

Insight:

Calculates revenue by region without using GROUP BY.

Example 5: Using CASE WHEN in ORDER BY

SELECT product_name, revenue
FROM products
ORDER BY 
    CASE 
        WHEN revenue > 1000 THEN 1
        ELSE 2
    END;

Insight:

Prioritizes high-revenue products.

Example 6: Handling NULL Values

SELECT customer_id,
       CASE 
           WHEN email IS NULL THEN 'Missing'
           ELSE 'Available'
       END AS email_status
FROM customers;

Insight:

Identifies missing data.

Example 7: Multiple Conditions

SELECT employee_id, salary,
       CASE 
           WHEN salary > 100000 THEN 'High'
           WHEN salary > 50000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_level
FROM employees;

Insight:

Creates salary bands.

When to Use CASE WHEN

CASE WHEN is useful for:

• Data categorization
• Conditional calculations
• Creating new columns
• Business logic inside queries

It is widely used in reporting and dashboards.

Common Mistakes to Avoid

1. Missing ELSE Statement

If you don’t include ELSE, SQL returns NULL when no condition matches.

2. Overcomplicating Logic

Too many conditions can make queries hard to read.

Keep logic simple and clear.

3. Using CASE Instead of WHERE

CASE is for creating values, not filtering rows.

Use WHERE for filtering.

CASE WHEN vs WHERE

• CASE WHEN → Creates new values
• WHERE → Filters rows

Understanding this difference is important.

Real-World Use Cases

• Customer segmentation (Loyal vs New)
• Sales categorization
• KPI calculations
• Data cleaning and labeling

The CASE WHEN statement is one of the most powerful features in SQL.

It allows you to apply logic directly in your queries, making your analysis more flexible and insightful.

For data analysts, mastering CASE WHEN is essential for solving real-world business problems and creating meaningful reports.

FAQs