Unterabfragen benennen mit WITH

Unterabfragen in FROM können eine Abfrage schwer lesbar machen. Du musst sie von innen nach außen lesen, und mit jeder Unterabfrage verschachtelt sie sich tiefer. Mit WITH schreibst du die Unterabfrage zuerst, gibst ihr einen Namen und benutzt den Namen dann wie eine Tabelle:

WITH summen AS (
    SELECT bestellung_id, SUM(menge * preis) AS gesamt
    FROM positionen
    GROUP BY bestellung_id
)
SELECT ROUND(AVG(gesamt), 2) AS durchschnitt_bestellung
FROM summen;

Es ist die durchschnittliche Bestellung aus der vorigen Übung, aber jetzt liest es sich von oben nach unten: Berechne zuerst die Summen der Bestellungen und nenne sie summen; berechne dann ihren Durchschnitt. Der Teil WITH name AS (abfrage) heißt Common Table Expression, kurz CTE. Er gehört zu der Abfrage, die ihm folgt: Zusammen sind sie eine Anweisung mit einem Semikolon am Ende, und der Name summen existiert nur innerhalb dieser Anweisung.

Eine benannte Abfrage kann mehrfach benutzt werden. Die größte Bestellung ist die, deren Summe der größten Summe entspricht:

WITH summen AS (
    SELECT bestellung_id, SUM(menge * preis) AS gesamt
    FROM positionen
    GROUP BY bestellung_id
)
SELECT bestellung_id, gesamt
FROM summen
WHERE gesamt = (SELECT MAX(gesamt) FROM summen);

Ohne WITH müsstest du die Abfrage, die die Summen berechnet, zweimal schreiben, einmal in FROM und einmal in der Unterabfrage in WHERE.

Ein WITH kann mehrere Abfragen benennen, durch Kommas getrennt, wobei das Wort WITH nur am Anfang steht. Jede von ihnen kann die vor ihr benutzen. Hier behält bezahlte die bezahlten Bestellungen, bezahlte_summen berechnet ihre Summen, und die Hauptabfrage addiert sie:

WITH bezahlte AS (
    SELECT *
    FROM bestellungen
    WHERE status = 'bezahlt'
),
bezahlte_summen AS (
    SELECT p.bestellung_id, SUM(p.menge * p.preis) AS gesamt
    FROM positionen AS p
    JOIN bezahlte AS bz ON bz.id = p.bestellung_id
    GROUP BY p.bestellung_id
)
SELECT COUNT(*) AS bezahlte_bestellungen, SUM(gesamt) AS umsatz
FROM bezahlte_summen;

Typische Fehler: ein Komma nach der letzten benannten Abfrage, direkt vor dem SELECT der Hauptabfrage (near "SELECT": syntax error), und ein Semikolon nach ihrer schließenden Klammer, das die Anweisung beendet, bevor die Hauptabfrage begonnen hat (near ";": syntax error).