CASE Statement

15 June 2024

|
4 min read
Blog Image

The CASE statement in SQL Server is used to execute conditional logic within a SQL query. It evaluates a list of conditions and returns one of multiple possible result expressions. The CASE statement comes in two forms: the simple CASE expression and the searched CASE expression.

Simple CASE Expression

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

CASE input_expression

    WHEN when_expression THEN result_expression

    [ ...n ]

    [ ELSE else_result_expression ]

END

Searched CASE Expression

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

CASE

WHEN Boolean_expression THEN result_expression

[ ...n ]

[ ELSE else_result_expression ]

END

Take the Leap from Reader to Developer - Join Our SQL Server Course at TrainingHub.io!

Examples

  • Example 1: Simple CASE Expression

In this example, we have a table Employees with a column JobTitle. We want to categorize employees based on their job title.

SELECT 

    EmployeeID,

    FirstName,

    LastName,

    JobTitle,

    CASE JobTitle

        WHEN 'Manager' THEN 'Management'

        WHEN 'Developer' THEN 'Technical'

        WHEN 'Analyst' THEN 'Business'

        ELSE 'Other'

    END AS JobCategory

FROM Employees;

  • Example 2: Searched CASE Expression

In this example, we have a table Sales with columns SaleAmount and SaleDate. We want to classify sales into different categories based on the amount.

SELECT

SaleID,

SaleAmount,

SaleDate,

CASE

WHEN SaleAmount < 100 THEN 'Low'

WHEN SaleAmount BETWEEN 100 AND 500 THEN 'Medium'

WHEN SaleAmount > 500 THEN 'High'

ELSE 'Undefined'

END AS SaleCategory

FROM Sales;

Explanation of Components

1. Simple CASE Expression:

  • input_expression: The expression to be compared.
  • when_expression: The expression against which input_expression is compared.
  • result_expression: The value returned if input_expression matches when_expression.
  • else_result_expression: The value returned if no when_expression matches. This part is optional.

2. Searched CASE Expression:

  • Boolean_expression: A condition that returns a Boolean value (TRUE or FALSE).
  • result_expression: The value returned if Boolean_expression is TRUE.
  • else_result_expression: The value returned if none of the Boolean_expressions are TRUE. This part is optional.

Notes

  • The CASE statement can be used in SELECT, INSERT, UPDATE, and DELETE statements.
  • It is useful for transforming data, making it easier to interpret results, and for implementing complex logic within queries.
  • Proper use of CASE statements can significantly simplify queries and improve readability.

  • Scenario 1: Data Transformation

Transforming data values in a column based on specific conditions.

Example: Categorizing Products by Price Range

SELECT

ProductID,

ProductName,

Price,

CASE

WHEN Price < 50 THEN 'Low'

WHEN Price BETWEEN 50 AND 150 THEN 'Medium'

WHEN Price > 150 THEN 'High'

ELSE 'Unknown'

END AS PriceCategory

FROM Products;

  • Scenario 2: Conditional Aggregation

Using CASE statements within aggregate functions to perform conditional aggregation.

Example: Summarizing Sales by Region

SELECT

Region,

SUM(CASE WHEN Year = 2023 THEN Sales ELSE 0 END) AS Sales2023,

SUM(CASE WHEN Year = 2022 THEN Sales ELSE 0 END) AS Sales2022

FROM SalesData

GROUP BY Region;

  • Scenario 3: Conditional Updates

Updating table data conditionally.

Example: Updating Employee Salaries Based on Performance Ratings

UPDATE Employees

SET Salary = CASE

WHEN PerformanceRating = 'Excellent' THEN Salary * 1.10

WHEN PerformanceRating = 'Good' THEN Salary * 1.05

WHEN PerformanceRating = 'Average' THEN Salary * 1.02

ELSE Salary

END;

Boost your data career by enrolling in TrainingHub.io's Data Analyst courses in Toronto, featuring industry-relevant curriculum and expert instructors.

Scenario 4: Filtering Data with CASE in WHERE Clause

Using CASE statements within the WHERE clause for complex filtering.

Example: Filtering Orders Based on Variable Conditions

DECLARE @filterType INT = 1; -- 1 for High Value Orders, 2 for Recent Orders

SELECT

OrderID,

OrderDate,

TotalAmount

FROM Orders

WHERE

CASE

WHEN @filterType = 1 THEN

CASE

WHEN TotalAmount > 1000 THEN 1

ELSE 0

END

WHEN @filterType = 2 THEN

CASE

WHEN OrderDate > '2023-01-01' THEN 1

ELSE 0

END

ELSE 1

END = 1;

  • Scenario 5: Conditional Joins

Using CASE statements to dynamically change the join condition.

  • Scenario 6: Dynamic Column Selection

Using CASE to select different columns based on conditions.

Example: Selecting Columns Dynamically Based on User Role

DECLARE @UserRole NVARCHAR(50) = 'Admin';

SELECT

EmployeeID,

FirstName,

LastName,

CASE

WHEN @UserRole = 'Admin' THEN Salary

ELSE NULL

END AS Salary,

CASE

WHEN @UserRole = 'Admin' THEN Bonus

ELSE NULL

END AS Bonus

FROM Employees;

  • Scenario 7: Handling NULL Values

Using CASE statements to handle NULL values and provide default values.

Example: Replacing NULL Values with Default Text ,Typically we do ISNULL function, Colaesce function

SELECT

CustomerID,

CustomerName,

CASE

WHEN Address IS NULL THEN 'Address Not Provided'

ELSE Address

END AS Address

FROM Customers;

  • Scenario 8: Conditional Sorting

Using CASE statements in ORDER BY clause to sort data conditionally.

Example: Sorting Employees by Role Priority

SELECT

EmployeeID,

FirstName,

LastName,

Role

FROM Employees

ORDER BY

CASE

WHEN Role = 'Manager' THEN 1

WHEN Role = 'Developer' THEN 2

WHEN Role = 'Analyst' THEN 3

ELSE 4

END;

Join thousands of learners in Canada who have advanced their careers with TrainingHub.io’s IT online courses.

Recent blogs

Featured Image
GROUP BY Clause

21 June 2024

|
3 min read
Featured Image
CASE Statement

15 June 2024

|
4 min read
Featured Image
ORDER BY Clause

10 June 2024

|
3 min read