Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 to determine the possible result. The following SQL statement utilizes the other syntax for the function:

SELECT CONCAT(name_last, SPACE(1), name_first) AS Prospect,
CASE
  WHEN YEAR(NOW( )) - YEAR(birth_date) ≤ 17 THEN 'Minor'
  WHEN YEAR(NOW( )) - YEAR(birth_date) > 17 < 26 THEN 'Too Young'
  WHEN YEAR(NOW( )) - YEAR(birth_date) > 60 THEN 'Elderly'
  ELSE home_telephone;
END
AS Telephone
FROM prospects;

In this example, the SQL statement analyzes a table containing a list of people that the broker might call to buy an investment. The table contains the birth dates and the telephone numbers of each prospect. The SQL statement provides the telephone numbers only for prospects aged 26 to 60 because anyone younger or older would not be suitable for this particular investment. However, a message for each prospect that is disqualified is given based on the clauses of the CASE() statement.

When using a CASE statement within a stored procedure, it cannot be given a NULL value for the ELSE clause. Also, a CASE statement ends with END CASE.