What is the SQL Searched CASE Expression? Provide an example and explanation of the searched CASE statement.
The SQL searched CASE expression gives you more flexibility when writing your comparison conditions. The reason you get more flexibility is because of the fact that each comparison condition is written out as a complete condition, and the comparison operator (like “>”, “<", etc.) is also written out in the condition.
Syntax of SQL Searched CASE Statement
Here is the general syntax for the searched CASE statement:
CASE WHEN condition1 THEN result_expression1 [ WHEN condition2 THEN result_expression2 ] [ ELSE result_expression ] END
How the SQL Searched CASE Expression works
In the SQL CASE statement shown above, each WHEN statement will be evaluated to see if the condition is TRUE. And, if that particular condition returns TRUE, the corresponding result_expression will be returned and none of the other WHEN conditions will be evaluated. Basically the first matching WHEN condition will “win”. We go through an actual simple example below of how the SQL CASE statement can be used so that you can see it in action.
What if no matching condition in the SQL Searched CASE statement?
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.
SQL Searched CASE Statement Example
Let’s go through an example of how the searched CASE statement can be used.
Suppose we have a table called People, and columns for FIRSTNAME, LASTNAME, and AGE. Let’s say that we want to classify people as a Child (less than 13 years old), a Teenager (13 – 19 years old), or an Adult (20 years or older) in the results returned from a SQL statement.
Let’s write a SQL Searched CASE Expression to do that for us:
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
Here are some sample results from running the SQL statement above:
FIRSTNAME LASTNAME AGE LIFE_STAGE TIM HARDY 12 CHILD SAM HARRIS 51 ADULT JOE HARVEY 15 TEENAGER
SQL Searched CASE Statement versus normal CASE Statement
Let’s also compare the SQL Searched CASE Statement to the normal, simpler SQL Case Statement. In that article, the simple CASE statement looks like this:
Example Code of a 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;
Repeated Example Code of searched CASE Statement
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
The searched CASE Statement versus simple CASE expression
In the simple CASE statement above, note that we highlighted the column name RATING_CODE in red, because that is the major difference between the normal, simpler CASE statement and the Searched CASE Statement. 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. And that is the primary difference between the two types of CASE statements.