SQL CTEs: Ein vollständiger Überblick über Common Table Expressions

Dieser Artikel bietet eine detaillierte Anleitung zu allem, was Sie über Common Table Expressions (CTEs) wissen müssen, von der grundlegenden Definition bis hin zu den komplexesten rekursiven Abfragen. Wenn die von Ihnen verwendete Datenbank CTEs unterstützt, finden Sie hier alles, was Sie brauchen, um das Beste daraus zu machen.

Gemeinsame Tabellenausdrücke - auch CTEs, WITH-Klauseln oder rekursive Abfragen genannt (obwohl der letzte Name eigentlich eine spezielle Anwendung ist) - sind eine relativ neue Funktion von SQL. Sie zielen darauf ab, komplexe Abfragen zu vereinfachen, damit sie leichter zu lesen, zu verstehen und zu pflegen sind. Lesen Sie zunächst, was ein allgemeiner Tabellenausdruck ist, um sich einen Überblick zu verschaffen. Dann schnallen Sie sich an und machen Sie sich bereit, denn dieser Artikel wird Sie durch die verschiedenen CTE-Ressourcen (Kurse, Übungen, Beispiele und Erklärungen) führen, die auf unserer LearnSQL.de Website.

CTEs sind nützlich, um große, komplexe Abfragen in kleinere, besser verständliche Teile zu zerlegen - ähnlich wie Views, Subqueries und temporäre Tabellen. Und - ebenso wie Views, Subqueries und temporäre Tabellen - können Sie CTEs kombinieren, um ein Endergebnis zu erhalten. Der Vorteil von CTEs ist jedoch, dass sie keine Objekte erzeugen, die in der Datenbank bestehen bleiben oder Platz belegen (wie Views und temporäre Tabellen). Außerdem sind sie einfacher zu lesen und zu interpretieren als Unterabfragen.

Wenn Sie unseren Rekursive Abfragen Kurs besuchen, erhalten Sie einen vollständigen Überblick über SQL CTEs. Sie lernen die CTE-Syntax und die verschiedenen Möglichkeiten, sie zu verwenden. Sie erhalten über 100 interaktive Übungen, die Sie frei in unserer Online-Datenbank ausführen können. Alles, was Sie brauchen, sind Grundkenntnisse in SQL und einen Browser mit Internetanschluss.

CTE-Syntax

Die allgemeine Form einer SQL-CTE beginnt mit einer WITH-Klausel, gefolgt von einer Abfragedefinition (eine ganz normale SELECT -Anweisung), der ein Name zugewiesen wird. Nach dieser Definition folgt eine SELECT -Anweisung, die sich auf die CTE-Abfrage mit dem ihr zugewiesenen Namen bezieht, so als ob es sich um eine Tabelle oder einen View handeln würde. Ein Beispiel:

WITH cte_name AS (cte_query_definition)
SELECT *
FROM cte_name;

Die WITH-Klausel leitet die Abfrage ein und enthält eine Unterabfrage, der ein Name zugewiesen wird. Die Hauptabfrage (nach der WITH -Klausel) ist eine normale SELECT -Anweisung, die die benannte Unterabfrage so oft wie nötig verwenden kann.

Wenn Sie eine Abfrage ausführen, die CTEs enthält, führt die Datenbankmaschine zunächst die CTE-Abfrage(n) aus und speichert die Ergebnisse der CTE-Abfrage, bis sie die Ausführung der gesamten Abfrage abgeschlossen hat; die CTE-Ergebnisse können als Zwischenergebnisse betrachtet werden. Sie verwendet diese Zwischenergebnisse wie Tabellen, um das Endergebnis zusammenzustellen. Anschließend gibt sie das Endergebnis zurück und verwirft die zuvor erzeugten Zwischenergebnisse.

Ein einfaches CTE-Beispiel

