SQL Case Statement Syntax?

Question

What is the full correct syntax for the SQL Case statement?

Answer 1

Well, full, depends on the database engine you're talking to.

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: http://msdn.microsoft.com/en-us/library/ms181765.aspx

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.

Answer 2

I dug up the Oracle page for the same and it looks like this is the same syntax, just described slightly different.

Link: http://www.techonthenet.com/oracle/functions/case.php

Answer 3

Here are the examples from the PostgreSQL docs (Postgres follows the 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;

Original source

Answer 4

Oracle syntax from the 11g Documentation:

CASE { simple_case_expression | searched_case_expression }
     [ else_clause ]
     END

simple_case_expression

expr { WHEN comparison_expr THEN return_expr }...

searched_case_expression

{ WHEN condition THEN return_expr }...

else_clause

ELSE else_expr

Answer 5

Considering you tagged multiple products, I'd say the full correct syntax would be the one found in the ISO/ANSI SQL-92 standard:

     <case expression> ::=
            <case abbreviation>
          | <case specification>

     <case abbreviation> ::=
            NULLIF <left paren> <value expression> <comma>
                  <value expression> <right paren>
          | COALESCE <left paren> <value expression>
                  { <comma> <value expression> }... <right paren>

     <case specification> ::=
            <simple case>
          | <searched case>

     <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>.

Answer 6

One point to note in Oracle's case, if no when matches and there is no else part an exception is raised.

Answer 7

Sybase has the same case syntax as SQL Server:

Description

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