CASE in SELECT: Creating Derived Columns
What You'll Learn
You'll learn how to use CASE expressions in the SELECT clause to create brand-new columns with conditional logic based on existing data.
The Core Idea
Now that you understand CASE expressions (both simple and searched), you can put them to work creating derived columns—columns that don't exist in your original table but are calculated on-the-fly based on conditions you define.
Think of it like adding a custom label or category to each row. Imagine you have a list of students with test scores, but no column for pass/fail status. You can create one using CASE in your SELECT statement.
Creating Conditional Columns
The basic pattern looks like this:
SELECT
name,
score,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
WHEN score >= 60 THEN 'D'
ELSE 'F'
END AS letter_grade
FROM students;
The new letter_grade column doesn't exist in students—you're creating it based on the score column. Each row gets its grade calculated independently.
Multiple Derived Columns
You can create several derived columns in one query:
SELECT
product_name,
price,
CASE
WHEN price < 10 THEN 'Budget'
WHEN price < 50 THEN 'Standard'
ELSE 'Premium'
END AS price_tier,
CASE
WHEN stock_quantity = 0 THEN 'Out of Stock'
WHEN stock_quantity < 10 THEN 'Low Stock'
ELSE 'In Stock'
END AS stock_status
FROM products;
Key Takeaway: CASE in SELECT lets you create new, conditional columns that categorize, label, or transform existing data without modifying the underlying table—perfect for reporting and analysis.