CASE Expressions - DSA Guide

🧙‍♂️ Introduction: The School Sorting Hat

Imagine you are a freshman entering wizarding school, and you sit on a stool to put on the Sorting Hat:

The hat runs a list of checks:

Rule 1: "IF student is Brave, THEN put them in Gryffindor!"
Rule 2: "IF student is Smart, THEN put them in Ravenclaw!"
Rule 3: "For anyone ELSE, put them in Hufflepuff!"

In SQL, a CASE expression is this exact Sorting Hat! It inspects columns in your rows and assigns a new label or value based on whether they meet your "if-then" rules.

🛠️ The CASE Syntax and Kinds

                    Row Data Column
                          |
                  (Meets Rule 1?)
                  /                            YES               NO
        /                                v                           v
 [Assign Val 1]             (Meets Rule 2?)
                            /                                      YES               NO
                  /                                          v                           v
           [Assign Val 2]             [Assign ELSE Val]

SQL supports two main styles of writing CASE expressions:

1. Searched CASE (Most Flexible)

Evaluates custom logical comparison checks for each branch:

sql

SELECT name,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        ELSE 'C'
    END AS grade
FROM students;

2. Simple CASE (Equivalency Check)

Compares a single column directly with specific candidate values (like a switch-case statement):

sql

SELECT name,
    CASE department
        WHEN 'HR' THEN 'Human Resources'
        WHEN 'ENG' THEN 'Engineering'
        ELSE 'Other'
    END AS dept_name
FROM employees;

💻 Code Examples

Let's categorize items into discount levels based on price.

SQL CASE Query

sql

SELECT name, price,
    CASE 
        WHEN price >= 100.00 THEN 'Expensive'
        WHEN price >= 50.00 THEN 'Moderate'
        ELSE 'Cheap'
    END AS price_category
FROM products;

Multi-Language Execution

⚠️ Common Mistakes

1. Forgetting the END Keyword

Leaving off the closing END keyword. The SQL parser will report a syntax error because it can't find where the conditional block terminates!

Bad: CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail';
Good: CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END;

2. Misordering Overlapping Conditions

SQL checks conditions in order from top to bottom and stops at the first matching rule. If you write ranges in the wrong order, you will get wrong categories!

Bad:
sql
CASE WHEN score >= 50 THEN 'Average' WHEN score >= 90 THEN 'Excellent' -- NEVER REACHED! END
Good:
sql
CASE WHEN score >= 90 THEN 'Excellent' WHEN score >= 50 THEN 'Average' END

🔍 Interview Corner

Q1: Can you use aggregate functions inside a CASE expression?

Yes, you can use aggregate functions inside CASE statements. This is commonly used to do conditional aggregation (e.g. summing sales only for a specific region):

sql

SELECT department,
       SUM(CASE WHEN status = 'Completed' THEN amount ELSE 0 END) AS completed_sales
FROM sales GROUP BY department;

Q2: What happens if none of the WHEN conditions are met and there is no ELSE clause?

If none of the WHEN conditions evaluate to true, and no ELSE fallback branch is specified, the CASE expression will return NULL.

📝 Practice Links

Explore related coding challenges on the platform:

Trips and Users

📝 Summary

CASE is SQL's version of an if-then-else statement.
It lets you create new labels or compute conditional values inline inside a query.
Always terminate the expression block using the END keyword.