Lassen Sie uns ein einfaches Beispiel betrachten. Sie haben ein Schema mit drei Tabellen: employee, division, und payment. Sie benötigen eine Liste der Mitarbeiter mit ihrem Höchstgehalt und der Abteilung, zu der jeder Mitarbeiter gehört. Sie können eine CTE erstellen, die zunächst das Höchstgehalt für jeden Mitarbeiter in einer Unterabfrage namens max_paid. Dann verknüpfen Sie in der Hauptabfrage SELECTmax_paid mit employee und division um das Endergebnis zu erhalten:

WITH max_paid (employee_id, max_payment) AS (
	SELECT
		emp.employee_id,
		MAX(pay.payment) AS max_payment
	FROM
		employee AS emp
INNER JOIN payment AS pay ON
pay.employee_id = emp.employee_id
	GROUP BY
		emp.employee_id
	)
SELECT
	emp.employee_id,
	emp.name AS employee_name,
	div.name AS division_name,
	mp.max_payment
FROM max_paid mp
	INNER JOIN employee AS emp ON
		emp.employee_id = mp.employee_id
	INNER JOIN division AS div ON
		div.division_id = emp.division_id;

Wenn die Datenbank diese Abfrage ausführt, erstellt sie zunächst ein temporäres Dataset mit den Ergebnissen der CTE-Abfrage und benennt es max_paid. Dann verwendet sie in dem äußeren SELECT max_paid genau so, als wäre es eine Tabelle. Es verknüpft max_paid mit den echten Tabellen im Schema (employee und division), um das Endergebnis zu erstellen.

Für ein tieferes Verständnis der SQL CTE-Syntax empfehle ich diese 5 praktischen Beispiele für WITH-Klauseln.

Mehrere CTEs in einer einzigen Abfrage

In einer Abfrage kann mehr als ein CTE verwendet werden. Dazu wird jede CTE-Definition einfach durch ein Komma getrennt:

WITH cte1 AS (
	SELECT ...
	FROM ...
),
cte2 AS (
	SELECT ...
	FROM ...
)
SELECT ...
FROM cte1 JOIN cte2, ...

Jedes CTE kann andere, zuvor definierte CTEs in derselben Abfrage verwenden; dies wird als Verschachtelung bezeichnet. Auf diese Weise können CTEs große und komplexe Abfragen in kleinere (und besser zu handhabende) Unterabfragen zerlegen. Sie können jeden Teil eines Problems schrittweise lösen, bis es in eine endgültige SELECT vereinfacht wird.

Das folgende Beispiel verwendet zwei verschachtelte CTEs, die Informationen aus einer Tabelle namens sales. Die erste CTE, SalesByDayRegionermittelt die Umsatzsummen nach Tag und Region. Die zweite, SalesByDaybaut auf SalesByDayRegion auf, um die Umsatzsummen für jeden Tag zu erhalten. Die abschließende SELECT verknüpft beide CTEs, um den prozentualen Anteil der Verkäufe für jede Region im Verhältnis zu den Gesamtsummen pro Tag zu berechnen.

Hier ist die Abfrage:

WITH SalesByDayRegion AS (
	SELECT
		day,
		region,
		SUM(amount) AS AmountByDayRegion
	FROM 	Sales
	GROUP BY day, region
	),
	SalesByDay AS (
	SELECT
		day
		SUM(GroupedAmount1) AS AmountByDay
	FROM 	Sales
	GROUP BY day
	)
SELECT
	sdr.day,
	sdr.region,
	AmountByDayRegion
	AmountByDayRegion / AmountByDay AS Percentage
FROM SalesByDayRegion sdr 
INNER JOIN SalesByDay sd
ON sdr.day = sd.day;

Rekursive Abfragen

WITH Klauseln - d. h. CTEs - geben SQL die Möglichkeit, Rekursionen zu implementieren. Dadurch entfällt die Notwendigkeit, dies in anderen Programmiersprachen zu tun, wodurch eine größere Effizienz bei der Ausführung von Abfragen erreicht wird.

In jeder Programmiersprache wird eine Rekursion implementiert, indem eine Funktion oder Prozedur sich selbst wiederholt aufruft, bis eine Abbruchbedingung erfüllt ist. Bei jeder rekursiven Iteration werden die aus der vorherigen Iteration resultierenden Daten ergänzt oder geändert und die Ergebnisse der nächsten Iteration zur Verfügung gestellt. Wenn die Abbruchbedingung erfüllt ist, wird das Endergebnis zurückgegeben.

