MySQL in a Nutshell

Name

CASE( )

Synopsis

CASE value

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

END

CASE

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

This function allows a particular result from a list of results to be chosen based on various conditions. It is similar to the IF( ) function except that multiple conditions and results may be strung together. In the first syntax shown, value is compared to each WHEN value. The second syntax tests each condition independently, and they are not based on a single value. Both syntaxes return NULL if there is no match and no ELSE clause.

Here is an example of the first syntax:

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 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 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) < 18 THEN 'Minor' WHEN YEAR(NOW( )) - YEAR(birth_date) > 17 < 26 THEN 'Too Young' WHEN YEAR(NOW( )) - YEAR(birth_date) ...