Case When – Guided Study Workspace

Introduction: The Need for Conditional Logic in SQL

Real-world data is stored as raw numbers, strings, and codes. A database table might record an employee's salary as 85000 or an order status as 3. But business stakeholders don't want raw numbers; they want meaningful categories:

Salary Categorization:

salary >= 50000 ➔ "High"
salary < 50000 ➔ "Low"

Customer Segmentation:

orders_count >= 10 ➔ "VIP"
orders_count >= 1 ➔ "Active"

In programming languages like Python or JavaScript, you use if...else statements. In SQL, this is achieved using the CASE expression.

What Is CASE WHEN? (Syntax & Anatomy)

The CASE statement evaluates conditions and returns a value when the first condition is met. Here is the fundamental syntax:

SELECT name, salary,
    CASE
        WHEN salary >= 50000 THEN 'High'
        ELSE 'Low'
    END AS salary_level
FROM employees;

Anatomy of the CASE Expression

CASE: Initiates the conditional expression.
WHEN <condition>: The boolean condition tested against the row.
THEN <value>: The value returned if the preceding WHEN condition evaluates to TRUE.
ELSE <default_value>: The fallback value returned if none of the WHEN conditions match.
END: Concludes the CASE expression. (Aliasing with AS column_name gives the output column a readable header).

Diagram 1: Basic Single-Condition CASE Evaluation Flow

Incoming Row
e.g. Salary = 60000

── Evaluates ──▶

Is salary >= 50000?
Boolean Test

── YES ──▶

Return 'High'
Execution Halts

CASE WHEN Step-by-Step Row Evaluation

SQL processes the CASE expression row-by-row. Let's trace how a 3-row dataset is evaluated:

Employee	Salary	Condition Tested	Evaluation	Returned salary_level
Rahul	60000	`60000 >= 50000`	TRUE	'High'
Neha	45000	`45000 >= 50000`	FALSE ➔ Falls to ELSE	'Low'
Priya	80000	`80000 >= 50000`	TRUE	'High'

Multiple WHEN Conditions & Why Order Matters

You can chain as many WHEN clauses as needed to create multi-tier classification logic:

SELECT name, salary,
    CASE
        WHEN salary >= 80000 THEN 'High'
        WHEN salary >= 50000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_tier
FROM employees;

Critical Rule: Top-to-Bottom Short-Circuit Evaluation

SQL checks conditions in exact written order and stops at the first match. If you place WHEN salary >= 50000 before WHEN salary >= 80000, an executive earning $100,000 will match the first condition and be incorrectly labeled 'Medium'! Always list the most specific (highest) conditions first.

Diagram 2: Top-to-Bottom Short-Circuit Waterfall

Step 1: Is salary >= 80000?
If YES ➔ Return 'High' and STOP

Step 2: Is salary >= 50000?
If YES ➔ Return 'Medium' and STOP

Step 3: Fallback ELSE
Return 'Low'

CASE With Comparison Operators

Any standard SQL comparison operator can be utilized inside a WHEN clause:

WHEN score > 90 THEN 'A' (Greater than)
WHEN score >= 80 THEN 'B' (Greater than or equal)
WHEN score < 50 THEN 'Fail' (Less than)
WHEN status = 'active' THEN 'Open' (Equality)
WHEN status <> 'closed' THEN 'In Progress' (Inequality <> or !=)

CASE With AND / OR Compound Logic

You can combine multiple columns using AND and OR boolean operators:

SELECT name, department, salary,
    CASE
        WHEN department = 'Sales' AND salary >= 60000
            THEN 'High Earner - Sales'
        WHEN department = 'Engineering' OR department = 'Product'
            THEN 'Tech Team'
        ELSE 'General Operations'
    END AS employee_category
FROM employees;

CASE With NULL Handling (IS NULL)

When checking for missing or unknown data, you must use IS NULL or IS NOT NULL:

SELECT name,
    CASE
        WHEN salary IS NULL THEN 'Unassigned / Missing'
        ELSE 'Salary Verified'
    END AS audit_status
FROM employees;

Why "= NULL" Fails

In SQL, salary = NULL evaluates to UNKNOWN, which causes the WHEN branch to fail even if the salary is indeed null! Always use IS NULL.

CASE in the SELECT Statement: Derived Columns

The most common place to use CASE is in the SELECT list to generate a calculated or transformed column without altering physical table records:

SELECT
    name,
    salary,
    CASE
        WHEN salary >= 50000 THEN 'Eligible for Bonus'
        ELSE 'Not Eligible'
    END AS bonus_status
FROM employees;

CASE in ORDER BY: Custom Business Sorting

Standard alphabetical or numerical sorting (ASC / DESC) cannot order custom domain values like Urgent ➔ High ➔ Normal ➔ Low. You can place a CASE expression directly inside ORDER BY to assign custom sort priorities:

SELECT ticket_id, title, priority
FROM support_tickets
ORDER BY
    CASE
        WHEN priority = 'Urgent' THEN 1
        WHEN priority = 'High' THEN 2
        WHEN priority = 'Normal' THEN 3
        ELSE 4
    END;

CASE in Aggregate Functions: Conditional Aggregation