In SQL ist die Rekursion möglich, weil CTEs auf sich selbst verweisen können. In den meisten SQL-Dialekten (außer Transact SQL) wird das Wort RECURSIVE nach WITH verwendet, um eine Rekursion anzuzeigen. Mehr darüber, was eine rekursive CTE ist, erfahren Sie hier.

Rekursive CTEs bestehen aus den folgenden Elementen: einem Ankerelement, einem rekursiven Element, einer Abbruchprüfung und einem Aufruf.

Das Ankerelement legt den Startpunkt für die Rekursion fest. Dieser Teil des CTEs muss lösbar sein, ohne sich selbst aufzurufen. Das rekursive Element verwendet die Ergebnisse einer vorherigen Iteration, um weitere Daten zu verarbeiten oder zu den Ergebnissen hinzuzufügen und so eine neue Ergebnismenge für die nächste Iteration zu erhalten - oder, wenn die Abbruchbedingung erfüllt ist, eine Ergebnismenge für das aufrufende Element.

Das Ankerelement und das rekursive Element werden mit einem UNION -Operator kombiniert. Das bedeutet, dass die Ergebnismengen beider Mitglieder die gleiche Spaltenstruktur haben müssen: Ihre Spalten müssen in der gleichen Reihenfolge stehen und die gleichen Datentypen haben.

Im folgenden Beispiel wird eine rekursive CTE verwendet, um die Fakultät der Zahlen 1 bis 5 zu berechnen:

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

In diesem Beispiel ist das Ankermitglied der rekursiven CTE:

SELECT 1, 1

Und das rekursive Element ist, zusammen mit der Abbruchbedingung, das folgende SELECT:

SELECT n + 1, (n +1) * factorial 
FROM factorial 
WHERE n < 5

Der Aufruf ist einfach eine SELECT * der CTE factorial. Beachten Sie, dass diese SELECT behandelt factorial so behandelt, als wäre es eine Tabelle. Wenn wir nur an den ersten drei Zeilen des Ergebnisses der Faktorberechnung interessiert sind, können wir dies dem Aufruf hinzufügen:

SELECT * 
FROM factorial 
WHERE n <= 3;

Dieses Beispiel ist extrem einfach. Rekursive CTEs können verwendet werden, um komplexere Aufgaben zu lösen, z. B. das Durchlaufen eines Datensatzes, der eine Baumstruktur darstellt. Sie könnten dies verwenden, um das Organigramm eines Unternehmens zu untersuchen - wie wir in einem Beispiel unten sehen werden.

Wenn Sie tiefer in die Rekursivität in SQL einsteigen möchten, besuchen Sie unseren Online-KursRekursive Abfragen .

Die Vorteile von Common Table Expressions

Gemeinsame Tabellenausdrücke haben viele Vorteile, von der besseren Verständlichkeit von Abfragen bis hin zur Durchquerung hierarchischer Datenstrukturen. Sie können mit SQL sogar Bilder malen. Als Ausgangspunkt empfehle ich Ihnen die Lektüre der bereits erwähnten Artikel CTEs erklärt mit Beispielen und 5 praktische Beispiele für CTEs in SQL. Lesen Sie dann weiter, um zu sehen, wann der Einsatz von CTEs sinnvoll und effektiv ist.

Erleichterung der Abfrageorganisation und Klarheit

Mit CTEs können Sie komplexe Abfragen lösen, indem Sie sie in mehrere kleinere, besser handhabbare und leichter zu lesende Abfragen aufteilen. Durch die Möglichkeit, verschachtelte CTEs zu verwenden, können Sie sich schrittweise an eine Lösung herantasten.

