Provides conditional SQL expressions.
WHEN expression2 THEN expression3, ...
[ ELSE expression4 ]
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
THEN expression1, ...
[ ELSE expression2 ]
Case expressions can be used anywhere a regular expression can be used.
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.
The following statement uses a CASE expression as the third clause of a SELECT statement to associate a string with a search condition. It selects a row from the Product table where the name column does not have a value of Tee Shirt and the quantity is less than fifty.
SELECT id, name, ( CASE WHEN name='Tee Shirt' THEN 'Sale' WHEN quantity >= 50 THEN 'Big Sale' ELSE 'Regular price' END ) as Type FROM Product