CASE Statement in SQL - Scaler Topics

The CASE statement in SQL is a powerful conditional expression that allows for implementing if-then-else logic directly within SQL queries. It evaluates a list of conditions and returns a value as soon as a true condition is found.

It navigates through each condition, much like a sequence of if-then statements in programming, and selects the first one that matches. If no condition matches, it can return a default value specified in an ELSE clause. Without an ELSE part, if no condition is met, the CASE statement results in NULL, ensuring flexibility in handling various data retrieval scenarios efficiently.

Case Syntax

Understanding Simple CASE Structure in SQL

A simple CASE structure in SQL evaluates a specified expression against a series of values. For each case, if the expression matches a given value, the corresponding result is returned. Here's how it's laid out:

Key components:

evaluated_expression: The column or computation that the CASE statement assesses.
match_value1, match_value2, ..., match_valueN: These values are directly compared with evaluated_expression.
return_result1, return_result2, ..., return_resultN: These are outcomes corresponding to each match.
alternative_result: This serves as a fallback outcome if there's no match with evaluated_expression.

Exploring Searched CASE Structure in SQL

The searched CASE structure allows for the evaluation of various Boolean conditions to decide the outcome, offering a way to incorporate more complex logic beyond simple value matching:

Components explained:

boolean_condition1, boolean_condition2, ..., boolean_conditionN: These are conditions that, if true, trigger the return of their associated results.
result_for_condition1, result_for_condition2, ..., result_for_conditionN: Results corresponding to each true condition.
fallback_result: A default outcome if no conditions are met.

SQL CASE Examples

To illustrate the usage of the CASE statement in SQL with practical examples, let's create a table Employees that can serve as a reference. This table will represent employees in a company, containing columns for employee ID, name, department, salary, and years of service. This setup will help us demonstrate both simple and searched CASE expressions effectively.

EmployeeID	Name	Department	Salary	YearsOfService
1	John Doe	Engineering	90000	5
2	Jane Smith	Marketing	75000	3
3	Emily Ray	Sales	50000	10
4	Chris Lee	Engineering	110000	7
5	Pat Jordan	Marketing	65000	2

Let's see some SQL queries using the Employees table as our reference.

1. CASE Statement with Comparison Operator

Query: Categorize employees based on whether their salary is above or below the average salary.

Output:

Name	Salary	SalaryStatus
John Doe	90000	Above Average
Jane Smith	75000	Below Average
Emily Ray	50000	Below Average
Chris Lee	110000	Above Average
Pat Jordan	65000	Below Average

2. CASE Statement with ORDER BY Clause

Query: Order employees by their department, and within each department by their salary, categorizing into 'Top', 'Middle', and 'Bottom' tiers.

Output:

Name	Department	Salary	SalaryTier
John Doe	Engineering	90000	Top
Chris Lee	Engineering	110000	Top
Jane Smith	Marketing	75000	Middle
Pat Jordan	Marketing	65000	Middle
Emily Ray	Sales	50000	Bottom

3. CASE Statement with GROUP BY Clause

Query: Group employees by department and count how many are in each salary tier.

Output:

Department	SalaryTier	EmployeeCount
Engineering	Top	2
Marketing	Middle	2
Sales	Bottom	1

4. Update Statement with a CASE Statement

Query: Increase salary based on years of service: more than 8 years get 10%, 5-8 years get 5%, less than 5 years get 2%.

Output: No direct table output for an update operation, but salaries in the Employees table would be updated accordingly. Running SELECT command gives:

EmployeeID	Name	Department	Salary	YearsOfService
1	John Doe	Engineering	94500	5
2	Jane Smith	Marketing	76500	3
3	Emily Ray	Sales	55000	10
4	Chris Lee	Engineering	115500	7
5	Pat Jordan	Marketing	66300	2

Note: The salary for Emily Ray has been rounded off to a more conventional format.

5. Insert Statement with CASE Statement

Query: Insert a new employee, with a starting salary determined by the department they are joining.

Output: No direct table output for an insert operation, but a new row would be added to the Employees table. Running SELECT command gives:

EmployeeID	Name	Department	Salary	YearsOfService
1	John Doe	Engineering	90000	5
2	Jane Smith	Marketing	75000	3
3	Emily Ray	Sales	50000	10
4	Chris Lee	Engineering	110000	7
5	Pat Jordan	Marketing	65000	2
6	Alex Morgan	Sales	55000	1

6. Using CASE with Aggregate Functions

Query: Calculate the average salary for each department, labeling departments with average salaries above $70,000 as 'High' and others as 'Low'.

Output:

Department	AverageSalary	SalaryLevel
Engineering	100000	High
Marketing	70000	Low
Sales	55000	Low

7. Using CASE Inside of Aggregate Functions

Query: Calculate the total salary budget for each department, but only include salaries of employees with more than 5 years of service in the calculation.

Output:

Department	SalaryBudget
Engineering	110000
Marketing	0
Sales	50000

Case Statement Limitations

We have multiple conditions in a CASE statement, but as it works sequentially, if a condition is found true, then all other conditions after it will not even be checked.
We cannot check NULL values in a table using a CASE statement.
A CASE statement cannot be used to control the execution flow of a user-defined function.

Conclusion

The CASE statement allows for sophisticated conditional logic within SQL queries, akin to if-then-else statements in programming, offering a robust tool for data analysis and manipulation.
It can be used in two forms—simple CASE for direct value comparison and searched CASE for complex Boolean conditions, catering to a wide range of conditional logic requirements.
CASE statements seamlessly integrate with other SQL clauses like ORDER BY and GROUP BY, as well as in DML statements (INSERT, UPDATE), enhancing SQL's ability to perform conditional sorting, grouping, and data manipulation.
It can be used alongside or within aggregate functions to perform conditional aggregations, providing deeper insights into data sets based on dynamic criteria.
Despite its simplicity, the CASE statement significantly improves the efficiency of SQL queries by eliminating the need for multiple queries and enabling conditional logic directly within a single statement.