Eine CTE entspricht einer Unterabfrage, jedoch mit einem sehr wichtigen Vorteil: Sie können einer CTE einen Namen geben und sie wiederholt in verschiedenen Teilen Ihrer Abfrage verwenden. Es ist so, als ob Sie einen View erstellen würden - mit dem Unterschied, dass der View ein dauerhaftes Datenbankobjekt ist. Das CTE existiert nur so lange, wie Sie die Abfrage ausführen; danach verschwindet es spurlos. Das folgende Beispiel zeigt, wie Sie eine CTE erstellen, um Zusammenfassungsdaten aus einer sales Tabelle abzurufen und dann diese CTE für verschiedene Abfragen zu verwenden:

WITH TotalSalesByCategory AS (
	SELECT customer_category, SUM(amount) AS total_sales
	FROM sales
	GROUP BY customer_category
	)
SELECT 'big customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('A', 'B', 'C')
UNION
SELECT 'medium customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('D', 'E', 'F')
UNION
SELECT 'small customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('G', 'H', 'I');

In diesen Artikeln erfahren Sie, wie Sie Ihre Abfragen entrümpeln können:

• Wie man SQL-Abfragen mit CTEs organisiert
• Verbessern der Abfrage-Lesbarkeit mit Common Table Expressions

Multi-Level-Aggregationen

Wir haben verschiedene Optionen für die Einbeziehung mehrerer CTEs in dieselbe Abfrage gesehen. Auf diese Weise vervielfachen Sie die Leistung von CTEs, um die Komplexität einer Abfrage zu reduzieren und ihre Lesbarkeit zu verbessern. Die Optionen für die Verwendung mehrerer CTEs in SQL lassen sich wie folgt zusammenfassen:

1. Mehrere unabhängige CTEs.
2. Verschachtelte CTEs.
3. Verwendung einer rekursiven CTE zusammen mit nicht rekursiven CTEs.

In den folgenden Artikeln erfahren Sie, wie Sie die Vorteile kombinierter CTEs nutzen können:

• Wie man mehrere CTEs in SQL schreibt
• Wie man 2 CTEs in einer einzigen SQL-Abfrage verwendet
• Können mehrere WITH-Anweisungen in SQL verwendet werden?

Hierarchische Daten abfragen

Hierarchische Daten zeichnen sich dadurch aus, dass sie eine geordnete Beziehung zwischen ihren Elementen aufweisen. Im Allgemeinen sind hierarchische Daten in Ebenen organisiert; Sie können herausfinden, welche Daten sich "über" oder "unter" anderen Informationen befinden.

In relationalen Datenbanken können hierarchische Beziehungen innerhalb einer Tabelle hergestellt werden, indem jede Zeile ein Feld enthält, das es ermöglicht, sie mit einer übergeordneten Zeile innerhalb derselben Tabelle zu verknüpfen. Auf diese Weise ist es möglich, hierarchische Datenstrukturen mit "Eltern"-Zeilen und "Kind"-Zeilen aufzubauen, die in der Regel visuell als baumartige Struktur dargestellt werden.

CTEs werden auch zur Abfrage hierarchischer Daten verwendet. Lesen Sie mehr über die Abfrage hierarchischer Daten in SQL, um zu erfahren, wie das geht.

Ein typisches Beispiel für eine hierarchische Datenstruktur ist das Organigramm eines Unternehmens, bei dem die Zeilen der Tabelle Mitarbeiterdaten enthalten und jeder Mitarbeiter auf seinen Chef verweist. Für weitere Details lesen Sie bitte , wie Sie alle Mitarbeiter jedes Managers in SQL finden können.

Viel Spaß beim Zeichnen von Bildern mit CTEs

Suchen Sie nach einer unterhaltsamen Möglichkeit, geniale Abfragen in SQL zu schreiben? Mit SQL können Sie keine großartigen Kunstwerke schaffen, aber Sie können mit rekursiven CTEs schematische Zeichnungen anfertigen.

Wir haben bereits erwähnt, dass Sie die Rekursion verwenden können, um hierarchische, baumartige Datenstrukturen zu durchlaufen, wie z. B. ein Organigramm. Es wird Sie vielleicht überraschen, dass Sie mit SQL nicht nur hierarchische Strukturen zeichnen, sondern auch durchlaufen können. Lesen Sie dazu diesen Artikel über die Verwendung von SQL zum Zeichnen eines ziemlich einzigartigen Weihnachtsbaums.

