In this lesson, you will learn how to create "if-then" logic directly within your SQL queries to transform raw data into categorized insights.
The Concept of Mapping
Up until now, you have retrieved data exactly as it is stored in your tables. However, business needs often require us to classify data on the fly. For example, if you have a table of product prices, you might want to label them as "Budget," "Standard," or "Premium" based on their cost.
Instead of exporting the data to an external tool to perform this logic, we use the CASE expression. This is the SQL equivalent of an if-else statement found in programming languages. It allows you to evaluate specific conditions for each row and assign a custom value depending on the result.
How it Works
A CASE expression acts as a column transformation. It evaluates conditions in order and stops as soon as it finds a match. If no conditions are met, you can provide a default value using ELSE.
Consider a scenario where we categorize customers based on their account age:
SELECT
username,
CASE
WHEN account_age_days < 30 THEN 'New User'
WHEN account_age_days BETWEEN 30 AND 365 THEN 'Active User'
ELSE 'Veteran'
END AS user_status
FROM users;
In this example, the database inspects the account_age_days for every single row. If the value is less than 30, it maps that row to 'New User'. If it falls between 30 and 365, it maps to 'Active User'. Anything else is labeled as a 'Veteran'.
Notice the AS keyword—we use this to alias our resulting column so that our output is easy to read. The original data in the table remains unchanged; this mapping only exists for the duration of this specific query. This approach is powerful because it allows you to simplify complex data points into readable categories before the results even reach your application.
Key Takeaway: The CASE expression acts as a procedural decision tree, allowing you to categorize data dynamically during extraction without altering the underlying table structure.