Subquery vs. CTE: Eine SQL-Fibel

Haben Sie sich jemals über die Unterschiede zwischen einer Subquery und einem Common Table Expression (CTE) in SQL gewundert? Die Konzepte scheinen sehr ähnlich zu sein, aber wenn Sie den Unterschied kennen - und wissen, wann Sie welches Konzept verwenden sollten -, können Sie effiziente und lesbare Abfragen schreiben.

Zunächst erklären wir die Konzepte, die Unterabfragen und CTEs zugrunde liegen. Dann sehen wir uns einige Beispiele an und analysieren schließlich die Vor- und Nachteile der beiden Techniken.

Was ist eine Unterabfrage?

Eine Unterabfrage ist eine Abfrage innerhalb einer Abfrage. Sie kann auf verschiedene Weise verwendet werden: in der FROM -Klausel, zum Filtern oder sogar als Spalte. Um eine Subquery zu verwenden, fügen Sie einfach Klammern hinzu und fügen die Abfrage in diese Klammern ein.

In unseren ersten Beispielen werden wir mit Daten zu den Ergebnissen eines Weitsprungwettbewerbs arbeiten. Wir erhalten zwei Tabellen:

participant - Speichert Informationen über Wettbewerbsteilnehmer:

• id - Eine eindeutige ID für jeden Teilnehmer.
• first_name - Den Vornamen des Teilnehmers.
• last_name - Der Nachname des Teilnehmers.

jump - Speichert Informationen über die Sprünge, die die Teilnehmer gemacht haben:

• id - Die ID eines jeden Sprungs.
• participant_id - Die ID des Teilnehmers, der den Sprung gemacht hat.
• contest_id - Die ID des Wettbewerbs, in dem der Sprung gemacht wurde.
• length - Die Sprunglänge in Zentimetern.

participant

jump

Da Sie die verwendeten Daten kennen, sehen Sie sich die folgenden Beispiele für Unterabfragen an:

SELECT
  first_name,
  last_name,
  length
FROM participant
JOIN jump
  ON jump.participant_id = participant.id
WHERE length > (
  SELECT
    AVG(length)
  FROM jump
);

Diese Abfrage zeigt Teilnehmer mit Sprüngen, die länger als die durchschnittliche Sprunglänge waren. In der WHERE-Bedingung haben wir eine Unterabfrage verwendet, um die durchschnittliche Sprunglänge zu ermitteln. Da diese Unterabfrage nur einen Wert zurückgibt, können wir die Spaltendaten leicht damit vergleichen.

Als nächstes ein weiteres Beispiel:

SELECT
  MAX(avg_length) AS max_length
FROM (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
) AS average_lengths;

In dieser Abfrage zeigen wir die größte durchschnittliche Sprunglänge aller Teilnehmer an. Um das Ergebnis zu erhalten, berechnen wir zunächst die durchschnittliche Sprunglänge für jeden Teilnehmer. Dazu verwenden wir eine Unterabfrage innerhalb der FROM Klausel. Dann verwenden wir einfach MAX(), um die größte durchschnittliche Länge zu ermitteln.

Dies sind nur zwei Beispiele für Unterabfragen. Das Thema ist sehr umfangreich - auch wenn die Anwendungsfälle eher einfach sind - und es gibt zu viele Konzepte, um sie in diesem Artikel zu beschreiben. Einen kurzen Überblick über Subqueries finden Sie im Artikel SQL-Unterabfragen auf dem LearnSQL.de Blog. Wenn Sie sich für die Arbeit mit Subqueries interessieren, schauen Sie sich Teil 6 des SQL für Anfänger Natürlich (er heißt sogar Subqueries).

Sie können sich auch Episoden unserer Serie We Learn SQL auf Youtube ansehen. Einige davon sind den SQL-Subqueries gewidmet. Vergessen Sie nicht, unseren Kanal zu abonnieren.

Was ist CTE?

Ein gemeinsamer Tabellenausdruck (kurz CTE genannt) ist eine Abfrage, die wir erstellen, bevor wir die Hauptabfrage schreiben. Dann können wir sie einfach als reguläre Tabelle in unserem Code verwenden.

Sehen Sie sich das folgende Beispiel an. Auch hier verwenden wir die Daten des Weitsprungwettbewerbs:

WITH average_lengths AS (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
)

SELECT
  MAX(avg_length) AS max_length
FROM average_lengths;

Dies liefert genau das gleiche Ergebnis wie das vorherige Beispiel mit der Unterabfrage: die größte durchschnittliche Sprunglänge aller Teilnehmer. Anstatt unsere Abfrage jedoch in die FROM Klausel zu schreiben, haben wir sie in die WITH Klausel eingefügt (die vor der Hauptabfrage steht). Wir haben unsere CTE average_length aufgerufen und in der FROM -Klausel in der Hauptabfrage verwendet.

