CASE Statement in SQL Server

In this SQL Server tutorial, I will explain how to apply conditions logic in your query using the CASE statement in SQL Server.

As a database developer, you must understand how to use CASE statements to simplify the condition logic in your query. Also, how to categorize the data as requirements arise more simply.

First, I will begin by explaining what a CASE statement is, and then you will understand the types of CASE statements in SQL Server.

After that, you will understand the syntax of both types of CASE statements. Then, with an example, you will learn how to use the CASE statement practically.

Table of Contents

If you are familiar with If-else concepts, you know how the CASE statement works. The CASE statement in SQL Server consists of expressions and multiple conditions; it evaluates conditions and returns a specific value when the first condition is met.

If none of the conditions matches, then it returns the default value. But in SQL Server, there are two types of CASE statements. Simple CASE and searched CASE statement.

The syntax of the simple CASE statement is given below.

CASE input_expression
     WHEN condtion THEN result_1
     WHEN condtion THEN result_2
     ...
     WHEN condtion THEN result_3

     [ ELSE default_result ]
END

Where,

CASE input_expression: CASE keyword followed by input_expression. This expression can be any valid expression that evaluates the single data value.
WHEN condition THEN result: you can specify multiple conditions with corresponding results. If any of the condition matches, then the result of that condition is returned.
ELSE else_result_expression: If none of the WHEN conditions match, the ELSE is executed, and the default result is returned.

The syntax of the searched CASE statement is given below.

CASE
     WHEN Boolean_expression THEN result_expression [ ...n ]
     [ ELSE default_result ]
END

Where,

WHEN boolean_expression THEN result_expression: if the expression matches or is true, the corresponding result is returned.
ELSE default_result: If none of the WHEN condition matches, the default result specified with ELSE is returned.

Let’s understand the two variants of the CASE statement one by one through an example.

Using Simple CASE Statement in SQL Server

Here, you will learn how to use a simple CASE statement. For example, there is an Orders table, as shown below.

The table contains the OrdeID and OrderStatus columns; the OrderStatus column contains the statute of orders as ‘Processed’, ‘Shipped’, or ‘Delivered’. You must convert these statuses into a more human-friendly format; run the query below.

SELECT OrderID,
   	CASE OrderStatus
       	WHEN 'Processed' THEN 'In Progress'
       	WHEN 'Shipped' THEN 'On the Way'
       	WHEN 'Delivered' THEN 'Completed'
       	ELSE 'Status Unknown'
   	END AS StatusDescription
FROM Orders;

Using Simple CASE Statement in SQL Server

Look how the order status is converted into a status description, which is more human-friendly.

Let’s understand the query part CASE OrderStatus. This logic means the order is applied to the OrderStatus column of each row in the Orders table.

Here, the CASE statement reads the value OrderStatus for each row and matches it against the conditions specified after each WHEN keyword. Depending on which condition is met, it returns a corresponding string value:

If the OrderStatus value is Processed, the CASE statement returns the string ‘In Progress’.
If the OrderStatus value is Shipped, it returns the string ‘On the Way’.
If the OrderStatus value is Delivered, it returns the string ‘Completed’.
If the OrderStatus value doesn’t match any of these values, the ELSE clause takes effect, returning ‘Status Unknown’.

END AS StatusDescription: This line means the output of the query; the column containing the results of the CASE statement will be labelled as ‘StatusDescription’.

This is how to use simple CASE statements in SQL Server.

Using Searched CASE Statement in SQL Server

The searched CASE statement in SQL Server evaluates multiple conditions to determine the result. This differs from a simple CASE statement, which evaluates a single expression against the multiple values.

Let’s understand how to use search CASE statement in SQL Server. For example, you have an Employee table, as shown below.

Using Searched CASE Statement in SQL Server on Employees Table

As you can see, the Employees table contains three columns: Name, Department, and Salary. Here, you have a task to categorize employees based on their salary into Low, Medium, and High tiers; for that, you can use the searched CASE statement as shown below.

SELECT Name, Department,
   	CASE
       	WHEN Salary < 40000 THEN 'Low'
       	WHEN Salary BETWEEN 40000 AND 80000 THEN 'Medium'
       	WHEN Salary > 80000 THEN 'High'
       	ELSE 'Not Specified'
   	END AS SalaryTier
FROM Employees;

Using Searched CASE Statement in SQL Server

In the output of the above query, each employee is categorized into a specific tier based on their salary. For example, Ethan Lee and Emily Davis have a medium salary, Chloe Wilson has a low salary, and Olivia Martin has a High tier.

Remember, the CASE statement in SQL Server is decisive but impacts the query performance, particularly in large datasets.

Using too many CASE statements in the query makes it harder to read the query. When using the CASE statement, aim for simplicity and clarity, break down the very complex queries into multiple queries or use a temporary table if required.

So this is how you can insert the conditional logic in the query using the CASE statement in SQL Server. Knowing about CASE statements is necessary as it allows you to present the data more simply and categorize data whenever required.

Conclusion

In this SQL Server tutorial, you learned how to apply the CASE statement to present and categorize the data based on the conditions.

Additionally, you learned two types of CASE statements: simple and searched CASE statement in SQL Server, how to use them and how they differ.