CASE und fehlende Werte
Kurz gesagt: CASE WHEN liefert je nach Bedingung einen anderen Wert, COALESCE ersetzt NULL durch einen Standardwert, und NULLIF macht aus einem bestimmten Wert NULL.
Problem
Wörter statt Codes
Auf der Seite „Meine Bestellungen“ sieht der Kunde den Status so, wie er in der Datenbank steht: paid, shipped. Die Codes sind für das Programm praktisch, aber der Kunde erwartet „Bezahlt“ und „Unterwegs“.
Die Website will außerdem einen Filter „günstig, mittel, teuer“. So eine Spalte gibt es in der Datenbank nicht, nur einen Preis.
Die Idee
CASE WHEN
CASE liefert je nach Bedingung einen anderen Wert:
Die Bedingungen werden von oben nach unten geprüft. Das erste wahre WHEN liefert den Wert des danebenstehenden THEN, die übrigen werden nicht angesehen. ELSE ist für alles, wofür nichts wahr ist. END schließt den Ausdruck, und AS gibt ihm einen Namen.
Wenn du eine Spalte mit mehreren Werten vergleichst, gibt es eine kürzere Form: Die Spalte steht einmal hinter CASE, und jedes WHEN enthält nur den Wert:
Probier es aus
Die Status in Worten
Zeige Nummer, Datum und den Status in Worten jeder Bestellung, in einer Spalte namens status_text: new ist „Eingegangen“, paid ist „Bezahlt“, shipped ist „Unterwegs“, delivered ist „Zugestellt“, cancelled ist „Storniert“.
Lösung
SELECT id, ordered_at,
CASE status
WHEN 'new' THEN 'Eingegangen'
WHEN 'paid' THEN 'Bezahlt'
WHEN 'shipped' THEN 'Unterwegs'
WHEN 'delivered' THEN 'Zugestellt'
WHEN 'cancelled' THEN 'Storniert'
END AS status_text
FROM orders;Fünf WHEN in der kurzen Form CASE status … END AS status_text. Falls je ein neuer Status auftaucht, kommt er ohne ELSE als NULL heraus. Dann merkst du es wenigstens.
Sieh es dir an
Die teuren sind verschwunden
Ein Kollege schreibt die Preisgruppen mit > um. Führe es aus und sieh dir die Küche an:
Noch etwas: Wenn es kein ELSE gibt und kein WHEN wahr ist, ist das Ergebnis NULL. Die Brote bekommen nur dann eine Notiz, wenn sie ausverkauft sind oder zur Neige gehen:
Manchmal ist das genau das, was du willst. Wenn nicht, schreib ELSE.
Die Idee
COALESCE und NULLIF
COALESCE(a, b, …) liefert den ersten Wert, der nicht NULL ist. So setzt du ein Wort an die Stelle von dem, was fehlt:
Manchmal ist „fehlt“ nicht als NULL gespeichert, sondern als leerer Text. In den beiden folgenden Beispielen hat die Datenbank einen Kunden mehr, Anna, gespeichert über ein Formular, das '' statt NULL sendet. COALESCE fängt sie nicht auf, denn leerer Text ist ein Wert:
NULLIF(a, b) liefert NULL, wenn a gleich b ist, sonst a. So wird der leere Text zu NULL, und COALESCE fängt beide Kunden auf:
Genauer, für Neugierige
NULLIF benutzt man auch bei der Division: total / NULLIF(count, 0). Ist count gleich 0, wird der Divisor NULL und damit auch das Ergebnis. In SQLite ergibt eine Division durch null ohnehin NULL, in PostgreSQL aber einen Fehler, der die ganze Abfrage stoppt.
Probier es aus
Die Zeile für den Kurier
Korrigiere die Zeile „Name, Stadt“ aus der Lektion über Text: Wenn die Stadt fehlt, soll an ihrer Stelle „keine Stadt“ stehen. Die Spalte soll contact heißen.
Lösung
SELECT name || ', ' || COALESCE(city, 'keine Stadt') AS contact FROM customers;name || ', ' || COALESCE(city, 'keine Stadt') AS contact. Jetzt hat auch Boris Mitev eine Zeile: „Boris Mitev, keine Stadt“.
Übung
Zwei Aufgaben
1. Neben jedem Produkt zeigt die Website den Lagerbestand in Worten: „ausverkauft“ bei 0, „letzte Stücke“ unter 10, sonst „auf Lager“. Zeige Name, Lagerbestand und die Worte in einer Spalte namens availability.
Der Lagerbestand in Worten für jedes Produkt:
Lösung
SELECT name, stock,
CASE
WHEN stock = 0 THEN 'ausverkauft'
WHEN stock < 10 THEN 'letzte Stücke'
ELSE 'auf Lager'
END AS availability
FROM products;Zuerst WHEN stock = 0, dann WHEN stock < 10, und ELSE zuletzt. Vertauschst du die ersten beiden, werden die ausverkauften Produkte zu „letzte Stücke“, denn 0 ist auch unter 10.
2. Das Lager arbeitet in dieser Reihenfolge: Zuerst packt es die bezahlten Bestellungen (paid), dann sieht es sich die neuen an (new), und zuletzt prüft es die verschickten (shipped). Zeige Nummer, Status und Datum dieser Bestellungen in der Arbeitsreihenfolge, und innerhalb eines Status die älteste zuerst. Bei gleichem Datum die kleinere Nummer zuerst.
Die offenen Bestellungen in der Reihenfolge, in der das Lager sie bearbeitet:
Lösung
SELECT id, status, ordered_at
FROM orders
WHERE status IN ('paid', 'new', 'shipped')
ORDER BY CASE status WHEN 'paid' THEN 1 WHEN 'new' THEN 2 ELSE 3 END, ordered_at, id;ORDER BY CASE status WHEN 'paid' THEN 1 WHEN 'new' THEN 2 ELSE 3 END, ordered_at, id. CASE macht aus dem Status eine Zahl, nach der sortiert wird. Hier prüft die Kontrolle auch die Reihenfolge.
Genauer, für Neugierige
SQLite hat auch IIF(Bedingung, wenn wahr, wenn nicht): ein kurzes CASE mit einem WHEN und einem ELSE. SQL Server hat es ebenfalls, PostgreSQL aber nicht. CASE ist die Standardform und funktioniert überall.
Test