DbSchema Tutorial | SQL CASE EXPRESSION
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
ENDkeyword. - 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!!
