5 Gründe, warum Sie CTEs anstelle von Unterabfragen verwenden sollten

Common Table Expressions (CTEs) wurden in SQL:1999 eingeführt, um Fälle zu behandeln, in denen die Ausgabe einer Abfrage in einer anderen Abfrage verwendet wird. Aber hatten wir dafür nicht schon Unterabfragen? In diesem Artikel werde ich anhand mehrerer Beispiele zeigen, warum CTEs für die Struktur und Lesbarkeit Ihrer SQL-Abfragen besser sind als Unterabfragen.

Erinnern wir uns zunächst daran, was CTEs und Subqueries sind und wie sie sich unterscheiden.

Gemeinsame Tabellenausdrücke vs. Unterabfragen

Eine Subquery ist eine Abfrage, die in eine andere Abfrage eingebettet ist. Sie kann überall in einer Abfrage platziert werden, sogar innerhalb einer anderen Unterabfrage. Die Syntax ist sehr einfach - Sie setzen Ihre Unterabfrage einfach in Klammern und fügen sie in die Hauptabfrage ein, wo immer sie benötigt wird. Beachten Sie, dass Unterabfragen vor der Hauptabfrage (auch Elternabfrage oder äußere Abfrage genannt) ausgeführt werden, damit ihre Ergebnisse von der Hauptabfrage verwendet werden können. Es ist oft recht schwierig, eine SQL-Abfrage mit mehreren Unterabfragen zu lesen, denn obwohl sie zuerst ausgeführt werden, sind sie irgendwo in der Hauptabfrage definiert. Mehr über SQL-Unterabfragen erfahren Sie in diesem Einführungshandbuch.

Ein Common Table Expression (CTE), auch als WITH-Klausel bezeichnet, ist ein temporärer benannter Ergebnissatz, auf den Sie überall in Ihrer Abfrage verweisen können. Im Gegensatz zu Unterabfragen, die genau dort eingefügt werden, wo Sie sie benötigen, werden alle CTEs vor der Hauptabfrage definiert und dann in der Abfrage über den zugewiesenen Namen referenziert. In den folgenden Beispielen werden wir sehen, wie dies die Struktur und Lesbarkeit einer SQL-Abfrage erheblich verbessert. Sie definieren zunächst alle temporären Ergebnismengen, die Sie benötigen, und verwenden sie dann bei Bedarf in der Hauptabfrage.

Eine ausführliche Einführung in allgemeine Tabellenausdrücke finden Sie hier. Erfahren Sie mehr über den Unterschied zwischen SQL CTEs und Subqueries in diesem Übersichtsartikel.

5 Gründe für die Wahl von CTEs

SQL-Lernende fragen sich oft, ob sie beim Schreiben einer komplexen SQL-Abfrage einen allgemeinen Tabellenausdruck oder eine Unterabfrage verwenden sollten. Gehen wir einige Beispiele durch, um zu erklären, warum Sie eine CTE einer Subquery vorziehen sollten. Für diese Beispiele verwende ich die folgende Tabelle, in der die Leistung von Mitarbeitern aus verschiedenen Abteilungen zusammengefasst ist.

Für jeden Mitarbeiter haben wir die ID, den vollständigen Namen, die Abteilung, die Jahre der Erfahrung und eine KPI-Bewertung auf einer Skala von 0 bis 100.

Schauen wir uns an, wie CTEs im Vergleich zu Unterabfragen uns bei der Analyse dieser Daten helfen können.

5 Gründe für die Verwendung von CTEs anstelle von Unterabfragen

#1. CTEs verwenden aussagekräftige Namen

Sie können CTEs aussagekräftige Namen geben, die Ihre Absicht spezifizieren und die Abfrage besser lesbar machen. Nehmen wir zum Beispiel an, wir möchten die Leistung jedes Mitarbeiters mit dem durchschnittlichen KPI-Abschluss in den jeweiligen Abteilungen vergleichen. Um die entsprechende Spalte zu unserer Tabelle hinzuzufügen, müssen wir zunächst den durchschnittlichen KPI für jede Abteilung berechnen. Unsere gesamte Abfrage mit einer Unterabfrage lautet wie folgt:

SELECT p.name, p.department, p.kpi, avg.average_dep_kpi
FROM performance p
JOIN 
  (SELECT department, AVG(kpi) AS average_dep_kpi
   FROM performance
   GROUP BY department) AS avg
ON p.department = avg.department;

Sie können die gleiche Ausgabe mit einem gemeinsamen Tabellenausdruck erhalten:

