Rajendra Gupta
SQL Case Statement

CASE statement in SQL

April 1, 2019 by

The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By and Group By clause. It can be used in Insert statement as well. In this article, we would explore CASE statement and its various use cases.

Suppose you have a table that stores the ProductID for all products in a mini-store. You want to get Productname for a particular ProductID.

Look at the following example; We declared a variable @ProductID and specified value 1 for it. In Case statement, we defined conditions. Once a condition is satisfied, its corresponding value is returned.

SQL Case Statement

Similarly, if we change the condition in a Case statement in SQL, it returns appropriate expression. In the following example, we want to get Product name for ProductID 4.it does not satisfy Case statement condition; therefore, it gave output from Else expression.

SQL Case Statement

Let us explore a few examples of the Case statement in SQL. Before we proceed, create a sample table and insert few records in it.

We have following records in Employee table.

Demo Table

Different Formats of CASE Statements

A simple CASE statement expression

In this format, we evaluate one expression against multiple values. In a simple case statement, it evaluates conditions one by one. Once the condition and expression are matched, it returns the expression mentioned in THEN clause.

We have following syntax for a case statement in SQL with a simple expression

Usually, we store abbreviations in a table instead of its full form. For example, in my Employee table, I have used abbreviations in Gender and StateCode. I want to use a Case statement to return values as Male and Female in the output instead of M and F.

Execute the following code and notice that we want to evaluate CASE Gender in this query.

In the following image, you can notice a difference in output using a Case statement in SQL.

SQL Case Statement example

The CASE statement and comparison operator

In this format of a CASE statement in SQL, we can evaluate a condition using comparison operators. Once this condition is satisfied, we get an expression from corresponding THEN in the output.

We can see the following syntax for Case statement with a comparison operator.

Suppose we have a salary band for each designation. If employee salary is in between a particular range, we want to get designation using a Case statement.

In the following query, we are using a comparison operator and evaluate an expression.

In the following image you can see, we get designation as per condition specified in CASE statement.

SQL Case Statement example

Case Statement with Order by clause

We can use Case statement with order by clause as well. In SQL, we use Order By clause to sort results in ascending or descending order.

Suppose in a further example; we want to sort result in the following method.

  1. For Female employee, employee salaries should come in descending order
  2. For Male employee, we should get employee salaries in ascending order

We can define this condition with a combination of Order by and Case statement. In the following query, you can see we specified Order By and Case together. We defined sort conditions in case expression.

In the output, we have satisfied our sort requirement in ascending or descending order

  • For Female employee, salary is appearing in descending order

SQL Case Statement in Order By clause

  • For Male employee, salary is appearing in ascending order

SQL Case Statement in Order By clause

Case Statement in SQL with Group by clause

We can use a Case statement with Group By clause as well. Suppose we want to group employees based on their salary. We further want to calculate the minimum and maximum salary for a particular range of employees.

In the following query, you can see that we have Group By clause and it contains i with the condition to get the required output.

We have following output of this query. In this output, we get minimum and maximum salary for a particular designation.

SQL Case Statement in Group By clause

Update statement with a CASE statement

We can use a Case statement in SQL with update DML as well. Suppose we want to update Statecode of employees based on Case statement conditions.

In the following code, we are updating statecode with the following condition.

  • If employee statecode is AR, then update to FL
  • If employee statecode is GE, then update to AL
  • For all other statecodes update value to IN

Execute the following update command to fulfil our requirement using a Case statement.

In the following output, you can see old Statcode (left-hand side) and updated Statecode for the employees based on our conditions in the Case statement.

SQL Case Statement in Update statement

Insert statement with CASE statement

We can insert data into SQL tables as well with the help of Case statement in SQL. Suppose we have an application that inserts data into Employees table. We get the following values for gender.

Value

Description

Required value in Employee table

0

Male Employee

M

1

Female Employee

F

We do not want to insert value 0 and 1 for Male and Female employees. We need to insert the required values M and F for employee gender.

In the following query, we specified variables to store column values. In the insert statement, you can we are using a Case statement to define corresponding value to insert in the employee table. In the Case statement, it checks for the required values and inserts values from THEN expression in the table.

In the following screenshot, we can see newly inserted row contains Gender M instead of value 0.

SQL Case Statement in Insert clause

Case Statement limitations

  • We cannot control the execution flow of stored procedures, functions using a Case statement in SQL
  • We can have multiple conditions in Case statement; however, it works in a sequential model. If one condition is satisfied, it stops checking further conditions
  • We cannot use a Case statement for checking NULL values in a table

Conclusion

The Case statement in SQL provides flexibility in writing t-SQL for DDL and DML queries. It also adds versatility to SQL Server queries. You should practice Case statement in your queries.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
104,548 Views