WITH

SQL-Konstrukt

Definiert eine benannte Unterabfrage (Common Table Expression, CTE), die die Hauptabfrage wie eine Tabelle verwendet.

PostgreSQL 17.5

WITH [RECURSIVE] name_cte [(column[, ...])] AS (subquery) main_query

Parameter

RECURSIVESchlüsselwortoptional: Erlaubt dem Tabellenausdruck, auf sich selbst zu verweisen
name_cteName: Name des Tabellenausdrucks
columnNameoptional: Spaltennamen des Tabellenausdrucks, standardmäßig aus subquery übernommen
subqueryUnterabfrage: Unterabfrage, deren Ergebnis unter dem Namen name_cte verfügbar ist
main_queryUnterabfrage: Hauptabfrage, die name_cte wie eine Tabelle verwendet

Rückgabewert

Temporäre benannte Ergebnismenge, die nur in dieser Abfrage verfügbar ist

Beispiele

Status mit mehr als einem Mitglied

PostgreSQL 17.5

WITH status_counts AS (
	SELECT status,
		COUNT(*) AS members_count
	FROM FamilyMembers
	GROUP BY status
)
SELECT status,
	members_count
FROM status_counts
WHERE members_count > 1
ORDER BY status;

status	members_count
daughter	3
father	2
mother	2

Zahlen 1 bis 5 mit RECURSIVE

PostgreSQL 17.5

WITH RECURSIVE numbers (n) AS (
	SELECT 1
	UNION ALL
	SELECT n + 1
	FROM numbers
	WHERE n < 5
)
SELECT n
FROM numbers;

n
1
2
3
4
5

Untergebene der Vorgesetzten nach Ebene

PostgreSQL 17.5

WITH RECURSIVE subordinates AS (
	SELECT id,
		name,
		manager_id,
		1 AS LEVEL
	FROM employees
	WHERE manager_id IS NULL
	UNION ALL
	SELECT e.id,
		e.name,
		e.manager_id,
		s.level + 1
	FROM employees AS e
		JOIN subordinates AS s ON e.manager_id = s.id
)
SELECT id,
	name,
	LEVEL
FROM subordinates
ORDER BY LEVEL,
	id;

id	name	level
1	Anna Ivanova	1
6	Olga Novikova	1
10	Tatiana Lebedeva	1
13	Roman Fedorov	1
17	Alexey Belov	1
20	Sergey Kuznetsov	1
24	Larisa Titova	1
28	Polina Nikitina	1
32	Vadim Sorokin	1
36	Boris Panov	1

Details

In einem WITH kannst du mehrere Ausdrücke durch Kommas getrennt angeben, und jeder kann auf die vorherigen verweisen.

Ein rekursiver Ausdruck (WITH RECURSIVE) besteht aus zwei Teilen, die mit UNION ALL oder UNION verbunden sind. Die Startabfrage läuft einmal und liefert die ersten Zeilen. Der rekursive Teil verweist auf den Ausdruck selbst und läuft immer wieder: Bei jedem Schritt sieht er nur die Zeilen, die im vorherigen Schritt hinzugekommen sind. Die Arbeit endet, wenn ein Schritt keine Zeilen mehr hinzufügt.

Die Anzahl der Schritte ist nicht begrenzt, deshalb braucht der rekursive Teil eine Abbruchbedingung, zum Beispiel WHERE n < 5. UNION verwirft doppelte Zeilen, was die Rekursion ebenfalls beenden kann, UNION ALL behält alle.

Siehe auch

GENERATE_SERIES

Wo du es lernst

Common Table Expression, WITH