CASE Statements in Queries

The Anatomy of a CASE Statement

At its core, a CASE statement goes through a series of conditions and returns a value when the first condition is met. The structure is simple and mirrors natural language:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

The ELSE part is optional. If no conditions are met and there is no ELSE, the statement will return NULL. The entire block must conclude with the END keyword.

This flowchart provides a great visual of how SQL processes a CASE statement.

To see this structure in action, let's start with a video that breaks down the syntax and execution logic.

Two critical points from the video to always remember are:

1. Order Matters: SQL stops at the first WHEN clause that evaluates to true. If a value meets the criteria for multiple WHEN statements, only the first one it encounters will be applied. Therefore, you should always write your conditions from most specific to least specific.
2. The ELSE Clause: Including an ELSE clause is a best practice to handle all other cases and prevent unintentionally creating NULL values in your new column.

Use Case 1: Creating Categories

The most common use of CASE is in the SELECT clause to categorize data and create a new descriptive column. This is a fundamental part of data transformation, often used to make raw data more interpretable for analysis or visualization.

For instance, you might want to transform raw sales numbers into meaningful tiers like 'High', 'Medium', and 'Low'. Let's see how this is done.

Here's an example of what that query might look like, using a CTE for readability:

WITH SalesWithCategory AS (
    SELECT
        order_id,
        sales,
        CASE
            WHEN sales > 50 THEN 'High'
            WHEN sales > 20 THEN 'Medium'
            ELSE 'Low'
        END AS sales_category
    FROM sales_orders
)
SELECT
    sales_category,
    SUM(sales) AS total_sales,
    COUNT(order_id) AS number_of_orders
FROM SalesWithCategory
GROUP BY sales_category;

A Quick but Important Rule

One rule you must follow when using CASE is that all possible outcomes—that is, all values in your THEN and ELSE clauses—must be of the same or compatible data types. You cannot have one THEN return a string ('High') and another return a number (100).

Use Case 2: Conditional Calculations and Aggregations

Beyond simple categorization, CASE statements are incredibly powerful for performing calculations conditionally.

Conditional Calculations

Imagine you need to give employees a salary raise, but the percentage depends on their job title. A CASE statement is perfect for this.

The query from the video looks like this:

SELECT
    first_name,
    last_name,
    job_title,
    salary,
    CASE
        WHEN job_title = 'Salesman' THEN salary * 1.10
        WHEN job_title = 'Accountant' THEN salary * 1.05
        WHEN job_title = 'HR' THEN salary * 1.01
        ELSE salary * 1.03
    END AS salary_after_raise
FROM employee_salary;

Conditional Aggregations

You can also place a CASE statement inside an aggregate function like SUM() or COUNT() to perform "conditional aggregations." This allows you to create pivot-table-like summaries in a single query. For example, you can calculate the sum of sales for different regions in separate columns instead of separate rows.

SELECT
    SUM(CASE WHEN region = 'North' THEN sales ELSE 0 END) AS north_sales,
    SUM(CASE WHEN region = 'South' THEN sales ELSE 0 END) AS south_sales,
    SUM(CASE WHEN region = 'East' THEN sales ELSE 0 END) AS east_sales,
    SUM(CASE WHEN region = 'West' THEN sales ELSE 0 END) AS west_sales
FROM orders;

The next video demonstrates a similar technique using COUNT to count only the orders that meet a specific condition, without filtering out the other rows.

CASE in Other Clauses

While most commonly used in SELECT, CASE is versatile and can be used in other parts of your query, such as ORDER BY and WHERE.

Here’s a summary of those use cases:

• In ORDER BY: To implement custom sorting logic. For example, you could sort active users by their last login date but sort inactive users by their registration date.

  SELECT user_id, status, last_login
  FROM users
  ORDER BY
      CASE
          WHEN status = 'active' THEN last_login
          ELSE registration_date
      END DESC;
  

• In WHERE: To apply different filtering logic based on some condition. This is less common but can be useful in complex scenarios.

  SELECT *
  FROM sales
  WHERE
      CASE
          WHEN category = 'furniture' THEN sales > 100
          WHEN category = 'office' THEN sales > 50
          ELSE sales > 20
      END;
  

  This is equivalent to writing (category = 'furniture' AND sales > 100) OR (category = 'office' AND sales > 50) OR ..., which might be more readable in some cases.

Conclusion

You've now mastered the CASE statement, a fundamental tool for applying conditional logic in SQL. You can now write more intelligent queries that transform, categorize, and calculate data based on rules you define. This moves you beyond simple data retrieval into true data manipulation and preparation.

Key Takeaways:

• The CASE statement applies if-then-else logic directly within a SQL query.
• The syntax is CASE WHEN ... THEN ... ELSE ... END.
• The order of WHEN conditions is critical, as SQL executes the THEN for the first true condition it finds.
• The most common uses are in the SELECT clause for categorization and inside aggregate functions for conditional aggregation.
• CASE can also be used in ORDER BY for custom sorting and in WHERE for conditional filtering.
• All THEN/ELSE outcomes must have compatible data types.

In our next and final lesson of this module, we will focus on handling NULL values. We briefly saw that CASE WHEN column IS NULL ... is one way to manage them. Next time, we'll explore the specialized functions COALESCE and NULLIF, which provide more concise and efficient ways to handle NULLs in your data.