SQL CASE Statement - Conditional Logic Inside Queries

Introduction

Imagine you’re preparing a report card, and you want to assign grades based on marks. You need conditional logic — something like “if marks > 90, then grade is A.” In SQL, this kind of logic is handled beautifully using the CASE statement.

What is the CASE Statement?

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.

Syntax

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

Sample Table – student_results

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);

1. Basic CASE Usage – Grading

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

2. CASE with Multiple Conditions

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;

3. CASE in ORDER BY

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;

4. CASE in WHERE Clause (Not Directly)

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';

Best Practices

• 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.

Summary

The CASE statement adds intelligence to your SQL — letting you handle complex decisions right within your queries. Whether you’re assigning grades, calculating statuses, or customizing output, CASE gives you complete control over conditional logic.

What’s Next?

Next, we’ll explore SQL Subqueries — queries within queries — to unlock advanced filtering, comparisons, and result generation.