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.
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 1"Download the Code" 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.
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
in the Movie table to be included in the result set, even if there are no corresponding records in the MovieReview table. Non-corresponding MovieReview records are represented by NULL values. Because this code is filtering the records to only show reviews conducted by the employee whose ID is 1, you have to include the code OR EmployeeID IS NULL in the WHERE clause. Otherwise, these non-corresponding MovieReview records would be removed from the result set. The CASE function's ELSE clause transforms each NULL in the Stars column to the string Not yet rated in the Short Review column. Figure 1 shows the results.
An Example of a Searched CASE Function
To see a searched CASE function in action, execute the code in Listing 2 in the MyDB database.
This query produces a "career advice" report based on the salary information stored in the Employee table. The CASE function examines the salary of each employee and produces a custom message depending on the range the salary falls within. I wrapped the salary ranges in parentheses for readability and to indicate that each range statement is a Boolean expression. Figure 2 shows the results.
In Figure 2, note that Garret Testerson's salary is $100,000, which matches the upper range specified in the BETWEEN 75000 AND 100000 portion of the WHEN clause highlighted in callout A in Listing 2. Because the BETWEEN clause is inclusive (i.e., both the upper and lower values specified are included in the range), this Boolean expression evaluates to true. Garret's salary also meets the condition in the last WHEN clause, but by this point, the CASE function has already found a match and has terminated.
The Case for Using CASE
Simple and searched CASE functions let you easily transform and categorize data stored in databases. As the code in Listing 1 and Listing 2 show, you can use CASE functions in SELECT statements. However, you aren't limited to SELECT queries. You can use a CASE function to transform data in an UPDATE statement, in an INSERT statement, or wherever a valid SQL Server expression is required. So, the next time you're faced with having to transform data, try using a CASE function. You'll be glad you did.