SQL CTE vs. Subquery: Ein Leitfaden

Eine Subquery ist eine Abfrage, die in einer anderen Abfrage verschachtelt ist. Eine CTE (Common Table Expression) ist dieselbe innere Abfrage, nur mit WITH an den Anfang der Anweisung verschoben und mit einem Namen versehen – so liest sich eine lange Abfrage wie eine Abfolge von Schritten. Die Beispiele funktionieren in PostgreSQL, MySQL, SQLite und SQL Server.

So verwendest du CTEs und Subqueries

Eine Subquery steht dort, wo sie gebraucht wird: In FROM verhält sie sich wie eine Tabelle, in WHERE oder SELECT liefert sie einen Wert oder eine Liste. Eine CTE wird zuerst definiert und dann von der folgenden Abfrage wie eine Tabelle verwendet:

-- Subquery: written where it's used
SELECT columns FROM (SELECT ...) AS name;

-- CTE: named first, then used like a table
WITH name AS (SELECT ...)
SELECT columns FROM name;

SELECT n * 2 AS doubled FROM (SELECT 21 AS n) AS numbers;
-- Output: 42

WITH numbers AS (SELECT 21 AS n)
SELECT n * 2 AS doubled FROM numbers;
-- Output: 42

Auf einer echten Tabelle liefern beide Varianten dieselben Zeilen. Hier ist ein Filter, auf beide Arten geschrieben:

-- Input: people
-- first_name | last_name | job      | favorite_food
-- -----------+-----------+----------+--------------
-- Matthew    | Ortiz     | Chef     | cheese
-- Matt       | Novak     | Pilot    | pizza
-- Mattie     | Brooks    | Engineer | cheese
-- Priya      | Nair      | Teacher  | cheese

-- Subquery
SELECT *
FROM (SELECT first_name, last_name, job FROM people WHERE favorite_food = 'cheese') AS cheese_lovers
WHERE first_name LIKE 'Matt%';

-- CTE
WITH cheese_lovers AS (
  SELECT first_name, last_name, job FROM people WHERE favorite_food = 'cheese'
)
SELECT * FROM cheese_lovers
WHERE first_name LIKE 'Matt%';

-- Output of both:
-- first_name | last_name | job
-- -----------+-----------+---------
-- Matthew    | Ortiz     | Chef
-- Mattie     | Brooks    | Engineer

Wichtige Regeln

• Eine CTE gilt nur für eine Anweisung. Die Abfrage direkt nach WITH kann sie nutzen, die nächste Anweisung nicht. Wenn du ein Ergebnis für mehrere Abfragen behalten willst, nimm eine temporäre Tabelle oder eine View.
• Gib einer Subquery in FROM einen Namen, wie AS numbers oben. MySQL und SQL Server lehnen sie sonst ab.
• Eine Subquery, die als einzelner Wert verwendet wird, muss genau eine Zeile zurückgeben:

SELECT (SELECT 1 UNION ALL SELECT 2) AS n;
-- ERROR: more than one row returned by a subquery used as an expression
-- (SQLite doesn't complain: it quietly uses the first row, 1)

• Die Geschwindigkeit ist meist gleich. Datenbanken planen eine einmal verwendete CTE in der Regel genauso wie die entsprechende Subquery – nimm also, was sich besser liest.

Alternative Syntax

Eine CTE, die sich auf sich selbst bezieht (siehe unten), wird mit WITH RECURSIVE geschrieben:

-- In SQL Server, leave out RECURSIVE
WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 3
)
SELECT n FROM counter;

-- Output:
-- n
-- -
-- 1
-- 2
-- 3

In SQL Server muss die Anweisung vor einem WITH mit einem Semikolon enden. Deshalb beginnt T-SQL-Code eine CTE oft mit ;WITH.

Wann solltest du eine CTE statt einer Subquery verwenden?

Für einen kleinen Schritt reicht eine Subquery. Wechsle zu einer CTE, wenn die Abfrage mehrere Schritte hat, dasselbe Ergebnis zweimal braucht oder sich auf sich selbst beziehen muss.

Gegen einen berechneten Wert filtern

Wenn die innere Abfrage nur einen Wert oder eine Liste zum Vergleichen liefert, etwa den durchschnittlichen Bestellwert, ist eine Subquery in WHERE die kürzeste Schreibweise. Eine CTE ginge auch, fügt aber einen Namen hinzu, den niemand braucht.

Eine lange Abfrage in benannte Schritte aufteilen

Wenn eine Abfrage aus Stufen besteht (Summe pro Kunde, dann die besten paar, dann die finale Liste), gib jeder Stufe einen Namen. Liste mehrere CTEs nach einem WITH auf, getrennt durch Kommas: Jede kann die vorherigen verwenden, und die Abfrage liest sich von oben nach unten statt von innen nach außen.

Dasselbe Ergebnis mehrfach verwenden

Eine Anweisung kann beliebig oft auf eine CTE verweisen. Ein Ergebnis, das du zweimal brauchst (eine Liste von Summen und ihr Durchschnitt oder jede Summe und die Gesamtsumme), schreibst du also nur einmal. Mit Subqueries müsstest du die ganze innere Abfrage kopieren.

Eine Hierarchie durchlaufen

