SQL CASE Expression Explained with Practical Examples

For SQL users who already write SELECT statements and want to turn a column's value into a label or a different output value inside the query itself, without a separate UPDATE step or application code.

What Is the SQL CASE Expression?

A report that needs one column's raw value turned into a label at query time has no if/then keyword to reach for outside a CASE expression. CASE evaluates a list of conditions in order and returns the result tied to the first one that matches, or the ELSE value when none of them do.

SQL has two forms of the expression: simple CASE, which compares one expression against a list of values, and searched CASE, which evaluates independent boolean conditions:

-- Simple CASE
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE resultN
END

-- Searched CASE
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE resultN
END

A Students table with a numeric grade per row makes the pattern concrete:

Turning that Grade column into a Performance label for a report needs one CASE expression in the SELECT list:

SELECT Name,
    CASE
        WHEN Grade >= 85 THEN 'Excellent'
        WHEN Grade >= 75 THEN 'Good'
        ELSE 'Average'
    END AS Performance
FROM Students;

CASE in SELECT with Comparison Operators

The simple form matches a column against individual literal values instead of evaluating a range, which suits a status code or a fixed category rather than a numeric threshold. Labeling only the row where Grade equals 85 as Perfect, and everything else as Other:

SELECT Name,
    CASE Grade
        WHEN 85 THEN 'Perfect'
        ELSE 'Other'
    END AS Label
FROM Students;

CASE with ORDER BY

The same expression sorts rows by a computed rank instead of raw column order. Reusing the Performance labels to put Excellent rows first, Good next, and Average last:

SELECT Name, Grade,
    CASE
        WHEN Grade >= 85 THEN 'Excellent'
        WHEN Grade >= 75 THEN 'Good'
        ELSE 'Average'
    END AS Performance
FROM Students
ORDER BY
    CASE
        WHEN Grade >= 85 THEN 1
        WHEN Grade >= 75 THEN 2
        ELSE 3
    END;

CASE with GROUP BY

The same labels drive grouping too: put the CASE expression in the GROUP BY clause and every row sharing a label aggregates together.

SELECT
    CASE
        WHEN Grade >= 85 THEN 'Excellent'
        WHEN Grade >= 75 THEN 'Good'
        ELSE 'Average'
    END AS Performance,
    COUNT(*) AS NumberOfStudents
FROM Students
GROUP BY
    CASE
        WHEN Grade >= 85 THEN 'Excellent'
        WHEN Grade >= 75 THEN 'Good'
        ELSE 'Average'
    END;

CASE in UPDATE, INSERT and DELETE

The same expression computes a value inside UPDATE, INSERT and DELETE as well as SELECT. In an UPDATE, CASE computes the new value a SET clause assigns:

UPDATE Students
SET Grade = Grade +
    CASE
        WHEN Grade < 75 THEN 5
        ELSE 0
    END;

In an INSERT, CASE computes a value for the row being added. Giving a new student a Label based on their starting Grade:

INSERT INTO Students (Name, Grade, Label)
VALUES ('Eve', 88,
    CASE
        WHEN 88 >= 85 THEN 'Excellent'
        ELSE 'Average'
    END);

Eve's row is inserted with Label set to Excellent.

In a DELETE, CASE can drive the WHERE condition itself. Running this against the original table removes every row CASE evaluates as Average:

DELETE FROM Students
WHERE 'Average' =
    CASE
        WHEN Grade < 75 THEN 'Average'
        ELSE 'Not Average'
    END;

Bob and Carol are removed, since both have a Grade under 75.

CASE in Aggregate Functions

Using CASE inside aggregate functions turns conditional counting, summing, or averaging into a single pass over the rows, often paired with SQL NULL functions or an implicit NULL to exclude a row from the computation.

Averaging only the Excellent grades needs NULL in the ELSE branch so AVG skips every other row:

SELECT AVG(
    CASE
        WHEN Grade >= 85 THEN Grade
        ELSE NULL
    END) AS AvgExcellent
FROM Students;

Preventing Divide-by-Zero Errors with CASE

Dividing a column that can be zero raises a divide-by-zero error unless the query guards the divisor first. A Sales table with a zero transaction count on one row is enough to show it:

The CASE expression substitutes NULL for a zero divisor, so the division returns NULL instead of erroring:

SELECT TotalSales /
    CASE
        WHEN NumberOfTransactions = 0 THEN NULL
        ELSE NumberOfTransactions
    END AS AvgSale
FROM Sales;

Nested CASE Expressions

A CASE expression can nest inside its own ELSE branch when one condition needs a second, unrelated check. Labeling Alice's exact 85 as Perfect, anything above it as Excellent, and splitting the rest by whether Grade is even or odd:

SELECT Name,
    CASE
        WHEN Grade = 85 THEN 'Perfect'
        WHEN Grade > 85 THEN 'Excellent'
        ELSE
            CASE
                WHEN Grade % 2 = 0 THEN 'Even Grade'
                ELSE 'Odd Grade'
            END
    END AS Label
FROM Students;

Validate CASE logic in DbSchema

Complex CASE expressions are easier to trust when tested incrementally. In the DbSchema query editor, run each branch condition against sample data first, then apply the full expression in reporting or update queries.

Common Mistakes

• Forgetting the END keyword.
• Leaving out ELSE, which returns NULL for every row that matches no condition.
• Nesting CASE more than two levels deep, which makes the logic hard to read back.

DbSchema's SQL editor runs a CASE expression against your own tables and shows the result before it goes into a report or an update. Download DbSchema Community Edition to test the examples above against your own database.