Home arrow MySQL arrow Page 3 - Advanced Query Writing, concluded

The CASE Expression - MySQL

This article, the second of two parts, covers some advanced topics concerning SQL queries and functions. It is excerpted from chapter six of the book SQL DeMYSTiFied, written by Andrew Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).

TABLE OF CONTENTS:
  1. Advanced Query Writing, concluded
  2. Using SQL to Generate SQL
  3. The CASE Expression
  4. Quiz
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 8
March 09, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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



 
 
>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: