Home > Articles > Data > SQL Server

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account