CASE Operator

Returns a value depending on a set of conditions.

 

Syntax

 

CASE value WHEN value THEN value [WHEN value THEN value ...] [ELSE value] END

 

or

 

CASE WHEN condition THEN value [WHEN condition THEN value ...] [ELSE value] END

 

The CASE operator has these parts:

 

Part

Description

value

The examined value, the example value, or the returned value.

condition

The examined Boolean expression.

 

Remarks

 

Use the first form of the CASE operator to translate a value to another value. A common use of the CASE operator is to replace codes or abbreviations with more readable values or vice versa. When used in the first form, the operator checks the examined value against the values in the WHEN clauses in the order they appear in the operator, and if the examined value is equal to that in a WHEN clause, the operator returns the value in the corresponding THEN clause. If the examined value is not equal to that in any WHEN clause, the operator returns the value in the ELSE clause, or NULL if the ELSE clause is omitted.

 

Use the second form of the CASE operator to evaluate one or more conditions and return a value depending on the results of the evaluation. When used in the second form, the operator evaluates the conditions in the WHEN clauses in the order they appear in the operator, and if a condition evaluates to True, returns the value in the corresponding THEN clause. If none of the conditions returns True, the operator returns the value in the ELSE clause, or NULL if the ELSE clause is omitted.

 

Examples

 

This example uses the CASE operator to report a home country for each employee, replacing "UK" with "United Kingdom," and "USA" with "United States of America":

 

SELECT [First Name], [Last Name], CASE [Country] WHEN "UK" THEN "United Kingdom" WHEN "USA" THEN "United States of America" ELSE [Country] END AS [Country] FROM [Employees];

 

This example uses the CASE operator to classify the order date of each order into seasons:

 

SELECT [Orders].*, CASE WHEN Month([Order Date]) BETWEEN 3 AND 5 THEN "Spring" WHEN Month([Order Date]) BETWEEN 6 AND 8 THEN "Summer" WHEN Month([Order Date]) BETWEEN 9 AND 11 THEN "Autumn" ELSE "Winter" END AS [Order Season] FROM [Orders];

 

This example uses the CASE operator to classify the freight of each order into "high," "medium," and "low" classes:

 

SELECT [Orders].*, CASE WHEN [Freight] >= 200 THEN "High" WHEN [Freight] >= 50 THEN "Medium" WHEN [Freight] > 0 THEN "Low" END AS [Freight Class] FROM [Orders];