SQL CASE Statement

The CASE statement is SQL's way of implementing conditional logic directly inside a query. It evaluates a series of conditions and returns a specific value based on which condition is true — similar to an if-else structure in programming.

Think of it like a set of instructions: "If the score is above 90, label it 'Excellent'. If it is between 70 and 90, label it 'Good'. Otherwise, label it 'Average'." The CASE statement does this inside the SQL query itself.

Two Forms of the CASE Statement

There are two ways to write a CASE statement:

• Searched CASE — evaluates a condition (expression) for each row.
• Simple CASE — compares a single column to a list of specific values.

The Reference Table

Searched CASE — Using Conditions

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE default_result
END

The ELSE clause is optional but recommended — it handles any row that does not match any of the WHEN conditions. If ELSE is omitted and no condition matches, the CASE returns NULL.

Example 1: Assign a Grade Based on Score

SELECT StudentName, Score,
    CASE
        WHEN Score >= 90 THEN 'A - Excellent'
        WHEN Score >= 75 THEN 'B - Good'
        WHEN Score >= 60 THEN 'C - Average'
        WHEN Score >= 50 THEN 'D - Below Average'
        ELSE 'F - Fail'
    END AS Grade
FROM Students;

Result:

Example 2: Classify Fees Into Brackets

SELECT StudentName, Fees,
    CASE
        WHEN Fees < 40000 THEN 'Economy'
        WHEN Fees BETWEEN 40000 AND 50000 THEN 'Standard'
        ELSE 'Premium'
    END AS FeeCategory
FROM Students;

Result:

Simple CASE — Comparing a Column to Specific Values

Syntax

CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

Example 3: Expand City Abbreviations

SELECT StudentName, City,
    CASE City
        WHEN 'Delhi'   THEN 'North India'
        WHEN 'Mumbai'  THEN 'West India'
        WHEN 'Chennai' THEN 'South India'
        WHEN 'Kolkata' THEN 'East India'
        ELSE 'Other Region'
    END AS Region
FROM Students;

Result:

CASE in ORDER BY — Custom Sort Order

CASE can be used in ORDER BY to sort rows in a custom, non-alphabetical order.

-- Sort students: Fail first, then ascending by grade
SELECT StudentName, Score
FROM Students
ORDER BY
    CASE
        WHEN Score >= 90 THEN 1
        WHEN Score >= 75 THEN 2
        WHEN Score >= 60 THEN 3
        WHEN Score >= 50 THEN 4
        ELSE 5
    END;

This sorts results from 'A - Excellent' to 'F - Fail' — a custom priority order not possible with a simple alphabetical or numeric sort.

CASE in GROUP BY — Conditional Aggregation

CASE inside aggregate functions allows computing multiple aggregations in a single query.

-- Count how many students fall into each grade category
SELECT
    COUNT(CASE WHEN Score >= 90 THEN 1 END) AS GradeA,
    COUNT(CASE WHEN Score >= 75 AND Score < 90 THEN 1 END) AS GradeB,
    COUNT(CASE WHEN Score >= 60 AND Score < 75 THEN 1 END) AS GradeC,
    COUNT(CASE WHEN Score < 60 THEN 1 END) AS FailCount
FROM Students;

Result:

CASE in UPDATE — Conditional Updates

-- Apply different discounts based on fees
UPDATE Students
SET Fees =
    CASE
        WHEN Fees > 55000 THEN Fees * 0.90   -- 10% discount for high fees
        WHEN Fees > 40000 THEN Fees * 0.95   -- 5% discount for medium fees
        ELSE Fees                             -- No discount for low fees
    END;

Nested CASE Statements

A CASE can be placed inside another CASE for multi-level conditions.

SELECT StudentName, Score, City,
    CASE
        WHEN Score >= 75 THEN
            CASE City
                WHEN 'Delhi'  THEN 'Scholarship - Delhi Campus'
                WHEN 'Mumbai' THEN 'Scholarship - Mumbai Campus'
                ELSE 'Scholarship - Other'
            END
        ELSE 'No Scholarship'
    END AS ScholarshipStatus
FROM Students;

Key Points to Remember

• The CASE statement evaluates conditions from top to bottom and returns the result for the first matching condition.
• Always add an ELSE clause to handle rows that do not match any condition — otherwise those rows return NULL.
• CASE can appear in SELECT, WHERE, ORDER BY, GROUP BY, and UPDATE SET.
• CASE inside an aggregate function (COUNT, SUM) enables conditional aggregation — a very powerful pattern.

Summary

The CASE statement brings conditional logic into SQL queries. It evaluates conditions row by row and returns different values based on which condition matches. This makes it possible to classify data, create custom labels, apply different sorting, and even perform conditional aggregation — all without writing separate queries. The CASE statement is one of the most versatile and widely used constructs in practical SQL work.