CASE Expression (SQL)

CASE_expression ::= simple_CASE | conditional_CASE
simple_CASE ::= CASE expression1 { WHEN expression2 THEN expression3 } [ ELSE expression4 ] END
conditional_CASE ::= CASE { WHEN condition THEN expression3 } [ ELSE expression4 ] END

The CASE expression serves for selecting a value of one of the expressions based on the specified conditions. The first WHEN branch is searched for (from left to right) where the expression2 value is the same as expression1 in a simple CASE. The first WHEN branch is searched for (from left to right) where the condition is fulfilled in the conditional CASE. If a suitable branch is found, the CASE expression value will be the expression3 in this branch. If no suitable branch is found, the CASE expression value will be the expression4, or NULL if expression4 is not specified.

All expression2 expressions must be of a type that is comparable with expression1. All expression3 and expression4 expressions must be convertible to a certain common type.

Usage Example:

SELECT Customers.name, CASE 
                          WHEN SUM(Loans.price) IS NULL THEN 0
                          ELSE SUM(Loans.price)
                        END
FROM Customers LEFT OUTER JOIN Loans ON (Customers.personal_id=Loans.personal_id)
GROUP BY Customers.name

NOTE: This example may be solved more effectively using the COALESCE function.

Usage example:

...
CASE
  WHEN n <> 0 THEN x/n
  ELSE 0
END
...

Simple CASE example:

SELECT name, age, CASE sex
                     WHEN 1 THEN 'male'
                     WHEN 2 THEN 'female'
                     ELSE 'not specified'
                   END,
       address
FROM People

Expressions

Table Column Names