Nur eine CTE kann sich auf sich selbst beziehen. Eine rekursive CTE startet bei bestimmten Zeilen und joint immer wieder mit der Tabelle, bis keine neuen Zeilen mehr hinzukommen. So durchläufst du ein Organigramm oder einen Kategoriebaum oder erzeugst eine Reihe von Zahlen oder Datumswerten.

Praxisbeispiele für CTEs und Subqueries

Produkte über dem Durchschnittspreis ihrer Kategorie

Die Subquery verweist auf p.category aus der äußeren Abfrage und wird daher für die Kategorie jedes einzelnen Produkts berechnet (eine korrelierte Subquery):

-- Input: products
-- name          | category  | price
-- --------------+-----------+-------
-- Desk Lamp     | Lighting  | 24.99
-- Floor Lamp    | Lighting  | 89.00
-- Office Chair  | Furniture | 149.00
-- Standing Desk | Furniture | 399.00
-- Bookshelf     | Furniture | 120.00

SELECT name, category, price
FROM products p
WHERE price > (
  SELECT AVG(price) FROM products WHERE category = p.category
)
ORDER BY category, name;

-- Output:
-- name          | category  | price
-- --------------+-----------+-------
-- Standing Desk | Furniture | 399.00
-- Floor Lamp    | Lighting  | 89.00

Das meistverkaufte Produkt jeder Kategorie

Drei Schritte mit je einem Namen: Stückzahlen pro Produkt summieren, die Produkte innerhalb jeder Kategorie ranken und dann das beste behalten:

-- Input: sales
-- id | product      | category  | units
-- ---+--------------+-----------+------
-- 1  | Desk Lamp    | Lighting  | 3
-- 2  | Floor Lamp   | Lighting  | 5
-- 3  | Desk Lamp    | Lighting  | 4
-- 4  | Office Chair | Furniture | 2
-- 5  | Bookshelf    | Furniture | 6

WITH product_totals AS (
  SELECT category, product, SUM(units) AS units
  FROM sales
  GROUP BY category, product
),
ranked AS (
  SELECT category, product, units,
         RANK() OVER (PARTITION BY category ORDER BY units DESC) AS place
  FROM product_totals
)
SELECT category, product, units
FROM ranked
WHERE place = 1
ORDER BY category;

-- Output:
-- category  | product   | units
-- ----------+-----------+------
-- Furniture | Bookshelf | 6
-- Lighting  | Desk Lamp | 7

Umsatzanteil jeder Region

region_totals wird zweimal verwendet: einmal für die Zeilen und einmal in einer Subquery für die Gesamtsumme:

-- Input: orders
-- id | region | amount
-- ---+--------+--------
-- 1  | North  | 1200.00
-- 2  | South  | 450.00
-- 3  | North  | 300.00
-- 4  | West   | 800.00
-- 5  | South  | 250.00

WITH region_totals AS (
  SELECT region, SUM(amount) AS total
  FROM orders
  GROUP BY region
)
SELECT
  region,
  total,
  ROUND(100.0 * total / (SELECT SUM(total) FROM region_totals), 1) AS pct_of_sales
FROM region_totals
ORDER BY total DESC;

-- Output:
-- region | total   | pct_of_sales
-- -------+---------+-------------
-- North  | 1500.00 | 50.0
-- West   | 800.00  | 26.7
-- South  | 700.00  | 23.3

Mit Subqueries geschrieben, würde die GROUP BY-Abfrage zweimal auftauchen.

Die Vorgesetztenkette eines Mitarbeiters

Starte bei einem Mitarbeiter und folge manager_id nach oben, bis die Kette endet:

-- Input: employees
-- id | name      | manager_id
-- ---+-----------+-----------
-- 1  | Maya Chen | NULL
-- 2  | Sam Ortiz | 1
-- 3  | Jo Kim    | 2
-- 4  | Lee Ross  | 2
-- 5  | Ana Diaz  | 1

-- In SQL Server, leave out RECURSIVE
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE name = 'Jo Kim'
  UNION ALL
  SELECT e.id, e.name, e.manager_id, chain.level + 1
  FROM employees e
  JOIN chain ON e.id = chain.manager_id
)
SELECT level, name FROM chain ORDER BY level;

-- Output:
-- level | name
-- ------+----------
-- 0     | Jo Kim
-- 1     | Sam Ortiz
-- 2     | Maya Chen

Weiterführende Artikel zu CTEs und Subqueries

Auf diesem Blog:

• Leitfaden zu SQL-Temp-Tabellen: ein Zwischenergebnis für mehr als eine Anweisung behalten
• Duplikate in SQL finden: CTEs mit ROW_NUMBER(), um doppelte Zeilen zu erkennen
• Duplikate in SQL entfernen: Duplikate über eine CTE löschen
• Leitfaden zu SQL GROUP BY: die Aggregate, aus denen die meisten CTEs bestehen
• SQL JOINs in Diagrammen: eine CTE mit anderen Tabellen joinen

Offizielle Dokumentation:

• PostgreSQL: WITH-Abfragen und Subquery-Ausdrücke
• MySQL: WITH (Common Table Expressions) und Subqueries
• SQLite: die WITH-Klausel
• SQL Server: WITH common_table_expression und Subqueries