Unterabfragen in Google Standard-SQL

Unterabfragen

Eine Unterabfrage ist eine Abfrage, die in einer anderen Abfrageanweisung angezeigt wird. Unterabfragen werden auch als Sub-SELECTs oder verschachtelte SELECTs bezeichnet. Die vollständige SELECT-Syntax ist in Unterabfragen gültig.

Die WITH-Klausel wird für eine Unterabfrage nicht unterstützt. Dies gibt einen Fehler zurück:

SELECT account
FROM (
  WITH result AS (SELECT * FROM NPCs)
  SELECT *
  FROM result);

Ausdruck-Unterabfragen

Ausdruck-Unterabfragen werden in einer Abfrage an Stellen verwendet, an denen Ausdrücke gültig sind. Sie geben einen einzelnen Wert anstatt einer Spalte oder Tabelle zurück. Ausdruck-Unterabfragen können korreliert sein.

Skalare Unterabfragen

( subquery )

Beschreibung

Eine Unterabfrage innerhalb eines Ausdrucks wird als skalare Unterabfrage interpretiert. Skalare Unterabfragen werden häufig in der SELECT-Liste oder der WHERE-Klausel verwendet.

Eine skalare Unterabfrage muss eine einzelne Spalte auswählen. Der Versuch, mehrere Spalten auszuwählen, führt zu einem Analysefehler. Die SELECT-Liste mit einem einzelnen Ausdruck ist die einfachste Möglichkeit, eine einzelne Spalte auszuwählen. Der Ergebnistyp der skalaren Unterabfrage ist der Typ dieses Ausdrucks.

Eine weitere Möglichkeit besteht darin, mit SELECT AS STRUCT eine Unterabfrage zu definieren, die einen einzelnen Wert vom Typ STRUCT auswählt, dessen Felder durch einen oder mehrere Ausdrücke definiert werden.

Wenn die Unterabfrage genau eine Zeile zurückgibt, ist dieser einzelne Wert das skalare Unterabfrageergebnis. Wenn bei der Unterabfrage keine Zeilen zurückgegeben werden, ist das Ergebnis NULL. Wenn die Unterabfrage mehr als eine Zeile zurückgibt, schlägt die Abfrage mit einem Laufzeitfehler fehl.

Beispiele

In diesem Beispiel gibt eine korrelierte skalare Unterabfrage die Maskottchen für eine Liste von Spielern mithilfe der Tabellen Players und Guilds zurück:

SELECT account, (SELECT mascot FROM Guilds WHERE Players.guild = id) AS player_mascot
FROM Players;

+---------------------------+
| account   | player_mascot |
+---------------------------+
| gorbie    | cardinal      |
| junelyn   | finch         |
| corba     | parrot        |
+---------------------------+

In diesem Beispiel berechnet eine aggregierte skalare Unterabfrage avg_level, die durchschnittliche Ebene eines Nutzerkontos in der Tabelle Players.

SELECT account, level, (SELECT AVG(level) FROM Players) AS avg_level
FROM Players;

+---------------------------------------+
| account   | level      | avg_level    |
+---------------------------------------+
| gorbie    | 29         | 24.66        |
| junelyn   | 2          | 24.66        |
| corba     | 43         | 24.66        |
+---------------------------------------+

ARRAY-Unterabfragen

ARRAY ( subquery )

Beschreibung

Eine ARRAY-Unterabfrage ist ein Sonderfall der Ausdruck-Unterabfrage, in dem ein ARRAY zurückgegeben wird. Wenn die Unterabfrage null Zeilen zurückgibt, wird ein leeres ARRAY zurückgegeben. Gibt nie ein NULL-ARRAY zurück.

Die SELECT-Liste in einer ARRAY-Unterabfrage muss genau eine Spalte eines beliebigen Typs haben, die den Elementtyp des Arrays angibt, das von der Array-Unterabfrage zurückgegeben wird. Andernfalls wird ein Fehler zurückgegeben. Wenn die Unterabfrage mit SELECT AS STRUCT geschrieben wird, kann die Liste SELECT mehrere Spalten enthalten. Der von der Array-Unterabfrage zurückgegebene Wert ist ein ARRAY des erstellten STRUCT-Werts. Die Auswahl mehrerer Spalten ohne Verwendung von SELECT AS führt zu einem Fehler.

