case — bedingte Ausdrücke

SQL ist eine deklarative Programmiersprache: sie erlaubt keine Steuerung des Programmablaufes wie es if in imperativen Programmen macht. Dennoch hat SQL etwas sehr ähnliches: den case-Ausdruck. Dass case ein Ausdruck ist – anstatt einer Kontrollstruktur - bedeutet, dass case das Ergebnis von Formeln (Ausdrücken) abhängig von Bedingungen macht. Die Verwendung entspricht der des ternären Bedingungsoperator ?: in anderen Programmiersprachen.

Syntax

Die Syntax des case-Ausdruckes ist sehr flexible und kennt einige Abkürzungen. Das folgende Beispiel zeigt die Syntax der allgemeinsten Form – das sogenannte searched case. Alle anderen Formen können auf einen Gleichwertiges searched case zurückgeführt werden. Darüber hinaus funktioniert searched case in praktisch allen SQL-Datenbanken. Man könnte also sagen, searched case ist die einzige Syntax, die man sich unbedingt merken muss.

CASE WHEN <Bedingung> THEN <Ergebnis>
    [WHEN <Bedingung> THEN <Ergebnis>
     ...]
    [ELSE <Ergebnis>]
END

Dabei ist <Bedingung> eine Bedingung, wie man sie in der where-Klausel verwendet – zum Beispiel spaltenname IS NULL. Daher auch der Name searched case. <Ergebnis> steht für einen beliebigen SQL-Ausdruck: Das kann ein einfacher Ausdruck wie eine Konstante oder ein Spaltenname sein, oder aber ein komplexer Ausdruck mit Unterabfragen und – natürlich auch – verschachtelten case-Ausdrücken.

Ein case-Ausdruck kann mehrere when-then Paare enthalten. Dadurch kann man sogar ohne Verschachtelung mehrere Bedingungen nutzen – im Gegensatz zum ?:-Operator in anderen Programmiersprachen. Andererseits entsteht beim Verschachteln eine inhärente Rangordnung unter den Bedingungen. Die Bedingungen eines flachen case-Ausdruckes haben dagegen keine inhärente Rangordnung. Um diese Lücke zu schließen verwendet SQL die Reihenfolge der Nennung der Bedingungen0 im case-Ausdruck als Rangordnung. Einfach gesagt: case liefert das <Ergebnis> der der ersten wahren <Bedingung>.

Bevor der case-Ausdruck durch end beendet wird, ist noch ein optionaler else-Zweig erlaubt. Das <Ergebnis> des else-Zweiges kommt zur Anwendung, wenn keine <Bedingung> wahr ist1. Fehlt der else-Zweiges wird er als else null angenommen2.

Der SQL-Standard schreibt nicht vor, wie ein case-Ausdruck abgearbeitet wird – der Standard spezifiziert nur das Ergebnis3. Tatsächlich gibt es Produkte, die ein then-Zweig manchmal sogar vor dem entsprechenden when-Zweig ausgewertet wird (nochmal: then vor when). Letzten Endes liefern diese Produkte natürlich auch das richtige Ergebnis – unnötig ermittelte Werte werden einfach verworfen. Letztendlich schlägt hier die deklarative Natur von SQL zu: Die Datenbank – nicht der Entwickler – entscheidet, wie das Ergebnis ermittelt wird. Mehr dazu in Proprietäre Erweiterungen: Dokumentierter Evaluierungsablauf.

Die simple Form

Damit ein Operand, der in allen when-Klausel vorkommt, nicht ständig wiederholt werden muss, bietet der SQL-Standard das sogenannte simple case an:

CASE <gemeinsamer Operand>
     WHEN <Ausdruck> THEN <Ergebnis>
    [WHEN <Ausdruck> THEN <Ergebnis>
     ...]
    [ELSE <Ergebnis>]
END

Dabei wird die <Bedingung> zweigeteilt: der gemeinsame Operand (z. B. ein Spaltenname) wird unmittelbar nach dem Schlüsselwort case angeführt. Die andere Seite des Vergleiches bleibt in den when-Klauseln. Der Vergleichsoperator Ist-gleich (=) ist implizit4.

