SQL Case Expression Syntax

Question

What is the complete and correct syntax for the SQL Case expression

User · Accepted Answer

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

User · Answer

Considering you tagged multiple products  I d say the full correct syntax would be the one found in the ISO ANSI SQL-92 standard    lt case expression gt              lt case abbreviation gt          lt case specification gt    lt case abbreviation gt             NULLIF  lt left paren gt   lt value expression gt   lt comma gt                 lt value expression gt   lt right paren gt         COALESCE  lt left paren gt   lt value expression gt                     lt comma gt   lt value expression gt        lt right paren gt    lt case specification gt              lt simple case gt          lt searched case gt    lt simple case gt           CASE  lt case operand gt             lt simple when clause gt                lt else clause gt         END   lt searched case gt           CASE         lt searched when clause gt             lt else clause gt         END   lt simple when clause gt      WHEN  lt when operand gt  THEN  lt result gt    lt searched when clause gt      WHEN  lt search condition gt  THEN  lt result gt    lt else clause gt      ELSE  lt result gt    lt case operand gt       lt value expression gt    lt when operand gt       lt value expression gt    lt result gt       lt result expression gt    NULL   lt result expression gt       lt value expression gt    Syntax Rules  1  NULLIF  V1  V2  is equivalent to the following  lt case specification gt         CASE WHEN V1 V2 THEN NULL ELSE V1 END  2  COALESCE  V1  V2  is equivalent to the following  lt case specification gt         CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END  3  COALESCE  V1  V2         n    for n  gt   3  is equivalent to the    following  lt case specification gt         CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE  V2         n        END  4  If a  lt case specification gt  specifies a  lt simple case gt   then let CO    be the  lt case operand gt       a  The data type of each  lt when operand gt  WO shall be comparable       with the data type of the  lt case operand gt       b  The  lt case specification gt  is equivalent to a  lt searched case gt        in which each  lt searched when clause gt  specifies a  lt search       condition gt  of the form  CO WO    5  At least one  lt result gt  in a  lt case specification gt  shall specify a     lt result expression gt    6  If an  lt else clause gt  is not specified  then ELSE NULL is im-    plicit   7  The data type of a  lt case specification gt  is determined by ap-    plying Subclause 9 3   Set operation result data types   to the    data types of all  lt result expression gt s in the  lt case specifica-    tion gt    Access Rules     None   General Rules  1  Case      a  If a  lt result gt  specifies NULL  then its value is the null       value      b  If a  lt result gt  specifies a  lt value expression gt   then its value       is the value of that  lt value expression gt    2  Case      a  If the  lt search condition gt  of some  lt searched when clause gt  in       a  lt case specification gt  is true  then the value of the  lt case       specification gt  is the value of the  lt result gt  of the first        leftmost   lt searched when clause gt  whose  lt search condition gt  is       true  cast as the data type of the  lt case specification gt       b  If no  lt search condition gt  in a  lt case specification gt  is true        then the value of the  lt case expression gt  is the value of the        lt result gt  of the explicit or implicit  lt else clause gt   cast as       the data type of the  lt case specification gt

User · Answer

Here you can find a complete guide for MySQL case statements in SQL  CASE      WHEN some condition THEN return some value      ELSE return some other value END

User · Answer

I dug up the Oracle page for the same and it looks like this is the same syntax  just described slightly different   Link  Oracle PLSQL  Case Statement

User · Answer

Case statement syntax in SQL SERVER   CASE column    WHEN value1 THEN 1    WHEN value3 THEN 2    WHEN value3 THEN 3    WHEN value1 THEN 4    ELSE    END   And we can use like below also   CASE     WHEN column value1 THEN 1    WHEN column value3 THEN 2    WHEN column value3 THEN 3    WHEN column value1 THEN 4    ELSE    END

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Case Expression Syntax?

Examples related to sql

Examples related to sql-server

Examples related to oracle

Examples related to syntax

Examples related to case