SQL data manipulation language

SELECT statement

The CASE statement may be used in one of two syntax forms:

The first form

CASE < input expression >
   WHEN < WHEN expression 1>
   THEN < RETURN expression 1 >
   …
   WHEN < WHEN expression N >
   THEN < RETURN expression N >
[ELSE < RETURN expression >]
END

The second form

CASE
   WHEN < predicate 1 >
   THEN < RETURN expression 1 >
   …
   WHEN < predicate N >
   THEN < RETURN expression N >
[ELSE < RETURN expression >]
END

All WHEN clauses must be in the same syntax form, i.e. first and second forms cannot be mixed. When using the first syntax form, the WHEN condition is satisfied as soon as the value of when expression will be equal to the value of input expression. When using the second syntax form, the WHEN condition is satisfied as soon as the predicate evaluates to TRUE. When satisfying condition, the CASE statement returns the return expression from the corresponding THEN clause. If no WHEN expression is satisfied, the return expression from the ELSE clause will be used. If no ELSE clause is specified, a NULL value will be returned. If more than one condition are satisfied, the first return expression from them will be returned.

The above example uses the second form of the CASE statement.

It should be noted that checking for NULL could also be made using the standard function COALESCE, which is simpler. This function has arbitrary number of arguments and returns the first not-NULL expression among them. In the case of two arguments, COALESCE(A, B) is equivalent to the following CASE statement:

CASE 
 WHEN A IS NOT NULL 
 THEN A 
 ELSE B 
END

When using the COALESCE function, the solution to the above example may be rewritten as follows

Console

Execute

SELECT DISTINCT Product.model, 
 COALESCE(CAST(price AS CHAR(20)),'Not available') price
FROM Product LEFT JOIN 
 PC ON Product.model = PC.model
WHERE Product.type = 'pc';

SELECT DISTINCT Product.model, COALESCE(CAST(price as CHAR(20)),'Not available') price FROM Product LEFT JOIN PC ON Product.model = PC.model WHERE Product.type = 'pc';

    Scheme

Usage of the first syntax form of the CASE statement can be demonstrated by the following example: Get all available PC models, their prices, and information about the most expensive and cheap models.

Example 5.10.1

Get all available PC models, their prices, and information about the most expensive and cheap models.

Console

Execute

SELECT DISTINCT model, price, 
 CASE price 
 WHEN (SELECT MAX(price) 
 FROM PC
 ) 
 THEN 'Most expensive' 
 WHEN (SELECT MIN(price) 
 FROM PC
 ) 
 THEN 'Most cheap' 
 ELSE 'Mean price' 
 END comment 
FROM PC 
ORDER BY price;

SELECT DISTINCT model, price, CASE price WHEN (SELECT MAX(price) FROM PC ) THEN 'Most expensive' WHEN (SELECT MIN(price) FROM PC ) THEN 'Most cheap' ELSE 'Mean price' END comment FROM PC ORDER BY price;

    Scheme

The result set

CASE operator can be used not only in SELECT clause. Here you can find other examples of its use.