Aggregate functions like COUNT() and SUM() naturally ignore NULL values. By pairing CASE inside an aggregate function, you can count or sum specific subsets in a single query without multiple queries:

SELECT
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN department = 'Sales' THEN 1 END) AS sales_count,
    COUNT(CASE WHEN department = 'Engineering' THEN 1 END) AS eng_count
FROM employees;

CASE vs. WHERE: The Fundamental Distinction

Core Conceptual Comparison

WHERE determines which rows make it into the result (it filters/deletes rows from the stream).
CASE determines what value appears for each row (it keeps every row intact and produces a calculated label).

Query Approach	SQL Example	Effect on Rows
WHERE Clause (Filter)	`SELECT name FROM emp WHERE salary >= 50000;`	Removes all rows where salary < 50000.
CASE Expression (Transform)	`SELECT name, CASE WHEN salary >= 50000 THEN 'High' ELSE 'Low' END FROM emp;`	Preserves 100% of rows and labels each row.

Diagram 3: Row Filtering (WHERE) vs Row Transformation (CASE)

WHERE salary >= 50000

Row 1 (60k) ➔ ✅ KEPT
Row 2 (40k) ➔ ❌ DROPPED
Row 3 (80k) ➔ ✅ KEPT

Result: 2 Rows Total

CASE WHEN salary >= 50000...

Row 1 (60k) ➔ 'High'
Row 2 (40k) ➔ 'Low'
Row 3 (80k) ➔ 'High'

Result: 3 Rows Total (All Preserved)

Common SQL CASE WHEN Mistakes

1. Forgetting the END Keyword

Every CASE must have a matching END. Omitting it triggers a fatal SQL syntax error.

2. Inverted Condition Ordering

Writing broader conditions before narrower ones (e.g. >= 50000 before >= 80000), causing high values to be caught early.

3. Missing ELSE Fallback

If no conditions match and ELSE is absent, SQL returns NULL unexpectedly.

4. Incompatible Data Types

Returning text from one branch (THEN 'Yes') and an integer from another (ELSE 0). All branches should return compatible types.

Practical Query Exercises

Exercise 1: 3-Tier Employee Compensation Banding

SELECT name, salary,
    CASE
        WHEN salary >= 90000 THEN 'Band A (Executive)'
        WHEN salary >= 60000 THEN 'Band B (Professional)'
        ELSE 'Band C (Associate)'
    END AS compensation_band
FROM employees;

Exercise 2: Priority Sorting for Support Tickets

SELECT ticket_id, customer_name, status
FROM tickets
ORDER BY
    CASE
        WHEN status = 'Critical' THEN 1
        WHEN status = 'Pending' THEN 2
        ELSE 3
    END ASC;

⚡ Live Interactive SQL CASE WHEN Lab

Test live conditional transformations, row-by-row short-circuit evaluations, and fix condition order bugs

Select Scenario:

Generated SQL Statement

SELECT name, department, salary,
  CASE
    WHEN salary >= 90000 THEN 'Executive / Tier 1'
    WHEN salary >= 60000 THEN 'Mid-Level / Tier 2'
    ELSE 'Associate / Tier 3'
  END AS salary_tier
FROM employees;

Evaluated Output Table (Click any row to inspect evaluation)6 Rows Processed

id	name	department	salary	rating	Calculated CASE Result
1	Rahul Sharma	Sales	$95,000	Outstanding	Executive / Tier 1
2	Priya Patel	Engineering	$120,000	Exceeds	Executive / Tier 1
3	Amit Verma	Sales	$62,000	Meets	Mid-Level / Tier 2
4	Neha Singh	Marketing	$45,000	Meets	Associate / Tier 3
5	Rohan Gupta	Engineering	$78,000	Exceeds	Mid-Level / Tier 2
6	Sneha Rao	Support	NULL	Pending	Unranked

🔍 Evaluation Trace for Row #1 (Rahul Sharma):

• Raw Input: Salary = $95,000, Department = Sales
• Triggered Branch: WHEN salary >= 90000
• Output Value: Executive / Tier 1

SQL CASE WHEN Best Practices

Always Order From Most Specific to Broadest: Place restrictive conditions (e.g. >= 90000) before permissive ones (>= 50000).
Always Include an Explicit ELSE: An explicit ELSE 'Other' prevents unintended NULL values from polluting downstream calculations.
Always Give the Expression an Alias: Use END AS clear_column_name so query consumers receive descriptive headers.
Keep Data Types Consistent: Ensure all THEN and ELSE clauses return identical data types (strings with strings, numbers with numbers).

What You Should Know Now: Checklist

✓CASE Syntax: Starts with CASE, tests with WHEN, returns with THEN, defaults with ELSE, ends with END.
✓Short-Circuit Order: Evaluates top-to-bottom and stops at the very first true branch.
✓CASE in SELECT: Generates calculated derived columns without altering underlying tables.
✓CASE in ORDER BY: Enables custom business domain sorting priorities.
✓CASE vs WHERE: WHERE filters rows out of the query; CASE preserves rows and computes a label.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL CASE WHEN

Test your understanding of conditional logic, condition ordering, short-circuit evaluation, NULL handling, and CASE vs WHERE differences.

Question 1 of 8Current Score: 0 / 0

What is the primary purpose of the CASE WHEN expression in SQL?