SQL case when: Conditional Logic in Queries

SQL is like storytelling for your database. Every query tells a tale of what you’re trying to find out. But what if your story needs a little twist, like an “if this, then that” kind of thing? That’s where CASE WHEN steps in! These handy words let you add logic directly in your SQL queries, changing how data appears based on rules you create.

TLDR:

• SQL CASE WHEN lets you add conditional logic into your queries.
• It’s similar to “if-else” in traditional programming.
• You can customize results based on rules you define.
• It’s great for things like labeling values, handling missing info, or grouping data.

What is CASE WHEN, Really?

Think of CASE WHEN like a decision-maker in your SQL query. It asks a question and acts based on the answer.

Here’s a simple structure:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE fallback_result
END

You can use it in a SELECT clause, WHERE clause, or even ORDER BY clause. It’s that flexible!

Let’s Jump into a Quick Example

Imagine you have a table of employees and their salaries. You want to categorize them:

SELECT name,
       salary,
       CASE
         WHEN salary > 80000 THEN 'High'
         WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
         ELSE 'Low'
       END as salary_level
FROM employees;

This will add a new column called salary_level where each employee is labeled “High”, “Medium”, or “Low” based on how much they make. Neat, huh?

Why Use CASE WHEN? Real Reasons You’ll Love It

• Data Cleanup: Fix or re-label messy data in real-time.
• Custom Sorting: Order stuff how you want, not just numerically or alphabetically.
• Easy Categorization: Group or label values based on rules.
• Conditional Aggregations: Count or sum only when certain conditions are met.

Fun Use Case: Grade Assignment

Say you have exam scores and want to assign a letter grade. Here’s how:

SELECT student_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 grade
FROM exam_results;

Just like your teachers did. Only now you are the grader!

CASE WHEN Inside Aggregates

Want to count how many customers bought more than $100 worth of stuff? Combine CASE WHEN with SUM() or COUNT():

SELECT
  COUNT(CASE WHEN total_spent > 100 THEN 1 END) as big_spenders,
  COUNT(CASE WHEN total_spent <= 100 THEN 1 END) as small_spenders
FROM customers;

Each CASE is a filter inside the COUNT().

CASE WHEN in WHERE Clauses? Sneaky!

Normally, you use it in the SELECT part. But you can sneak it into WHERE too!

SELECT *
FROM orders
WHERE
  CASE
    WHEN status = 'delivered' THEN 1
    WHEN status = 'in transit' THEN 1
    ELSE 0
  END = 1;

However, be careful. Performance can dip if overused in WHERE. Use with care!

Bonus: Nested CASE WHEN

Yes, you can put CASE inside CASE. Mind = blown!

SELECT student_name,
       score,
       CASE
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN
            CASE
              WHEN score >= 85 THEN 'B+'
              ELSE 'B'
            END
         ELSE 'Below B'
       END as grade
FROM exam_results;

You now have the power of mini decision trees in one column.

Gotchas and Tips

• Watch your order: First matching WHEN wins. Later ones are ignored.
• Always end with ELSE: It’s your safety net if nothing matches.
• NULL is tricky: WHEN column = NULL won’t work. Use IS NULL instead.

Real-Life Example: Sales Territories

Your company handles different regions. You want to label orders:

SELECT order_id,
       region,
       CASE
         WHEN region IN ('NY', 'NJ') THEN 'East Coast'
         WHEN region IN ('CA', 'WA') THEN 'West Coast'
         ELSE 'Other'
       END as territory
FROM sales;

Your coworkers will love how readable this is. It turns codes into clear categories.

Can I Use THIS in ORDER BY?

Absolutely! Customize sort order, like so:

SELECT product_name,
       stock_count
FROM inventory
ORDER BY
  CASE
    WHEN stock_count = 0 THEN 1
    ELSE 0
  END;

Guess what — all out-of-stock items will show at the top. Boom!

Use CASE WHEN in UPDATEs Too

Want to update values based on conditions? Easy.

UPDATE employees
SET bonus = 
  CASE
    WHEN performance_rating = 'Excellent' THEN 1000
    WHEN performance_rating = 'Good' THEN 500
    ELSE 100
  END;

It’s like automatic reward distribution!

Final Thoughts

Think of CASE WHEN as your SQL sidekick. It adds logic where logic usually isn’t allowed. With it, you can:

• Add intelligent labels
• Categorize data
• Make reports sparkle

Start experimenting with it in your queries. You’ll wonder how you ever lived without it.

Have fun deciding what happens WHEN!