Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
CASE and COALESCE
Perhaps the most commonly used function, which is classified as a system function, is CASE. There are two general uses of CASE. The first one is used to replace occurrences of one value with other values, as specified by the programmer. Syntax for this flavor of CASE is as follows:
SELECT column_name = CASE
WHEN column_name = 'a' THEN 'b'
...
ELSE 'c'
END
For instance, suppose that we want to specify the salary level for each job category within my publishing company. Using the pubs database, we could write the following query:
SELECT job_desc, salary_level =
CASE
WHEN job_desc = 'New Hire - Job Not Specified' THEN '25K'
WHEN job_desc = 'Chief Executive Officer' THEN '500K'
WHEN job_desc = 'Business Operations Manager' THEN '350K'
WHEN job_desc = 'Chief Financial Officier' THEN '400K'
WHEN job_desc = 'Publisher' THEN '75K'
WHEN job_desc = 'Managing Editor' THEN '65K'
WHEN job_desc = 'Marketing Manger' THEN '55K + commissions'
ELSE 'you get the idea'
END
FROM jobs
Results:
|
job_desc |
salary_level |
|
New Hire—job not specified |
25K |
|
Chief Executive Officer |
500K |
|
Business Operations Manager |
350K |
|
Chief Financial Officer |
400K |
|
Publisher |
75K |
|
Managing Editor |
65K |
|
Marketing Manager |
You get the idea |
|
Public Relations Manager |
You get the idea |
The other variation of CASE, which is sometimes referred to as the searched CASE, evaluates a Boolean expression and returns different values accordingly. For instance, we could use the searched CASE to categorize the price of titles as cheap, affordable, expensive, or unknown, as follows:
SELECT title_id, price, category= CASE
WHEN price IS NULL THEN 'unknown'
WHEN price < = 7 THEN 'cheap'
WHEN price BETWEEN 7.1 AND 15 THEN 'affordable'
ELSE 'expensive'
END
FROM titles
Results:
|
title_id |
price |
Category |
|
BU1032 |
19.99 |
Expensive |
|
BU1111 |
11.95 |
Affordable |
|
BU2075 |
2.99 |
Cheap |
|
BU7832 |
19.99 |
Expensive |
|
MC2222 |
19.99 |
Expensive |
|
MC3021 |
2.99 |
Cheap |
|
MC3026 |
NULL |
Unknown |
|
PC1035 |
22.95 |
Expensive |
|
PC8888 |
20 |
Expensive |
|
PC9999 |
NULL |
Unknown |
|
PS1372 |
21.59 |
Expensive |
|
PS2091 |
10.95 |
Affordable |
|
PS2106 |
7 |
Cheap |
|
PS3333 |
19.99 |
Expensive |
|
PS7777 |
7.99 |
Affordable |
|
TC3218 |
20.95 |
Expensive |
|
TC4203 |
11.95 |
Affordable |
|
TC7777 |
14.99 |
Affordable |
The COALESCE function hardly qualifies as a system function because it doesn't retrieve any system information. COALESCE simply returns the first value out of a list that is not NULL. COALESCE is a powerful tool if you are returning numerous values to a user and want to substitute occurrences of NULL with values from a different column or with an expression.
For instance, suppose that we need to return a list of cities and states. If a publisher is located in a country that does not have a state, we'll substitute a NULL value with an expression:
SELECT
city,
COALESCE(state, 'no state specified') AS state
FROM publishers
Results:
|
City |
State |
|
Boston |
MA |
|
Washington |
DC |
|
Berkeley |
CA |
|
Chicago |
IL |
|
Dallas |
TX |
|
München |
no state specified |
|
New York |
NY |
|
Paris |
no state specified |
The only requirement of COALESCE is that all expressions in the list must have compatible data types. For instance, you can't coalesce DATETIME and INTEGER. Notice that the COALESCE function can accept multiple values.