Provides conditional SQL expressions.

Syntax 1

CASE  expression1
WHEN expression2 THEN expression3, ...
[ ELSE expression4 ]
END

SELECT id,
   ( CASE name
      WHEN 'Tee Shirt' THEN 'Shirt'
      WHEN 'Sweatshirt' THEN 'Shirt'
      WHEN 'Baseball Cap' THEN 'Hat'
      ELSE 'Unknown'
   END ) as Type
FROM Product;

Syntax 2

CASE
WHEN search-condition
THEN expression1, ...
[ ELSE expression2 ]
END

Remarks

For compatibility reasons, you can end this expression with either ENDCASE or END CASE.

You can use case expressions anywhere you can use regular expression.

Syntax 1   If the expression following the CASE keyword is equal to the expression following the first WHEN keyword, then the expression following the associated THEN keyword is returned. Otherwise the expression following the ELSE keyword is returned, if specified.

For example, the following code uses a case expression as the second clause in a SELECT statement. It selects a row from the Product table where the name column has a value of Sweatshirt.

Syntax 2   If the search-condition following the first WHEN keyword is TRUE, the expression following the associate THEN keyword is returned. Otherwise the expression following the ELSE clause is returned, if specified.

NULLIF function for abbreviated CASE expressions   The NULLIF function provides a way to write some CASE statements in short form. The syntax for NULLIF is as follows:

NULLIF ( expression-1, expression-2 )

NULLIF compares the values of the two expressions. If the first expression equals the second expression, NULLIF returns NULL. If the first expression does not equal the second expression, NULLIF returns the first expression.

Example

The following statement uses a CASE expression as the third clause of a SELECT statement to associate a string with a search condition. If the name column's value is Tee Shirt, this query returns Sale. And if the name column's value is not Tee Shirt and the quantity is greater than fifty, it returns Big Sale. However, for all others, the query then returns Regular price.

SELECT id, name,
   ( CASE
      WHEN name='Tee Shirt' THEN 'Sale'
      WHEN quantity >= 50  THEN 'Big Sale'
      ELSE 'Regular price'
   END ) as Type
FROM Product;