Verwenden von Unterabfragen¶

Eine Unterabfrage ist eine Abfrage in einer anderen Abfrage. Unterabfragen in einer FROM- oder WHERE-Klausel werden verwendet, um Daten bereitzustellen, die zum Begrenzen oder Vergleichen/Auswerten der von der enthaltenden Abfrage zurückgegebenen Daten verwendet werden.

Unter diesem Thema:

Typen von Unterabfragen¶

Korrelierte und unkorrelierte Unterabfragen¶

Unterabfragen können als korreliert oder unkorreliert kategorisiert werden:

Eine korrelierte Unterabfrage verweist auf eine oder mehrere Spalten außerhalb der Unterabfrage. (Die Spalten werden normalerweise in der WHERE-Klausel der Unterabfrage referenziert.) Eine korrelierte Unterabfrage kann als Filter für die Tabelle betrachtet werden, auf die sie verweist, als ob die Unterabfrage für jede Zeile der Tabelle in der äußeren Abfrage ausgewertet würde.
Eine unkorrelierte Unterabfrage enthält keine derartigen externen Spaltenverweise. Sie ist eine unabhängige Abfrage, deren Ergebnisse einmal an die äußere Abfrage zurückgegeben und von dieser verwendet werden (nicht pro Zeile).

Beispiel:

-- Uncorrelated subquery:
SELECT c1, c2
  FROM table1 WHERE c1 = (SELECT MAX(x) FROM table2);

-- Correlated subquery:
SELECT c1, c2
  FROM table1 WHERE c1 = (SELECT x FROM table2 WHERE y = table1.c2);

Copy

Skalare vs. nicht skalare Unterabfragen¶

Unterabfragen können auch als skalar oder nicht skalar kategorisiert werden:

Eine skalare Unterabfrage gibt einen einzelnen Wert zurück (eine Spalte einer Zeile). Wenn keine Zeilen für die Rückgabe qualifiziert sind, gibt die Unterabfrage NULL zurück.
Eine nicht skalare Unterabfrage gibt 0, 1 oder mehrere Zeilen zurück, von denen jede 1 oder mehrere Spalten enthalten kann. Wenn bei keiner Spalte ein zurückzugebender Wert vorhanden ist, gibt die Unterabfrage NULL zurück. Wenn keine Zeilen für die Rückgabe qualifiziert sind, gibt die Unterabfrage 0 Zeilen zurück (nicht NULLs).

Von Snowflake unterstützte Typen¶

Snowflake unterstützt derzeit die folgenden Typen von Unterabfragen:

Unkorrelierte skalare Unterabfragen an einer beliebigen Stelle, an der ein Wertausdruck verwendet werden kann.
Korrelierte skalare Unterabfragen in WHERE-Klauseln.
EXISTS-, ANY/ALL- und IN-Unterabfragen in WHERE-Klauseln. Diese Unterabfragen können korreliert oder unkorreliert sein.

Operatoren für Unterabfragen¶

Unterabfrageoperatoren bearbeiten verschachtelte Abfrageausdrücke. Sie können verwendet werden, um Werte zu berechnen, die folgende Eigenschaften aufweisen:

Zurückgegeben in einer SELECT-Liste
Gruppiert in einer GROUP BY-Klausel
Verglichen mit anderen Ausdrücken in der WHERE- oder HAVING-Klausel

Unterschiede zwischen korrelierten und unkorrelierten Unterabfragen¶

Die folgende Abfrage veranschaulicht eine unkorrelierte Unterabfrage in einer WHERE-Klausel. Die Unterabfrage ermittelt das Pro-Kopf-BIP von Brasilien, und die äußere Abfrage wählt alle Jobs (in jedem Land) aus, für die weniger als das Pro-Kopf-BIP von Brasilien gezahlt wird. Die Unterabfrage ist nicht korreliert, da der zurückgegebene Wert von keiner Spalte der äußeren Abfrage abhängt. Die Unterabfrage muss während der gesamten Ausführung der äußeren Abfrage nur einmal aufgerufen werden.

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p
  WHERE p.annual_wage < (SELECT per_capita_GDP
                           FROM international_GDP
                           WHERE name = 'Brazil');

Copy

