CTE vs. Subquery in SQL: Was ist der Unterschied?

Was sind Common Table Expressions (CTEs)? Sind sie dasselbe wie Unterabfragen? Wann sollten Sie CTEs verwenden? Dieser Artikel befasst sich mit den Gemeinsamkeiten und Unterschieden zwischen CTE und Unterabfrage.

Wenn ich einem Schüler Common Table Expressions vorstelle, ist seine erste Reaktion: "Das ist doch nur eine Unterabfrage! Warum muss ich das lernen?". Beantworten wir diese Frage, indem wir uns ansehen, was man mit einer SQL-Subquery tun kann und welche zusätzlichen Vorteile die Verwendung einer CTE bietet.

Wenn Sie sich nach dem Lesen des Artikels dafür entscheiden, die Verwendung von SQL CTEs zu erlernen, bietet LearnSQL.de einen umfassenden Kurs über Common Table Expressions an, der das Thema eingehend behandelt. Sie beginnen mit einfachen Beispielen und arbeiten sich zu schwierigen Konzepten wie der Verarbeitung von Bäumen und Graphen durch. Zu jedem Thema gibt es schrittweise Erklärungen und Beispiele. Mit 114 interaktiven Übungen können Sie viel üben. Sie werden wahrscheinlich etwa 18 Stunden benötigen, um den Kurs abzuschließen.

Was ist eine Unterabfrage?

Eine Subquery ist eine Abfrage, die innerhalb der Hauptabfrage verschachtelt ist; dies lässt sich am besten anhand eines Beispiels erklären. Alle Beispiele in diesem Artikel verwenden SQL Server; die Syntax kann in anderen SQL-Dialekten leicht abweichen.

Angenommen, die Geschäftsleitung möchte allen Kunden, deren Jahreseinkäufe über dem Durchschnitt aller Kunden lagen, einen Rabatt gewähren. Die Abfrage zum Extrahieren einer Liste dieser Kunden könnte wie folgt aussehen:

SELECT 
  account_no, 
  name 
FROM customers 
WHERE annual_purchases >
  (SELECT AVG(annual_purchases) FROM customers);

Die Hauptabfrage listet die Kunden auf, und die Unterabfrage berechnet die durchschnittlichen jährlichen Einkäufe für alle Kunden. Die Unterabfrage braucht keinen Namen (außer wenn Sie in PostgreSQL arbeiten).

Wenn Sie mit Subqueries nicht vertraut sind, können Sie einen Blick auf den Kurs LearnSQL.de' SQL für Anfänger werfen, in dem es einen Abschnitt über Subqueries gibt. Mehr über Subqueries erfahren Sie in unserem Anleitung für Anfänger zur SQL-Subquery. Wie man SQL-Unterabfragen übt gibt Ihnen einige Ideen, wie Sie diese Konzepte in die Praxis umsetzen können.

Und was sind CTEs?

Common Table Expressions sind benannte Ergebnismengen, die am Anfang einer Abfrage definiert werden und auf die die Abfrage zugreifen kann, als wären es Tabellen. Angenommen, wir möchten die Gehälter der Mitarbeiter mit dem Durchschnittsgehalt für ihre Rolle vergleichen. Die Abfrage könnte wie folgt aussehen:

WITH avg_salary AS (
  SELECT 
    role, 
    avg(salary) AS average 
  FROM employee 
  GROUP BY role
)
SELECT 
  employee.role, 
  name, 
  salary, 
  avg_salary
FROM employee 
JOIN avg_salary ON avg_salary.role = employee.role
ORDER BY role, name

CTEs werden durch das Schlüsselwort WITH eingeleitet, und die Ergebnisse werden in einer benannten temporären Tabelle gespeichert. In diesem Beispiel werden die Ergebnisse der CTE in der Tabelle avg_salary gespeichert, und diese wird von der Hauptabfrage verwendet, um das Durchschnittsgehalt für jede Rolle zu ermitteln.

