CTE vs. Temporäre Tabelle: Was ist der Unterschied?

SQL bietet Ihnen zwei Möglichkeiten, um komplexe Abfragen effizient zu bearbeiten: CTEs und temporäre Tabellen. Lesen Sie weiter, um die Vor- und Nachteile von CTEs und temporären Tabellen abzuwägen.

In SQL (Structured Query Language) können Sie mit einem gemeinsamen Tabellenausdruck (CTE) eine Unterabfrage definieren und ihr einen Namen zuweisen, damit sie innerhalb einer Hauptabfrage verwendet werden kann. Eine temporäre Tabelle ist eine Datenbanktabelle, die Sie explizit erstellen, wie jede andere Tabelle auch - mit der Besonderheit, dass sie verschwindet, wenn Sie die Datenbankverbindung schließen, in der sie erstellt wurde. Also ja, CTEs und temporäre Tabellen haben einige Dinge gemeinsam. Aber die Debatte zwischen CTEs und temporären Tabellen geht noch weiter.

Um dieses Thema gründlich zu beherrschen, können Sie unseren KursRekursive Abfragen besuchen. Sie lernen, wie Sie CTEs verwenden, um komplexe Datenstrukturen wie Bäume und Graphen zu verarbeiten. Mit seinen 114 Übungen führt Sie dieser Kurs zur Beherrschung der komplexesten SQL-Abfragen. Außerdem bietet er ein abschließendes Quiz, um Ihr erworbenes Wissen zu testen.

CTEs und temporäre Tabellen haben einen gemeinsamen Zweck. Beide erzeugen Zwischenergebnisse für eine Abfrage, ohne permanente Objekte in der Datenbank zu hinterlassen; das spart Speicherplatz. Es gibt jedoch wichtige Unterschiede zwischen ihnen, so dass es nützlich ist, zu wissen, wann das eine oder das andere zu verwenden ist. Beginnen wir mit einem Blick auf die Syntax der CTE.

CTE-Syntax

Im Folgenden sehen wir einen Anwendungsfall, in dem eine SQL CTE einer rekursiven Abfrage vorzuziehen ist.

Eine SQL CTE beginnt mit einer WITH Klausel, gefolgt von einem Namen und der Definition der Unterabfrage. Danach folgt eine gewöhnliche SQL-Anweisung (in der Regel eine SELECT), die die Ergebnisse der CTE wie eine normale Tabelle abfragt. In ihrer einfachsten Form sieht eine CTE wie folgt aus:

WITH cte_name AS (
  cte_query_definition
)
SELECT * 
FROM cte_name;

Weitere Einzelheiten zur CTE-Syntax finden Sie unter Was ist ein Common Table Expression in SQL.

Syntax für temporäre Tabellen

Die Syntax für temporäre Tabellen variiert je nach Ihrem relationalen Datenbankmanagementsystem (RDBMS) leicht. Die folgende Tabelle zeigt, wie Sie temporäre Tabellen in den gängigsten RDBMS erstellen:

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

-- Local temp table:
CREATE TABLE #MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

-- Global temp table:
CREATE TABLE ##MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50)
)
-- data is kept until the end 
-- of the current transaction
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50))
-- data is kept until the end 
-- of the current session
ON COMMIT PRESERVE ROWS;

Wenn Sie mehr über die Erstellung aller Arten von Tabellen in SQL erfahren möchten, besuchen Sie unseren Kurs The Basics of Creating Tables in SQL. Die 80 Übungen werden Ihnen helfen, den Befehl CREATE TABLE und alle seine Optionen zu beherrschen. Sie beginnen mit der Erstellung einer Tabelle in ihrer grundlegendsten Form und gehen dann zur Definition von Primär- und Fremdschlüsseln, NULL/NICHT NULL-Spalten und zur Änderung der Struktur bestehender Tabellen über.

Nachdem wir nun die Syntax sowohl für CTEs als auch für temporäre Tabellen kennengelernt haben, wollen wir nun untersuchen, wie beide funktionieren

Lösen desselben Problems mit CTEs und Temp-Tabellen

Versuchen wir ein Beispielproblem, das mit einer CTE und einer temporären Tabelle gelöst werden kann.

Sie haben zwei Tabellen: eine detaillierte Sales Tabelle und eine Countries Tabelle mit länderspezifischen Informationen. Sie möchten die Umsatzsummen nach Ländern auflisten, zusammen mit detaillierten Länderinformationen. Dies können Sie entweder mit einem gemeinsamen Tabellenausdruck oder mit einer temporären Tabelle erreichen. Wir werden beide Methoden untersuchen, um zu sehen, wie sie für diese Aufgabe effektiv genutzt werden können.

Die CTE-basierte Lösung verwendet eine CTE namens CountryTotals mit einer Unterabfrage, die die nach CountryCode gruppierten Summen berechnet. Sie verfügt außerdem über eine Hauptabfrage, die diese Summen mit den Daten für jedes Land kombiniert:

WITH CountryTotals (CountryCode, TotalSales) AS (
  SELECT 
    CountryCode, 
    SUM(Sales) AS TotalSales
  FROM Sales
  GROUP BY CountryCode
)
SELECT 
  c.*, 
  ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
  ON ct.CountryCode = C.CountryCode;

