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.


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

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.


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

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.


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.

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:

     ELSE b

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

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'

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

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.


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:


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.

            ELSE 1
        END AS hit
  FROM demo

nvl, nvl2, ifnull, … (Oracle, MySQL, DB2 LUW)

The functions nvl (Oracle, DB2) and ifnull (MySQL) 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 (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—for that he also published the book SQL Performance Explained.


  1. SQL:2011-2:§6.12, General Rule 2a.

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

  3. SQL:2001-2: §6.12, Syntax Rule 4.

  4. SQL:2011-2: §6.12, General Rule 2a: uses the term “some” in the context of true conditions—this suggests that the evaluation may continue after finding the first true condition.

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

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

  7. More precisely, the known types of the <Result> expressions (SQL:2011-2, §9.5 Syntax Rule 2). That means that the null literal is not considered..

  8. SQL:2011-2: §9.5

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

  10. SQL:2011-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:2011-2: § 6.12 Syntax Rule 3)—otherwise the overall result type cannot be determined (SQL:2011-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.

  11. SQL:2011-2: §9.5, Syntax Rule 3d.

  12. SQL:2011-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