SQL CASE Statement

SQL CASE statement is very powerful and versatile. It can be used in SELECT, UPDATE and INSERT statement. Even it can be used in ORDER BY and GROUP BY clause. Let’s examine them one by one.

Syntax

First check out the syntax of CASE statement:

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2

...
WHEN conditionN THEN resultN
ELSE result
END;

Data Preparation

First chose your preferred database and run below queries for creating a table “Persons” and populating with data.

IF OBJECT_ID('Persons', 'U') IS NOT NULL
DROP TABLE Persons;
GO

CREATE TABLE Persons(
name varchar(20)
,age int
,sex varchar(2)
)

GO

INSERT INTO Persons (name,age,sex) values('Bob',5, 'M');
INSERT INTO Persons (name,age,sex) values('Harry',15, 'M');
INSERT INTO Persons (name,age,sex) values('Jasmine',25, 'F');
INSERT INTO Persons (name,age,sex) values('Fanny',65, 'F');
INSERT INTO Persons (name,age,sex) values('Evan',-1, 'N');

GO

SELECT Statement

Let’s use it in our SELECT statement.

SELECT Name, age as 'Actual Age',
CASE
WHEN age >= 0 AND age <= 12 THEN 'CHILD'
WHEN age >= 13 AND age <= 19 THEN 'TEEN AGE'
WHEN age >= 20 AND age <= 40 THEN 'YOUNG'
WHEN age >= 41 AND age <= 60 THEN 'MIDDLE AGE'
WHEN age >= 61 THEN 'OLD'
ELSE 'NOT DEFINED' END as 'New Age using CASE Stmt',
sex as 'Actual Gender',
CASE
WHEN sex = 'M' THEN 'MALE'
WHEN sex = 'F' THEN 'FEMALE'
WHEN sex = 'N' THEN 'DONT WANT TO DISCLOSE'
END as  'New Gender  using CASE Stmt'
FROM Persons

Output

INSERT Statement

DECLARE @age INT
DECLARE @sex VARCHAR(10)

SET @age = 40
SET @sex = 'MALE'
INSERT INTO Persons (Name, Age, Sex)
VALUES(
'Jack',
CASE WHEN @age < 0 THEN -1 ELSE @age END,
CASE WHEN @sex = 'MALE' THEN 'M'
WHEN @sex = 'FEMALE' THEN 'F'
ELSE 'N' END
)

Output

If we run the our first SELECT statement again, then output would be:

UPDATE Statement

DECLARE @age INT
SET @age = -4

UPDATE Persons SET
age = CASE WHEN @age < 0 THEN -1 ELSE @age END
WHERE Name = 'Bob'

Output

If we run the above SELECT statement again, then output would be:

ORDER BY Clause

CASE can be added in ORDER BY clause in above SELECT statement
SELECT Name, age as 'Actual Age',
CASE
WHEN age >= 0 AND age <= 12 THEN 'CHILD'
WHEN age >= 13 AND age <= 19 THEN 'TEEN AGE'
WHEN age >= 20 AND age <= 40 THEN 'YOUNG'
WHEN age >= 41 AND age <= 60 THEN 'MIDDLE AGE'
WHEN age >= 61 THEN 'OLD'
ELSE 'NOT DEFINED' END as 'New Age using CASE Stmt',
sex as 'Actual Gender',
CASE
WHEN sex = 'M' THEN 'MALE'
WHEN sex = 'F' THEN 'FEMALE'
WHEN sex = 'N' THEN 'DONT WANT TO DISCLOSE'
END as  'New Gender  using CASE Stmt'
FROM Persons
ORDER BY
CASE WHEN sex='M' THEN age END,
CASE WHEN sex='F' THEN Name END

Output

GROUP BY Clause

Now time to explore CASE in GROUP BY clause in our first SELECT statement:

SELECT
CASE
WHEN age <= 40 THEN 'YOUNG'
WHEN age >= 41 THEN 'OLD'
END as 'New Age using CASE Stmt', COUNT(*)
FROM Persons
GROUP BY CASE
WHEN age <= 40 THEN 'YOUNG'
WHEN age >= 41 THEN 'OLD'
END

Output

We covered all cases of our today’s CASE topic.

Stay Home! Stay Safe!

It was first published in my blog.

 2,077 views last month,  2 views today

About M A A Mehedi Hasan

This is M A A Mehedi Hasan working in EBS Group since 2005. Starting career as a Software Developer, I am now Chief Technology Officer (CTO) of EBS Group. In this long journey, I have got opportunity to work with various technologies like Microsoft Data Platform, Telecom Value Added Service, and Audio/Video streaming.I have hands-on experience in managing Telecom Value Added projects, Web/App/Audio/Video streaming projects. Apart from these, I also manage large database, testing and deployment, performance tuning, long term capacity planning, and streamlining operational workflow.Being a member of techforumbd (https://www.facebook.com/techforumbd), a Bangladeshi tech community, I regularly speak and organize sessions at local and virtual PASS chapters, SQL Saturdays, and Azure conferences. In free time, I love to play with Machine Learning. By the way, you can count me as a traveler too!

One comment

  1. Thanks Mehedi bhai

Leave a Reply

Do NOT follow this link or you will be banned from the site!