CASE Statement

Introduction

Sometimes a query needs to make a decision based on a value — for example, turning a raw numeric score into a letter grade, or labeling an order as "Small," "Medium," or "Large." The CASE expression lets you write this kind of if/else logic directly inside a SQL statement.

In this lesson we will use a "students" table with a numeric marks column to build a CASE expression that converts marks into a letter grade.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    marks INT
);

INSERT INTO students (id, name, marks) VALUES
(1, 'Alex', 92),
(2, 'Priya', 78),
(3, 'Sam', 65),
(4, 'Jordan', 41);

Why CASE Matters

Without CASE, converting a numeric score into a label like "A" or "B" would require pulling the raw data into an application and writing if/else logic there. CASE lets you do this transformation right inside the SELECT statement, so the labeled result comes straight out of the database.

CASE Syntax

A CASE expression checks a series of conditions in order, and returns the result tied to the first condition that is true. If none of the conditions match, the ELSE result is returned instead.

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

Worked Example: Letter Grades

Let's use CASE to convert each student's numeric marks into a letter grade, directly inside a SELECT statement.

SELECT
    name,
    marks,
    CASE
        WHEN marks >= 90 THEN 'A'
        WHEN marks >= 75 THEN 'B'
        WHEN marks >= 60 THEN 'C'
        WHEN marks >= 50 THEN 'D'
        ELSE 'F'
    END AS grade
FROM students;

+--------+-------+-------+
| name   | marks | grade |
+--------+-------+-------+
| Alex   | 92    | A     |
| Priya  | 78    | B     |
| Sam    | 65    | C     |
| Jordan | 41    | F     |
+--------+-------+-------+

MySQL checks each WHEN condition from top to bottom for every row and stops at the first one that is true. For Alex (92), the very first condition marks >= 90 is true, so the CASE expression immediately returns 'A' without checking the rest.

CASE with Multiple Conditions

A WHEN clause can include more than one condition combined with AND or OR, letting you build more precise rules.

SELECT
    name,
    marks,
    CASE
        WHEN marks >= 50 AND marks < 60 THEN 'Needs Improvement'
        WHEN marks < 50 THEN 'Failing'
        ELSE 'Passing Well'
    END AS status
FROM students;

+--------+-------+----------------+
| name   | marks | status         |
+--------+-------+----------------+
| Alex   | 92    | Passing Well   |
| Priya  | 78    | Passing Well   |
| Sam    | 65    | Passing Well   |
| Jordan | 41    | Failing        |
+--------+-------+----------------+

Bonus: CASE Inside ORDER BY

As a bonus technique, CASE can also be used inside an ORDER BY clause to create a custom sort order that does not follow simple alphabetical or numeric rules — for example, always showing failing students first regardless of their exact marks.

SELECT name, marks
FROM students
ORDER BY
    CASE WHEN marks < 50 THEN 0 ELSE 1 END,
    marks DESC;

+--------+-------+
| name   | marks |
+--------+-------+
| Jordan | 41    |
| Alex   | 92    |
| Priya  | 78    |
| Sam    | 65    |
+--------+-------+

This ORDER BY first sorts by the CASE result (failing students get 0 and appear first), and within that, sorts by marks descending. Custom sorting with CASE is a handy technique to keep in mind, and is covered in more depth in later lessons on sorting.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

The CASE expression brings conditional, if/else-style logic directly into your SQL queries, letting you transform raw values into meaningful labels without any extra application code.

In this lesson, you learned CASE WHEN syntax, used it to convert numeric marks into letter grades, and saw how it can also power a custom ORDER BY. Next, you will begin learning about joins, starting with why they exist.