SQL CASE

List the availability of all products.

SELECT ProductName AS Product,
       CASE IsDiscontinued
         WHEN 0 THEN 'Available'
         WHEN 1 THEN 'Not Available'
         ELSE 'Invalid'
       END AS Availability
  FROM Product

ProductName	Availability
Chai	Available
Chang	Available
Aniseed Syrup	Available
Chef Anton's Cajun Seasoning	Available
Chef Anton's Gumbo Mix	Not Available
Grandma's Boysenberry Spread	Available
Uncle Bob's Organic Dried Pears	Available

There are two different CASE formats: simple and searched.

Syntax for the simple CASE format.

CASE input_expression
 WHEN when_expression1 THEN result_expression1
 WHEN when_expression2 THEN result_expression2
 ...
 [ ELSE else_result_expression ]
END

The when-expressions are evaluated against the input-expression.

Syntax for the searched CASE format.

CASE
 WHEN if_expression1 THEN result_expression1
 WHEN if_expression2 THEN result_expression2
 ...
 [ ELSE else_result_expression ]
END

The if-expressions are arbitrary logic expressions.

CASE statements can have any number of WHEN expressions. ELSE is optional.

SELECT CompanyName, Country,
       CASE Country
         WHEN 'Australia' THEN 'English'
         WHEN 'Brazil' THEN 'Portuguese'
         WHEN 'Canada' THEN 'English'
         WHEN 'Denmark' THEN 'Danish'
         WHEN 'Finland' THEN 'Finnish'
         WHEN 'France' THEN 'French'
         WHEN 'Germany' THEN 'German'
         WHEN 'Italy' THEN 'Italian'
         WHEN 'Japan' THEN 'Japanese'
         WHEN 'Netherlands' THEN 'Dutch'
         WHEN 'Norway' THEN 'Norwegian'
         WHEN 'Singapore' THEN 'English'
         WHEN 'Spain' THEN 'Spanish'
         WHEN 'Sweden' THEN 'Swedish'
         WHEN 'UK' THEN 'English'
         WHEN 'USA' THEN 'English'
       END AS Language
  FROM Supplier

ProductName	Country	Language
Exotic Liquids	UK	English
New Orleans Cajun Delights	USA	English
Grandma Kelly's Homestead	USA	English
Tokyo Traders	Japan	Japanese
Cooperativa de Quesos 'Las Cabras'	Spain	Spanish
Mayumi's	Japan	Japanese
Pavlova, Ltd.	Australia	English
Specialty Biscuits, Ltd.	UK	English
PB Knäckebröd AB	Sweden	Swedish

 SELECT ProductName, UnitPrice,
        CASE
          WHEN UnitPrice > 50 THEN 'Expensive'
          WHEN UnitPrice > 20 AND UnitPrice <= 50 THEN 'Medium'
          WHEN UnitPrice > 0 AND UnitPrice <= 20 THEN 'Cheap'
          ELSE 'N/a'
        END AS 'Price Category'
  FROM Product
 ORDER BY ProductName

ProductName	UnitPrice	Price Category
Alice Mutton	39.00	Medium
Aniseed Syrup	10.00	Cheap
Boston Crab Meat	18.40	Cheap
Camembert Pierrot	34.00	Medium
Carnarvon Tigers	62.50	Expensive
Chai	18.00	Cheap

• Our Sql SELECT Tutorial
• Our Sql COALESCE Function Reference
• Our Sql NULLIF Function Reference