Natürlich können CTEs viel komplizierter sein als dieses Beispiel. Aber darauf gehen wir hier nicht ein. Wenn Sie mehr über CTEs erfahren möchten, schauen Sie sich den Rekursive Abfragen Kurs unter LearnSQL.de.

Überhaupt kein Unterschied...?

Auf den ersten Blick könnte man meinen, dass es fast keinen Unterschied zwischen Unterabfragen und CTEs gibt. Wir haben sowohl eine Subquery als auch eine CTE in der FROM -Klausel verwendet, und die Syntax war nur geringfügig anders. Vergessen Sie jedoch nicht das erste Beispiel - dort haben wir eine Subquery in der WHERE -Klausel verwendet. Sie konnten dort keine CTE verwenden, und das ist nicht der einzige Unterschied!

Subquery vs. CTE: Was ist der Unterschied?

Das bedeutet natürlich nicht, dass CTEs den Subqueries unterlegen sind. Untersuchen wir die Unterschiede zwischen den beiden, beginnend mit CTEs.

Unterschied Nr. 1: CTEs können rekursiv sein

Schauen wir uns den ersten Vorteil von CTEs an. Mit CTEs können Sie ein leistungsfähiges Konzept nutzen: Rekursion. Dank der Rekursion ist SQL jetzt Turing-komplett - jedes Programm, das in einer beliebigen Programmiersprache geschrieben werden kann, kann auch in SQL geschrieben werden. (Wenn Sie daran zweifeln, dass SQL eine Programmiersprache ist, lesen Sie den Artikel Ist SQL eine Programmiersprache? auf dem Blog LearnSQL.de ).

Wie funktioniert die Rekursion in SQL? Sie ermöglicht es Ihrer CTE, sich selbst aufzurufen, bis eine bestimmte Endbedingung erfüllt ist. In jedem Schritt erweitert sich die Abfrage selbst und ändert die Daten, die sie hat. Sehen wir uns ein Beispiel an.

Wir werden mit den folgenden Daten für einige fiktive Mitarbeiter eines Unternehmens arbeiten. Sie sind in der Tabelle employee Tabelle gespeichert, die die folgenden Spalten enthält:

• id - Eine eindeutige ID für jeden Mitarbeiter.
• first_name - Den Vornamen des Mitarbeiters.
• last_name - Der Nachname des Mitarbeiters.
• manager_id - Die ID des Vorgesetzten dieses Mitarbeiters.

employee

Nun möchten wir die Managementkette vom CEO (eine Person ohne Wert in der Spalte manager_id ) bis zu jedem Mitarbeiter anzeigen. Die nachstehende Abfrage wird dieses Problem lösen. Schauen Sie sich das an:

WITH RECURSIVE employee_chain AS (
  SELECT
    id,
    first_name,
    last_name,
    first_name || ' ' || last_name AS chain
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  SELECT
    employee.id,
    employee.first_name,
    employee.last_name,
    chain || '->' || employee.first_name || ' ' || employee.last_name
  FROM employee_chain
  JOIN employee
    ON employee.manager_id = employee_chain.id
)

SELECT
  first_name,
  last_name,
  chain
FROM employee_chain;

Das Ergebnis sieht wie folgt aus:

Wir haben eine Abfrage geschrieben, mit der sich leicht eine ganze Kette von Beziehungen erstellen lässt. Man könnte meinen, dass dies mit Unterabfragen erreicht werden könnte, aber da die Verwaltungskette immer tiefer geht, müssten Sie immer mehr Code schreiben. Der Umfang des zu schreibenden Codes würde von der Tiefe der Kette abhängen - und das lässt sich nur mit einer rekursiven CTE überprüfen.

Wie funktioniert diese Abfrage? Zunächst wird der erste Teil (vor UNION ALL) ausgeführt und ein Mitarbeiter ohne Vorgesetzten (d. h. Maisy Bloom) ausgewählt. Dann wählt der Teil unterhalb der UNION ALL die Mitarbeiter aus, die direkt von Maisy verwaltet werden (Caine Farrow). Da die Abfrage sich selbst aufruft, wird derselbe Teil erneut ausgeführt und wählt alle von Caine verwalteten Mitarbeiter aus (Waqar Jarvis und Lacey-Mai Rahman). Dieser Vorgang wird so lange wiederholt, wie es Zeilen zu verbinden gibt. Nachdem die gesamte Verwaltungskette durchlaufen wurde, wird die Abfrage angehalten.

Wenn Sie zum ersten Mal mit der Rekursion in SQL in Berührung kommen, ist das vielleicht ein wenig schwer zu verstehen. Und das ist völlig normal. Sehen Sie sich Machen Sie es in SQL: Rekursives SQL-Baum-Traversal für eine ausführlichere Erklärung.

