SQL CASE Statement

The CASE statement allows us to check conditions and return different results.

SELECT
    student_name,
    CASE
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS result
FROM students;

The basic syntax of CASE is:

CASE
    WHEN condition THEN result
    ELSE result
END

The WHEN condition is checked first. If it is true, the corresponding result is returned.

WHEN defines a condition and THEN defines the value returned when that condition is true.

SELECT
    student_name,
    CASE
        WHEN marks >= 40 THEN 'Pass'
    END AS result
FROM students;

ELSE specifies the result when none of the WHEN conditions are true.

SELECT
    student_name,
    CASE
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS result
FROM students;

Every SQL CASE expression must end with the END keyword.

SELECT
    CASE
        WHEN age >= 18 THEN 'Adult'
        ELSE 'Minor'
    END AS category
FROM students;

A CASE statement can contain multiple WHEN conditions.

SELECT
    student_name,
    marks,
    CASE
        WHEN marks >= 80 THEN 'Excellent'
        WHEN marks >= 60 THEN 'Good'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS grade
FROM students;

CASE can be used with numeric columns.

SELECT
    student_name,
    marks,
    CASE
        WHEN marks >= 90 THEN 1
        WHEN marks >= 75 THEN 2
        WHEN marks >= 50 THEN 3
        ELSE 4
    END AS grade_level
FROM students;

CASE can compare text values and return meaningful descriptions.

SELECT
    student_name,
    status,
    CASE
        WHEN status = 'Active' THEN 'Currently Studying'
        ELSE 'Not Active'
    END AS student_status
FROM students;

CASE supports comparison operators such as >, <, =, >=, and <=.

SELECT
    student_name,
    fees,
    CASE
        WHEN fees >= 10000 THEN 'High Fee'
        ELSE 'Normal Fee'
    END AS fee_category
FROM students;

Multiple conditions can be combined using AND.

SELECT
    student_name,
    marks,
    attendance,
    CASE
        WHEN marks >= 40 AND attendance >= 75
        THEN 'Eligible'
        ELSE 'Not Eligible'
    END AS eligibility
FROM students;

The OR operator can also be used inside a CASE condition.

SELECT
    student_name,
    CASE
        WHEN course = 'Python' OR course = 'Java'
        THEN 'Programming Course'
        ELSE 'Other Course'
    END AS course_type
FROM students;

The BETWEEN operator can be used inside CASE.

SELECT
    student_name,
    marks,
    CASE
        WHEN marks BETWEEN 80 AND 100 THEN 'A'
        WHEN marks BETWEEN 60 AND 79 THEN 'B'
        WHEN marks BETWEEN 40 AND 59 THEN 'C'
        ELSE 'F'
    END AS grade
FROM students;

The IN operator can be used when checking multiple possible values.

SELECT
    student_name,
    course,
    CASE
        WHEN course IN ('Python', 'Java', 'PHP')
        THEN 'Programming'
        ELSE 'Other'
    END AS category
FROM students;

CASE can check whether a value is NULL using IS NULL.

SELECT
    student_name,
    CASE
        WHEN mobile IS NULL THEN 'Mobile Missing'
        ELSE 'Mobile Available'
    END AS mobile_status
FROM students;

Use IS NOT NULL when you want to check for an available value.

SELECT
    student_name,
    CASE
        WHEN email IS NOT NULL THEN 'Email Available'
        ELSE 'Email Missing'
    END AS email_status
FROM students;

CASE can be used with ORDER BY to create custom sorting.

SELECT student_name, status
FROM students
ORDER BY
    CASE
        WHEN status = 'Active' THEN 1
        ELSE 2
    END;

Active students will appear before other students.

CASE can be combined with aggregate functions such as SUM().

SELECT
    SUM(
        CASE
            WHEN status = 'Paid' THEN amount
            ELSE 0
        END
    ) AS total_paid
FROM payments;

CASE can be used inside COUNT() to count records matching a condition.

SELECT
    COUNT(
        CASE
            WHEN status = 'Paid' THEN 1
        END
    ) AS paid_students
FROM payments;

CASE can create categories that can then be grouped.

SELECT
    CASE
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS result,
    COUNT(*) AS total
FROM students
GROUP BY
    CASE
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END;

CASE can be used with UPDATE to assign different values based on conditions.

UPDATE students
SET grade =
    CASE
        WHEN marks >= 80 THEN 'A'
        WHEN marks >= 60 THEN 'B'
        WHEN marks >= 40 THEN 'C'
        ELSE 'F'
    END;

CASE is useful for displaying fee status based on paid and total fees.

SELECT
    student_name,
    total_fee,
    paid_fee,
    CASE
        WHEN paid_fee >= total_fee THEN 'Paid'
        WHEN paid_fee > 0 THEN 'Partially Paid'
        ELSE 'Pending'
    END AS fee_status
FROM students;

CASE can classify students according to their attendance percentage.

SELECT
    student_name,
    attendance,
    CASE
        WHEN attendance >= 90 THEN 'Excellent'
        WHEN attendance >= 75 THEN 'Good'
        WHEN attendance >= 60 THEN 'Average'
        ELSE 'Low'
    END AS attendance_status
FROM students;

CASE can classify employees based on salary.

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary >= 50000 THEN 'High'
        WHEN salary >= 25000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

CASE can also be used with dates.

SELECT
    student_name,
    admission_date,
    CASE
        WHEN admission_date >= '2026-01-01'
        THEN 'New Admission'
        ELSE 'Old Admission'
    END AS admission_type
FROM students;

A simple CASE expression compares one expression with multiple values.

SELECT
    student_name,
    course,
    CASE course
        WHEN 'Python' THEN 'Programming'
        WHEN 'Java' THEN 'Programming'
        WHEN 'PHP' THEN 'Web Development'
        ELSE 'Other'
    END AS category
FROM students;

This form is useful when comparing one column with several fixed values.

A searched CASE expression checks different conditions.

SELECT
    student_name,
    marks,
    CASE
        WHEN marks >= 80 THEN 'A'
        WHEN marks >= 60 THEN 'B'
        WHEN marks >= 40 THEN 'C'
        ELSE 'F'
    END AS grade
FROM students;

This is useful when conditions involve comparisons or calculations.

The ELSE part is optional. If no condition matches and ELSE is not provided, CASE returns NULL.

SELECT
    student_name,
    CASE
        WHEN marks >= 40 THEN 'Pass'
    END AS result
FROM students;

A common mistake is forgetting the END keyword.

-- Incorrect
CASE
    WHEN marks >= 40 THEN 'Pass'

-- Correct
CASE
    WHEN marks >= 40 THEN 'Pass'
    ELSE 'Fail'
END

Always close a CASE expression with END.

We can use CASE to generate a complete result category.

SELECT
    student_name,
    marks,
    CASE
        WHEN marks >= 90 THEN 'Outstanding'
        WHEN marks >= 75 THEN 'Very Good'
        WHEN marks >= 60 THEN 'Good'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS performance
FROM students;

The following example combines CASE with fee and attendance information.

SELECT
    student_name,
    marks,
    attendance,
    total_fee,
    paid_fee,
    CASE
        WHEN marks >= 40 AND attendance >= 75
        THEN 'Eligible'
        ELSE 'Not Eligible'
    END AS exam_status,
    CASE
        WHEN paid_fee >= total_fee THEN 'Paid'
        WHEN paid_fee > 0 THEN 'Partially Paid'
        ELSE 'Pending'
    END AS fee_status
FROM students;

This query creates two useful categories: examination eligibility and fee status.