Think of this as the IF...THEN statement for SQL. Most languages have a way to determine if some condition has occurred then return this value. The CASE function is the way to do that in SQL.

The CASE function is used to vary a result expression based upon one or more conditions. ELSE is also a part of the CASE condition and is used as the logical NOT of the selected case.

CASE has two formats for working with data:

• Simple case of an expression comparison to a set of expressions to determine the result.
  

Evaluation of boolean expressions to determine the result.

CASE should not be used in a WHERE clause

See the complete article on how to rewrite queries that have a case in a where clause.

Example

Example Schema

Example Data

The SQL statement will replace the single letter 'A' with 'Application' in the result set.

SELECT Name, CASE AppType
      WHEN 'A' THEN 'Application'
      WHEN 'S' THEN 'Screen Saver'
      WHEN 'D' THEN 'DLL'
      WHEN 'Z' THEN 'ZIP'
      ELSE 'Unknown Type'
   END
FROM AppTable 

This SQL example will return a string from the AppCost rather than the exact number. 

SELECT Name, CASE
      WHEN AppCost = 0 THEN 'Free'
      WHEN AppCost < 50 THEN 'Under $50'
      WHEN AppCost >= 50 and AppCost < 250 THEN 'Under $250'
      WHEN AppCost >= 250 and AppCost < 1000 THEN 'Under $1000'
      ELSE 'Special Pricing'
   END AS PRICETYPECOL
FROM AppTable