SQL Case Expression-SQL Conditions

SQL case are used to evaluate a set of expression and return the expression that fulfils the condition.

There are two types of SQL CASE –

Simple CASE
Searched CASE

The simple CASE expression compares an expression to a set of simple expressions to determine the result. The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Syntax:

CASE EXPRESSION
WHEN EXP_1 THEN
result_1
WHEN EXP_2 THEN
result_2
When EXP_3 THEN
result_3
.
.
.
ELSE default_result
END

The SQL CASE EXPRESSION above will evaluate itself with other expressions (EXP_1, EXP_2, EXP_3) and outputs the suitable result.

Example 1:

To showcase the use of CASE we will use the table named ‘EMP’

SELECT *,
CASE 
	WHEN EMP_AGE BETWEEN 22 AND 30 THEN 'YOUNG'
	WHEN EMP_AGE BETWEEN 31 AND 45 THEN 'MIDDLE AGE'
	WHEN EMP_AGE BETWEEN 46 AND 55 THEN 'AGED'
	WHEN EMP_AGE > 55 THEN 'CLOSE TO RETIREMENT'
	END 'AGE_BRACKET'
FROM EMP;

As you can see there is a new column named ‘AGE_BRACKER’ where ages are categorized into different category. We did not use the ELSE statement hence there are NULLs in some places

EXAMPLE 2 :

SELECT *,
CASE (department_id / 100)
	WHEN 1 THEN 'IT'
	WHEN 2 THEN 'HR'
	WHEN 3 THEN 'FINANCE'
	WHEN 4 THEN 'MARKETING'
	WHEN 5 THEN 'SALES'
	END 'DEPT_NAME'
FROM EMP;

Related Posts