Der SQL-Standard definiert simple case als eine Transformation in ein searched case. Daher bleiben die oben beschriebenen Regeln aufrecht.

Kurzformen zum Umgang mit null

SQL bietet zwei Kurzformen von case für den Umgang mit null: coalesce und nullif. Beide lassen sich wie Funktionen benutzen – kommen also ohne die Schlüsselworte case, when, then, else und end aus.

Coalesce liefert den ersten nicht-null-Parameter oder null, wenn alle Parameter null sind. Die Anzahl der Parameter ist nicht begrenzt. Der Standard definiert coalesce durch Transformation in einen case-Ausdruck. Die folgenden beiden Ausdrücke sind daher völlig gleichwertig:

COALESCE(a, b)

CASE WHEN a IS NOT NULL THEN a
     ELSE b
END

Nullif braucht zwei Parameter und liefert grundsätzlich den Wert des ersten zurück. Nur wenn die Werte beider Parameter gleich sind (=), wird null geliefert. nullif ist ebenfalls durch Übersetzung auf case definiert und wird oft verwendet um „Division durch Null (0)“-Fehler zu vermeiden:

x / NULLIF(y, 0)

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

Kaum unterstützte Formen

Die bisher gezeigten Formen von case wurden alle mit fortgeschrittenem SQL-92 eingeführt und sind in de-facto allen SQL-Datenbanken verfügbar. SQL:2003 hat zwei weitere Kurzformen eingeführt, die beide das simple case erweitern. Beide sind optionale Funktionen und werden nur vereinzelt unterstützt.

Das sogenannte extended case akzeptiert einen Operator direkt nach when und hebt damit die Beschränkung von simple case auf, nur mit Ist-gleich (=) vergleichen zu können. Das folgende Beispiel verwendet den Weniger-als (<) Operator, um Werte in Intervalle einzuteilen. Das Beispiel verlässt sich dabei auf die Rangordnung der when-Klauseln: die erste wahre Bedingung gewinnt.

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

Für in-Listen bietet SQL:2003 eine weitere Verkürzung: kommaseparierte Listen in der when-Klausel:

CASE <gemeinsamer Operand>
     WHEN <Ausdruck>[, <Ausdruck> ...] THEN <Ergebnis>
    [WHEN <Ausdruck>[, <Ausdruck> ...] THEN <Ergebnis>
     ...]
    [ELSE <Ergebnis>]
END

Ermittlung des Ergebnistyps

Der Datentyp des Ergebnisses eines case-Ausdruckes wird durch die Gesamtheit der <Ergebnis>-Ausdrücke6 bestimmt. Der SQL-Standard definiert explizite Regeln7, wie sich der Datentyp des Ergebnisses aus der Kombination artverwandter Datentypen ergibt – also zum Beispiel wenn eine then-Klausel vom Typ char(3) ist, eine andere aber vom Typ varchar(255). Diese Regeln entsprechen im Wesentlichen dem Hausverstand: Das Ergebnis des case-Ausdruckes ist der kleinste Typ, der alle möglichen Ergebniswerte darstellen kann8. Bei diesem Beispiel ist das Ergebnis vom Typ varchar(255).

Wie so oft nimmt null hier auch eine Sonderstellung ein: Da ein null-Literal keinen bekannten Datentyp hat, werden then null und else null-Klauseln bei der Ermittlung des Ergebnistyps nicht berücksichtigt9. Im Endeffekt passt sich null damit automatisch an den Ergebnistypen des cast-Ausdruckes an.

Gleitkommazahlen sind ebenfalls besonders: sobald ein <Ergebnis> vom Typ float, real oder double precision ist, legt der Standard lediglich fest, dass der case-Ausdruck einen Gleitkommatypen hat – welcher Typ genau ist der Implementierung überlassen10. Ebenso überlässt es der SQL-Standard den Herstellern zu entscheiden, ob und welche Typenkonvertierungen implizit stattfinden11. Dadurch erlauben manche Produkte die Mischung ungleichartiger Datentypen ohne explizitem cast.

Anwendungsfälle

Die folgenden Artikel beschreiben häufige Anwendungen von case:

