Konvertierung von Unterabfragen in Joins

Nicht alle Abfragen sind gleich, insbesondere in Bezug auf die Leistung. In diesem Artikel sehen wir uns an, wie Sie SQL-Subqueries in Joins umwandeln können, um die Effizienz zu verbessern.

Wann sollte ich SQL-Subqueries verwenden?

Gute Frage! Leider gibt es darauf keine konkrete Antwort. SQL-Anfänger neigen dazu, Unterabfragen zu oft zu verwenden. Sobald sie herausgefunden haben, dass die SQL-Konstruktion in einer bestimmten Situation funktioniert, versuchen sie in der Regel, diesen Ansatz auch auf andere Situationen anzuwenden. Das ist ganz natürlich.

In einigen Fällen kann eine Subquery jedoch durch ein effizienteres JOIN ersetzt werden. Wenn Sie eine Subquery vermeiden und durch eine JOIN-Klausel ersetzen können, sollten Sie dies ohne zu zögern tun. Aber natürlich gibt es Fälle, in denen die Verwendung einer Subquery die einzige Möglichkeit ist, eine Datenfrage zu lösen. In diesem Artikel zeige ich Beispiele für beide Fälle: wann eine Subquery ein Muss ist und wann eine Subquery vermieden und durch einen JOIN ersetzt werden sollte.

Die Daten

Bevor wir zu den Beispielen kommen, wollen wir uns kurz die Beispieldatenbank ansehen, die wir verwenden werden. Die Datenbank enthält zwei Tabellen, die die Produktionsstatistiken einer fiktiven Apfelfirma namens EverRed darstellen. Das Unternehmen hat drei Farmen.

Die erste Tabelle ist current_year_productiondie Informationen über die Anzahl der im laufenden Jahr von den einzelnen Betrieben produzierten Äpfel sowie über die Fläche und die Anzahl der Bäume in jedem Betrieb enthält. Die zweite Tabelle, production_historyspeichert Informationen über die vergangene Produktion der einzelnen Betriebe. Nachstehend finden Sie einige Beispieldaten aus diesen beiden Tabellen.

Beispiel: Ersetzen einer Subquery durch einen JOIN

Angenommen, Sie sind ein SQL-Datenanalyst, der bei EverRed arbeitet. Der Eigentümer des Unternehmens möchte, dass Sie die Namen der Farmen ermitteln, in denen das Unternehmen im aktuellen Jahr mehr Äpfel produziert als im Vorjahr (2017).

Lösung mit einer Subquery:

SELECT farm_name, 
FROM current_year_production CYP
WHERE production_in_kg > (
  SELECT production_in_kg 
  FROM production_history PH  
  WHERE PH.farm_id = CYP.farm_id
  AND year = 2017
) 

Lösung unter Verwendung einer JOIN-Klausel:

SELECT farm_name, 
FROM current_year_production CYP 
JOIN production_history PH
  ON PH.farm_id = CYP.farm_id 
WHERE PH.year = 2017
  AND CYP.production_in_kg > PH.production_in_kg 

Der Unterschied zwischen diesen beiden Ansätzen liegt in der Leistung. Während die JOIN-Klausel im zweiten Beispiel nur einmal ausgeführt werden muss, wird die Unterabfrage im ersten Beispiel einmal pro Betrieb ausgeführt. In diesem Fall haben wir nur drei Farmen, so dass der Unterschied vernachlässigbar ist. Aber was wäre, wenn Sie für ein größeres Unternehmen mit 10.000 globalen Farmen arbeiten würden? Dann müsste die Subquery 10.000 Mal ausgeführt werden. Es ist klar, dass eine Subquery für unsere Zwecke ineffizient ist.

In einer Testdatenbank mit nur wenigen Farmen werden beide Abfragen mit einer akzeptablen Antwortzeit ausgeführt. Wenn wir jedoch zu einer produktiven Datenbank übergehen (wo das Datenvolumen in der Regel viel höher ist), wird die Antwortzeit des Subquery-Ansatzes deutlich ansteigen, während die Antwortzeit des JOIN-Ansatzes stabil bleibt.

Das Ergebnis der beiden vorherigen gleichwertigen Abfragen ist:

Beispiel: Wenn Unterabfragen die einzige Möglichkeit sind