In diesen mit Beispielen erläuterten CTEs finden Sie viele weitere Beispiele für gängige Tabellenausdrücke.

Um das gleiche Ergebnis mit einer temporären Tabelle zu erhalten, müssen Sie zunächst die temporäre Tabelle mit den Spalten CountryCode und TotalSales erstellen. Der folgende Beispielcode verwendet dazu die MySQL-Syntax:

CREATE TEMPORARY TABLE CountryTotals (
  CountryCode char(3), 
  TotalSales decimal(15, 2)
);

Die Spalten, die die Struktur der temporären Tabelle definieren, sind die gleichen wie die in der CTE-Definition von oben. Um die temporäre Tabelle zu erstellen, muss der Datentyp jeder Spalte mit dem übereinstimmen, der von der entsprechenden Spalte der Abfrage zurückgegeben wird, die Sie verwenden werden, um sie mit Daten zu füllen. Diese Abfrage ist unten dargestellt:

Beachten Sie, dass die SELECT, die nach der INSERT steht, derjenigen ähnelt, die die Unterabfrage in der ursprünglichen CTE definiert.

Da Sie nun die Umsatzsummen nach Land in der temporären Tabelle haben, können Sie sie mit der Tabelle Countries Tabelle verknüpfen, um das Endergebnis zu erhalten:

SELECT
  c.*,
  ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
  ON ct.CountryCode = C.CountryCode;

Diese Abfrage ist auch identisch mit der Haupt-CTE-Abfrage (diejenige außerhalb der WITH -Klausel), da die temporäre Tabelle denselben Namen wie die CTE hat.

Es ist wichtig zu beachten, dass es nicht immer möglich ist, temporäre Tabellen durch CTEs zu ersetzen. Dies ist möglich, wenn die Zeilen der temporären Tabelle aus einer einzigen SELECT stammen. Wenn die Daten jedoch durch UPDATE, DELETE oder andere INSERT Befehle geändert werden, nachdem sie in die temporäre Tabelle geladen wurden, können Sie die temporäre Tabelle nicht durch eine CTE ersetzen.

Wann sollte man eine CTE und wann eine temporäre Tabelle verwenden?

Selbst in den Situationen, in denen eine CTE durch eine temporäre Tabelle ersetzt werden kann (oder umgekehrt), ist es nicht immer eine gute Praxis, dies zu tun. Die Anwendungsfälle von CTEs und temporären Tabellen sind unterschiedlich; es gibt Situationen, in denen eine CTE vorzuziehen ist, und Situationen, in denen eine temporäre Tabelle vorzuziehen ist.

Untersuchen wir die Anwendungsfälle von CTEs und temporären Tabellen und beginnen wir mit CTEs.

CTE-Verwendungsfälle

CTEs erzeugen keine dauerhaften Objekte in der Datenbank. Mit anderen Worten, die CTE lebt nur so lange, wie die Abfrage, die sie enthält, ausgeführt wird. Sobald die Ausführung der Abfrage beendet ist, verschwindet das CTE spurlos. Darüber hinaus sind das CTE und die Abfrage, die es verbraucht, in derselben SQL-Anweisung enthalten. Diese Eigenschaften machen die CTE zu einem bevorzugten Mittel, um die Lesbarkeit und Organisation von SQL-Skripten zu verbessern, d. h. um Abfragen für Menschen leichter verständlich zu machen.

Kleine Datensätze

Wenn das Ergebnis einer Unterabfrage eine relativ kleine Anzahl von Zeilen ergibt (z. B. 10 oder 20 Tausend Zeilen), ist eine CTE die ideale Methode zur Implementierung. Da kein persistenter Speicher verwendet wird, fallen keine E/A-Operationen auf der Festplatte an. Daher werden Abfragen viel schneller aufgelöst.

Innerhalb von Ansichten

Sie können innerhalb eines Views keine temporäre Tabelle erstellen, mit Daten füllen oder abfragen. Sie können jedoch eine CTE in einen View einfügen und sie überall so verwenden, als wäre sie eine schreibgeschützte Tabelle.

Überarbeitung von

Beim Refactoring von Datenbankabfragen sind CTEs großartige Verbündete. In langen und komplexen Abfragen ermöglichen CTEs die Definition von Zwischenergebnissen (d. h. benannten Unterabfragen), die innerhalb derselben Abfrage wiederholt verwendet werden können.

Außerdem kann die Komplexität einer langen Abfrage mit verschachtelten CTEs schrittweise reduziert werden, bis sie auf eine SELECT von nur wenigen Zeilen vereinfacht ist.

Verbesserung der Datenbankleistung

In einigen RDBMS (z. B. MS SQL Server) können wir mit CTEs effizientere Ausführungspläne erstellen als mit temporären Tabellen. Dies kann die Leistung der Datenbank durch die Verkürzung der Ausführungszeiten von Abfragen erheblich verbessern.

