Introduction of SQL CASE:
In this article we’ll learn about SQL CASE Statement. Like if-else the CASE statement goes through multiple conditions and returns a value when the first condition is met. When the condition meets, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
What you’ll learn below:
Syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result 2
-----------------------
WHEN conditionN THEN resultN
ELSE result
END;Let’s create a table with data and check SQL CASE statement:
CREATE TABLE [dbo].[User](
[Uid] [int] NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Stars] [int],
[Gander] [nvarchar](1) NULL
)
GO
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars], [Gander]) VALUES (1, N'Rakesh', N'kumar', N'5', N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars], [Gander]) VALUES (2, N'Mukesh', N'Kumar', N'5', N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars], [Gander]) VALUES (3, N'Ram', N'Singh', N'4', N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars], [Gander]) VALUES (4, N'Merry', N'smith', N'3', N'F')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars], [Gander]) VALUES (5, N'Sham', N'Kumar', N'2', N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars], [Gander]) VALUES (6, N'Thomas', N'Hawkins', N'1', N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars], [Gander]) VALUES (7, N'Garry', N'Smith', N'4', N'F')
GO
SQL CASE with example:
The following SQL goes through conditions and returns a value when the first condition is met.
Select FirstName, Lastname,
CASE
WHEN stars >= 4 THEN 'Excellent Rating'
WHEN stars <= 2 THEN 'poor'
ELSE 'Average Rating '
END AS UserRating
,
CASE
WHEN Gander = 'M' THEN 'Male'
WHEN Gander = 'F' THEN 'Female'
ELSE 'NA'
END AS Gander
from [user]In above query we have added two case statements. You can add multiple Case statements as per your need in query. First case will check stars and display meaningful user Rating and second CASE will display meaningful Gander rather than M or F.
Adding multiple conditions in CASE using AND:
Select FirstName, Lastname,
CASE
WHEN stars >= 1 and Stars<= 2 THEN 'Poor Rating '
WHEN stars >= 3 and Stars <=4 THEN 'Average Rating'
ELSE 'Excellent Rating'
END AS UserRating
from [user]We can add multiple AND in CASE statement to meet our requirement. In above example we have shown stars 1 & 2 as “Poor Rating” 3 & 4 as “Average Rating” and 5 as “Excellent Rating”.
Output:
Nesting of multiple CASE Statement:
Now we have changed the requirement with reference to “Male” and “Female” users for displaying their “User ratings”. Now with in the same SQL query, we’ll nest the case statement and achieve the same. Lest check out below.
For Males:
- 1 & 2 stars will be ‘Poor rating’
- 3 & 4 stars will be ’Average rating’.
- 5 will be ‘Excellent rating’.
For Females:
- 1 star will be ‘Poor rating’ only.
- 2 & 3 stars will be ‘Average rating’.
- 4 & 5 will be ‘Excellent rating’.
Select FirstName, Lastname,
CASE
when gander ='M' then
case
WHEN stars >= 1 and Stars<= 2 THEN 'Poor Rating '
WHEN stars >= 3 and Stars <=4 THEN 'Average Rating'
ELSE 'Excellent Rating'
END
when gander ='F' then
case
WHEN stars = 1 THEN 'Poor Rating '
WHEN stars >= 2 and Stars <=3 THEN 'Average Rating'
ELSE 'Excellent Rating'
END
END AS UserRating,Stars,Gander
from [user]Check out the below results, stars=4 showing “Average rating” for Male (record number -3) whereas same stars=4 is “Excellent Rating” for female. So, we have achieved the required results by nesting of CASE statement.
I hope this article has given you deep understanding of CASE statement. Now you will be able it uses this in you daily routine SQL development. If you have any comments or suggestion, feel free to post in comment section. For more information you can check out Microsoft Docs
For more knowledge of MS SQL, you can check this section HERE