"The complete syntax depends on the database engine you're working with:
For SQL Server:
CASE case-expression WHEN when-expression-1 THEN value-1 [ WHEN when-expression-n THEN value-n ... ] [ ELSE else-value ] END
or:
CASE WHEN boolean-when-expression-1 THEN value-1 [ WHEN boolean-when-expression-n THEN value-n ... ] [ ELSE else-value ] END
expressions, etc:
case-expression - something that produces a value when-expression-x - something that is compared against the case-expression value-1 - the result of the CASE statement if: the when-expression == case-expression OR the boolean-when-expression == TRUE boolean-when-exp.. - something that produces a TRUE/FALSE answer
Link: CASE (Transact-SQL)
Also note that the ordering of the WHEN statements is important. You can easily write multiple WHEN clauses that overlap, and the first one that matches is used.
Note: If no ELSE clause is specified, and no matching WHEN-condition is found, the value of the CASE expression will be NULL."
<simple case> ::= CASE <case operand> <simple when clause>... [ <else clause> ] END
<searched case> ::= CASE <searched when clause>... [ <else clause> ] END
<simple when clause> ::= WHEN <when operand> THEN <result>
<searched when clause> ::= WHEN <search condition> THEN <result>
<else clause> ::= ELSE <result>
<case operand> ::= <value expression>
<when operand> ::= <value expression>
<result> ::= <result expression> | NULL
<result expression> ::= <value expression>
Syntax Rules
1) NULLIF (V1, V2) is equivalent to the following <case specification>:
CASE WHEN V1=V2 THEN NULL ELSE V1 END
2) COALESCE (V1, V2) is equivalent to the following <case specification>:
CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END
3) COALESCE (V1, V2, . . . ,n ), for n >= 3, is equivalent to the following <case specification>:
CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,n ) END
4) If a <case specification> specifies a <simple case>, then let CO be the <case operand>:
a) The data type of each <when operand> WO shall be comparable with the data type of the <case operand>.
b) The <case specification> is equivalent to a <searched case> in which each <searched when clause> specifies a <search condition> of the form ""CO=WO"".
5) At least one <result> in a <case specification> shall specify a <result expression>.
6) If an <else clause> is not specified, then ELSE NULL is im- plicit.
7) The data type of a <case specification> is determined by ap- plying Subclause 9.3, ""Set operation result data types"", to the data types of all <result expression>s in the <case specifica- tion>.
Access Rules
None.
General Rules
1) Case:
a) If a <result> specifies NULL, then its value is the null value.
b) If a <result> specifies a <value expression>, then its value is the value of that <value expression>.
2) Case:
a) If the <search condition> of some <searched when clause> in a <case specification> is true, then the value of the <case specification> is the value of the <result> of the first (leftmost) <searched when clause> whose <search condition> is true, cast as the data type of the <case specification>.
b) If no <search condition> in a <case specification> is true, then the value of the <case expression> is the value of the <result> of the explicit or implicit <else clause>, cast as the data type of the <case specification>."
"Here are the CASE statement examples from the PostgreSQL docs (Postgres follows the SQL standard here):
SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test;
or
SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'other' END FROM test;
Obviously the second form is cleaner when you are just checking one field against a list of possible values. The first form allows more complicated expressions."
Supports conditional SQL expressions; can be used anywhere a value expression can be used.
Syntax
case when search_condition then expression [when search_condition then expression]... [else expression] end
Case and values syntax
case expression when expression then expression [when expression then expression]... [else expression] end
Parameters
case
begins the case expression.
when
precedes the search condition or the expression to be compared.
search_condition
is used to set conditions for the results that are selected. Search conditions for case expressions are similar to the search conditions in a where clause. Search conditions are detailed in the Transact-SQL User’s Guide.
then
precedes the expression that specifies a result value of case.
expression
is a column name, a constant, a function, a subquery, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators. For more information about expressions, see “Expressions” in.
Example
select disaster, case when disaster = ""earthquake"" then ""stand in doorway"" when disaster = ""nuclear apocalypse"" then ""hide in basement"" when monster = ""zombie apocalypse"" then ""hide with Chuck Norris"" else then ""ask mom"" end from endoftheworld"
