Single-Table Queries in Microsoft SQL Server 2012
- 7/15/2012
CASE Expressions
A CASE expression is a scalar expression that returns a value based on conditional logic. Note that CASE is an expression and not a statement; that is, it doesn’t let you control flow of activity or do something based on conditional logic. Instead, the value it returns is based on conditional logic. Because CASE is a scalar expression, it is allowed wherever scalar expressions are allowed, such as in the SELECT, WHERE, HAVING, and ORDER BY clauses and in CHECK constraints.
The two forms of CASE expression are simple and searched. The simple form allows you to compare one value or scalar expression with a list of possible values and return a value for the first match. If no value in the list is equal to the tested value, the CASE expression returns the value that appears in the ELSE clause (if one exists). If a CASE expression doesn’t have an ELSE clause, it defaults to ELSE NULL.
For example, the following query against the Production.Products table uses a CASE expression in the SELECT clause to produce the description of the categoryid column value.
SELECT productid, productname, categoryid,
CASE categoryid
WHEN 1 THEN 'Beverages'
WHEN 2 THEN 'Condiments'
WHEN 3 THEN 'Confections'
WHEN 4 THEN 'Dairy Products'
WHEN 5 THEN 'Grains/Cereals'
WHEN 6 THEN 'Meat/Poultry'
WHEN 7 THEN 'Produce'
WHEN 8 THEN 'Seafood'
ELSE 'Unknown Category'
END AS categoryname
FROM Production.Products;
This query produces the following output, shown in abbreviated form.
productid productname categoryid categoryname ----------- ------------------- ----------- ---------------- 1 Product HHYDP 1 Beverages 2 Product RECZE 1 Beverages 3 Product IMEHJ 2 Condiments 4 Product KSBRM 2 Condiments 5 Product EPEIM 2 Condiments 6 Product VAIIV 2 Condiments 7 Product HMLNI 7 Produce 8 Product WVJFP 2 Condiments 9 Product AOZBW 6 Meat/Poultry 10 Product YHXGE 8 Seafood ... (77 row(s) affected)
The preceding query is a simple example of using the CASE expression. Unless the set of categories is very small and static, your best design choice is probably to maintain (for example) the product categories in a table, and join that table with the Products table when you need to get the category descriptions. In fact, the TSQL2012 database has just such a Categories table.
The simple CASE form has a single test value or expression right after the CASE keyword that is compared with a list of possible values in the WHEN clauses. The searched CASE form is more flexible because it allows you to specify predicates, or logical expressions, in the WHEN clauses rather than restricting you to equality comparisons. The searched CASE expression returns the value in the THEN clause that is associated with the first WHEN logical expression that evaluates to TRUE. If none of the WHEN expressions evaluates to TRUE, the CASE expression returns the value that appears in the ELSE clause (or NULL if an ELSE clause is not specified). For example, the following query produces a value category description based on whether the value is less than 1,000.00, between 1,000.00 and 3,000.00, or greater than 3,000.00.
SELECT orderid, custid, val,
CASE
WHEN val < 1000.00 THEN 'Less than 1000'
WHEN val BETWEEN 1000.00 AND 3000.00 THEN 'Between 1000 and 3000'
WHEN val > 3000.00 THEN 'More than 3000'
ELSE 'Unknown'
END AS valuecategory
FROM Sales.OrderValues;
This query generates the following output.
orderid custid val valuecategory ----------- ----------- -------- ---------------------- 10248 85 440.00 Less than 1000 10249 79 1863.40 Between 1000 and 3000 10250 34 1552.60 Between 1000 and 3000 10251 84 654.06 Less than 1000 10252 76 3597.90 More than 3000 10253 34 1444.80 Between 1000 and 3000 10254 14 556.62 Less than 1000 10255 68 2490.50 Between 1000 and 3000 10256 88 517.80 Less than 1000 10257 35 1119.90 Between 1000 and 3000 ... (830 row(s) affected)
You can see that every simple CASE expression can be converted to the searched CASE form, but the reverse is not necessarily true.
T-SQL supports some functions that you can consider as abbreviations of the CASE expression: ISNULL, COALESCE, IIF, and CHOOSE. Note that of the four, only COALESCE is standard. Also, IIF and CHOOSE are available only in SQL Server 2012.
The ISNULL function accepts two arguments as input and returns the first that is not NULL, or NULL if both are NULL. For example ISNULL(col1, ‘’) returns the col1 value if it isn’t NULL, and an empty string if it is NULL. The COALESCE function is similar, only it supports two or more arguments and returns the first that isn’t NULL, or NULL if all are NULL. As mentioned earlier, when there’s a choice, it is generally recommended that you use standard features, hence it is recommended that you use the COALESCE function and not ISNULL.
The nonstandard IIF and CHOOSE functions were added in SQL Server 2012 to support easier migrations from Microsoft Access. The function IIF(<logical_expression>, <expr1>, <expr2>) returns expr1 if logical_expression is TRUE and expr2 otherwise. For example, the expression IIF(col1 <> 0, col2/col1, NULL) returns the result of col2/col1 if col1 is not zero, otherwise it returns a NULL. The function CHOOSE(<index>, <expr1>, <expr2>, …, <exprn>) returns the expression from the list in the specified index. For example, the expression CHOOSE(3, col1, col2, col3) returns the value of col3. Of course, actual expressions that use the CHOOSE function tend to be more dynamic—for example, relying on user input.
So far, I’ve just used a few examples to familiarize you with the CASE expression and functions that can be considered abbreviations of the CASE expression. Even though it might not be apparent at this point from these examples, the CASE expression is an extremely powerful and useful language element.