• Pivot – Zeilen in Spalten wandeln – case in Aggregatfunktionen

• Division durch Null (0) vermeiden – nullif(divisor, 0)

• Weitere folgen: Bestell den Newsletter!

Kompatibilität

Der case-Ausdruck wurde mit SQL-92 im fortgeschrittenen Level eingeführt. SQL:1999 hat case als Funktion F261 in Core-SQL aufgenommen und damit verpflichtend gemacht. De-facto alle SQL-Produkten unterstützt diese Funktion heutzutage.

SQL:2003 hat die kaum unterstützten, optionalen Erweiterungen „Extended CASE expression“ (F262) und „Comma-separated predicates in simple CASE expression“ (F263) eingeführt.

Proprietäre Erweiterungen

Dokumentierter Evaluierungsablauf

Der SQL-Standard legt legt nicht fest, wie case-Ausdrücke auszuwerten sind. Insbesondere schreibt der Standard nicht vor, dass die Bedingungen in der Reihenfolge der Nennung zu prüfen sind. Solange sichergestellt ist, dass letztendlich das richtige Ergebnis geliefert wird, ist alles erlaubt.

Obwohl verschiedene Abläufe dasselbe Ergebnis liefern müssen, können sich die Nebenwirkungen, die verschiedene Abläufe haben, unterschieden. Wenn man zum Beispiel Funktionen aufruft, die Daten ändern, könnten diese auch aufgerufen werden, wenn es nicht unbedingt notwendig ist. Die Nebenwirkungen, die der Funktionsaufruf hat – Daten werden geändert –, wäre dennoch sichtbar. Ebenso kann das Auftreten oder Ausbleiben von Laufzeitfehlern vom Evaluierungsablauf abhängen: Wenn der Fehler in einem Teil des case-Ausdruckes auftritt, der nicht unbedingt evaluiert werden muss, könnte der Fehler auftreten, oder auch nicht. Zu guter Letzt ist natürlich auch die Ausführungsdauer eine Nebenwirkung, die vom Evaluierungsablauf abhängig ist.

Da es schon alleine aus Performancesicht sinnvoll ist, die when-Klauseln der Reihe nach auszuwerten und bei der ersten wahren Bedingung abzubrechen, ist genau dieser Evaluierungsablauf in der Dokumentation einiger Produkte festgeschrieben. Dennoch gibt es Fälle, bei denem der Evaluierungsablauf den Anwender überraschen kann.

Ein Beispiel für einen überraschenden Evaluierungsablauf ist folgendes: Einige Produkte ziehen die Evaluierung konstanter Ausdrücke vor – z. B. in die prepare-Phase vor der eigentlichen Ausführung. Das ist vergleichbar mit einem Compiler, der einen Ausdruck (z. B. 1/3) bereits während der Übersetzung auflöst, anstatt Code zur Berechnung zur Laufzeit zu erzeugen. Wenn diese Optimierung einen Teil eines case-Ausdruckes betrifft, kommt der Evaluierungsablauf durcheinander.

Das folgende Beispiel kann daher einen „Division durch Null (0)“-Fehler auslösen, selbst wenn die Bedingung id = 0 nie wahr ist. Selbst wenn die demo-Tabelle komplett leer ist, kann der Fehler auftreten, wenn die Evaluierung des konstanten Ausdruckes 1/0 vorgezogen wird.

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

nvl, nvl2, ifnull, isnull, … (Oracle, MySQL, Db2)

Die Funktionen nvl (Oracle, Db2), ifnull (Google BigQuery, MySQL) und isnull (SQL Server) entsprechen im wesentlichen coalesce, nur dass sie auf zwei Argumente beschränkt sind.

decode (Oracle, MySQL)

Bei der Funktion decode wird deutlich, dass proprietäre Funktionen in verschiedenen Produkten verschiedenes bewirken können. Während decode in MySQL eine (überholte) kryptographische Funktion ist, ist decode in der Oracle-Datenbank eine proprietäre Kurzform für case.

if (Google BigQuery, MySQL)

Die Funktion if ist eine proprietäre Kurzfrom eines searched Case-Ausdruckes mit nur einem When-Zweig – ähnlich dem ternären Operator ?:.