Tatsächlich können einfache CTEs wie diese auch als Unterabfragen geschrieben werden. Im Artikel "Was ist eine CTE?" werden CTEs ausführlicher erklärt.

Was sind die Unterschiede zwischen CTEs und Unterabfragen?

Ich werde zunächst kurz die wichtigsten Unterschiede aufzählen und dann auf einige von ihnen näher eingehen.

• CTEs werden am Anfang der Abfrage definiert, während Unterabfragen inline definiert werden.
• CTEs müssen immer benannt werden. Nur PostgreSQL besteht darauf, dass Unterabfragen einen Namen haben müssen.
• CTEs können rekursiv verwendet werden. Ich erkläre dies später in diesem Artikel.
• CTEs sind viel lesbarer als Unterabfragen, wenn Sie einen komplexen Bericht schreiben.
• Eine CTE kann innerhalb einer Abfrage viele Male verwendet werden, während eine Unterabfrage nur einmal verwendet werden kann. Dadurch kann die Abfragedefinition viel kürzer werden, was aber nicht unbedingt zu einer verbesserten Leistung führt.
• Unterabfragen können in einer WHERE -Klausel in Verbindung mit den Schlüsselwörtern IN oder EXISTS verwendet werden, was bei CTEs nicht möglich ist.
• Unterabfragen können verwendet werden, um ein einzelnes Datenelement aus einer Tabelle zu holen, um einen Wert in einer anderen Tabelle zu aktualisieren.

Einige Funktionen sind nur auf Unterabfragen beschränkt. Ich werde zwei Beispiele nennen, bei denen Unterabfragen nicht durch CTEs ersetzt werden können. Im ersten Beispiel ist die Unterabfrage Teil der WHERE-Klausel. Im zweiten Beispiel wird sie verwendet, um einen einzelnen Wert aus einer Tabelle zu holen, um eine andere zu aktualisieren. Wie bereits erwähnt, erlaubt SQL die Verwendung von CTEs für diese Aufgaben nicht.

Nur Unterabfragen: Verwendung von Unterabfragen in WHERE

Im ersten Beispiel speichert eine Bank die Details aller Transaktionen des Tages in einer Tabelle namens daily_trans. Die Daten in dieser Tabelle enthalten eine Kontonummer, einen Transaktionscode und einen Betrag.

Die Datenbank hat auch eine Tabelle namens transaction_typesDie Spalten dieser Tabelle enthalten den Transaktionscode und ein Kennzeichen namens debit_credit, das für Transaktionsarten, die dem Konto des Kunden gutgeschrieben werden, auf 1 und für solche, die das Konto belasten, auf 2 gesetzt ist.

Wenn die Bank eine Liste aller Gutschriftstransaktionen des Tages wünscht, könnte die Abfrage wie folgt aussehen:

SELECT 
  account_no, 
  tran_code, 
  amount
FROM daily_trans
WHERE tran_code IN
  (SELECT tran_code 
   FROM transaction_types
   WHERE debit_credit = 1);

Diese Art von Abfrage kann nur mit einer Subquery geschrieben werden. Sie können die Unterabfrage hier nicht durch eine CTE ersetzen.

Nur Unterabfragen: Verwendung von Unterabfragen in UPDATE

Im zweiten Beispiel hat die gleiche Bank eine Tabelle namens customer, deren Spalten eine Kontonummer, einen Kundennamen und die Mitarbeiternummer des zuständigen Kundenbetreuers enthalten.

Die Bank hat eine Umverteilung der Zuständigkeiten vorgenommen und möchte den Kundenbetreuer für einige Kunden neu zuweisen. Zu diesem Zweck hat sie eine Tabelle namens reassignments erstellt, die die ID-Nummer des alten Support-Mitarbeiters und die Mitarbeiternummer des Support-Mitarbeiters enthält, der dessen Aufgaben übernehmen wird.

Um die Neuzuweisungen vorzunehmen, könnten wir eine Abfrage wie die folgende schreiben:

UPDATE customer 
SET support_person =
  (SELECT new_employee 
   FROM reassignments
   WHERE old_employee = customer.support_person);