ARRAY-Unterabfragen können SELECT AS STRUCT verwenden, um Arrays von Strukturen zu erstellen.

Die vollständige Semantik finden Sie unter Arrayfunktionen.

Beispiele

In diesem Beispiel gibt eine ARRAY-Unterabfrage ein Array von Konten zurück, die der roten Guilde in der Tabelle NPCs zugewiesen sind:

SELECT ARRAY(SELECT account FROM NPCs WHERE guild = 'red') as red
FROM NPCs LIMIT 1;

+-----------------+
| red             |
+-----------------+
| [niles,jujul]   |
+-----------------+

IN-Unterabfragen

value [ NOT ] IN ( subquery )

Beschreibung

Gibt TRUE zurück, wenn value in der von der Unterabfrage zurückgegebenen Zeilen enthalten ist. Gibt FALSE zurück, wenn die Unterabfrage Nullzeilen zurückgibt.

Die SELECT-Liste der Unterabfrage muss eine einzige Spalte eines beliebigen Typs haben und ihr Typ muss mit dem Typ für value vergleichbar sein. Andernfalls wird ein Fehler zurückgegeben. Die vollständige Semantik, einschließlich der NULL-Verarbeitung, finden Sie unter IN-Operator.

Wenn Sie eine IN-Unterabfrage mit einem Array verwenden müssen, sind diese äquivalent:

value [ NOT ] IN ( subquery )
value [ NOT ] IN UNNEST( ARRAY( subquery ) )

Beispiele

In diesem Beispiel wird mit dem IN-Operator geprüft, ob in der Tabelle Players bereits ein Konto namens corba vorhanden ist:

SELECT "corba" IN (SELECT account FROM Players) as result;

+--------+
| result |
+--------+
| TRUE   |
+--------+

EXISTS-Unterabfragen

EXISTS( subquery )

Beschreibung

Gibt TRUE zurück, wenn die Unterabfrage eine oder mehrere Zeilen erzeugt. Gibt FALSE zurück, wenn die Unterabfrage Nullzeilen erzeugt. Gibt niemals NULL zurück. Im Gegensatz zu allen anderen Ausdruck-Unterabfragen gibt es keine Regeln über die Spaltenliste. Es können beliebig viele Spalten ausgewählt werden. Das Abfrageergebnis wird davon nicht beeinflusst.

Beispiele

In diesem Beispiel prüft der Operator EXISTS mit der Tabelle Players, ob Zeilen erstellt wurden:

SELECT EXISTS(SELECT account FROM Players WHERE guild = 'yellow') AS result;

+--------+
| result |
+--------+
| FALSE  |
+--------+

Tabellen-Unterabfragen

FROM ( subquery ) [ [ AS ] alias ]

Beschreibung

Mit Tabellen-Unterabfragen behandelt die äußere Abfrage das Ergebnis der Unterabfrage als Tabelle. Sie können sie nur in der FROM-Klausel verwenden.

Beispiele

In diesem Beispiel gibt eine Unterabfrage eine Tabelle aus der Tabelle Players zurück:

SELECT results.account
FROM (SELECT * FROM Players) AS results;

+-----------+
| account   |
+-----------+
| gorbie    |
| junelyn   |
| corba     |
+-----------+

Korrelierte Unterabfragen

Eine korrelierte Unterabfrage ist eine Unterabfrage, die von außerhalb dieser Unterabfrage auf eine Spalte verweist. Die Korrelation verhindert die Wiederverwendung des Unterabfrageergebnisses. Weitere Informationen

Beispiele

In diesem Beispiel wird eine Liste von Maskottchen zurückgegeben, denen keine Spieler zugeordnet sind. Auf die Tabellen Guilds und Players wird verwiesen.

SELECT mascot
FROM Guilds
WHERE NOT EXISTS(SELECT account FROM Players WHERE Guilds.id = Players.guild)

