CASE Statement in SQL for Conditional Logic

Updated on September 13, 2024

Article Outline

Overview of Simple and Searched CASE Statements Detailed Syntax of the CASE Statement with Examples Applying CASE Statements in SQL SELECT Queries Using CASE Statements with ORDER BY Clauses Grouping Data with CASE Statements and the GROUP BY Clause Implementing CASE Statements Inside Aggregate Functions Updating Data with CASE Statements in SQL Conditional Logic in SQL Insert Statements Using CASE Limitations and Considerations When Using CASE Statements Conclusion FAQs

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.

Overview of Simple and Searched CASE Statements

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:

Expression: What you’re comparing (like a column’s value).
Value: What you’re checking the expression against.
Result: What you get if the expression matches the value.

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:

Condition: The test you’re running (like checking if a number is greater than 100).
Result: What you get if the condition is true.

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.

Detailed Syntax of the CASE Statement with Examples

Simple Case Statement

A Simple CASE statement looks like this:

CASE expression

WHEN value1 THEN result1

WHEN value2 THEN result2

ELSE default_result

END

Searched Case Statement

And here’s how a Searched CASE statement looks:

CASE

WHEN condition1 THEN result1

WHEN condition2 THEN result2

ELSE default_result

END

Practical Example

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:

SELECT EmployeeName, Salary,

CASE

WHEN Salary > 80000 THEN 'High Salary'

WHEN Salary BETWEEN 50000 AND 80000 THEN 'Moderate Salary'

ELSE 'Low Salary'

END AS SalaryCategory

FROM EmployeeDetails;

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

Applying CASE Statements in SQL SELECT Queries

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:

SELECT StudentName, Grade,

CASE

WHEN Grade >= 90 THEN 'A'

WHEN Grade >= 80 THEN 'B'

WHEN Grade >= 70 THEN 'C'

ELSE 'F'

END AS GradeLetter

FROM StudentGrades;

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:

SELECT StudentName, Grade,

CASE

WHEN Grade >= 90 THEN 'Excellent'

WHEN Grade >= 80 THEN 'Good'

WHEN Grade >= 70 THEN 'Satisfactory'

ELSE 'Needs Improvement'

END AS PerformanceCategory

FROM StudentGrades;

Output Table:

StudentName	Grade	PerformanceCategory
Divya	95	Excellent
Ravi	85	Good
Sumit	75	Satisfactory
Neeraj	65	Needs Improvement
Shubham	55	Needs Improvement

Using CASE Statements with ORDER BY Clauses

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:

SELECT ProductName, Category, SalesAmount

FROM ProductSales

ORDER BY

CASE

WHEN Category = 'Electronics' THEN 1

WHEN Category = 'Books' THEN 2

ELSE 3

END, SalesAmount DESC;

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 with CASE Statements and the GROUP BY Clause

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:

SELECT Region,

CASE

WHEN Revenue > 100000 THEN 'High Revenue'

ELSE 'Low Revenue'

END AS RevenueCategory,

COUNT(*) AS NumberOfSales

FROM SalesData

GROUP BY Region, RevenueCategory;

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.

Implementing CASE Statements Inside Aggregate Functions

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:

SELECT Category,

SUM(CASE WHEN SalesAmount > 50000 THEN SalesAmount ELSE 0 END) AS TotalHighSales

FROM ProductSales

GROUP BY Category;

Output Table:

Category	TotalHighSales
Electronics	200000
Books	0
Home Goods	0

Updating Data with CASE Statements in SQL

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:

UPDATE EmployeeSalaries

SET Salary = CASE

WHEN Department = 'HR' THEN Salary * 1.1

WHEN Department = 'Engineering' THEN Salary * 1.2

ELSE Salary * 1.05

END;

Output Table:

EmployeeID	Department	Salary
1	HR	66000
2	Engineering	102000
3	Sales	52500
4	Marketing	78750
5	HR	68200

Conditional Logic in SQL Insert Statements Using CASE

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?

Input Scenario: NewEmployees Table

You have a table named NewEmployees with the columns EmployeeName, Department, and StartingSalary.

You want to assign different starting salaries based on the department.

INSERT INTO NewEmployees (EmployeeName, Department, StartingSalary)

VALUES ('John Doe', 'Engineering',

CASE

WHEN 'Engineering' = 'Engineering' THEN 80000

WHEN 'Engineering' = 'HR' THEN 60000

ELSE 50000

END);

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.

Limitations and Considerations When Using CASE Statements

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.

Sequential Evaluation: CASE statements are evaluated in order. Once a condition is met, the rest are ignored. This means the order of conditions matters.
No NULL Handling: CASE can struggle with NULL values if not handled explicitly. Always include a condition to deal with NULLs where they might appear.
No Flow Control: CASE isn’t designed to control flow in SQL procedures. It’s meant for conditional logic within a query, not for deciding which SQL statements to execute next.

Conclusion

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.

FAQs

What is the primary difference between Simple and Searched CASE Statements?

Simple CASE compares an expression to a set of values, while Searched CASE evaluates a series of conditions.

Can I use a CASE statement in a WHERE clause?

Yes, CASE statements can be used in a WHERE clause to apply conditional logic when filtering data.

Is it possible to use a CASE statement within an aggregate function?

Absolutely, CASE statements can be integrated into aggregate functions like SUM or COUNT to conditionally process data.

What happens if no conditions in a CASE statement are met?

If no conditions are met and there’s no ELSE clause, the CASE statement returns NULL.

Can CASE statements handle NULL values?

Yes, but special attention is needed. Explicit conditions should be added to handle NULLs properly.

Updated on September 13, 2024

Link

Programs tailored for your success

Popular

IIT Courses

Management

Data Science

Finance

Technology

Future Tech

3 ASSURED INTERVIEWS

Accelerator Program in

Business Analytics and Data Science

With

Part-time · 10 months

Apply by : 23 November, 2024

Financial Analysis, Valuation, & Risk Management

3 ASSURED INTERVIEWS

Certificate Program in

Financial Analysis, Valuation, & Risk Management

With

Part-time · 7 months

Apply by : 23 November, 2024

INTERNSHIP ASSURANCE

Certificate Program in

DevOps & Cloud Engineering

With

Part-time · 7.5 months

Apply by : 23 November, 2024

Upskill with expert articles

View all

Hero Vired is a leading LearnTech company dedicated to offering cutting-edge programs in collaboration with top-tier global institutions. As part of the esteemed Hero Group, we are committed to revolutionizing the skill development landscape in India. Our programs, delivered by industry experts, are designed to empower professionals and students with the skills they need to thrive in today’s competitive job market.

Data Science

Accelerator Program in Business Analytics & Data Science

Integrated Program in Data Science, AI and ML

Accelerator Program in AI and Machine Learning

Advanced Certification Program in Data Science & Analytics

Certification Program in Data Analytics

Technology

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

Finance