CTEs machen eine komplexe Abfrage besser lesbar

Um zu veranschaulichen, wie CTEs das Verständnis erleichtern können, nehmen wir eine Abfrage, die mehrere Unterabfragen verwendet, und kodieren sie mit CTEs um.

Angenommen, wir haben ein Geschäft, das drei Arten von Produkten verkauft: Bücher, Musik und Videos. Der Geschäftsführer möchte wissen, wie viel jeder Kunde in jeder Kategorie gekauft hat.

Der Bericht könnte etwa so aussehen:

Hier ist eine Abfrage, die Unterabfragen verwendet, um den Bericht zu erstellen:

SELECT 
	customer, 
	sum(purchases) AS Total, 
	total_books AS Books, 
	total_music AS Music, 
	total_videos AS Videos
FROM sales
JOIN 
	(SELECT account_no, sum(purchases) AS total_books FROM sales 
	WHERE product_type = 'Books'
	GROUP BY account_no) books
ON books.account_no = sales.account_no
JOIN 
	(SELECT account_no, sum(purchases) AS total_music FROM sales 
	WHERE product_type = 'Music'
	GROUP BY account_no) music
ON music.account_no = sales.account_no
JOIN 
	(SELECT account_no, sum(purchases) AS total_videos FROM sales 
	WHERE product_type = 'Videos'
	GROUP BY account_no) videos
ON videos.account_no = sales.account_no
GROUP BY customer
ORDER BY customer

Wie Sie sehen können, ist das ziemlich kompliziert. Und es ist schwer nachzuvollziehen, wenn jemand anderes später Änderungen daran vornehmen muss.

Schauen wir uns nun an, wie diese Abfrage aussehen könnte, wenn wir sie mit Common Table Expressions umschreiben:

WITH books AS (
  SELECT 
    customer, 
    sum(purchases) AS total_books 
  FROM sales 
  WHERE product_type = 'Books'
  GROUP BY customer
),

music AS (
  SELECT 
    customer, 
    sum(purchases) AS total_music 
  FROM sales 
  WHERE product_type = 'Music'
  GROUP BY customer
),

videos as (
  SELECT 
    customer, 
    sum(purchases) AS total_videos 
  FROM sales 
  WHERE product_type = 'Videos'
  GROUP BY customer
)

SELECT 
  customer, 
  sum(purchases) AS Total, 
  total_books AS Books, 
  total_music AS Music, 
  total_videos AS Videos
FROM sales
JOIN books ON books.customer = sales.customer
JOIN music ON music.customer = sales.customer
JOIN videos ON videos.customer = sales.customer
GROUP BY customer
ORDER BY customer

Die meisten Leute werden diese zweite Version viel einfacher zu verstehen finden, obwohl beide Abfragen genau die gleichen Ergebnisse liefern.

Was sind rekursive CTEs?

Ich sagte bereits, dass nur CTEs rekursiv sein können. Was ist also eine rekursive Abfrage? Rekursive Abfragen ermöglichen es Ihnen, in hierarchischen Daten zu navigieren und Berichte zu erstellen, die für baum- und grafikartige Daten geeignet sind. Beispiele für hierarchische Daten sind:

• In einem Unternehmen kann ein Angestellter an einen Sub-Manager berichten; der Sub-Manager berichtet an einen Manager, und der Manager berichtet an den Vorgesetzten.
• In der Fertigung kann ein Produkt aus mehreren Komponenten bestehen. Jedes Bauteil kann auch aus vielen Unterbauteilen bestehen, und die Unterbauteile können aus verschiedenen Rohstoffen hergestellt werden.

Schauen wir uns ein Beispiel an. Eine Eisdiele hat mehrere Produkte auf der Speisekarte. Jeder Menüpunkt kann aus mehreren Zutaten bestehen: Ein Bananensplit besteht aus Bananen, Schokoladensoße und Eiscreme. Aber auch die Schokoladensoße besteht aus mehreren Zutaten. Dazu gehören Kakaopulver, Zucker und einige andere Dinge.

