CASE Statement SyntaxThere 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 inputIn 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.
WHEN test THEN result
The format of the advanced CASE statement is:
CASEAs 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.
WHEN test THEN result
CASE Statements: An ExampleSuppose 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
SELECT first_name, last_name, weight_class =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.
WHEN weight<172 THEN 'Welterweight'
WHEN weight<=192 THEN 'Middleweight'
WHEN weight<=214 THEN 'Light heavyweight'
WHEN weight<=220 THEN 'Cruiserweight'