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>] END
<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 expression can contain several
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
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:
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>] 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.
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
else clause applies.
Abbreviations to Cope With
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:
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
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
null is special: the literal
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
Approximate numeric types (
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.
The following articles describe common use cases of
Pivot – Rows to Columns –
casein aggregate functions
Prevent division by zero –
More to follow: Subscribe to the newsletter!
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).
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
ifnull, isnull, … (Oracle, MySQL, DB2 LUW, SQL Server)
nvl (Oracle, DB2),
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
coalesce: “Coalesce and isnull in Microsoft SQL Server – the gotchas.”
decode (Oracle, MySQL)
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
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