Executive Summary:
T-SQL's CASE function comes in two types: simple and searched. Simple CASE functions examine an expression and compare it to a list of expressions. If a match is found, T-SQL returns a specified result. Searched CASE functions examine a set of Boolean expressions. If a Boolean expression evaluates to true, T-SQL returns a specified result. |
Data is often stored in a format that's optimized for speed and efficiency but not necessarily understandability. Making sense of long lists of numerical codes might be second nature for some, but most people prefer to have more meaningful information displayed. Using T-SQL's CASE function, you can quickly transform data from one format to another. Before I tell you about the two types of CASE functions and give examples of how to use them, you need to make sure you have the right tables to run the sample code.
The Prerequisites
To run the sample code in this lesson, make sure your MyDB database contains the following tables:
- The Employee table created in Lesson 3
- The Movie table created in Lesson 5
- The revised MovieReview table created in Lesson 6 (and not the original MovieReview table created in Lesson 4)
If you haven't created these database objects, you'll find the code in the 100152.zip file. To download this file, click the 100152.zip hotlink at the top of the page.
The Two Types of CASE Functions
The CASE function is a powerful tool for evaluating several conditions and returning a single value for the first condition met. This function comes in two types: simple and searched. The simple CASE function examines an expression and compares it to a list of expressions. If a match is found, a specified result is returned. The searched CASE function examines a set of Boolean expressions. If a Boolean expression evaluates to true, a specified result is returned.
The simple CASE function consists of the following components:
- The CASE keyword
- The input expression to be evaluated
- One or more WHEN clauses that specify a comparison expression
- For every WHEN clause, one THEN clause that specifies the expression to be returned when a match is found
- An optional ELSE clause that specifies the expression to be returned when no matches are found
- The END keyword
The searched CASE function consists of the following components:
- The CASE keyword
- One or more WHEN clauses specifying a Boolean expression to be evaluated
- For every WHEN clause, one THEN clause that specifies the expression to be returned when the Boolean expression evaluates to true
- An optional ELSE clause that specifies the expression to be returned when no Boolean expressions evaluate to true
- The END keyword
When you're writing a CASE function, you need to pay special attention to the ordering of the WHEN clauses. CASE functions evaluate WHEN clauses in sequential order and terminate on the first match found.
An Example of a Simple CASE Function
To see a simple CASE function in action, execute the code in Listing 1 in the MyDB database.
Listing 1: Query That Uses a Simple CASE Function |
 |
This query uses the Movie and MovieReview tables to produce a list of short reviews for the movies seen by a particular employee. The query's CASE function adds a short review based on the value in the Movie table's Stars column.
Note the use of the RIGHT OUTER JOIN clause in callout A in Listing 1. As I discussed in "T-SQL 101, Lesson 5," this type of join causes all records