CASE statement page 2
The CASE statement may be used in one of two syntax forms:
The first form
The second form
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:
When using the COALESCE function, the solution to the above example may be rewritten as follows
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.
Get all available PC models, their prices, and information about the most expensive and cheap models.
The result set
CASE operator can be used not only in SELECT clause. Here you can find other examples of its use.
Suggested exercises: 52, 59, 64, 65, 66, 68, 69, 74, 75, 83, 84, 86, 91, 92, 93, 99, 110, 111, 113, 118, 126, 128, 130, 131, 132