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.