SQL CASE

A CASE expression handles conditional logic inside a query.

CASE returns a value based on a specified condition.

Comprehensive logic can be built with CASE, WHEN, THEN, and ELSE.

CASE can be used with SELECT, UPDATE, WHERE, ORDER BY, and HAVING.

Example

#

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
Result:  78 records
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

Syntax

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.


More Examples

CASE, SIMPLE format.

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: List suppliers and the language they speak.
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
Result:  29 records
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

CASE, SEARCHED format.

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Categorize products as cheap, medium, or expensive.
 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
Result:  78 records
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

You may also like



Guides


vsn 3.1