How to Use the SQL CASE Expression with Search Conditions
One powerful way to use the SQL CASE expression is to search a table for rows in which a specified search condition is True. If you use CASE this way, the expression uses the following syntax:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionn THEN resultn ELSE resultx END
CASE examines the first qualifying row (the first row that meets the conditions of the enclosing WHERE clause, if any) to see whether condition1 is True. If it is, the CASE expression receives a value of result1.
If condition1 is not True, CASE evaluates the row for condition2. If condition2 is True, the CASE expression receives the value of result2, and so on. If none of the stated conditions are True, the CASE expression receives the value of resultx.
The ELSE clause is optional. If the expression has no ELSE clause and none of the specified conditions are True, the expression receives a null value. After the SQL statement containing the CASE expression applies itself to the first qualifying row in a table and takes the appropriate action, it processes the next row. This sequence continues until the SQL statement finishes processing the entire table.
Update values based on a condition
Because you can embed a CASE expression within an SQL statement almost anywhere a value is possible, this expression gives you tremendous flexibility. You can use CASE within an UPDATE statement, for example, to make changes to table values — based on certain conditions. Consider the following example:
UPDATE FOODS SET RATING = CASE WHEN FAT < 1 THEN 'very low fat' WHEN FAT < 5 THEN 'low fat' WHEN FAT < 20 THEN 'moderate fat' WHEN FAT < 50 THEN 'high fat' ELSE 'heart attack city' END ;
This statement evaluates the WHEN conditions in order until the first True value is returned, after which the statement ignores the rest of the conditions.
A database table that shows the fat content of 100 grams of certain foods can contain a RATING column that gives a quick assessment of the fat content’s meaning. If you run the preceding UPDATE on the FOODS table, the statement assigns asparagus a value of very low fat, gives chicken a value of low fat, and puts roasted almonds in the heart attack city category.
Avoid conditions that cause errors
Another valuable use of CASE is exception avoidance — checking for conditions that cause errors.
Consider a case that determines compensation for salespeople. Companies that compensate their salespeople by straight commission often pay their new employees by giving them a draw against the future commissions they’re expected to earn. In the following example, new salespeople receive a draw against commission; the draw is phased out gradually as their commissions rise:
UPDATE SALES_COMP SET COMP = COMMISSION + CASE WHEN COMMISSION > DRAW THEN 0 WHEN COMMISSION < DRAW THEN DRAW END ;
If the salesperson’s commission is zero, the structure in this example avoids a division-by-zero operation, which would cause an error if allowed to happen. If the salesperson has a nonzero commission, the total compensation is the commission plus a draw that’s reduced in proportion to the size of the commission.
All of the THEN expressions in a CASE expression must be of the same type — all numeric, all character, or all date. The result of the CASE expression is also of the same type.