Unterabfrage vs. JOIN

Eine der Herausforderungen beim Schreiben von SQL-Abfragen ist die Entscheidung, ob eine Subquery oder ein JOIN verwendet werden soll. Es gibt viele Situationen, in denen ein JOIN die bessere Lösung ist, und es gibt andere, in denen eine Subquery besser ist. Lassen Sie uns dieses Thema im Detail betrachten.

Unterabfragen werden in komplexen SQL-Abfragen verwendet. Normalerweise gibt es eine äußere Hauptabfrage und eine oder mehrere Unterabfragen, die in die äußere Abfrage eingebettet sind.

Unterabfragen können einfach oder korreliert sein. Einfache Unterabfragen stützen sich nicht auf die Spalten in der äußeren Abfrage, während korrelierte Unterabfragen sich auf Daten aus der äußeren Abfrage beziehen.

Sie können mehr über Subqueries im Abschnitt Subqueries im interaktiven Kurs "SQL für Anfänger" erfahren oder das Schreiben von Subqueries im Abschnitt Subqueries des Kurses "SQL-Übungssatz" üben. Oder lesen Sie einfach den Artikel "SQL-Unterabfragen" von Maria Alcaraz.

Die JOIN Klausel enthält keine zusätzlichen Abfragen. Sie verbindet zwei oder mehr Tabellen und wählt Daten aus ihnen in einer einzigen Ergebnismenge aus. Sie wird am häufigsten verwendet, um Tabellen mit Primär- und Fremdschlüsseln zu verbinden. Sie können SQL-JOINs in unserem interaktiven SQL-JOINs Kurs üben. Er enthält über 90 Übungen, um verschiedene Arten von JOINs zu überprüfen und zu üben. Sie können auch mehr über JOINin dem Artikel "Wie man SQL JOINs übt" von Emil Drkušić lesen.

Unterabfragen und JOINkönnen beide in einer komplexen Abfrage verwendet werden, um Daten aus mehreren Tabellen auszuwählen, aber sie tun dies auf unterschiedliche Weise. Manchmal haben Sie die Wahl zwischen beiden, aber es gibt Fälle, in denen eine Subquery die einzige echte Option ist. Im Folgenden werden die verschiedenen Szenarien beschrieben.

Betrachten Sie zwei einfache Tabellen, product und sale, die wir in unseren Beispielen verwenden werden.

Hier ist die product Tabelle.

Diese Tabelle enthält die folgenden Spalten:

• id: den Bezeichner des Produkts.
• nameden Namen des Produkts
• costden Preis des Produkts
• yeardas Jahr, in dem das Produkt hergestellt wurde
• city: die Stadt, in der das Produkt hergestellt wurde.

Und die andere Tabelle, sale:

die die folgenden Spalten enthält:

• id: die Kennung des Verkaufs.
• product_id: die Kennung des verkauften Produkts.
• price: der Verkaufspreis.
• year: das Jahr, in dem das Produkt verkauft wurde
• city: die Stadt, in der das Produkt verkauft wurde.

Wir werden diese beiden Tabellen verwenden, um komplexe Abfragen mit Unterabfragen und JOINzu schreiben.

Wann sollten Unterabfragen mit JOINs umgeschrieben werden?

SQL-Anfänger verwenden oft Unterabfragen, wenn dieselben Ergebnisse auch mit JOINerzielt werden können. Während Unterabfragen für viele SQL-Benutzer leichter zu verstehen und zu verwenden sind, sind JOINs oft effizienter. JOINs sind auch leichter zu lesen, wenn die Abfragen komplexer werden. Daher werden wir uns zunächst darauf konzentrieren, wann Sie eine Subquery durch eine JOIN ersetzen können, um die Effizienz und Lesbarkeit zu verbessern.

Skalare Unterabfrage

Der erste solche Fall ist die skalare Subquery. Eine skalare Subquery gibt einen einzelnen Wert zurück (eine Spalte und eine Zeile), der von der äußeren Abfrage verwendet wird. Hier ein Beispiel.

