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.
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.
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;
Read More: Common Table Expression(CTE)
Thanks For Reading!