Unterschied #2: CTEs sind wiederverwendbar

Ein großer Vorteil von CTEs ist, dass sie mehrfach in einer Abfrage verwendet werden können. Sie müssen nicht den gesamten CTE-Code kopieren, sondern geben einfach den CTE-Namen an.

Mit den Daten aus dem vorherigen Abschnitt möchten wir 1) die Mitarbeiter herausfiltern, die keinen Vorgesetzten haben, und 2) jeden Mitarbeiter mit seinem Vorgesetzten anzeigen - aber nur, wenn er einen Vorgesetzten hat. Das Ergebnis sieht dann so aus:

Schauen wir uns nun an, wie ein CTE diese Aufgabe lösen würde:

WITH not_null_manager AS (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
)

SELECT
  nnm1.first_name,
  nnm1.last_name,
  nnm2.first_name,
  nnm2.last_name
FROM not_null_manager AS nnm1
JOIN not_null_manager AS nnm2
  ON nnm1.manager_id = nnm2.id;

Sehen wir uns nun an, wie eine Unterabfrage das gleiche Ergebnis erzielen würde:

SELECT
  nnm1.first_name,
  nnm1.last_name,
  nnm2.first_name,
  nnm2.last_name
FROM (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
) AS nnm1
JOIN (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
) AS nnm2
  ON nnm1.manager_id = nnm2.id;

Wie Sie sehen können, enthält die CTE-Abfrage weniger Code. Sie ist auch besser lesbar: Sie wiederholen einfach den CTE-Namen (not_null_manager) anstelle eines ganzen Stücks Code.

In Bezug auf die Leistungsfähigkeit der beiden Abfragen gibt es keinen großen Unterschied. Obwohl Sie die CTE nur einmal deklarieren, ist die Ausführungszeit fast die gleiche.

Unterschied Nr. 3: CTEs können besser lesbar sein

Sie wissen also, dass Sie mit CTEs weniger Code schreiben können. Aber wie sieht es mit der Codeorganisation aus? Hier ist ein weiteres Beispiel, das sich auf die FROM Klausel konzentriert.

Erinnern Sie sich noch an die ersten Beispiele? Diejenigen, bei denen die größte durchschnittliche Sprunglänge zurückgegeben wird? Falls nicht, hier ein kurzer Rückblick.

In diesem Beispiel wird eine Unterabfrage verwendet:

SELECT
  MAX(avg_length) AS max_length
FROM (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
) AS average_lengths;

Und dieses Beispiel verwendet eine CTE:

WITH average_lengths AS (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
)

SELECT
  MAX(avg_length) AS max_length
FROM average_lengths;

Welche ist Ihrer Meinung nach besser lesbar? Ich würde sagen, die zweite (CTE) ist ein bisschen besser. Der Code ist weniger verschachtelt und es gibt eine kleine Abstraktionsebene. Sie wissen auf den ersten Blick, dass diese Abfrage die maximale durchschnittliche Länge auswählt.

Die Lesbarkeit von CTEs wird in den Beispielen aus dem vorherigen Abschnitt noch deutlicher. Und wenn Ihre Abfragen immer umfangreicher werden, wird die Verwendung von CTEs die Lesbarkeit Ihres Codes erheblich verbessern. Und Lesbarkeit ist der Schlüssel für die effiziente Entwicklung von Code.

Wenn Sie mehr über die Verwendung von CTEs zur Verbesserung der Lesbarkeit Ihres Codes erfahren möchten, sollten Sie sich unbedingt den Artikel Wie man SQL-Abfragen mit CTEs organisiert ansehen.

Aber Unterabfragen sind manchmal unersetzlich

Bis jetzt haben Sie einige Unterschiede zwischen Unterabfragen und CTEs kennengelernt. Um ehrlich zu sein, habe ich versucht, Sie davon zu überzeugen, dass CTEs viel besser sind als Subqueries. Aber in diesem Abschnitt erfahren Sie, warum Unterabfragen von unschätzbarem Wert sein können.

Filtern mit einer Unterabfrage

Das erste Beispiel in diesem Artikel verwendete eine Unterabfrage in der WHERE-Klausel. Ein ähnliches Beispiel habe ich im Abschnitt CTE nicht gezeigt. Das liegt daran, dass nur Unterabfragen in der WHERE-Klausel verwendet werden können!