Angenommen, wir benötigen die Namen und Kosten der Produkte, die für 2.000 $ verkauft wurden.

Schauen wir uns den Code mit einer Subquery an:

SELECT name, cost 
FROM product
WHERE id=(SELECT product_id 
  FROM sale 
    WHERE price=2000 
    AND product_id=product.id
  );

und das Ergebnis:

Die äußere Abfrage wählt die Namen (name) und die Kosten (cost) der Produkte aus. Da wir nicht alle Produkte haben wollen, verwenden wir eine WHERE Klausel, um die Zeilen nach den Produkt-IDs zu filtern, die von der Unterabfrage zurückgegeben werden.

Schauen wir uns nun die Unterabfrage an. Die Tabelle sale Tabelle enthält die Verkaufsdatensätze der Produkte. Die Unterabfrage filtert zunächst nur die Datensätze heraus, deren Verkaufspreis 2.000 $ beträgt (price=2000). Sie verwendet dann die Produkt-IDs (product_id) in den ausgewählten Verkäufen, um die Datensätze aus der product Tabelle (product_id=product.id). Dies ist eine korrelierte Subquery, da die zweite Bedingung in der Subquery auf eine Spalte in der äußeren Abfrage verweist. Nur zwei Produkte wurden für 2.000 $ verkauft: der Sessel und der Fernsehtisch.

Diese Abfrage ist nicht sehr effizient. Wie sollten wir sie ändern?

Wir können eine JOIN Struktur erstellen und erhalten das gleiche Ergebnis. Sehen Sie sich die Abfrage mit einer JOIN an:

SELECT p.name, p.cost 
FROM product p 
JOIN sale s ON p.id=s.product_id
WHERE s.price=2000;

In dieser Abfrage verbinden wir die beiden Tabellen product und sale mit einem JOIN Operator. In der Bedingung JOIN werden die Datensätze aus der Tabelle product Tabelle mit den Datensätzen aus der Tabelle sale Tabelle über die Produkt-IDs verknüpft. Am Ende werden die Zeilen durch eine WHERE -Klausel gefiltert, um den Datensatz auszuwählen, bei dem der Verkaufspreis des Produkts gleich $2.000 ist.

Unterabfrage innerhalb der IN-Klausel

Eine weitere Subquery, die leicht durch eine JOIN ersetzt werden kann, wird in einem IN -Operator verwendet. In diesem Fall gibt die Unterabfrage eine Liste von Werten an die äußere Abfrage zurück.

Nehmen wir an, wir möchten die Namen und die Kosten der in unserem Beispiel verkauften Produkte erhalten.

SELECT name, cost 
FROM product 
WHERE id IN (SELECT product_id FROM sale);

Die äußere Abfrage wählt die Namen und die Kosten der Produkte aus; sie filtert dann nach den Datensätzen, deren Produkt-IDs in der von der Unterabfrage zurückgegebenen Liste enthalten sind. Die Unterabfrage wählt die Produkt-IDs aus der Tabelle sale Tabelle (SELECT product_id FROM sale), so dass nur die verkauften Produkte von dieser Abfrage in der endgültigen Ergebnismenge zurückgegeben werden, wie hier:

Es gibt mehr Produkte in der Tabelle product Tabelle, aber nur vier von ihnen wurden verkauft.

Die nachstehende Abfrage gibt das gleiche Ergebnis zurück, indem sie eine JOIN verwendet:

SELECT DISTINCT p.name, p.cost 
FROM product p 
JOIN sale s ON s.product_id=p.id;

Es wird eine sehr einfache Abfrage. Sie verbindet die beiden Tabellen nach Produkt-ID und wählt die Namen und die Kosten dieser Produkte aus. Es handelt sich um eine INNER JOIN, d. h. wenn die ID eines Produkts nicht in der sale Tabelle hat, wird es nicht zurückgegeben.

