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.

Tip

Do not use non-deterministic functions or data-changing functions in case expressions. The undefined evaluation process can have unpredictable side effects.

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.

Caution

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.

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

On my Own Behalf

If you like this article, you might also like my book SQL Performance Explained or my training.

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

Tip

Avoid mixing types in the then clauses. If needed, use cast to explicitly convert them to the required type.

Use Cases

The following articles describe common use cases of case:

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 is a case that can disturb this procedure (see below). This exception is sometimes documented (PostgreSQL), sometimes not (SQL Server). Ultimately, you cannot even rely on a documented evaluation procedure.

A common case that can disturb the evaluation process is as follows: 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, … (Oracle, MySQL, DB2 LUW, SQL Server)

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

Rocco Sen wrote a nice article comparing SQL Server's isnull to coalesce: “Coalesce and isnull in Microsoft SQL Server – the gotchas.”

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 (MySQL)

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

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing. His book entitled SQL Performance Explained has become standard reading.

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

The quick and easy way to benefit from his extensive knowledge and experience.
Learn more »

Footnotes

0

SQL:2016-2:§6.12, General Rule 2a.

1

SQL:2016-2: §6.12, General Rule 2b.

2

SQL:2016-2: §6.12, Syntax Rule 4.

3

SQL:2011-1: §6.3.3.3.

4

SQL:2016-2: §6.12, Syntax Rule 2fi. See also extended form and comma-separated form.

5

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.

6

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.

7

SQL:2016-2: §9.5

8

SQL:2016-2: §9.5, Syntax Rule 3aiii (for character strings); Syntax Rule 3c (for exact numbers); Syntax Rule 3e and 3f (for temporal types)

9

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.

10

SQL:2016-2: §9.5, Syntax Rule 3cii2.

11

SQL:2016-2: §4.11

“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license