Außerdem gibt es eine ganze Reihe von Schlüsselwörtern, die Sie in der WHERE-Bedingung verwenden können - z. B. ALL, ANY, EXISTS, und einige mehr! Leider kann ich sie hier nicht erklären; das würde zu viel Zeit in Anspruch nehmen. Stattdessen empfehle ich Ihnen, den Abschnitt Unterabfragen in unserem SQL für Anfänger Kurses. Sie werden nicht nur etwas über diese Schlüsselwörter lernen, sondern auch ein paar Probleme damit lösen! Oder schauen Sie sich den Artikel SQL-Unterabfragen in unserem Blog an, wenn Sie eine kurze Erklärung wünschen.

Unterabfragen können wie Spalten wirken

Sie können auch Unterabfragen wie eine Spalte verwenden. Die einzige Einschränkung ist, dass die Unterabfrage nur einen Wert zurückgeben darf. Schauen Sie sich das an:

SELECT DISTINCT
  contest_id,
  (
    SELECT
      COUNT(length)
    FROM jump AS inside_jump
    WHERE inside_jump.contest_id = outside_jump.contest_id
      AND inside_jump.length > 600
  ) AS longer_jumps,
  (
    SELECT
      COUNT(length)
    FROM jump AS inside_jump
    WHERE inside_jump.contest_id = outside_jump.contest_id
      AND inside_jump.length <= 600
  ) AS shorter_jumps
FROM jump AS outside_jump;

Für jeden Wettbewerb gibt diese Abfrage die Anzahl der Sprünge zurück, die länger als 600 cm sind (berechnet in der ersten Unterabfrage) und die Anzahl der Sprünge, die kürzer oder gleich 600 cm sind (berechnet in der zweiten Unterabfrage). Das Ergebnis sieht dann so aus:

Korrelierte Unterabfragen

Sehen Sie sich das obige Beispiel noch einmal an. Ist Ihnen aufgefallen, dass ich in der äußeren Abfrage innerhalb der Unterabfrage einen Verweis auf eine Tabelle verwendet habe? Ich habe sogar auf den Wert der aktuellen Zeile aus dieser Tabelle verwiesen. Dies nennt man eine "korrelierte Unterabfrage". Damit können Sie Werte aus der äußeren Abfrage in der Unterabfrage verwenden.

Dies ist eine sehr praktische Technik, aber auch ziemlich kompliziert; wir werden sie in diesem Artikel nicht erklären. Sie können sich jedoch gerne Korrelierte Unterabfrage in SQL: Ein Leitfaden für Einsteiger in unserem Blog für eine Erklärung.

Ein weiterer Unterschied: CTEs müssen benannt werden

Der letzte Unterschied zwischen CTEs und Unterabfragen liegt in der Namensgebung. CTEs müssen immer einen Namen haben. Andererseits benötigen Unterabfragen in den meisten Datenbank-Engines keinen Namen (die einzige Ausnahme ist die FROM -Klausel in meiner Lieblings-Datenbank-Engine, PostgreSQL).

Es ist eine gute Praxis, Unterabfragen, die in den Klauseln FROM oder SELECT stehen, zu benennen, aber es ist keine Voraussetzung. Und, um genau zu sein, können Sie die Unterabfragen, die Sie in der WHERE Klausel verwenden, nicht benennen.

Man könnte meinen, dass die Namensgebung keinen großen Unterschied macht und keine großen Auswirkungen auf Sie hat. Es kann jedoch sein, dass Sie schnell etwas in der Datenbank überprüfen müssen. In diesem Fall ist die einfachere Syntax vielleicht die bessere Wahl. Auch wenn die Abfrage weniger gut lesbar ist, sollten Sie daran denken, dass solche Abfragen nach ihrer Verwendung selten gelesen werden.

Unterabfragen vs. CTEs - was ist besser?

Sie haben nun viel über die Unterschiede zwischen CTEs und Unterabfragen gelernt. Was ist also besser? Die Antwort lautet: weder noch, oder es kommt darauf an - sowohl Unterabfragen als auch CTEs haben Vor- und Nachteile. Jede Abfrage sollte analysiert werden, und die Wahl zwischen diesen beiden sollte von Fall zu Fall getroffen werden. Dazu müssen Sie jedoch beide Konzepte gründlich kennenlernen.

Um mehr über Subqueries zu erfahren, empfehle ich den Teil Subqueries des LearnSQL SQL für Anfänger Kurses empfehlen. Wenn Sie mehr über CTEs erfahren möchten, ist der Rekursive Abfragen Kurs die beste Wahl. Diese Kurse werden Ihnen helfen, diese Konzepte schnell zu erlernen. So werden Sie in der Lage sein zu entscheiden, welche Abfragen von CTEs profitieren und welche Unterabfragen erfordern.

Wenn Sie jedoch bereits einigermaßen mit korrelierten Unterabfragen vertraut sind und nicht noch einmal dasselbe lernen möchten, können Sie Ihre Kenntnisse auch in unserem SQL-Übungssatz Kurs.