SQL Searched CASE Statement

There are two types of CASE statements.

SQL Searched CASE Expression:

CASE
WHEN condition1 THEN result_expression1
[ WHEN condition2 THEN result_expression2 ]
[ ELSE result_expression ]
END

If there is no matching condition in any of the WHEN conditions in the SQL CASE Search statement, and if there is also no ELSE condition, then a NULL value will be returned.

SELECT FIRSTNAME, LASTNAME,
CASE
WHEN AGE < 13 THEN 'CHILD'
WHEN AGE < 19 THEN 'TEENAGER'
WHEN AGE > 19 THEN 'ADULT'
ELSE 'UNKNOWN'
END AS LIFE_STAGE
FROM PEOPLE

simple CASE Statement:
SELECT LANGUAGE_NAME, RATING_CODE AS RATING,
CASE RATING_CODE
WHEN '3' THEN 'DIFFICULT'
WHEN '2' THEN 'FAIRLY DIFFICULT'
WHEN '1' THEN 'EASY'
ELSE 'UNKNOWN'
END AS RATING_DESCRIPTION
FROM PROGRAMMING_LANGUAGES;

In the simple CASE statement above, the value in the RATING_CODE column will just be checked to see if it’s equal to the value in the “WHEN” statement. The searched CASE Statement does not use the value in the column name, but rather offers more flexibility because you can do any sort of comparison you want in the “WHEN” statements.
Read full article from SQL Searched CASE Statement