Alternativen zu SQL CTEs

Abgesehen von der Rekursion (die in SQL ein einzigartiger Vorzug von CTEs ist), können die übrigen Vorteile von CTEs mit Unterabfragen, Ansichten und temporären Tabellen erreicht werden. Vergleichen wir nun jede dieser Möglichkeiten mit einer CTE.

CTEs vs. Unterabfragen

Unterabfragen in SQL müssen in Klammern geschrieben und mit den übrigen Elementen der Hauptabfrage verbunden werden. Das bedeutet, dass sie der Abfrage nicht viel Klarheit verleihen. CTEs werden separat geschrieben, am Anfang der Abfrage und innerhalb ihrer eigenen WITH-Klausel. Das macht das Lesen der Abfrage viel einfacher. Wenn Sie nicht daran interessiert sind, das Innenleben der CTEs zu sehen, können Sie die WITH-Klausel übersehen und nur das Haupt-SELECT lesen, um die Ergebnisse zu verstehen.

Weitere Einzelheiten zu den Unterschieden zwischen CTEs und Unterabfragen finden Sie in diesem Artikel, der die Unterschiede zwischen CTEs und Unterabfragen eingehend erläutert.

Außerdem sind Unterabfragen nicht wiederverwendbar. Wenn Sie das Ergebnis einer Unterabfrage in verschiedenen Teilen einer Abfrage verwenden möchten, müssen Sie sie jedes Mal neu schreiben. CTEs hingegen können beliebig oft in der Hauptabfrage SELECT oder in anderen CTEs derselben Abfrage wiederverwendet werden. Dies verbessert die Lesbarkeit und die Leistung der Abfrage; jede CTE wird nur einmal ausgeführt, und ihre Ergebnisse stehen in der gesamten Abfrage zur Verfügung.

Schließlich ist zu beachten, dass Unterabfragen innerhalb der FROM -Klausel einen Alias tragen, der im Rest der Abfrage verwendet wird, um auf ihre Ergebnisse zuzugreifen. Andere Unterabfragen, die in der Spaltenliste einer SELECT - oder WHERE -Klausel enthalten sind, enthalten jedoch keine Aliasnamen. CTEs tragen immer einen Namen, der im Rest der Abfrage verwendet wird, um auf sie zu verweisen.

CTEs vs. Views

Views haben viel mit CTEs gemeinsam, aber es gibt einen entscheidenden Unterschied zwischen ihnen. Sichten bleiben als Objekte in der Datenbank, bis jemand sie entfernt. CTEs existieren nur im Kontext der jeweiligen Abfrage. Sobald die Ausführung der Abfrage beendet ist, verschwindet die CTE. Lesen Sie diesen Artikel über die Unterschiede zwischen CTEs und Views, um mehr zu erfahren.

CTEs vs. Temporäre Tabellen

Temporäre Tabellen weisen ebenfalls viele Ähnlichkeiten mit CTEs auf. Der Lebenszyklus einer temporären Tabelle endet mit der Datenbanksitzung, in der die temporäre Tabelle erstellt wurde, so dass Sie sich keine Sorgen machen müssen, dass unnötige Objekte das Datenbankschema durcheinander bringen. Es gibt jedoch einen grundlegenden Unterschied: Die Erstellung der temporären Tabelle muss in einem von der Abfrage, die sie verwendet, getrennten Befehl erfolgen. Sie können eine temporäre Tabelle nicht innerhalb einer SELECT Abfrage erstellen; wenn Sie dies täten, wäre es eine Unterabfrage und keine temporäre Tabelle.

Wenn Sie eine temporäre Tabelle erstellen und dann feststellen, dass Sie ihr eine Spalte hinzufügen oder etwas anderes ändern müssen, müssen Sie sie löschen und neu erstellen. Bei einer CTE müssen Sie nur die Änderung in ihrer Definition vornehmen und die Abfrage erneut ausführen.

CTEs in verschiedenen DBMSs