WITH avg_department_kpi AS
  (SELECT department, AVG(kpi) AS average_dep_kpi
   FROM performance
   GROUP BY department)
SELECT p.name, p.department, p.kpi, avg.average_dep_kpi
FROM performance p
JOIN avg_department_kpi avg
ON p.department = avg.department;

CTEs und Unterabfragen liefern die gleiche Ausgabe, und die Abfragen sind mehr oder weniger gleich lang. Beachten Sie jedoch, dass die CTE-Abfrage besser lesbar ist. Was eine komplexe Abfrage bedeutet, ist Ihnen vielleicht klar, wenn Sie sich Ihren eigenen Code ansehen, aber Ihren Kollegen ist es vielleicht nicht ganz klar. Wenn Sie die CTE-Version lesen:

• Sie sehen die Abfrage in der Reihenfolge, in der sie ausgeführt wird: zuerst die Unterabfrage und dann die Hauptabfrage.
• Sie können den Zweck der Unterabfrage anhand ihres Namens bestimmen. In unserem Fall bezieht sich avg_department_kpi auf die CTE, die den durchschnittlichen KPI für jede Abteilung ausgibt.

Gleichzeitig haben Sie vielleicht bemerkt, dass die CTE-Abfrage etwas länger ist als die Version der Unterabfrage. Das ist nicht immer der Fall, vor allem dann nicht, wenn wir die gleiche Unterabfrage mehrmals in unserer Hauptabfrage benötigen.

Wenn Sie mit der WITH-Syntax noch nicht vertraut sind, können Sie in unserem interaktiven Kurs gängige Tabellenausdrücke üben. Kommen wir nun zum zweiten Grund, CTEs den Unterabfragen vorzuziehen.

#2. CTEs sind innerhalb einer Abfrage wiederverwendbar

Im Gegensatz zu Unterabfragen müssen Sie eine CTE-Definition nicht jedes Mal wiederholen, wenn Sie sie in einer Abfrage benötigen. Sie definieren sie nur einmal, und zwar am Anfang der Abfrage, und verweisen dann bei Bedarf auf sie.

Nehmen wir an, wir möchten die Leistung verschiedener Abteilungen vergleichen. Insbesondere möchten wir den durchschnittlichen KPI in jeder Abteilung sowie den minimalen und maximalen durchschnittlichen KPI in allen Abteilungen sehen. Wir könnten dieses Problem mit einer SQL-Abfrage mit drei Unterabfragen angehen:

1. Ermittlung der durchschnittlichen KPI für jede Abteilung.
2. Ermittlung des minimalen durchschnittlichen KPI für alle Abteilungen.
3. Ermittlung des maximalen durchschnittlichen KPI für alle Abteilungen.

Beachten Sie, dass die letzten beiden Unterabfragen beide das Ergebnis der ersten Abfrage benötigen. Wenn wir also Unterabfragen verwenden, wird unsere Lösung Unterabfragen enthalten, die innerhalb anderer Unterabfragen definiert sind. Die ganze Sache sieht ziemlich verwirrend aus:

SELECT 
  ad.department, 
  ad.average_kpi, 
  min.min_avg_kpi_department,
  max.max_avg_kpi_department
FROM
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department) AS ad
CROSS JOIN
    (SELECT MIN (average_kpi) AS min_avg_kpi_department
     FROM
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS ad) AS min
CROSS JOIN
    (SELECT MAX (average_kpi) AS max_avg_kpi_department
     FROM
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS ad) AS max;

Im Gegensatz dazu definieren wir bei der Verwendung von Common Table Expressions unsere drei CTEs am Anfang der Abfrage, referenzieren sie bei Bedarf in der Hauptabfrage und vermeiden mehrere verschachtelte Unterabfragen:

WITH avg_per_department AS (
    SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department),

    min_kpi_department AS (
    SELECT MIN (average_kpi) AS min_avg_kpi_department
    FROM avg_per_department),

    max_kpi_department AS (
    SELECT MAX (average_kpi) AS max_avg_kpi_department
    FROM avg_per_department)
SELECT 
  ad.department, 
  ad.average_kpi, 
  min.min_avg_kpi_department,
  max.max_avg_kpi_department
FROM avg_per_department ad
CROSS JOIN min_kpi_department min
CROSS JOIN max_kpi_department max;

In diesem Fall können Sie feststellen, dass WITH-Abfragen Ihren Code nicht nur lesbarer, sondern auch viel kürzer machen.

#3. CTEs unterteilen komplexe Berechnungen in Teile