Der Inhaber möchte eine vollständige Liste der einzelnen Menüpunkte, gefolgt von allen Zutaten. Ein Teil der Liste könnte wie folgt aussehen:

In der Datenbank haben wir zwei Tabellen:

• Die Tabelle Item Tabelle enthält eine Liste der einzelnen Menüpunkte und der einzelnen Zutaten.
• Die Tabelle Bill_of_materials Tabelle enthält Verknüpfungen zwischen jedem Artikel und seinen Zutaten.

Die Tabelle Items Tabelle enthält diese Informationen:

Hier sind einige Beispiele für Einträge in der Bill_of_materials Tabelle. Die Spalte item_id enthält eine Verknüpfung zum übergeordneten Element in der Items Tabelle, während component_id einen Link zu einer seiner Zutaten enthält. Der erste Eintrag zeigt also, dass Artikel 10: Kakao eine Zutat von Artikel 9: Schokoladensoße ist.

Hier ist die rekursive Abfrage, die verwendet wird, um diese Informationen zu durchsuchen. Die Abfrage wurde in SQL Server geschrieben; in anderen Dialekten würde sie etwas anders aussehen.

WITH menu_ingredients (id, path, description, item_id)
AS (
  SELECT 
CAST (id AS bigint), 
CAST (description as varchar (255)), 
CAST ('' AS varchar(40)),
CAST (id AS bigint)
   FROM items 
   WHERE description = 'Menu'
   
   UNION ALL
   
   SELECT
CAST (bom.component_id AS bigint), 
CAST (m.path + ' > ' + i.description AS varchar(255)),
i.description,
CAST (bom.item_id AS bigint)
   FROM menu_ingredients m, bill_of_materials bom
   JOIN items i
   ON i.id = bom.component_id
   WHERE bom.item_id = m.id
)

SELECT 
  path, 
  description 
FROM menu_ingredients
ORDER BY path

Eine vollständige Erklärung der Vorgänge würde den Rahmen dieses Artikels sprengen, aber ich werde kurz die Grundlagen erläutern. Kurz gesagt, wenn eine Zeile zur Ergebnismenge einer rekursiven Abfrage hinzugefügt wird, kann sie die vorherige Zeile "sehen" und verwenden, um ein Stück Information zu finden, das zum Auffinden der nächsten Zeile verwendet werden kann. Diese Abfrage beginnt mit dem obersten Eintrag in der Stückliste: dem Menü selbst. Von dort aus kann sie durch alle untergeordneten Zeilen gehen - die Zutaten, aus denen es besteht. Und jede Zutat kann ihre eigenen untergeordneten Zeilen abrufen, wenn sie welche hat. Eine genauere Erklärung der Rekursion finden Sie unter Was ist eine rekursive CTE in SQL? Und wie ich bereits erwähnt habe, werden rekursive Abfragen in LearnSQL.de im Kurs "Common Table Expressions" ausführlich behandelt .

CTE vs. Subquery

Zusammenfassend lässt sich sagen, dass Sie eine CTE wählen sollten, wenn:

• Sie eine komplexe Abfrage besser lesbar machen wollen.
• Sie eine rekursive Abfrage verwenden müssen.

Wählen Sie eine Subquery, wenn:

• Sie die Schlüsselwörter der WHERE -Klausel IN oder EXISTS verwenden, um die Auswahlkriterien aus einer anderen Tabelle zu übernehmen.
• Sie ein einzelnes Datenelement aus einer anderen Tabelle als neuen Wert für ein Feld in einer UPDATE -Anweisung auswählen möchten.

In allen anderen Fällen haben Sie die Wahl: Sie können das verwenden, womit Sie sich am wohlsten fühlen.

Und wenn Sie etwas Übung mit CTEs oder Unterabfragen brauchen, können Sie die Website SQL-Übungssatz die Hunderte von interaktiven Übungen enthält, die Ihnen helfen, Ihre Kenntnisse zu festigen.

Viel Spaß beim Lernen!