Der erzielte Leistungsvorteil hängt vom RDBMS-Optimierer ab, was bedeutet, dass die Verwendung von CTEs nicht immer zu einer besseren Leistung führt. Es ist jedoch ein Faktor, der bei der Wahl zwischen einer CTE und einer temporären Tabelle für die Behandlung von Unterabfragen berücksichtigt werden sollte.

Rekursion

Ein Bereich, in dem die Verwendung von CTEs obligatorisch ist, sind rekursive Abfragen. In SQL ist eine rekursive CTE in der Lage, sich selbst aufzurufen, um rekursive Prozesse zu implementieren, wie z. B. das Durchlaufen von hierarchischen Datenstrukturen. Mit anderen Worten: Rekursion erfordert CTEs.

Durch die Erlaubnis zur Rekursion in SQL wird die Verwendung anderer Programmiersprachen vermieden, was die Effizienz von Abfragen, die sich selbst aufrufen müssen, erhöht. Weitere Informationen finden Sie in unserem Artikel Was ist eine rekursive CTE in SQL?

Jetzt, da Sie wissen, in welchen Fällen es sinnvoll ist, eine CTE zu verwenden, können Sie Ihr Wissen erweitern, indem Sie die häufigsten SQL-CTE-Interviewfragen durchgehen und sich mit diesen CTE-Übungen weiterbilden.

Anwendungsfälle für temporäre Tabellen

Der Hauptvorteil von CTE - die nicht dauerhafte Speicherung von Daten - kann auch ein Nachteil sein. In einigen Anwendungsfällen ist es besser, Daten in Tabellen (auch temporären) zu speichern. Schauen wir uns die typischsten Situationen an.

Wiederverwendung von Datensätzen

Die Wiederverwendung von CTE-Code ist auf eine einzige Abfrage beschränkt. Daten, die in einer temporären Tabelle gespeichert sind, können dagegen wiederholt in verschiedenen Abfragen verwendet werden. Die wichtigste Voraussetzung ist, dass diese Abfragen innerhalb der gleichen Datenbankverbindung ausgeführt werden.

ETL-Prozesse

Wenn Sie ETL-Prozesse (Extrahieren, Transformieren, Laden) mit Hilfe von SQL-Skripten implementieren, müssen Sie oft Zwischenergebnisse generieren und aufeinanderfolgende Transformationen auf einen einzigen Datensatz anwenden. Erst danach können Sie die Daten in permanente Tabellen auslagern. In solchen Situationen ist es sehr hilfreich, während des gesamten Transformationsprozesses temporäre Tabellen bereitzuhalten.

Große Datenmengen

Bei der Arbeit mit großen, temporären Datensätzen - z. B. Millionen von Datensätzen - sind temporäre Tabellen immer den CTEs vorzuziehen. Wenn Sie versuchen, eine CTE auszuführen, die eine großvolumige Subquery enthält, ist es sehr wahrscheinlich, dass die Datenbank-Engine versuchen würde, die temporären Ergebnisse im Speicher des Servers zu speichern. Dadurch würde die verfügbare Kapazität erschöpft oder es würden einige Betriebssystemressourcen benötigt, um den Speicher auf die Festplatte auszulagern. Kurz gesagt, es würde die Leistung der Datenbank beeinträchtigen. Wenn Sie also im Voraus wissen, dass die Ergebnisse einer Unterabfrage sehr groß sein werden, ist es am besten, temporäre Tabellen anstelle von CTEs zu verwenden.

Erzeugen von Indizes

Wie jede andere Tabelle in einer Datenbank unterstützen auch temporäre Tabellen die Erstellung von Indizes, um die Ausführung von Abfragen zu beschleunigen. Wenn Zwischenergebnisse eine große Anzahl von Zeilen erfordern (und wiederholt verwendet werden müssen), speichern Sie diese Ergebnisse in einer temporären Tabelle mit einem oder mehreren Indizes. Die Erstellung der Indizes kann einige Zeit in Anspruch nehmen, aber diese Zeit wird mehr als wettgemacht, wenn anschließend mehrere Abfragen auf demselben Datensatz ausgeführt werden müssen.

Im Allgemeinen sind die besten Kandidaten für eine Indexierung die Spalten, die in JOINs, WHERE oder GROUP BY verwendet werden.

Zusammenfassung der CTE- vs. Temp-Table-Debatte

Die Entscheidung zwischen CTEs und temporären Tabellen ist nicht immer einfach. Wir haben die Syntax und die typischen Anwendungsfälle für diese beiden Lösungen erörtert.

Vergessen Sie nicht, sich unseren Rekursive Abfragen Kurs, in dem Sie lernen, wie man allgemeine Tabellenausdrücke verwendet. Der Kurs richtet sich an angehende Datenbankanalysten, Studenten und Entwickler, die ihre SQL-Kenntnisse vertiefen möchten. Um den Kurs zu besuchen, benötigen Sie lediglich einen Computer mit Internetanschluss und einen Webbrowser.

Jetzt ist es an der Zeit, Ihr Wissen einzusetzen! Wenn Sie wissen, wann Sie CTEs und wann Sie temporäre Tabellen verwenden sollten, werden Sie bei der Arbeit mit temporären Daten immer maximale Effizienz erreichen.