SQL CASE Statement — Conditional Logic Inside Queries

A report needs a readable status beside each mark without storing a second copy of the status rule.

A CASE expression chooses a result value from ordered conditions. The first true WHEN condition determines the value for that row.

A searched CASE evaluates conditions in the written order. Put a more specific high threshold before a broader low threshold when both could be true. ELSE supplies a fallback; without it, a row with no matching condition receives NULL. CASE is an expression in a query, not a general instruction to update records.

Related concepts: Combine SQL Conditions and Missing Values and NULL.

The CASE statement allows you to add IF-ELSE type logic inside your SELECT queries. It evaluates conditions and returns values based on the first condition that is true.

SQL · portable concepts; preserved examples identify their engine

SELECT column1,
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
  END AS alias_name
FROM table_name;

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE student_results (
  roll_no INT,
  name VARCHAR(50),
  subject VARCHAR(30),
  marks INT
);

INSERT INTO student_results VALUES
(1, 'Aarav Sharma', 'Maths', 92),
(2, 'Diya Iyer', 'Maths', 76),
(3, 'Sneha Patil', 'Maths', 65),
(4, 'Mehul Agarwal', 'Maths', 58),
(5, 'Saira Bano', 'Maths', 47);

SQL · portable concepts; preserved examples identify their engine

Let’s assign grades based on marks:

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

name           | marks | grade
----------------+--------+-------
Aarav Sharma   | 92     | A+
Diya Iyer      | 76     | A
Sneha Patil    | 65     | B
Mehul Agarwal  | 58     | C
Saira Bano     | 47     | D

SQL · portable concepts; preserved examples identify their engine

Suppose you want to mark students as “Passed” or “Failed”:

SELECT name, marks,
  CASE
    WHEN marks >= 50 THEN 'Pass'
    ELSE 'Fail'
  END AS result
FROM student_results;

SQL · portable concepts; preserved examples identify their engine

You can even use CASE in an ORDER BY to prioritize results:

SELECT name, marks
FROM student_results
ORDER BY
  CASE
    WHEN marks >= 90 THEN 1
    WHEN marks >= 75 THEN 2
    WHEN marks >= 60 THEN 3
    ELSE 4
  END;

SQL · portable concepts; preserved examples identify their engine

You cannot use CASE directly inside WHERE, but you can use it in a subquery or with a derived column.

Example using derived column:

SELECT * FROM (
  SELECT *, CASE WHEN marks >= 50 THEN 'Pass' ELSE 'Fail' END AS result
  FROM student_results
) AS derived
WHERE result = 'Pass';

• Always end CASE with ELSE to handle unmatched conditions.
• Use aliases to name your CASE output columns clearly.
• Format CASE for readability, especially when there are many WHEN clauses.

An unknown condition does not match WHEN. Branch results need compatible types according to the engine’s conversion rules. For persisted validation, a display expression does not replace a constraint or application policy.

Label marks at least 80 as Strong and other marks as Developing.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT name, CASE WHEN marks >= 80 THEN 'Strong' ELSE 'Developing' END AS status FROM practice_students ORDER BY roll_no;

The condition is evaluated for each student. The status is a computed report field; the marks remain unchanged.

A CASE expression chooses a result value from ordered conditions. The first true WHEN condition determines the value for that row.

The condition is evaluated for each student. The status is a computed report field; the marks remain unchanged.

Continue your understanding with Combine SQL Conditions and Missing Values and NULL.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.