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 the Insert statement as well. In this article, we would explore the 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.
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
USE [SQLShackDemo] GO CREATE TABLE dbo.Employee ( EmployeeID INT IDENTITY PRIMARY KEY, EmployeeName VARCHAR(100) NOT NULL, Gender VARCHAR(1) NOT NULL, StateCode VARCHAR(20) NOT NULL, Salary money NOT NULL, ) GO USE [SQLShackDemo] GO SET IDENTITY_INSERT [dbo].[Employee] ON GO INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (201, N'Jerome', N'M', N'FL', 83000.0000) GO INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (202, N'Ray', N'M', N'AL', 88000.0000) GO INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (203, N'Stella', N'F', N'AL', 76000.0000) GO INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (204, N'Gilbert', N'M', N'Ar', 42000.0000) GO INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (205, N'Edward', N'M', N'FL', 93000.0000) GO INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (206, N'Ernest', N'F', N'Al', 64000.0000) GO INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (207, N'Jorge', N'F', N'IN', 75000.0000) GO INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (208, N'Nicholas', N'F', N'Ge', 71000.0000) GO INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (209, N'Lawrence', N'M', N'IN', 95000.0000) GO INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Gender], [StateCode], [Salary]) VALUES (210, N'Salvador', N'M', N'Co', 75000.0000) GO SET IDENTITY_INSERT [dbo].[Employee] OFF GO |
We have following records in Employee 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
|
1 2 3 4 5 6 |
SELECT CASE Expression When expression1 Then Result1 When expression2 Then Result2 ... ELSE Result END |
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.
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.
|
1 2 3 4 5 |
CASE WHEN ComparsionCondition THEN result WHEN ComparsionCondition THEN result ELSE other END |
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.
|
1 2 3 4 5 6 7 |
Select EmployeeName, CASE WHEN Salary >=80000 AND Salary <=100000 THEN 'Director' WHEN Salary >=50000 AND Salary <80000 THEN 'Senior Consultant' Else 'Director' END AS Designation from Employee |
In the following image you can see, we get designation as per condition specified in CASE statement.
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.
- For Female employee, employee salaries should come in descending order
- 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.
|
1 2 3 4 5 6 |
Select EmployeeName,Gender,Salary from Employee ORDER BY CASE Gender WHEN 'F' THEN Salary End DESC, Case WHEN Gender='M' THEN Salary END |
In the output, we have satisfied our sort requirement in ascending or descending order
- For Female employee, salary is appearing in descending order
- For Male employee, salary is appearing in ascending order
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Select CASE WHEN Salary >=80000 AND Salary <=100000 THEN 'Director' WHEN Salary >=50000 AND Salary <80000 THEN 'Senior Consultant' Else 'Director' END AS Designation, Min(salary) as MinimumSalary, Max(Salary) as MaximumSalary from Employee Group By CASE WHEN Salary >=80000 AND Salary <=100000 THEN 'Director' WHEN Salary >=50000 AND Salary <80000 THEN 'Senior Consultant' Else 'Director' END |
We have following output of this query. In this output, we get minimum and maximum salary for a particular designation.
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.
|
1 2 3 4 5 6 |
UPDATE employee SET StateCode = CASE StateCode WHEN 'Ar' THEN 'FL' WHEN 'GE' THEN 'AL' ELSE 'IN' END |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Declare @EmployeeName varchar(100) Declare @Gender int Declare @Statecode char(2) Declare @salary money Set @EmployeeName='Raj' Set @Gender=0 Set @Statecode='FL' set @salary=52000 Insert into employee values (@EmployeeName, CASE @Gender WHEN 0 THEN 'M' WHEN 1 THEN 'F' end, @Statecode, @salary) |
In the following screenshot, we can see the newly inserted row contains Gender M instead of value 0.
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 a 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 the Case statement in your queries.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023