SQL-Unterabfragen

Der Artikel beschreibt, was eine Unterabfrage ist und wie diese nützlichen Anweisungen aussehen. Wir behandeln grundlegende Beispiele mit den Operatoren IN, EXISTS, ANY und ALL, betrachten Unterabfragen in FROM- und WHERE-Klauseln und untersuchen den Unterschied zwischen korrelierten und verschachtelten Unterabfragen.

Lassen Sie uns zunächst mit einer Beispieldatenbank beginnen. Um einige dieser Anweisungen vorzustellen, benötigen wir eine Beispieltabelle, die wir mit einigen Daten füllen.

Was ist eine Unterabfrage?

Eine Subquery ist eine SELECT-Anweisung mit einer anderen SQL-Anweisung, wie im folgenden Beispiel.

SELECT *
FROM product
WHERE id IN (
  SELECT product_id
  FROM provider_offer
  WHERE provider_id = 156
);

Unterabfragen werden entweder als korrelierte Unterabfrage oder als verschachtelte Unterabfrage klassifiziert. Sie sind in der Regel so aufgebaut, dass sie Folgendes zurückgeben:

1. eine Tabelle

   SELECT MAX(average.average_price)
   FROM (
     SELECT
       product_category,
       AVG(price) AS average_price
     FROM product
     GROUP BY product_category
   ) average;
   

2. oder einen Wert

   SELECT id
   FROM purchase
   WHERE value > (
     SELECT AVG(value)
     FROM purchase
   );
   

Möchten Sie mehr über SQL-Unterabfragen erfahren? Sehen Sie sich unsere Serie We Learn SQL auf Youtube an. Vergessen Sie nicht, unseren Kanal zu abonnieren.

Verschachtelte Unterabfragen

Verschachtelte Unterabfragen sind Unterabfragen, die nicht auf einer äußeren Abfrage beruhen. Mit anderen Worten: Beide Abfragen in einer verschachtelten Subquery können als separate Abfragen ausgeführt werden.

Diese Art von Unterabfrage kann fast überall verwendet werden, aber sie nimmt normalerweise eines dieser Formate an:

SELECT
FROM
WHERE [NOT] IN (subquery)

SELECT *
FROM client
WHERE city IN (
  SELECT city
  FROM provider
);

Die Beispiel-Subquery gibt alle Kunden zurück, die aus derselben Stadt stammen wie die Produktanbieter.
Der IN-Operator prüft, ob der Wert in der Tabelle enthalten ist, und ruft die passenden Zeilen ab.

SELECT
FROM
WHERE expression comparison_operator [ANY| ALL] (subquery)

Unterabfrage mit ALL-Operator

Der ALL-Operator vergleicht einen Wert mit jedem Wert AUS der Ergebnistabelle.

Die folgende Abfrage gibt zum Beispiel alle Modelle und Hersteller von Fahrrädern zurück, deren Preis höher ist als der des teuersten Kopfhörers.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND price > ALL(
    SELECT price
    FROM product
    WHERE product_category = 'headphones'
  );

Ähnliche Unterabfrage, aber mit dem Operator ANY:

Unterabfrage mit ANY-Operator

Der ANY-Operator vergleicht einen Wert mit jedem Wert in einer Tabelle und wertet aus, ob das Ergebnis einer inneren Abfrage mindestens eine Zeile enthält oder nicht.

Die folgende Abfrage gibt alle Modelle und Hersteller von Fahrrädern zurück, deren Preis größer als mindestens einer der Kopfhörer ist.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND price > ANY(
    SELECT price
    FROM product
    WHERE product_category = 'headphones'
  );

Sie können auch eine Unterabfrage in einer anderen Unterabfrage verschachteln. Zum Beispiel:

Unterabfrage verschachtelt in einer anderen Unterabfrage mit IN-Operator

Diese Abfrage gibt die Hersteller und Modelle von Fahrrädern zurück, die in den Angeboten der Anbieter VON den USA existieren.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND id IN (
    SELECT distinct product_id
    FROM provider_offer
    WHERE provider_id IN (
      SELECT id
      FROM provider
      WHERE country = 'USA'
    )
  );

