case — Conditional Expressions

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.

Syntax

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.

CASE WHEN <condition> THEN <result>
    [WHEN <condition> THEN <result>
     ...]
    [ELSE <result>]
END

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

Before closing the case expression with end, the optional else clause can be used. The <result> of else is used if no <condition> is true.1 If else is omitted, else null is implicit.2

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.

The Simple Form

To avoid repeating an operand that is used in all when clauses over and over again, the SQL standard offers the so-called simple case:

CASE <common operand>
     WHEN <expression> THEN <result>
    [WHEN <expression> THEN <result>
     ...]
    [ELSE <result>]
END

The simple case splits the <condition> into two parts: the common operand (e.g., a column name) is put right after the keyword case. The other side of the comparison remains in the when clause. The comparison operator equals (=) is implied.4

The standard defines the simple case as a transformation to a searched case—the rules described above remain valid.

Abbreviations to Cope With null

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.

Coalesce returns the first not-null parameter (or null, if all parameters are null). The number of parameters is not limited. The standard defines coalesce as a transformation into a case expression. The following expressions are therefore equivalent:

COALESCE(a, b)

CASE WHEN a IS NOT NULL THEN a
     ELSE b
END

Nullif requires two parameters and generally returns the value of the first one. Only if both values are equal (=), null is returned instead. Nullif is also defined as a transformation to case and is typically used to prevent division by zero errors:

x / NULLIF(y, 0)

x / CASE WHEN y = 0 THEN null 
         ELSE y
    END

Barely Supported Forms

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.

CASE x WHEN < 0   THEN '< 0'
       WHEN < 10  THEN '[0, 10['
       WHEN < 100 THEN '[10, 100['
       ELSE '>100'
END

The second addition introduced by SQL:2003 allows comma separated lists in the when clause:

CASE <common operand>
     WHEN <expression>[, <expression> ...] THEN <result>
    [WHEN <expression>[, <expression> ...] THEN <result>
     ...]
    [ELSE <result>]
END

Determining the Result Type

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.

Use Cases

The following articles describe common use cases of case:

• Pivot – Rows to Columns – case in aggregate functions

• Prevent division by zero – nullif(divisor, 0)

• More to follow: Subscribe to the newsletter!

Compatibility

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

Proprietary Extensions

Documented Evaluation Process

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.

SELECT CASE WHEN id = 0 THEN 1/0
            ELSE 1
        END AS hit
  FROM demo

nvl, nvl2, ifnull, isnull, …

The functions nvl (Oracle, Db2), ifnull (Google BigQuery, MySQL), isnull (SQL Server) roughly correspond to coalesce except that they are limited to two arguments.

decode (Oracle, MySQL)

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.

if (Google BigQuery, MySQL)

The function if is a proprietary short form for a searched case expression with a single when clause—very similar to the ternary operator  ?:.