How to Use the SQL CASE Expression with Values

By Allen G. Taylor

You can use a more compact form of the SQL CASE expression if you’re comparing a test value for equality with a series of other values. This form is useful within a SELECT or UPDATE statement if a table contains a limited number of values in a column and you want to associate a corresponding result value to each of those column values.

If you use CASE in this way, the expression has the following syntax:

CASE test_value
 WHEN value1 THEN result1
 WHEN value2 THEN result2
 ...
 WHEN valuen THEN resultn
 ELSE resultx
END

If the test value (test_value) is equal to value1, then the expression takes on the value result1. If tests_value is not equal to value1 but is equal to value2, then the expression takes on the value result2. The expression tries each comparison value in turn, all the way down to valuen, until it achieves a match.

If none of the comparison values equal the test value, then the expression takes on the value resultx. Again, if the optional ELSE clause isn’t present and none of the comparison values match the test value, the expression receives a null value.

To understand how the value form works, consider a case in which you have a table containing the names and ranks of various military officers. You want to list the names preceded by the correct abbreviation for each rank. The following statement does the job:

SELECT CASE RANK
   WHEN 'general'   THEN 'Gen.'
   WHEN 'colonel'   THEN 'Col.'
   WHEN 'lieutenant colonel' THEN 'Lt. Col.'
   WHEN 'major'    THEN 'Maj.'
   WHEN 'captain'   THEN 'Capt.'
   WHEN 'first lieutenant' THEN '1st. Lt.'
   WHEN 'second lieutenant' THEN '2nd. Lt.'
   ELSE NULL
  END,
   LAST_NAME
  FROM OFFICERS ;

The result is a list similar to the following example:

Capt. Midnight
Col. Sanders
Gen. Washington
Maj. Disaster
  Nimitz

Chester Nimitz was an admiral in the United States Navy during World War II. Because his rank isn’t listed in the CASE expression, the ELSE clause doesn’t give him a title.

For another example, suppose Captain Midnight gets a promotion to major and you want to update the OFFICERS database accordingly. Assume that the variable officer_last_name contains the value ‘Midnight’ and that the variable new_rank contains an integer (4) that corresponds to Midnight’s new rank, according to the following table.

New_Rank Rank
1 general
2 colonel
3 lieutenant colonel
4 major
5 captain
6 first lieutenant
7 second lieutenant
8 NULL

You can record the promotion by using the following SQL code:

UPDATE OFFICERS
 SET RANK = CASE :new_rank
     WHEN 1 THEN 'general'
     WHEN 2 THEN 'colonel'
     WHEN 3 THEN 'lieutenant colonel'
     WHEN 4 THEN 'major'
     WHEN 5 THEN 'captain'
     WHEN 6 THEN 'first lieutenant'
     WHEN 7 THEN 'second lieutenant'
     WHEN 8 THEN NULL
    END
 WHERE LAST_NAME = :officer_last_name ;

An alternative syntax for the CASE expression with values is:

CASE
 WHEN test_value = value1 THEN result1
 WHEN test_value = value2 THEN result2
 ...
 WHEN test_value = valuen THEN resultn
 ELSE resultx
END