DbSchema Tutorial | SQL CASE EXPRESSION

SQL Tutorial • 13-Oct-2023

by Malik Umair Sajjad

SQL is a powerful language for managing and manipulating relational databases. Among its features is the CASE expression, which enables conditional logic directly within SQL. In this guide, we’ll explore this versatile tool in depth.

Introduction to SQL CASE Expression

The SQL CASE expression is a conditional construct, akin to “if-then-else” in many programming languages.

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END;

Example Database Table:

Consider a sample table Students:

StudentID	Name	Grade
1	Alice	85
2	Bob	60
3	Carol	72
4	Dave	90

To categorize students based on their grades:

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

Result:

Name	Performance
Alice	Excellent
Bob	Average
Carol	Good
Dave	Excellent

Explanation:

Each student’s grade is assessed, and they are labeled accordingly.

The CASE Statement and Comparison Operator

Example:

Using the Students table, we can label students with exactly 85 as ‘Perfect’:

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

Result:

Name	Label
Alice	Perfect
Bob	Other
Carol	Other
Dave	Other

Explanation:

Here, only Alice has a grade of 85, so she’s labeled ‘Perfect’. The rest are designated as ‘Other’.

Using the CASE Statement with the ORDER BY Clause

Example:

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;

Result:

Name	Grade	Performance
Alice	85	Excellent
Dave	90	Excellent
Carol	72	Good
Bob	60	Average

Explanation:

Students are sorted with ‘Excellent’ ones at the top, followed by ‘Good’, and then ‘Average’.

The CASE Statement with the GROUP BY Clause

Example:

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;

Result:

Performance	NumberOfStudents
Excellent	2
Good	1
Average	1

Explanation:

Here, students are grouped by their performance level. We see that there are two ‘Excellent’ students, one ‘Good’, and one ‘Average’.

Updating Records with the CASE Statement

Modify data conditionally using CASE.

Example:

Give a bonus of 5 points to students with ‘Average’ performance:

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

This will update Bob’s grade to 65.

Inserting Records Using the CASE Statement

Insert data with conditional values.

Example:

If a new student has a grade of 88, label them:

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

Eve will be labeled ‘Excellent’.

CASE in Aggregate Functions

Example:

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

Result:

AvgExcellent
87.5

Explanation:

The average grade of ‘Excellent’ students (Alice and Dave) is calculated to be 87.5.

Preventing Divide by Zero Errors

CASE can prevent division by zero.

Example:

If we had a table Sales with columns TotalSales and NumberOfTransactions, to find the average sale:

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

This ensures no division by zero occurs.

Searched CASE Statements

Example:

SELECT Name,
CASE
    WHEN Grade >= 80 AND Grade <= 85 THEN 'Almost Excellent'
    ELSE 'Other'
END AS Label
FROM Students;

Result:

Name	Label
Alice	Almost Excellent
Bob	Other
Carol	Other
Dave	Other

Explanation:

Alice, with a grade between 80 and 85, is labeled ‘Almost Excellent’. The others are labeled ‘Other’.

Nested CASE Statements

Example:

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;

Result:

Name	Label
Alice	Perfect
Bob	Even Grade
Carol	Odd Grade
Dave	Excellent

Explanation:

Alice, with a grade of 85, is labeled ‘Perfect’. Dave, with a grade above 85, is ‘Excellent’. Bob’s grade is even, hence ‘Even Grade’, and Carol’s grade is odd, making her ‘Odd Grade’.

Deleting Records Using CASE

Though not common, CASE can aid in deleting records.

Example:

Delete students labeled ‘Average’:

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

Bob will be removed from the table.

Common Mistakes

Forgetting the END keyword.
Using CASE without ELSE can result in NULL values.
Over-nesting can make code hard to read.

Frequently Asked Questions

Q: Can CASE be used with non-numeric data?
A: Yes, it can work with strings, dates, etc.

Q: Is CASE the only conditional expression in SQL?
A: No, there are other functions like IIF in some databases.

Practice Questions

Write a SQL query to label students with grades above 90 as ‘Topper’.
How would you use the CASE statement to categorize sales in a table into ‘Low’, ‘Medium’, and ‘High’ based on amount?
Use a nested CASE statement to label items in a store as ‘Perishable’ or ‘Non-Perishable’, and within ‘Non-Perishable’, further categorize as ‘Electronic’ or ‘Other’.

We hope this comprehensive guide on the SQL CASE expression aids in your understanding. The versatility of the CASE expression allows for a wide range of conditional logic directly within SQL. Practice is key to mastery, so be sure to experiment with the examples provided.

Happy querying!!