<p>What is the <strong>complete</strong> and correct syntax for the SQL Case expression?</p>

[{'answer_id': 4626, 'author': 'Lasse V. Karlsen', 'author_id': 267, 'author_profile': 'https://Stackoverflow.com/users/267', 'pm_score': 7, 'selected': True, 'text': '<p>The <strong>complete</strong> syntax depends on the database engine you\'re working with:</p>\n\n<p>For SQL Server:</p>\n\n<pre><code>CASE case-expression\n    WHEN when-expression-1 THEN value-1\n  [ WHEN when-expression-n THEN value-n ... ]\n  [ ELSE else-value ]\nEND\n</code></pre>\n\n<p>or:</p>\n\n<pre><code>CASE\n    WHEN boolean-when-expression-1 THEN value-1\n  [ WHEN boolean-when-expression-n THEN value-n ... ]\n  [ ELSE else-value ]\nEND\n</code></pre>\n\n<p>expressions, etc:</p>\n\n<pre><code>case-expression    - something that produces a value\nwhen-expression-x  - something that is compared against the case-expression\nvalue-1            - the result of the CASE statement if:\n                         the when-expression == case-expression\n                      OR the boolean-when-expression == TRUE\nboolean-when-exp.. - something that produces a TRUE/FALSE answer\n</code></pre>\n\n<p>Link: <a href="http://msdn.microsoft.com/en-us/library/ms181765.aspx" rel="noreferrer">CASE (Transact-SQL)</a></p>\n\n<p>Also note that the ordering of the WHEN statements is important. You can easily write multiple WHEN clauses that overlap, and <strong>the first one that matches is used</strong>.</p>\n\n<p><strong>Note</strong>: If no ELSE clause is specified, and no matching WHEN-condition is found, the value of the CASE expression will be <em>NULL</em>.</p>\n'}, {'answer_id': 4631, 'author': 'Lasse V. Karlsen', 'author_id': 267, 'author_profile': 'https://Stackoverflow.com/users/267', 'pm_score': 2, 'selected': False, 'text': '<p>I dug up the Oracle page for the same and it looks like this is the same syntax, just described slightly different.</p>\n\n<p>Link: <a href="http://www.techonthenet.com/oracle/functions/case.php" rel="nofollow noreferrer">Oracle/PLSQL: Case Statement</a></p>\n'}, {'answer_id': 4634, 'author': 'Neall', 'author_id': 619, 'author_profile': 'https://Stackoverflow.com/users/619', 'pm_score': 4, 'selected': False, 'text': '<p>Here are the <a href="http://www.postgresql.org/docs/current/static/functions-conditional.html" rel="noreferrer" title="Conditional Expressions"><code>CASE</code> statement examples from the PostgreSQL docs</a> (Postgres follows the SQL standard here):</p>\n\n<pre><code>SELECT a,\n   CASE WHEN a=1 THEN \'one\'\n        WHEN a=2 THEN \'two\'\n        ELSE \'other\'\n   END\nFROM test;\n</code></pre>\n\n<p>or</p>\n\n<pre><code>SELECT a,\n   CASE a WHEN 1 THEN \'one\'\n          WHEN 2 THEN \'two\'\n          ELSE \'other\'\n   END\nFROM test;\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 223167, 'author': 'Leigh Riffel', 'author_id': 27010, 'author_profile': 'https://Stackoverflow.com/users/27010', 'pm_score': 2, 'selected': False, 'text': '<p>Oracle <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/expressions004.htm#SQLRF20037" rel="nofollow noreferrer">syntax from the 11g Documentation</a>:</p>\n\n<pre><code>CASE { simple_case_expression | searched_case_expression }\n     [ else_clause ]\n     END\n</code></pre>\n\n<p><i>simple_case_expression</i></p>\n\n<pre><code>expr { WHEN comparison_expr THEN return_expr }...\n</code></pre>\n\n<p><i>searched_case_expression</i></p>\n\n<pre><code>{ WHEN condition THEN return_expr }...\n</code></pre>\n\n<p><i>else_clause</i></p>\n\n<pre><code>ELSE else_expr\n</code></pre>\n'}, {'answer_id': 224770, 'author': 'onedaywhen', 'author_id': 15354, 'author_profile': 'https://Stackoverflow.com/users/15354', 'pm_score': 4, 'selected': False, 'text': '<p>Considering you tagged multiple products, I\'d say the <em>full</em> correct syntax would be the one found in the ISO/ANSI SQL-92 standard:</p>\n\n<pre><code>&lt;case expression&gt; ::=\n       &lt;case abbreviation&gt;\n     | &lt;case specification&gt;\n\n&lt;case abbreviation&gt; ::=\n       NULLIF &lt;left paren&gt; &lt;value expression&gt; &lt;comma&gt;\n              &lt;value expression&gt; &lt;right paren&gt;\n     | COALESCE &lt;left paren&gt; &lt;value expression&gt;\n                { &lt;comma&gt; &lt;value expression&gt; }... &lt;right paren&gt;\n\n&lt;case specification&gt; ::=\n       &lt;simple case&gt;\n     | &lt;searched case&gt;\n\n&lt;simple case&gt; ::=\n     CASE &lt;case operand&gt;\n          &lt;simple when clause&gt;...\n        [ &lt;else clause&gt; ]\n     END\n\n&lt;searched case&gt; ::=\n     CASE\n       &lt;searched when clause&gt;...\n     [ &lt;else clause&gt; ]\n     END\n\n&lt;simple when clause&gt; ::= WHEN &lt;when operand&gt; THEN &lt;result&gt;\n\n&lt;searched when clause&gt; ::= WHEN &lt;search condition&gt; THEN &lt;result&gt;\n\n&lt;else clause&gt; ::= ELSE &lt;result&gt;\n\n&lt;case operand&gt; ::= &lt;value expression&gt;\n\n&lt;when operand&gt; ::= &lt;value expression&gt;\n\n&lt;result&gt; ::= &lt;result expression&gt; | NULL\n\n&lt;result expression&gt; ::= &lt;value expression&gt;\n</code></pre>\n\n<p>Syntax Rules</p>\n\n<pre><code>1) NULLIF (V1, V2) is equivalent to the following &lt;case specification&gt;:\n\n     CASE WHEN V1=V2 THEN NULL ELSE V1 END\n\n2) COALESCE (V1, V2) is equivalent to the following &lt;case specification&gt;:\n\n     CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END\n\n3) COALESCE (V1, V2, . . . ,n ), for n &gt;= 3, is equivalent to the\n   following &lt;case specification&gt;:\n\n     CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,n )\n     END\n\n4) If a &lt;case specification&gt; specifies a &lt;simple case&gt;, then let CO\n   be the &lt;case operand&gt;:\n\n   a) The data type of each &lt;when operand&gt; WO shall be comparable\n      with the data type of the &lt;case operand&gt;.\n\n   b) The &lt;case specification&gt; is equivalent to a &lt;searched case&gt;\n      in which each &lt;searched when clause&gt; specifies a &lt;search\n      condition&gt; of the form "CO=WO".\n\n5) At least one &lt;result&gt; in a &lt;case specification&gt; shall specify a\n   &lt;result expression&gt;.\n\n6) If an &lt;else clause&gt; is not specified, then ELSE NULL is im-\n   plicit.\n\n7) The data type of a &lt;case specification&gt; is determined by ap-\n   plying Subclause 9.3, "Set operation result data types", to the\n   data types of all &lt;result expression&gt;s in the &lt;case specifica-\n   tion&gt;.\n\nAccess Rules\n\n   None.\n\nGeneral Rules\n\n1) Case:\n\n   a) If a &lt;result&gt; specifies NULL, then its value is the null\n      value.\n\n   b) If a &lt;result&gt; specifies a &lt;value expression&gt;, then its value\n      is the value of that &lt;value expression&gt;.\n\n2) Case:\n\n   a) If the &lt;search condition&gt; of some &lt;searched when clause&gt; in\n      a &lt;case specification&gt; is true, then the value of the &lt;case\n      specification&gt; is the value of the &lt;result&gt; of the first\n      (leftmost) &lt;searched when clause&gt; whose &lt;search condition&gt; is\n      true, cast as the data type of the &lt;case specification&gt;.\n\n   b) If no &lt;search condition&gt; in a &lt;case specification&gt; is true,\n      then the value of the &lt;case expression&gt; is the value of the\n      &lt;result&gt; of the explicit or implicit &lt;else clause&gt;, cast as\n      the data type of the &lt;case specification&gt;.\n</code></pre>\n'}, {'answer_id': 687137, 'author': 'Eric Johnson', 'author_id': 58089, 'author_profile': 'https://Stackoverflow.com/users/58089', 'pm_score': 3, 'selected': False, 'text': '<p>Sybase has the same <a href="http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks115.htm" rel="noreferrer">case syntax</a> as SQL Server:</p>\n\n<h2>Description</h2>\n\n<p>Supports conditional SQL expressions; can be used anywhere a value expression can be used.</p>\n\n<h3>Syntax</h3>\n\n<pre><code>case \n     when search_condition then expression \n    [when search_condition then expression]...\n    [else expression]\nend\n</code></pre>\n\n<h3>Case and values syntax</h3>\n\n<pre><code>case expression\n     when expression then expression \n    [when expression then expression]...\n    [else expression]\nend\n</code></pre>\n\n<h2>Parameters</h2>\n\n<h3>case</h3>\n\n<p>begins the case expression.</p>\n\n<h3>when</h3>\n\n<p>precedes the search condition or the expression to be compared.</p>\n\n<h3>search_condition</h3>\n\n<p>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.</p>\n\n<h3>then</h3>\n\n<p>precedes the expression that specifies a result value of case.</p>\n\n<h3>expression</h3>\n\n<p>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.</p>\n\n<h2>Example</h2>\n\n<pre><code>select disaster, \n       case\n            when disaster = "earthquake" \n                then "stand in doorway"\n            when disaster = "nuclear apocalypse" \n                then "hide in basement"\n            when monster = "zombie apocalypse" \n                then "hide with Chuck Norris"\n            else\n                then "ask mom"\n       end \n  from endoftheworld\n</code></pre>\n'}, {'answer_id': 14558826, 'author': 'user2001117', 'author_id': 2001117, 'author_profile': 'https://Stackoverflow.com/users/2001117', 'pm_score': -1, 'selected': False, 'text': "<p>Case statement syntax in SQL SERVER:</p>\n\n<pre><code>CASE column\n   WHEN value1 THEN 1\n   WHEN value3 THEN 2\n   WHEN value3 THEN 3\n   WHEN value1 THEN 4\n   ELSE ''\nEND\n</code></pre>\n\n<p>And we can use like below also:</p>\n\n<pre><code>CASE \n   WHEN column=value1 THEN 1\n   WHEN column=value3 THEN 2\n   WHEN column=value3 THEN 3\n   WHEN column=value1 THEN 4\n   ELSE ''\nEND\n</code></pre>\n"}, {'answer_id': 65185982, 'author': 'Aruna Karunarathna', 'author_id': 760445, 'author_profile': 'https://Stackoverflow.com/users/760445', 'pm_score': -1, 'selected': False, 'text': '<p>Here you can find a complete guide for <a href="https://geekposting.com/case-statements-in-sql-complete-guide/" rel="nofollow noreferrer">MySQL case statements in SQL</a>.</p>\n<pre><code>CASE\n     WHEN some_condition THEN return_some_value\n     ELSE return_some_other_value\nEND\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/4622', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/383/']