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;
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!