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.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.

  • thanks that really nice explained and two types of cases