Beachten Sie, dass wir auch das Schlüsselwort DISTINCT verwenden, um doppelte Datensätze zu entfernen. Dies ist oft notwendig, wenn Sie Unterabfragen mit einer IN oder einer NOT IN in JOINumwandeln.

Möchten Sie mehr über SQL-Unterabfragen mit dem IN-Operator erfahren? Sehen Sie sich eine Episode unserer We Learn SQL-Serie auf Youtube an. Vergessen Sie nicht, unseren Kanal zu abonnieren.

Unterabfrage mit der Klausel NOT IN

Dies ist genau wie die vorherige Situation, aber hier wird die Unterabfrage in einem NOT IN Operator verwendet. Wir wollen die Namen und die Kosten der Produkte auswählen, die nicht verkauft wurden.

Unten sehen Sie ein Beispiel mit einer Unterabfrage innerhalb des NOT IN Operators:

SELECT name, cost 
FROM product 
WHERE id NOT IN (SELECT product_id FROM sale);

Die Ergebnisse:

Die Unterabfrage gibt die Produkt-IDs aus der Tabelle sale Tabelle (die verkauften Produkte) und vergleicht sie mit den Produkt-IDs in der äußeren Abfrage. Wenn ein Datensatz in der äußeren Abfrage seine Produkt-ID nicht in der von der Unterabfrage zurückgegebenen Liste findet, wird der Datensatz zurückgegeben.

Wie schreibt man diese Unterabfrage mit JOIN um? Das geht so:

SELECT DISTINCT p.name, p.cost
FROM product p 
LEFT JOIN sale s ON s.product_id=p.id 
WHERE s.product_id IS NULL;

Diese Abfrage verbindet die beiden Tabellen product und sale durch die Produkt-IDs. Sie sollten auch das Schlüsselwort DISTINCT verwenden, wie wir es bei der Umwandlung der vorherigen Unterabfrage mit IN in JOIN getan haben.

Beachten Sie, dass wir beim Umschreiben der Unterabfrage in NOT IN eine LEFT JOIN und eine WHERE verwendet haben. Auf diese Weise beginnen Sie mit allen Produkten, einschließlich der nicht verkauften, und wählen dann nur die Datensätze aus, die NULL in der Spalte product_id sind. Das NULL gibt an, dass das Produkt nicht verkauft wurde.

Korrelierte Unterabfragen in EXISTS und in NOT EXISTS

Die Unterabfragen in einer EXISTS oder in einer NOT EXISTS lassen sich auch leicht mit JOINumschreiben.

Die folgende Abfrage verwendet eine Unterabfrage, um die Details über Produkte zu erhalten, die im Jahr 2020 nicht verkauft wurden.

SELECT name, cost, city
FROM product  
WHERE NOT EXISTS ( SELECT id  
  FROM sale WHERE year=2020 AND product_id=product.id );

Das Ergebnis ist:

Für jedes Produkt in der äußeren Abfrage wählt die Unterabfrage die Datensätze aus, deren Verkaufsjahr 2020 ist (year=2020). Wenn es für ein bestimmtes Produkt in der Unterabfrage keine Datensätze gibt, gibt die NOT EXISTS -Klausel true zurück.

Die Ergebnismenge enthält sowohl die Produkte mit einem anderen Verkaufsjahr als 2020 als auch die Produkte ohne Datensätze in der sale Tabelle. Sie können dieselbe Abfrage mit einer JOIN umschreiben:

SELECT p.name, p.cost, p.city FROM product p 
LEFT JOIN  sale s ON s.product_id=p.id 
WHERE s.year!=2020 OR s.year IS NULL;

Hier verbinden wir die product Tabelle mit der sale Tabelle durch einen LEFT JOIN Operator. Dadurch können wir die Produkte, die nie verkauft wurden, in die Ergebnismenge aufnehmen. Die WHERE Klausel filtert die Datensätze, indem sie die Produkte auswählt, die keine Datensätze in der sale Tabelle (s.year IS NULL) sowie die Produkte mit einem anderen Verkaufsjahr als 2020 (s.year!=2020).