CTEs bringen Klarheit in den Berechnungsprozess. Wenn Unterabfragen für Berechnungen verwendet werden, verwandelt sich die Abfrage oft in ein Wirrwarr von Unterabfragen. CTEs hingegen zeigen den Berechnungsprozess klarer.

Nehmen wir an, wir möchten wissen, wie viele junge und erfahrene Mitarbeiter es in jeder Abteilung gibt. Wir betrachten die Mitarbeiter mit zwei oder weniger Jahren Erfahrung als Nachwuchskräfte und die mit mehr als zwei Jahren Erfahrung als erfahrene Mitarbeiter.

Im Grunde genommen benötigen wir hier zwei Unterabfragen:

1. Zur Berechnung der Anzahl der Junior-Mitarbeiter in jeder Abteilung.
2. Um die Anzahl der erfahrenen Mitarbeiter in jeder Abteilung zu berechnen.

Hier sehen Sie, wie wir dies mit Unterabfragen angehen können:

SELECT 
  e.department, 
  e.experienced_employees, 
  j.junior_employees
FROM (SELECT department, COUNT(*) AS experienced_employees
   FROM performance
   WHERE years_experience > 2
   GROUP BY department) AS e
JOIN (SELECT department, COUNT(*) AS junior_employees
   FROM performance
   WHERE years_experience <= 2
   GROUP BY department) AS j
ON e.department = j.department;

Dieses Beispiel ist nicht sehr kompliziert, so dass Sie wahrscheinlich in der Lage sind, die obige Abfrage zu verstehen. Beachten Sie jedoch Folgendes:

• Sie müssen den Zweck der einzelnen Unterabfragen erraten.
• Es ist nicht sofort klar, welche Tabellen verbunden werden.
• Die SELECT-Klausel listet die Felder auf, die in der Ausgabe angezeigt werden sollen, aber diese Felder werden erst später in den Unterabfragen definiert.

Wenn die Berechnungen komplizierter werden, ist es wirklich eine Herausforderung, den Berechnungsprozess durch alle Unterabfragen zu verfolgen. Im Gegensatz dazu ist der Berechnungsprozess mit CTEs viel übersichtlicher:

WITH experienced AS
  (SELECT department, COUNT(*) AS experienced_employees
   FROM performance
   WHERE years_experience > 2
   GROUP BY department),

  junior AS
  (SELECT department, COUNT(*) AS junior_employees
   FROM performance
   WHERE years_experience <= 2
   GROUP BY department)

SELECT 
  e.department, 
  e.experienced_employees, 
  j.junior_employees
FROM experienced e
JOIN junior j
ON e.department = j.department;

Hier definieren wir zunächst alle temporären Ergebnismengen, die für die Berechnungen erforderlich sind. Dann schreiben wir einfach eine einfache Abfrage, um die benötigte Ausgabe zu erhalten. Alles ist übersichtlich und klar.

Glauben Sie mir, Sie werden die Struktur, die gewöhnliche Tabellenausdrücke bieten, sehr zu schätzen wissen, wenn Sie mit komplexeren Berechnungen arbeiten. Um das zu beweisen, möchte ich noch ein weiteres Beispiel mit verschachtelten Berechnungen anführen.

#4. Verschachtelte CTEs sind wirklich cool

In Anbetracht der Wiederverwendbarkeit von CTEs und ihrer Fähigkeit, den Berechnungsprozess zu verdeutlichen, ist es nicht überraschend, dass sich WITH-Klauseln perfekt für verschachtelte Berechnungen eignen.

Nehmen wir an, wir möchten berechnen, wie viele Mitarbeiter in jeder Abteilung einen KPI-Wert haben, der (1) über dem Durchschnitt oder (2) unter dem Durchschnitt in ihrer jeweiligen Abteilung liegt. Konkret möchten wir für jede Abteilung den durchschnittlichen KPI-Wert, die Anzahl der Mitarbeiter mit überdurchschnittlichem KPI-Wert und die Anzahl der Mitarbeiter mit unterdurchschnittlichem KPI-Wert anzeigen.

Hierfür benötigen wir im Wesentlichen drei Unterabfragen:

1. Um den durchschnittlichen KPI für jede Abteilung zu erhalten.
2. Die Anzahl der Mitarbeiter, die einen überdurchschnittlichen KPI-Wert haben.
3. Um die Anzahl der Mitarbeiter zu ermitteln, die einen unterdurchschnittlichen KPI-Wert haben.

