Source: https://modern-sql.com/feature/case
Timestamp: 2019-04-26 05:54:21+00:00

Document:
SQL is a declarative language: it does not provide control over program flow like if does for imperative programs. Nevertheless, SQL has something similar: the case expression. Being an expression—rather than a control structure—means that case varies the result of formulas (expressions) based on conditions. Its use is similar to the ternary operator ?: in other programming languages.
The syntax of the case expression is very flexible and allows for some abbreviations. The following example shows the syntax in the most generic form—the so-called searched case. The other variants are abbreviations that could also be written as searched case. Furthermore searched case works in virtually all SQL databases. From that point of view, one could say that searched case is the only case syntax you absolutely need to remember.
Note that <condition> is a condition like those in where clauses—for example: column_name IS NULL. Thus the name searched case. <result> is an arbitrary SQL expression: it can be a simple expression like a constant or column name, or also a complex SQL expression containing subqueries and, of course, nested case expressions.
A case expression can contain several when-then pairs. This allows for multiple conditions even without nesting—unlike the ?: operator in other programming languages. On the other hand, nesting provides an inherent precedence among the conditions. A flat case expression does not have such an inherent precedence. To close that gap, SQL uses the order in which the conditions appear in the case expression to define their precedence.0 Put simply: case expressions evaluate to the <result> of the first true <condition>.
The SQL standard does not specify how to process case expressions—it just defines the result.3 In fact, there are products that sometimes process a then clause even before the corresponding when clause (read it again: then before when). Of course, these products eventually deliver the right result—they just discard the unnecessarily evaluated ones. Ultimately, this is a result of the declarative nature of SQL: the database—not the programmer—decides how to get the correct result. You'll find more about this in Proprietary Extensions: Documented Evaluation Process.
Do not use non-deterministic functions or data-changing functions in case expressions. The undefined evaluation process can have unpredictable side effects.
The standard defines the simple case as a transformation to a searched case—the rules described above remain valid.
You cannot use simple case to test for null because it always uses the equals operator (=). That is because the condition null = null is not true5—consequently, a when null clause never applies. If the <common operand> is null, the else clause applies.
SQL offers two case abbreviations to cope with null: coalesce and nullif. Both are used like functions and do not use the keywords case, when, then, else and end.
The above shown forms of case were all introduced with intermediate SQL-92 and work in practically all SQL databases. SQL:2003 has introduced two more abbreviations that extend the simple case. Both are optional features and are not yet widely supported.
The so-called extended case accepts a comparison operator right after when and thus lifts the limitation that simple case always uses equals (=) comparisons. The following example uses the less than operator (<) to map values into intervals. It also relies on the precedence of when clauses: the first true condition wins.
The results type of a case expression is determined by all <result> expressions collectively.6 The SQL standard defines strict rules on how to find the result type when mixing related data types7—for example, if one then clause has the type char(3) while another has the type varchar(255). The rules the standard defines for related types are basically common sense, i.e., the result type of the case expression is the shortest type that can accommodate all possible result values.8 In the example the result type is varchar(255).
As usual, null is special: the literal null—as in else null—doesn't have a known type. Thus it is ignored when determining the result type of case expressions.9 Effectively, null adjust to the result type of the overall case expression.
Approximate numeric types (float, real or double precision) require special care: if a <result> has an approximate numeric type, the SQL standard requires the result type of the case expression to be an approximate numeric type too—which one is implementation defined.10 Likewise it is implementation defined which implicit type conversions are done.11 Consequently, some products allow mixing unrelated data types in case expressions without explicit conversion.
Avoid mixing types in the then clauses. If needed, use cast to explicitly convert them to the required type.
More to follow: Subscribe to the newsletter!
The case expression was introduced with intermediate SQL-92. SQL:1999 incorporated case as feature F261 into Core SQL so that it became mandatory. Nowadays virtually all SQL databases support this feature.
SQL:2003 added the two barely supported optional features „Extended CASE expression“ (F262) and „Comma-separated predicates in simple CASE expression“ (F263).
The SQL standard does not define the procedure how to resolve case expressions. In particular, the standard does not mandate to check the conditions in the order of their appearance in the case expression. As long as the implementation yields the right result eventually, everything is allowed.
Even though different evaluation procedures must produce the same result, different procedures may cause different side effects. When using data-changing functions, for example, they might be called even if it is not strictly necessary—nevertheless you might see their side effects: the changed data. Likewise, whether or not a runtime error occurs might depend on the evaluation procedure: if the error is in a part of the case expression is that is not necessarily evaluated, it may or may not be triggered during evaluation. Finally, the time the evaluation takes is also a side effect that depends on the evaluation process.
Considering the last side effect—the performance—the obvious evaluation procedure is to check the when clauses in order until the first true condition is found. Of course it makes also sense to evaluate only one result—either the corresponding then clause or the else clause. Some products even document this evaluation procedure. Nevertheless there are cases that can lead to surprising side effects.
One common example that can cause surprising side effects is this: some products evaluate constant expressions early—i.e., during the prepare phase prior execution. This is very similar to a compiler that resolves constant expressions (e.g., 1/3) during compilation—rather then generating code that calculates the result at runtime. If this optimization affects a part of a case expression, the evaluation order gets disturbed.
The following example can therefore result in a division by zero error even if the condition id = 0 is never true. Even if the demo table is empty, the error can still happen, if the constant expression 1/0 is evaluated during the prepare phase.
The functions nvl (Oracle, DB2), ifnull (MySQL), isnull (SQL Server) roughly correspond to coalesce except that they are limited to two arguments.
The decode function demonstrates that proprietary functions might do completely different things in different products. In MySQL, decode is a (deprecated) cryptographic function. In the Oracle Database it is a proprietary short form of case.
The MySQL function if is a proprietary short form for a searched case expression with a single when clause—very similar to the ternary operator ?:.
SQL:2016-2: §6.12, General Rule 2b.
SQL:2016-2: §6.12, Syntax Rule 4.
SQL:2016-2: §6.12, Syntax Rule 2fi. See also extended form and comma-separated form.
In the three-valued-logic of SQL the condition null = null is neither true nor false but unknown. The when clause requires true conditions to match. A non-false result is not enough.
More precisely, the known types of the <Result> expressions (SQL:2016-2, §9.5 Syntax Rule 2). That means that the null literal is not considered.
SQL:2016-2: §6.12, Syntax Rule 5 only considers <result expression>s to determine the result type (not: <result>, which includes null).
This explains why at least one <result> shall be a <result expression> (SQL:2016-2: § 6.12 Syntax Rule 3)—otherwise the overall result type cannot be determined (SQL:2016-2: §9.5 Syntax Rule 2). In practice, this is not a limitation because a case expression having null in the else and all then clauses could be replaced by a literal null anyway.
SQL:2016-2: §9.5, Syntax Rule 3cii2.

References: §6
 §6
 §6
 §9
 §6
 § 6
 §9
 §9