CASE

This MySQL 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.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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

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

Explanation

This MySQL 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.

Examples

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 MySQL statement retrieves a list of clients and their telephone numbers, along with a description of their account type. However, the account type is a three-letter abbreviation, so CASE( ) is used to substitute each type with a more descriptive name.

The previous example uses the syntax in which a common parameter is evaluated to determine the possible result. The following MySQL 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 MySQL statement analyzes a table in MySQL 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 give a NULL value for the ELSE clause. Also, a CASE statement ends with END CASE.