+----------+
| mascot   |
+----------+
| sparrow  |
+----------+

In diesem Beispiel gibt eine korrelierte skalare Unterabfrage die Maskottchen für eine Liste von Spielern mithilfe der Tabellen Players und Guilds zurück:

SELECT account, (SELECT mascot FROM Guilds WHERE Players.guild = id) AS player_mascot
FROM Players;

+---------------------------+
| account   | player_mascot |
+---------------------------+
| gorbie    | cardinal      |
| junelyn   | finch         |
| corba     | parrot        |
+---------------------------+

Volatile Unterabfragen

Eine flüchtige Unterabfrage ist eine Unterabfrage, die nicht immer das gleiche Ergebnis für die gleichen Eingaben liefert. Wenn eine Unterabfrage beispielsweise eine Funktion enthält, die eine zufällige Zahl zurückgibt, ist die Unterabfrage flüchtig, da das Ergebnis nicht immer das gleiche ist.

Beispiele

In diesem Beispiel wird eine zufällige Anzahl von Konten aus der Tabelle Players zurückgegeben.

SELECT results.account
FROM (SELECT * FROM Players WHERE RAND() < 0.5) AS results;

-- The results are not always the same when you execute
-- the preceding query, but will look similar to this:
+---------+
| account |
+---------+
| gorbie  |
| junelyn |
+---------+

Bewertungsregeln für Unterabfragen

Einige Unterabfragen werden einmal ausgewertet, andere häufiger.

  • Eine nicht-korrelierte, flüchtige Unterabfrage wird je nach Abfrageplan einmal pro Zeile neu ausgewertet.
  • Eine korrelierte Unterabfrage muss logisch für jeden einzelnen Satz von Parameterwerten neu ausgewertet werden. Je nach Abfrageplan kann eine verknüpfte Unterabfrage einmal pro Zeile neu ausgewertet werden, auch wenn mehrere Zeilen die gleichen Parameterwerte haben.
  • Eine Unterabfrage, die von WITH einer temporären Tabelle zugewiesen wurde, wird einmal als "as-if" ausgewertet. Ein Abfrageplan kann die Unterabfrage nur dann neu bewerten, wenn sie neu bewertet wird, damit sie jedes Mal dieselbe Tabelle erzeugt.

Gängige, in den Beispielen verwendete Tabellen

Einige Beispiele verweisen auf eine Tabelle mit dem Namen Players:

+-----------------------------+
| account   | level   | guild |
+-----------------------------+
| gorbie    | 29      | red   |
| junelyn   | 2       | blue  |
| corba     | 43      | green |
+-----------------------------+

Einige Beispiele verweisen auf eine Tabelle mit dem Namen NPCs:

+-------------------+
| account   | guild |
+-------------------+
| niles     | red   |
| jujul     | red   |
| effren    | blue  |
+-------------------+

Einige Beispiele verweisen auf eine Tabelle mit dem Namen Guilds:

+-------------------+
| mascot   | id     |
+-------------------+
| cardinal | red    |
| parrot   | green  |
| finch    | blue   |
| sparrow  | yellow |
+-------------------+

Sie können mit dieser WITH-Klausel temporäre Tabellennamen für Players und NPCs in Unterabfragen emulieren, die die Klausel WITH unterstützen:

WITH
  Players AS (
    SELECT 'gorbie' AS account, 29 AS level, 'red' AS guild UNION ALL
    SELECT 'junelyn', 2 , 'blue' UNION ALL
    SELECT 'corba', 43, 'green'),
  NPCs AS (
    SELECT 'niles' AS account, 'red' AS guild UNION ALL
    SELECT 'jujul', 'red' UNION ALL
    SELECT 'effren', 'blue'),
  Guilds AS (
    SELECT 'cardinal' AS mascot , 'red' AS id UNION ALL
    SELECT 'parrot', 'green' UNION ALL
    SELECT 'finch', 'blue' UNION ALL
    SELECT 'sparrow', 'yellow')
SELECT * FROM (
  SELECT account, guild FROM Players UNION ALL
  SELECT account, guild FROM NPCs)