Popular
Data Science
Technology
Finance
Management
Future Tech
Have you ever thought about how to handle conditional logic in SQL without having to write multiple queries?
That’s where the CASE statement in SQL steps in.
It’s your go-to tool for embedding if-then-else logic directly within your SQL queries. Whether you’re working with simple conditions or need to execute more complex logic, the CASE statement has you covered.
Yet why should we care?
Often, our data needs to be categorised, summarised, or presented based on certain conditions. In this case, the CASE statement will be a major factor in making queries dynamically and flexibly regarding the data at hand.
Consider you are preparing a sales report and you require sorting the sales figures under the ‘High’ ‘Medium’ and ‘Low’ categories.
Instead of running multiple queries or using time-consuming nested statements, the CASE statement allows us to do it all at once.
The CASE statement comes in two types: Simple and Searched.
Simple CASE compares an expression against a set of values. It’s like asking, “Does this match?” If it does, we get the result tied to that value.
Here’s a quick breakdown:
Searched CASE is a bit more versatile. Instead of just comparing a single value, it evaluates a series of conditions.
Think of it as asking, “Is this condition true?” If it is, the associated result is returned.
Here’s how it breaks down:
Both types are useful, but they serve slightly different purposes.
The Simple CASE is great for direct comparisons, while the Searched CASE is your go-to for more complex logic.
Also Read: Mastering SQL
A Simple CASE statement looks like this:
And here’s how a Searched CASE statement looks:
Suppose we’re working with a table called EmployeeDetails, which has columns like EmployeeID, EmployeeName, and Salary. We want to categorise employees based on their salary.
Input Table: EmployeeDetails
EmployeeID | EmployeeName | Salary |
1 | Abhishek | 90000 |
2 | Chetan | 75000 |
3 | Dolly | 60000 |
4 | Farhan | 45000 |
5 | Garima | 30000 |
Here’s how we might do that with a Simple CASE:
Output Table:
EmployeeName | Salary | SalaryCategory |
Abhishek | 90000 | High Salary |
Chetan | 75000 | Moderate Salary |
Dolly | 60000 | Moderate Salary |
Farhan | 45000 | Low Salary |
Garima | 30000 | Low Salary |
Now, let’s talk about applying the CASE statement in SQL SELECT queries.
This is where we can really see the CASE statement’s power. It lets us create new columns in our result set, calculated on the fly based on the conditions we define.
For example, say we’re dealing with student grades. We have a table called StudentGrades, with columns like StudentID, StudentName, and Grade.
Input Table: StudentGrades
StudentID | StudentName | Grade |
1 | Divya | 95 |
2 | Ravi | 85 |
3 | Sumit | 75 |
4 | Neeraj | 65 |
5 | Shubham | 55 |
We want to convert numerical grades into letter grades. Here’s how we can do it:
Output Table:
StudentName | Grade | GradeLetter |
Divya | 95 | A |
Ravi | 85 | B |
Sumit | 75 | C |
Neeraj | 65 | F |
Shubham | 55 | F |
In this query, we’re converting grades into the standard A, B, C, and F system. The performance of the phoneme depends on the following hierarchy: anyone that scores above 90 gets an A, while scores between 80 and 89 get a B, and so on.
Now, suppose we want to do something more interesting, such as using multiple conditions to create a new category.
That’s where the CASE statement really shines.
Let’s say we want to group students not just by grade but also give them a performance category based on their scores:
Output Table:
StudentName | Grade | PerformanceCategory |
Divya | 95 | Excellent |
Ravi | 85 | Good |
Sumit | 75 | Satisfactory |
Neeraj | 65 | Needs Improvement |
Shubham | 55 | Needs Improvement |
How do we order data when our sorting needs go beyond simple ascending or descending?
That’s where the CASE statement comes to help us. It lets us control the order of our results based on custom logic.
Suppose we have a list of the product names and the total sales made. We would like to sort them by category; however, “Electronics” has to be before all “Books” and then everything else.
How do we do that?
Input Table: ProductSales
ProductName | Category | SalesAmount |
Laptop | Electronics | 120000 |
Headphones | Electronics | 80000 |
Novel | Books | 15000 |
Textbook | Books | 20000 |
Vacuum | Home Goods | 50000 |
Here’s the query we’d write:
Output Table:
ProductName | Category | SalesAmount |
Laptop | Electronics | 120000 |
Headphones | Electronics | 80000 |
Textbook | Books | 20000 |
Novel | Books | 15000 |
Vacuum | Home Goods | 50000 |
In this example, we’ve ordered our data so that Electronics come first, followed by Books, and then everything else.
Within each category, products are ordered by SalesAmount in descending order.
Grouping data is key when we want to summarise and analyse information.
But what if we need to group data based on custom conditions? Here’s how the CASE statement in SQL can help.
Assume we have a sales report and want to group regions by revenue categories: For clarity, anything above 100,000 has been labelled as “High Revenue”, while all the other values are categorised as “Low Revenue.”
Input Table: SalesData
Region | SalesPerson | Revenue |
North | Amit | 150000 |
South | Vinita | 95000 |
East | Saurav | 200000 |
West | Sachin | 85000 |
Central | Mitali | 120000 |
Here’s the query we’d use:
Output Table:
Region | RevenueCategory | NumberOfSales |
North | High Revenue | 1 |
South | Low Revenue | 1 |
East | High Revenue | 1 |
West | Low Revenue | 1 |
Central | High Revenue | 1 |
NumberOfSales: the count of sales entries. In this case, each region has one entry, so the count is 1 for each.
What if we need to perform calculations based on certain conditions? This is where we can use CASE statements inside aggregate functions like SUM, COUNT, or AVG.
Let’s say we want to calculate the total sales for products but only count sales above 50,000.
Input Table: ProductSales
ProductName | Category | SalesAmount |
Laptop | Electronics | 120000 |
Headphones | Electronics | 80000 |
Novel | Books | 15000 |
Textbook | Books | 20000 |
Vacuum | Home Goods | 50000 |
Here’s the query:
Output Table:
Category | TotalHighSales |
Electronics | 200000 |
Books | 0 |
Home Goods | 0 |
How do we update multiple rows with different values based on conditions?
The CASE statement in SQL is perfect for this.
Let’s say we’re managing employee salaries, and we want to give different raises depending on the department.
Input Table: EmployeeSalaries
EmployeeID | Department | Salary |
1 | HR | 60000 |
2 | Engineering | 85000 |
3 | Sales | 50000 |
4 | Marketing | 75000 |
5 | HR | 62000 |
Here’s how we’d write the query that gives HR a 10% raise, Engineering a 20% raise, and everyone else a 5% raise:
Output Table:
EmployeeID | Department | Salary |
1 | HR | 66000 |
2 | Engineering | 102000 |
3 | Sales | 52500 |
4 | Marketing | 78750 |
5 | HR | 68200 |
At any point, have you been in a position where you are required to enter data into a table, but the value to insert is determined by a condition?
This is the best place where the use of the CASE statement in SQL comes into play.
For instance, let’s assume that you are in charge of managing a company’s employee database, and you will have to incorporate new records concerning employees. But here’s the twist: It is noteworthy that the starting salary needs to be different depending on the department.
How do we handle this?
You have a table named NewEmployees with the columns EmployeeName, Department, and StartingSalary.
You want to assign different starting salaries based on the department.
Output Table:
EmployeeName | Department | StartingSalary |
John Doe | Engineering | 80000 |
This query checks the department and assigns the appropriate starting salary.
If the department is Engineering, the salary is set to 80,000. If it were HR, it would be 60,000. And for any other department, it defaults to 50,000.
This approach makes our INSERT operations smarter and more adaptable to different scenarios.
The CASE statement in SQL is powerful, but it’s not without its limits. Understanding these limitations is crucial to using CASE effectively and avoiding common pitfalls.
The CASE statement in SQL is a versatile tool that enhances our ability to handle conditional logic directly within queries.
It allows for dynamic data manipulation, whether ordering results based on specific criteria, grouping data into meaningful categories, or inserting and updating records with precision.
By understanding and applying CASE statements effectively, complex tasks become simpler, more efficient, and tailored to the specific needs of our data.
This ability to embed conditional logic directly in SQL queries makes CASE an essential feature for anyone looking to optimise their database operations and make smarter data-driven decisions.
So, the next time you face a complex data task, consider how a CASE statement might simplify the solution.
The DevOps Playbook
Simplify deployment with Docker containers.
Streamline development with modern practices.
Enhance efficiency with automated workflows.
Popular
Data Science
Technology
Finance
Management
Future Tech
Accelerator Program in Business Analytics & Data Science
Integrated Program in Data Science, AI and ML
Certificate Program in Full Stack Development with Specialization for Web and Mobile
Certificate Program in DevOps and Cloud Engineering
Certificate Program in Application Development
Certificate Program in Cybersecurity Essentials & Risk Assessment
Integrated Program in Finance and Financial Technologies
Certificate Program in Financial Analysis, Valuation and Risk Management
© 2024 Hero Vired. All rights reserved