CASE Statement in SQL

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

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.

EmployeeIDNameDepartmentSalaryYearsOfService
1John DoeEngineering900005
2Jane SmithMarketing750003
3Emily RaySales5000010
4Chris LeeEngineering1100007
5Pat JordanMarketing650002

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:

NameSalarySalaryStatus
John Doe90000Above Average
Jane Smith75000Below Average
Emily Ray50000Below Average
Chris Lee110000Above Average
Pat Jordan65000Below 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:

NameDepartmentSalarySalaryTier
John DoeEngineering90000Top
Chris LeeEngineering110000Top
Jane SmithMarketing75000Middle
Pat JordanMarketing65000Middle
Emily RaySales50000Bottom

3. CASE Statement with GROUP BY Clause

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

Output:

DepartmentSalaryTierEmployeeCount
EngineeringTop2
MarketingMiddle2
SalesBottom1

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:

EmployeeIDNameDepartmentSalaryYearsOfService
1John DoeEngineering945005
2Jane SmithMarketing765003
3Emily RaySales5500010
4Chris LeeEngineering1155007
5Pat JordanMarketing663002

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:

EmployeeIDNameDepartmentSalaryYearsOfService
1John DoeEngineering900005
2Jane SmithMarketing750003
3Emily RaySales5000010
4Chris LeeEngineering1100007
5Pat JordanMarketing650002
6Alex MorganSales550001

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:

DepartmentAverageSalarySalaryLevel
Engineering100000High
Marketing70000Low
Sales55000Low

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:

DepartmentSalaryBudget
Engineering110000
Marketing0
Sales50000

Case Statement Limitations

  1. 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.
  2. We cannot check NULL values in a table using a CASE statement.
  3. A CASE statement cannot be used to control the execution flow of a user-defined function.

Conclusion

  1. 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.
  2. 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.
  3. 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.
  4. It can be used alongside or within aggregate functions to perform conditional aggregations, providing deeper insights into data sets based on dynamic criteria.
  5. 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.