Case Expressions and Conditional Logic

In this lesson, you will learn how to use CASE expressions to perform procedural-style branching logic directly within your SQL queries to transform raw data into more meaningful categories.

Moving Beyond Simple Retrieval

Up until now, you have used SELECT to pull values exactly as they are stored in your tables. However, data in a database is often "raw"—it might contain numeric codes, status flags, or ranges that aren't very readable for human users. Instead of writing separate programs to clean this data after pulling it out of the database, you can use a CASE expression to handle the logic inside the query itself.

The Anatomy of a CASE Expression

A CASE expression acts like a "switch" or an "if-else" statement. It evaluates a set of conditions and returns a specific value as soon as it finds a match.

Imagine you have a table of Products with a price column. You want to label them based on their cost:

SELECT 
    product_name,
    CASE 
        WHEN price < 10 THEN 'Budget'
        WHEN price BETWEEN 10 AND 50 THEN 'Standard'
        ELSE 'Premium'
    END AS price_category
FROM Products;

How it Works

1. The WHEN clause: The database checks the condition. If it is true, the CASE statement stops searching and returns the value specified after the THEN.
2. The ELSE clause: This acts as a catch-all. If none of the WHEN conditions are met, the database returns the value in the ELSE branch. If you omit the ELSE and no conditions match, the result will be NULL.
3. The END alias: Because CASE expressions generate a new computed column, we use an alias (e.g., AS price_category) to give the output column a descriptive name.

This approach is powerful because it allows you to normalize your output values on the fly without changing the underlying data structure of your tables. You are effectively shifting the burden of "translation" from your application code into the database layer, which keeps your data transformations centralized and consistent.

Key Takeaway: The CASE expression allows you to dynamically classify or transform data during retrieval by evaluating conditional branches, turning raw database values into clean, readable information.