SQLite CTEs: Die WITH-Klausel verständlich erklärt

Eine CTE ist eine benannte Unterabfrage

Eine Common Table Expression (CTE) ist im Grunde eine Unterabfrage, die du nach außen ziehst und mit einem Namen versiehst. Statt ein SELECT in einem anderen SELECT zu verschachteln, definierst du es oben mit WITH, gibst ihm einen Namen und sprichst es in der Hauptabfrage an, als wäre es eine ganz normale Tabelle.

Der Aufbau ist immer derselbe:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

Lies sie einfach von oben nach unten: Zuerst entsteht ein benanntes Zwischenergebnis namens customer_totals, danach fragst du dieses Ergebnis ab. Eine CTE verhält sich wie eine temporäre View, die nur für die Dauer dieses einen Statements existiert.

Dieselbe Abfrage ohne CTE

Hier ist dieselbe Logik als Subquery geschrieben – so siehst du direkt, was die CTE eigentlich ersetzt:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

SELECT customer, total
FROM (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
WHERE total > 100;

Gleiche Antwort. Aber achte mal auf die Leserichtung: Dein Auge muss erst in die Klammern eintauchen, herausfinden, was da berechnet wird, und sich dann wieder hocharbeiten. Die CTE-Variante liest sich genau in der Reihenfolge, in der die Arbeit passiert — erst das Zwischenergebnis benennen, dann damit weiterarbeiten. Bei einer kleinen Query ist das egal. Sobald drei oder vier Schritte zusammenkommen, entscheidet das aber darüber, ob du den Code überfliegen kannst oder ihn entschlüsseln musst.

Mehrere CTEs in einer Query kombinieren

Du kannst mehrere CTEs hintereinanderhängen, getrennt durch Kommata. Jede CTE darf auf die vorher definierten zugreifen — so baust du dir Schritt für Schritt eine Pipeline aus benannten Zwischenergebnissen:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

Ein WITH, danach die CTE-Definitionen durch Kommas getrennt. Die zweite CTE (big_spenders) greift auf die erste (customer_totals) zu, als wäre sie eine ganz normale Tabelle. Das eigentliche SELECT kommt nach der letzten CTE-Definition.

Ein typischer Stolperstein: Vor der zweiten CTE nochmal WITH zu schreiben. Bitte nicht – das gibt einen Syntaxfehler. Ein einziges WITH deckt alle CTEs ab.

Eine CTE mehrfach referenzieren

Genau hier spielen CTEs ihre Stärke gegenüber Subqueries aus. Wenn du dasselbe Zwischenergebnis an mehreren Stellen brauchst, berechnest du es mit einer CTE einmal und verwendest es dann beliebig oft:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

Auf die CTE wird hier zweimal zugegriffen: einmal zur Berechnung des Durchschnitts und einmal als eigentliche Datenquelle. Ohne CTE müsstest du die GROUP BY-Abfrage doppelt schreiben – jede Änderung wäre dann an zwei Stellen nötig.

CTE mit INSERT, UPDATE und DELETE kombinieren

CTEs sind nicht nur für SELECT gedacht. Du kannst eine WITH-Klausel auch vor INSERT, UPDATE oder DELETE setzen und so eine benannte Unterabfrage beim Schreiben nutzen:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

Die CTE legt fest, welche Zeilen markiert werden sollen. Das INSERT ... SELECT nutzt sie dann als Quelle. Genauso funktioniert das mit DELETE FROM ... WHERE id IN (SELECT id FROM cte), wenn du Löschvorgänge in mehreren Schritten vorbereiten willst und die Auswahllogik etwas verzwickter ist.

Wann lohnt sich eine CTE?

Ein paar Faustregeln:

• Die Abfrage hat mehrere logische Schritte. Erst aggregieren, dann auf das Aggregat filtern, dann das Ergebnis joinen — das ist eine Pipeline, und mit je einer CTE pro Schritt wird sie lesbar.
• Du müsstest sonst dieselbe Subquery doppelt schreiben. Einmal definieren, zweimal referenzieren.
• Die Subquery hat einen Namen verdient. Wenn du sowieso einen Kommentar darüber schreiben würdest, um zu erklären, was sie darstellt, dann ist der CTE-Name genau dieser Kommentar — nur eben durch die Syntax erzwungen.
• Du willst eine rekursive Abfrage schreiben. Das geht überhaupt nur mit WITH RECURSIVE — darum geht's auf der nächsten Seite.

Wann es sich nicht lohnt:

• Eine einzelne, simple Subquery, die nur an einer Stelle vorkommt. WHERE id IN (SELECT id FROM ...) ist so völlig in Ordnung.
• Performance-kritische Abfragen, bei denen du bereits nachgewiesen hast, dass das Einbetten der Logik schneller ist. SQLite behandelt eine CTE in der Regel weniger streng als eine Optimierungsbarriere als manche anderen Datenbanken, aber bei heißen Pfaden lohnt sich ein Blick mit EXPLAIN QUERY PLAN.

Ein durchgespieltes Beispiel

Jetzt alles zusammen — ein kleiner Report, der für jeden Kunden die größte Bestellung findet und sie mit dem persönlichen Durchschnitt vergleicht:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

Zwei CTEs, jede mit einer klaren Aufgabe. Das eigentliche SELECT kümmert sich nur noch um die Formatierung. Du kannst die Query von oben nach unten lesen und jeden Schritt einzeln nachvollziehen – und genau darum geht es bei CTEs.

Als Nächstes: Rekursive CTEs

Bis hierher haben wir ausschließlich mit normalen CTEs gearbeitet – also mit benannten Unterabfragen, die einmal ausgewertet werden. SQLite kennt aber auch WITH RECURSIVE: Dabei referenziert sich ein CTE selbst, um Hierarchien abzulaufen, Zahlenreihen zu erzeugen oder Graphen zu durchsuchen. Genau das schauen wir uns auf der nächsten Seite an.