Die nächste Abfrage veranschaulicht eine korrelierte Unterabfrage in einer WHERE-Klausel. Die Abfrage listet Jobs auf, bei denen das Jahreseinkommen des Jobs unter dem Pro-Kopf-BIP in dem jeweiligen Land liegt. Diese Unterabfrage ist korreliert, da sie für jede Zeile in der äußeren Abfrage einmal aufgerufen wird und der Wert p.country (Ländername) aus dieser Zeile übergeben wird.

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p
  WHERE p.annual_wage < (SELECT MAX(per_capita_GDP)
                           FROM international_GDP i
                           WHERE p.country = i.name);

Copy

Bemerkung

Die Aggregatfunktion MAX ist in diesem Fall logisch nicht erforderlich, da die Tabelle international_GDP nur eine Zeile pro Land enthält. Da der Server dies jedoch nicht weiß und verlangt, dass die Unterabfrage nicht mehr als eine Zeile zurückgibt, verwendet die Abfrage die Aggregatfunktion, um den Server zu zwingen, zu erkennen, dass die Unterabfrage bei jeder Ausführung immer nur eine Zeile zurückgibt.

Die Funktionen MIN und AVG funktionieren auch, weil die Anwendung einer dieser Funktionen auf einen einzelnen Wert diesen Wert unverändert zurückgibt.

Skalare Unterabfragen¶

Eine skalare Unterabfrage ist eine Unterabfrage, die höchstens eine Zeile zurückgibt. Eine skalare Unterabfrage kann an einer beliebigen Stelle erscheinen, an der ein Wertausdruck erscheinen kann, einschließlich der SELECT-Liste, der GROUP BY-Klausel oder als Argument einer Funktion in einer WHERE- oder HAVING-Klausel.

Nutzungshinweise¶

Eine skalare Unterabfrage kann nur ein Element in der SELECT-Liste enthalten.
Wenn eine skalare Unterabfrage mehr als eine Zeile zurückgibt, wird ein Laufzeitfehler generiert.
Korrelierte skalare Unterabfragen werden derzeit nur unterstützt, wenn sie statisch festgelegt werden können, um eine Zeile zurückzugeben (z. B. wenn die SELECT-Liste eine Aggregatfunktion ohne GROUP BY enthält).
Unkorrelierte skalare Unterabfragen werden überall dort unterstützt, wo ein Wertausdruck zulässig ist.
Unterabfragen mit einer Korrelation innerhalb von FLATTEN werden derzeit nicht unterstützt.
Die LIMIT / FETCH-Klausel ist nur in unkorrelierten skalaren Unterabfragen zulässig.

Beispiele¶

Dieses Beispiel zeigt eine grundlegende unkorrelierte Unterabfrage in einer WHERE-Klausel:

SELECT employee_id
FROM employees
WHERE salary = (SELECT max(salary) FROM employees);

Copy

Dieses Beispiel zeigt eine unkorrelierte Unterabfrage in einer FROM-Klausel. Diese einfache Unterabfrage erhält lediglich eine Teilmenge der Informationen in der Tabelle international_GDP. In der Gesamtabfrage werden Jobs in „Hochlohnländern“ aufgelistet, in denen das Jahreseinkommen des Jobs dem Pro-Kopf-GDP in diesem Land entspricht.

SELECT p.name, p.annual_wage, p.country
  FROM pay AS p INNER JOIN (SELECT name, per_capita_GDP
                              FROM international_GDP
                              WHERE per_capita_GDP >= 10000.0) AS pcg
    ON pcg.per_capita_GDP = p.annual_wage AND p.country = pcg.name;

Copy

Einschränkungen¶

Unterabfragen können zwar eine Vielzahl von SELECT-Anweisungen enthalten, sie haben jedoch die folgenden Einschränkungen:

Einige Klauseln sind in ANY/ALL/NOT EXISTS-Unterabfragen nicht zulässig.
Der einzige Typ von Unterabfrage, der eine LIMIT / FETCH-Klausel zulässt, ist die unkorrelierte skalare Unterabfrage. Und da eine unkorrelierte skalare Unterabfrage nur eine Zeile zurückgibt, hat die LIMIT-Klausel wenig oder keinen praktischen Wert innerhalb einer Unterabfrage.