Das Gleiche könnte mit Joins gemacht werden.

SELECT product.producer, product.model
FROM product, provider_offer, provider
WHERE provider_offer.product_id = product.id
  AND provider_offer.provider_id = provider.id
  AND product_category = 'bike'
  AND provider.country = 'USA';

Korrelierte Unterabfragen

Unterabfragen sind korreliert, wenn die innere und die äußere Abfrage voneinander abhängig sind, d. h., wenn die äußere Abfrage eine Abfrage ist, die eine Unterabfrage enthält, und die Unterabfrage selbst eine innere Abfrage ist. Benutzer, die mit Programmierkonzepten vertraut sind, können dies mit einer verschachtelten Schleifenstruktur vergleichen.

Lassen Sie uns mit einem einfachen Beispiel beginnen.

Die innere Abfrage berechnet den Durchschnittswert und gibt ihn zurück. In der WHERE-Klausel der äußeren Abfrage werden nur die Einkäufe gefiltert, deren Wert größer ist als der von der inneren Abfrage zurückgegebene Wert.

Korrelierte Unterabfrage in WHERE-Klausel

SELECT id
FROM purchase p1
WHERE date > '2013-07-15'
  AND value > (
    SELECT AVG(value)
    FROM purchase p2
    WHERE p1.date = p2.date
  );

Die Abfrage gibt Käufe nach dem 15.07.2014 zurück, deren Gesamtpreis größer ist als der Durchschnittswert VON demselben Tag.

Das gleiche Beispiel, aber mit verbundenen Tabellen.

SELECT  p1.id
FROM purchase p1, purchase p2
WHERE p1.date = p2.date
  AND p1.date > '2013-07-15'
GROUP BY p1.id
HAVING p1.value > AVG(p2.value);

Dieses Beispiel kann auch als SELECT-Anweisung mit einer in einer FROM-Klausel korrelierten Unterabfrage geschrieben werden.

Die Unterabfrage gibt die Tabelle zurück, die den Durchschnittswert für jeden Kauf für jeden Tag enthält. Wir verknüpfen dieses Ergebnis mit der Tabelle "Kauf" in der Spalte "Datum", um die Bedingung Datum > "15/07/2014′ zu prüfen.

SELECT id
FROM
  purchase,
  (
    SELECT date, AVG(value) AS average_value
    FROM purchase
    WHERE date > '2013-07-15'
    GROUP BY date
  ) average
WHERE purchase.date  = average.date
  AND purchase.date  > '2013-07-15'
  AND purchase.value > average.average_value;

Normalerweise sollte diese Art von Unterabfrage vermieden werden, da Indizes nicht für eine temporäre Tabelle im Speicher verwendet werden können.

Unterabfrage mit EXISTS

SELECT
FROM
WHERE [NOT] EXISTS (subquery)

Der EXISTS-Operator prüft, ob die Zeile AUS der Unterabfrage mit einer Zeile in der äußeren Abfrage übereinstimmt. Wenn es keine übereinstimmenden Daten gibt, gibt der EXISTS-Operator FALSE zurück.

Diese Abfrage gibt alle Kunden zurück, die nach dem 10/07/2013 bestellt haben.

SELECT id, company_name
FROM client
WHERE EXISTS(
  SELECT *
  FROM purchase
  WHERE client.id = purchase.client_id
  WHERE date > '2013-07-10'
);

Wenn eine Unterabfrage verwendet wird, führt der Abfrageoptimierer zusätzliche Schritte durch, bevor die Ergebnisse aus der Unterabfrage verwendet werden. Wenn eine Abfrage, die eine Unterabfrage enthält, mit Hilfe eines Joins geschrieben werden kann, sollte sie auf diese Weise erstellt werden. Joins ermöglichen es dem Abfrageoptimierer normalerweise, die Daten auf effizientere Weise abzurufen.

Einschränkungen von Unterabfragen

Es gibt einige Einschränkungen bei der Verwendung von Unterabfragen:

• In Oracle können Sie bis zu 255 Ebenen von Unterabfragen in einer WHERE-Klausel verschachteln.
• In SQL Server können Sie bis zu 32 Ebenen verschachteln.