Advanced Query Writing, concluded - The CASE Expression

(Page 3 of 4 )

The CASE expression is a recent addition to the SQL standard but an important one. For the first time, parts of SQL statements can be executed conditionally. For example, a column in the query results may be formatted based on the values contained in another column. However, your SQL implementation may not support it just yet because it is so new.

The CASE expression allows two general forms.

Simple CASE Expression

Here is the general syntax of the simple form of the CASE expression:

CASE input_expression
  WHEN comparison_expression THEN result_expression
 [WHEN comparison_expression THEN result_expression ...]
 [ELSE result_expression]
END

NOTE:

• Each WHEN condition is evaluated as input_expression = comparision_expression, and if the result is a logical TRUE, the result_expression is returned and no other WHEN conditions are evaluated.
  
• If none of the WHEN conditions evaluates to TRUE, and there is an ELSE condition, the result_expression associated with the ELSE condition is returned.
  
• If none of the WHEN conditions evaluates to TRUE, and there is no ELSE condition, a null value is returned.

As an example, you can use the CASE expression to translate the MPAA Rating Code to a simple message that can be displayed at the checkout counter in the video store to remind sales clerks to check customer ages for movies rated above PG-13. Note the placement of the AS keyword just after the END keyword to assign a column name to the generated column in the result set. Here is the example:

SELECT MOVIE_ID, MPAA_RATING_CODE AS RATING,
  CASE MPAA_RATING_CODE
     WHEN 'G' THEN 'All ages'
     WHEN 'PG' THEN 'Parental guidance'
     WHEN 'PG-13' THEN 'Ages 13 and up'
     ELSE 'MUST be at least 17'
     END AS RATING_DESC
  FROM MOVIE
ORDER BY MOVIE_ID;
 MOVIE_ID RATING RATING_DESC
--------- ------ -------------------
        1 R      MUST be at least 17
        2 R      MUST be at least 17
        3 PG-13  Ages 13 and up
        4 PG-13  Ages 13 and up
        5 R      MUST be at least 17
        6 PG-13  Ages 13 and up
        7 PG-13  Ages 13 and up
        8 R      MUST be at least 17
        9 PG-13  Ages 13 and up
       10 R      MUST be at least 17
       11 PG-13  Ages 13 and up
       12 PG-13  Ages 13 and up
       13 PG-13  Ages 13 and up
       14 R      MUST be at least 17
       15 R      MUST be at least 17
       16 PG-13  Ages 13 and up
       17 PG-13  Ages 13 and up
       18 R      MUST be at least 17
       19 PG-13  Ages 13 and up
       20 R      MUST be at least 17

Searched CASE Expression

The so-called searched CASE expression allows for more flexible comparison conditions because each one is written as a complete condition, including the comparison operator. Here is the general syntax:

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

NOTE:

• Each condition can be any SQL expression that evaluates to TRUE or FALSE.
  
• Each WHEN is evaluated in sequence, and if one of them evaluates to TRUE, the associated result_condition is returned and no other WHEN conditions are evaluated.
  
• If none of the WHEN conditions evaluates to TRUE, and there is an ELSE condition, the result_expression associated with the ELSE condition is returned.
  
• If none of the WHEN conditions evaluates to TRUE, and there is no ELSE condition, a null value is returned.

As an example, here is a query that classifies VHS movies by price range:

SELECT MOVIE_ID, RETAIL_PRICE_VHS,
   CASE
     WHEN RETAIL_PRICE_VHS IS NULL THEN 'Not Available'
     WHEN RETAIL_PRICE_VHS < 10 THEN 'Bargain'
     WHEN RETAIL_PRICE_VHS < 20 THEN 'Budget'
     WHEN RETAIL_PRICE_VHS < 40 THEN 'Average'
     ELSE 'Premium'
  END AS PRICE_CATEGORY
 FROM MOVIE
ORDER BY MOVIE_ID;
 MOVIE_ID RETAIL_PRICE_VHS PRICE_CATEGORY
--------- ---------------- --------------
        1            58.97 Premium
        2            15.95 Budget
        3            14.95 Budget
        4            11.95 Budget
        5            24.99 Average
        6            24.99 Average
        7            14.95 Budget
        8            50.99 Premium
        9            12.98 Budget
       10            49.99 Premium
       11             6.93 Bargain
       12             9.95 Bargain
       13             6.93 Bargain
       14            24.99 Average
       15             9.99 Bargain
       16            11.69 Budget
       17            14.94 Budget
       18            24.99 Average
       19            12.98 Budget
       20            17.99 Budget

Next: Quiz >>
 

More MySQL Articles
More By McGraw-Hill/Osborne

Comments On: Advanced Query Writing, concluded

· This article is an excerpt from the book "SQL DeMYSTified," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter six of the book SQL DeMYSTiFied, written by Andrew Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249). Check it out today at your favorite bookstore. Buy this book now.