Classifying Results with SQL CASE Statements

SQL includes a CASE statement that allows you to return varying results based upon the evaluation of expressions. You can use a CASE statement anywhere within a SQL statement that you would normally include an expression. CASE statements are often found in SELECT, UPDATE and DELETE statements as well as WHERE and IN clauses.

CASE Statement Syntax

There are two different types of CASE statements. The simple CASE statement takes an input value and compares it to a list of expressions to select the appropriate output value. The advanced CASE statement evaluates each of a set of expressions to select the appropriate output value.

The format of the simple CASE statement is:

CASE input
WHEN test THEN result
ELSE result
END

In this case, the input expression is compared to each one of the test values (there may be as amny WHEN clauses as you wish). If the input expression matches a test value, the CASE statement evaluates to the corresponding result. If the input expression does not match any of the test values, the CASE statement evaluates to the ELSE clause’s result.

The format of the advanced CASE statement is:

CASE
WHEN test THEN result
ELSE result
END

As with the basic CASE statement, you may include as many WHEN clauses as you wish. Each of the test expressions must be a Boolean expression (meaning it evaluates to either true or false). When the database encounters the CASE statement it begins walking through the WHEN clauses in order, testing the Boolean expressions. The CASE statement returns the result expression corresponding to the first test expression that evaluates to true. If no test expression is true, the CASE statement returns the result expression from the ELSE clause.

CASE Statements: An Example

Suppose that you have a database of participants in a wrestling competition and wish to classify your wrestlers by weight. The database contains the actual weight of each wrestler, to the nearest pound, but you wish the results to include the following official weight classes:

• Welterweight: up to 172 lbs
• Middleweight: 172-192 lbs
• Light Heavyweight: 193-214 lbs
• Cruiserweight: 215-220 lbs
• Heavyweight: more than 220 lbs

You can implement this using the following SQL query that includes a CASE statement:

SELECT first_name, last_name, weight_class =
CASE
WHEN weight<172 THEN 'Welterweight'
WHEN weight<=192 THEN 'Middleweight'
WHEN weight<=214 THEN 'Light heavyweight'
WHEN weight<=220 THEN 'Cruiserweight'
ELSE 'Heavyweight'
END
FROM athletes

Note that you don’t need to include the lower bound of each weight class. Remember that the CASE statement returns the first matching result, so by the time we get to the Middleweight option, we already know that the wrestler is at least 172 pounds because he didn’t fall into the Welterweight class.

Learning More

If you would like to learn more about the Structured Query Language, read Introduction to SQL or sign up for our free Learning SQL e-mail course.