Wenn Sie jedoch Unterabfragen verwenden, müssen Sie die erste Unterabfrage dreimal hinzufügen, davon zweimal, wenn sie in den beiden anderen Unterabfragen verschachtelt wird. Die resultierende Abfrage sieht ziemlich unübersichtlich aus:

SELECT 
  avg.department, 
  avg.average_kpi, 
  aa.employees_above_average, 
  ba.employees_below_average
FROM
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department) AS avg
JOIN
    (SELECT p.department, count(*) AS employees_above_average
     FROM performance p
     JOIN
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS avg
     ON p.department = avg.department
     WHERE kpi > average_kpi
     GROUP BY p.department) AS aa
ON avg.department = aa.department
JOIN
    (SELECT p.department, count(*) AS employees_below_average
     FROM performance p
     JOIN
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS avg
     ON p.department = avg.department
     WHERE kpi < average_kpi
     GROUP BY p.department) AS ba
ON avg.department = ba.department;

Die Abfrage hat funktioniert, und wir haben die gewünschte Ausgabe erhalten, aber beachten Sie, wie schwierig es ist, die mehrfach verschachtelten Unterabfragen zu verfolgen. In der realen Welt wird es oft noch komplizierter.

Sehen Sie sich im Gegensatz dazu an, wie übersichtlich dieselbe Abfrage aussieht, wenn Sie CTEs statt Unterabfragen verwenden:

WITH avg_kpi_department AS
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department),

    above_average AS
    (SELECT p.department, count(*) AS employees_above_average
     FROM performance p
     JOIN avg_kpi_department avg
     ON p.department = avg.department
     WHERE kpi > average_kpi
     GROUP BY p.department),

     below_average AS
     (SELECT p.department, count(*) AS employees_below_average
     FROM performance p
     JOIN avg_kpi_department avg
     ON p.department = avg.department
     WHERE kpi < average_kpi
     GROUP BY p.department)

SELECT 
  avg.department, 
  avg.average_kpi, 
  aa.employees_above_average, 
  ba.employees_below_average
FROM avg_kpi_department avg
JOIN above_average aa
ON avg.department = aa.department
JOIN below_average ba
ON avg.department = ba.department;

Sie können eindeutig nachvollziehen, wie die drei temporären Ergebnismengen definiert sind. Sie können ihren Zweck anhand der zugewiesenen Namen verstehen. Sie brauchen dieselbe Unterabfrage nicht mehrmals innerhalb derselben Hauptabfrage zu wiederholen. Wenn Sie zur Hauptabfrage kommen, ist die ganze Vorbereitungsarbeit erledigt und Sie können einfach die bereits definierten CTEs verbinden.

#5. CTEs ermöglichen Rekursion

Nicht zuletzt eignen sich CTEs hervorragend für die Verarbeitung von Diagrammen, Bäumen und anderen hierarchischen Strukturen. Das liegt daran, dass die WITH-Syntax Rekursionen verarbeiten kann. Eine rekursive Abfrage ist eine Abfrage, die sich auf sich selbst bezieht.

Wenn wir zum Beispiel eine typische Organisationsstruktur haben, in der jeder Mitarbeiter einen Vorgesetzten hat und Vorgesetzte mehrere Untergebene haben, können rekursive CTEs uns helfen, diese Daten effizient zu analysieren. Mit nur einer SQL-Abfrage und Daten auf individueller Ebene können wir das Gesamtgehalt für jede der Abteilungen und Unterabteilungen oder die Gesamtzahl der nicht genutzten Urlaubstage in jeder der Organisationseinheiten berechnen.

Die Syntax von rekursiven CTEs ist recht kompliziert. Für eine detaillierte Erklärung empfehle ich Ihnen diesen Artikel, in dem erklärt wird , wie rekursive Abfragen hierarchische Strukturen verarbeiten. Vielleicht möchten Sie sich auch diese Beispiele für die Anwendung rekursiver Abfragen auf Diagrammstrukturen ansehen.

Lassen Sie uns CTEs üben!

Nun, da Sie die Vorteile von CTEs in SQL-Abfragen erkannt haben, ist es an der Zeit, zu üben! Ich empfehle Ihnen, mit unserem Rekursive Abfragen interaktiven Kurs (114 Coding Challenges) zu beginnen, der alle Arten von gängigen Tabellenausdrücken behandelt, einschließlich einfacher CTEs, verschachtelter CTEs und rekursiver CTEs. Weitere Details finden Sie in diesem Übersichtsartikel.

Vielen Dank für die Lektüre und viel Spaß beim Lernen!