Wenn Sie eine Unterabfrage nicht durch einen JOIN ersetzen können

JOINJOINs können effizient sein, aber es gibt Situationen, die eine Unterabfrage und nicht JOIN erfordern. Nachfolgend sind einige dieser Situationen aufgeführt.

Unterabfrage in FROM mit einer GROUP BY

Die erste dieser Situationen ist eine Unterabfrage in einer FROM Klausel, die eine GROUP BY verwendet, um Aggregatwerte zu berechnen.

Schauen wir uns das folgende Beispiel an:

SELECT city, sum_price  
 FROM  
(
  SELECT city, SUM(price) AS sum_price FROM sale 
  GROUP BY city 
) AS s
WHERE sum_price < 2100;

und das Ergebnis:

Hier wählt die Unterabfrage die Städte aus und berechnet die Summe der Verkaufspreise nach Stadt. Die Summe aller Verkaufspreise in jeder Stadt aus der sale Tabelle wird mit der Aggregatfunktion SUM() berechnet. Anhand der Ergebnisse der Unterabfrage wählt die äußere Abfrage nur die Städte aus, deren Gesamtverkaufspreis weniger als 2.100 $ beträgt (WHERE sum_price < 2100). Sie sollten sich aus früheren Lektionen erinnern, wie man Aliase für Unterabfragen verwendet und wie man einen Aggregatwert in einer äußeren Abfrage auswählt.

Unterabfrage, die einen Aggregatwert in einer WHERE-Klausel zurückgibt

Eine weitere Situation, in der Sie eine Subquery-Struktur nicht mit einer JOIN umschreiben können, ist ein Aggregatwert, der in einer WHERE -Klausel verglichen wird. Sehen Sie sich dieses Beispiel an:

SELECT name FROM product
WHERE cost<(SELECT AVG(price) from sale);

Das Ergebnis:

Diese Abfrage ruft die Namen der Produkte ab, deren Kosten niedriger sind als der durchschnittliche Verkaufspreis. Der durchschnittliche Verkaufspreis wird mit Hilfe der Aggregatfunktion AVG() berechnet und von der Unterabfrage zurückgegeben. Die Kosten der einzelnen Produkte werden in der äußeren Abfrage mit diesem Wert verglichen.

Unterabfrage in einer ALL-Klausel

Eine weitere Situation ist eine Unterabfrage mit einer ALL-Klausel.

SELECT name FROM product
WHERE cost > ALL(SELECT price from sale);

Die Unterabfrage gibt alle Verkaufspreise in der Tabelle sale Tabelle zurück. Die äußere Abfrage gibt den Namen des Produkts mit dem höheren Verkaufspreis als den Kosten zurück.

Das Ergebnis:

Wann wird eine Subquery im Vergleich zu einem JOIN verwendet?

Wir haben einige häufige Verwendungen von Unterabfragen und die Situationen besprochen, in denen einige Unterabfragen stattdessen mit JOINumgeschrieben werden können. Eine JOIN ist in den meisten Fällen effizienter, aber es gibt Fälle, in denen andere Konstrukte als eine Subquery nicht möglich sind. Während Unterabfragen für Anfänger besser lesbar sind, sind JOINs für erfahrene SQL-Programmierer besser lesbar, wenn die Abfragen komplexer werden. Es ist eine gute Praxis, mehrere Ebenen von verschachtelten Unterabfragen zu vermeiden, da sie nicht leicht lesbar sind und keine gute Leistung haben. Im Allgemeinen ist es besser, eine Abfrage mit JOINs statt mit Unterabfragen zu schreiben, wenn dies möglich ist, insbesondere wenn die Unterabfragen korreliert sind.

Wenn Sie mehr erfahren oder Ihre Fähigkeiten üben möchten, sehen Sie sich die Abschnitte über Unterabfragen im Kurs "SQL für Anfänger" oder im Kurs "SQL-Übungssatz" an.