CTEs wurden im SQL-Standard von 1999 eingeführt. Ihre Implementierung in den gängigsten Datenbankmanagementsystemen (DBMS) war wie folgt:

• PostgreSQL: Version 8.4, Juli 2009
• MS SQL Server: Version 2012, Mai 2012
• Oracle-Datenbank: Version 9.2, Juni 2002
• MySQL: Version 8.0, April 2018
• MariaDB: Version 10.2.2, Mai 2018

Google BigQuery bietet auch Unterstützung für CTEs. Da es in der Cloud als vollständig verwaltetes Data Warehouse läuft, reicht es aus zu wissen, dass es derzeit Unterstützung für CTEs bietet.

Die SQL-CTE-Syntax ist für alle DBMS gleich. Die einzige Ausnahme ist MS SQL Server (T-SQL), wo die Syntax für das Schreiben einer rekursiven CTE keine Verwendung des Schlüsselworts RECURSIVE erfordert. Sie können mehr über CTEs in MS SQL Server erfahren, indem Sie über Rekursion in SQL Server lesen und sich CTE-Beispiele in SQL Server ansehen.

Anwendung Ihrer Kenntnisse über SQL CTEs in der Praxis

In diesem Artikel haben Sie alles über allgemeine Tabellenausdrücke in SQL gelernt. Jetzt müssen Sie dieses Wissen in die Praxis umsetzen. Das geht am besten, wenn Sie einen unserer speziellen Kurse zu CTEs und rekursiven Abfragen besuchen, in denen Sie zahlreiche interaktive Übungen finden.

Diese Kurse richten sich an Studenten, fortgeschrittene Datenbankanalysten und angehende Datenwissenschaftler. Die einzige Voraussetzung ist die Beherrschung der SQL-Grundlagen wie SELECT Anweisungen, JOINs, GROUP BY, usw.

Sie können sich für unseren StandardkursRekursive Abfragen entscheiden, der SQL-Dialekt-unabhängig ist, oder einen speziellen Kurs für das DBMS Ihrer Wahl wählen:

• Rekursive Abfragen für MS SQL Server
• Rekursive Abfragen für MySQL 8
• Rekursive Abfragen für PostgreSQL

Für keinen dieser Kurse müssen Sie Ihre eigenen Datenbanken verwenden. Unsere Plattform stellt Ihnen eine gebrauchsfertige Datenbank zur Verfügung, komplett mit Schema und mit Informationen bestückt. Diese Übungsumgebungen sind sicher; Sie brauchen sich keine Sorgen zu machen, dass Sie Fehler machen, denn es besteht keine Gefahr, etwas kaputt zu machen.

Sehen Sie sich diese anderen wertvollen Ressourcen an, um Ihr Wissen über WITH Abfragen zu vertiefen:

• Die 5 wichtigsten SQL CTE-Interview-Fragen
• 11 SQL Common Table Expression-Übungen

Die Bedeutung des Wissens über CTEs in SQL

Die Beherrschung der Common Table Expressions (CTEs) und rekursiven Abfragen in SQL beginnt mit dem Erlernen der Grundlagen und geht dann zu komplexeren Anwendungen über. Unsere ausgewählten Ressourcen machen das Erlernen von CTEs sowohl unterhaltsam als auch praktisch.

Um Ihre CTE-Kenntnisse aufrechtzuerhalten, sollten Sie es sich zur Gewohnheit machen, täglich zu üben. Wenn Sie mit komplexen Abfragen konfrontiert werden, überlegen Sie, ob Sie die Aufgabe mit Hilfe eines oder mehrerer CTEs vereinfachen können, indem Sie sie in kleinere, besser handhabbare Teile zerlegen. Schon bald werden Sie CTEs routinemäßig bei Ihren SQL-Aufgaben verwenden.

Außerdem empfehle ich Ihnen, unseren KursRekursive Abfragen zu besuchen, um Ihr Verständnis für SQL-CTEs zu vertiefen. Er wird Ihnen die Werkzeuge an die Hand geben, mit denen Sie selbst die kompliziertesten Abfragen sicher bewältigen können. Viel Spaß beim Lernen!