Nehmen wir nun an, der Eigentümer des Unternehmens bittet Sie, nachdem er die Ergebnisse der vorherigen Abfrage gelesen hat, um die Namen der Betriebe, die in diesem Jahr mehr Äpfel pro Quadratmeter produzieren als der historische Durchschnitt. Das klingt kompliziert, ist aber einfacher als es scheint.

Lösung mit einer Unterabfrage:

SELECT
  farm_name, 
  production_in_kg / area AS "production_per_meter"
FROM Current_year_production 
WHERE production_in_kg / area > (
  SELECT AVG(PH.production_in_kg / CYP.area) 
  FROM production_history PH 
  JOIN Current_year_production CYP  
    ON PH.farm_id = CYP.farm_id 
) 

Wir können diese Unterabfrage nicht durch einen JOIN ersetzen, da wir keine Tabelle mit dem zuvor berechneten Durchschnitt haben. Mit anderen Worten, wir müssen zuerst den historischen Durchschnitt berechnen. Und dazu brauchen wir eine GROUP BY, die die für einen JOIN erforderliche Eins-zu-Eins-Beziehung aufheben kann. Ein weiterer wichtiger Punkt ist, dass die Metrik "Äpfel pro Quadratmeter" mit dem folgenden Ausdruck ermittelt wird:

	production_in_kg / area

Wir haben die Metrik "Äpfel pro Quadratmeter" verwendet, weil wir eine Möglichkeit brauchen, um die Produktivität der verschiedenen Betriebe zu vergleichen und sie in eine Rangfolge zu bringen. Die gesamte "Produktion_in_kg" eines Betriebs ist kein vergleichbarer Wert, da es wahrscheinlich ist, dass der größte Betrieb beispielsweise eine bessere production_in_kg hat. Daher teilen wir die "Produktion_in_kg" durch die Fläche jedes Betriebs, um die Werte zu standardisieren und eine vergleichbare Kennzahl zu schaffen.

Wir stellen fest, dass der historische Durchschnitt der Produktion pro Quadratmeter 0,42 beträgt. Das Ergebnis der vorangegangenen Abfrage lautet also:

Beispiel: Wenn JOINs und Unterabfragen gleich effizient sind

Als letzte Datenfrage wollen wir versuchen, die Jahre zu ermitteln, in denen das Unternehmen weniger Äpfel produziert hat als im aktuellen Jahr. Wir können diese Abfrage mit zwei verschiedenen Ansätzen schreiben.

Lösung mit einer Unterabfrage:

SELECT year, sum(production_in_kg) 
FROM production_history PH
GROUP BY year
HAVING sum(production_in_kg) < (
  SELECT sum(production_in_kg)
  FROM current_year_production
)

Lösung unter Verwendung einer JOIN-Klausel:

SELECT year, sum(PH.production_in_kg) 
FROM production_history PH 
JOIN current_year_production CYP
  ON PH.farm_id = CYP.farm_id
GROUP BY year
HAVING sum(PH.production_in_kg) < sum(CYP.production_in_kg)

Sie sehen, dass diese beiden Abfragen sehr ähnlich sind; der Hauptunterschied liegt in der JOIN-Klausel und der Unterabfrage. In diesem Fall sind beide Abfragen gleich effizient - die Unterabfrage wird nur einmal in der HAVING-Klausel ausgeführt, so dass es keine Leistungsprobleme gibt.

Probieren Sie es selbst aus!

Abschließend ist es wichtig, darauf hinzuweisen, dass Unterabfragen und JOINs für einen SQL-Entwickler sehr wichtige Ressourcen sind. Wir haben Beispiele gesehen, in denen wir eine Subquery durch einen JOIN ersetzen können, und Beispiele, in denen dies nicht möglich ist. Und manchmal sind Unterabfragen und JOINs gleichermaßen effizient.

Aber woher wissen Sie, wann Sie eine Unterabfrage und wann einen JOIN verwenden sollten? Um ehrlich zu sein, können Sie Ihre Intuition nur entwickeln, indem Sie regelmäßig SQL-Aufgaben lösen. Wenn Sie Ihre SQL-Kenntnisse auffrischen möchten, bietet unser SQL-Übungssatz bietet 88 Übungsaufgaben für eine umfassende Überprüfung.

Bleiben Sie dran für weitere Artikel!