MySQL in a Nutshell, 2nd Edition

Name

CASE

Synopsis

CASE value
  WHEN [value] THEN result
   . . .
  [ELSE result]
END

CASE
  WHEN [condition] THEN result
   . . .
 [ELSE result]
END

This function produces results that vary based on which condition is true. It is similar to the IF() function, except that multiple conditions and results may be strung together. In the first syntax shown, the value given after CASE is compared to each WHEN value. If a match is found, the result given for the THEN is returned. The second syntax tests each condition independently, and they are not based on a single value. For both syntaxes, if no match is found and an ELSE clause is included, the result given for the ELSE clause is returned. If there is no match and no ELSE clause is given, NULL is returned.

If the chosen result is a string, it is returned as a string data type. If result is numeric, the result may be returned as a decimal, real, or integer value.

Here’s an example of the first syntax shown:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Client,
telephone_home AS Telephone,
CASE type
  WHEN 'RET' THEN 'Retirement Account'
  WHEN 'REG' THEN 'Regular Account'
  WHEN 'CUS' THEN 'Minor Account'
END AS 'Account Type'
FROM clients;

This SQL statement retrieves a list of clients and their telephone numbers, along with a description of their account types. However, the account type is a three-letter abbreviation, so CASE() is used to substitute each type with a more descriptive name.

This example uses the syntax in which a common parameter is evaluated ...