Abfragesyntax

Abfrageanweisungen gehen eine oder mehrere Tabellen oder Ausdrücke durch und geben die berechneten Ergebniszeilen zurück. In diesem Thema wird die Syntax für SQL-Abfragen in Cloud Spanner SQL beschrieben.

SQL-Syntax

query_statement:
    [ statement_hint_expr ][ table_hint_expr ][ join_hint_expr ]
    query_expr

statement_hint_expr:
    '@{' statement_hint_key = statement_hint_value [, ...] '}'

statement_hint_key:
    { USE_ADDITIONAL_PARALLELISM | OPTIMIZER_VERSION  | LOCK_SCANNED_RANGES }

query_expr:
    [ WITH with_clause ]
    { select | ( query_expr ) | query_expr set_op query_expr }
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ LIMIT count [ OFFSET skip_rows ] ]

select:
    SELECT [ AS { STRUCT | VALUE } ] [{ ALL | DISTINCT }]
        { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
            [ REPLACE ( expression [ AS ] column_name [, ...] ) ]
        | expression [ [ AS ] alias ] } [, ...]
    [ FROM from_clause[, ...] ]
    [ WHERE bool_expression ]
    [ GROUP BY expression [, ...] ]
    [ HAVING bool_expression ]

set_op:
    UNION { ALL | DISTINCT } | INTERSECT { ALL | DISTINCT } | EXCEPT { ALL | DISTINCT }

table_hint_expr:
    '@{' table_hint_key = table_hint_value '}'

table_hint_key:
    { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION }

Notationsregeln

• Eckige Klammern "[ ]" zeigen optionale Klauseln an.
• Runde Klammern "( )" sind tatsächlich im Code vorhandene Klammern.
• Der senkrechte Strich "|" zeigt ein logisches ODER an.
• Geschweifte Klammern "{ }" umschließen einen Optionssatz.
• Ein Komma gefolgt von Auslassungspunkten in eckigen Klammern "[, ... ]" zeigt an, dass das vorhergehende Element in einer durch Kommas getrennten Liste wiederholt werden kann.

Für Anweisungen unterstützte Hinweise

Die folgenden Hinweise werden für Abfrageanweisungen unterstützt:

Hint-Taste	Mögliche Werte	Beschreibung
USE_ADDITIONAL_PARALLELISM	TRUE FALSE (Standardeinstellung)	Wenn TRUE, wird die Ausführungs-Engine nach Möglichkeit mehr Parallelität verwenden. Da hierdurch möglicherweise für andere Vorgänge weniger Ressourcen vorhanden sind, verwenden Sie diesen Hinweis nicht, wenn Sie latenzempfindliche Vorgänge für dieselbe Instanz ausführen.
OPTIMIZER_VERSION	1 nach N|latest	Führt die Abfrage mit der angegebenen Optimierungstool-Version aus. Mögliche Werte sind 1 bis N (die neueste Optimierungstool-Version) oder latest. Wenn der Hinweis nicht festgelegt ist, wird das Optimierungstool für die Version ausgeführt, die in den Datenbankoptionen festgelegt oder über die Client-API angegeben wurde. Wenn keine dieser Optionen festgelegt ist, verwendet das Optimierungstool standardmäßig die neueste Version. In Bezug auf die Versionseinstellung hat der von der Client-API festgelegte Wert Vorrang vor dem Wert in den Datenbankoptionen und der von diesem Hinweis festgelegte Wert hat Vorrang vor allem anderen. Weitere Informationen finden Sie unter Abfrageoptimierungstool.
LOCK_SCANNED_RANGES	exclusive shared (Standardeinstellung)	Mit diesem Hinweis können Sie eine exklusive Sperre für eine Reihe von Bereichen anfordern, die von einer Transaktion gescannt werden. Die Aufrechterhaltung einer ausschließlichen Sperre hilft in Szenarien mit hohen Schreibkonflikten, d. h., dass mehrere Transaktionen gleichzeitig versuchen, dieselben Daten zu lesen und zu schreiben. Das führt zu einer Vielzahl von Abbruchen. auf. Ohne den Hinweis ist es möglich, dass mehrere gleichzeitige Transaktionen gemeinsame Sperren erhalten und dann versuchen, ein Upgrade auf exklusive Sperren auszuführen. Dies führt zu einem "Deadlock", weil die gemeinsame Sperre jeder Transaktion verhindert, dass die anderen Transaktionen ein exklusives Upgrade ausführen. Cloud Spanner bricht alle bis auf eine der Transaktionen ab. Wenn Sie mit diesem Hinweis eine exklusive Sperre anfordern, erhält eine Transaktion die Sperre und die Ausführung wird fortgesetzt. Andere Transaktionen wiederum warten auf die Sperre. Der Durchsatz ist weiterhin begrenzt, da die in Konflikt stehenden Transaktionen jeweils nur eine Transaktion ausführen können. In diesem Fall führt Cloud Spanner jedoch immer nur eine einzige Transaktion aus, wodurch Zeit gespart und der Vorgang andernfalls abgebrochen und wiederholt wird. Transaktionen. Dieser Hinweis wird für alle Anweisungstypen sowie für die DML unterstützt. Cloud Spanner erzwingt immer die Serialisierbarkeit. Hinweise im Sperrmodus können beeinflussen, welche Transaktionen bei laufenden Arbeitslasten warten oder abbrechen, aber die Isolationsebene nicht ändern. Da es sich nur um einen Hinweis handelt, sollte sie nicht als mutex angesehen werden. Mit anderen Worten: Sie sollten keine exklusiven Sperren von Cloud Spanner als gegenseitiger Ausschlussmechanismus für die Ausführung von Code außerhalb von Cloud Spanner verwenden. Weitere Informationen finden Sie unter Sperren.

Beispieltabellen

In den folgenden Tabellen wird das Verhalten verschiedener Abfrageklauseln in dieser Referenz veranschaulicht.

Tabelle „Roster“ (Teilnehmerliste)

Die Tabelle Roster enthält eine Liste der Spielernamen (LastName) und die eindeutige ID, die der Schule (SchoolID) zugewiesen ist. Sie sieht so aus:

+-----------------------+
| LastName   | SchoolID |
+-----------------------+
| Adams      | 50       |
| Buchanan   | 52       |
| Coolidge   | 52       |
| Davis      | 51       |
| Eisenhower | 77       |
+-----------------------+

Sie können diese WITH-Klausel verwenden, um einen temporären Tabellennamen für die Beispiele in dieser Referenz zu emulieren:

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)
SELECT * FROM Roster

Tabelle „PlayerStats“ (Spielerstatistik)

Die Tabelle PlayerStats enthält eine Liste der Spielernamen (LastName) und die eindeutige ID der Gegner, gegen die in einem bestimmten Spiel gespielt wurde (OpponentID), sowie die Anzahl der Punkte, die der jeweilige Spieler in diesem Spiel erzielt hat (PointsScored).

+----------------------------------------+
| LastName   | OpponentID | PointsScored |
+----------------------------------------+
| Adams      | 51         | 3            |
| Buchanan   | 77         | 0            |
| Coolidge   | 77         | 1            |
| Adams      | 52         | 4            |
| Buchanan   | 50         | 13           |
+----------------------------------------+

Sie können diese WITH-Klausel verwenden, um einen temporären Tabellennamen für die Beispiele in dieser Referenz zu emulieren:

WITH PlayerStats AS
 (SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 77, 0 UNION ALL
  SELECT 'Coolidge', 77, 1 UNION ALL
  SELECT 'Adams', 52, 4 UNION ALL
  SELECT 'Buchanan', 50, 13)
SELECT * FROM PlayerStats

Tabelle „TeamMascot“ (Teammaskottchen)

Die Tabelle TeamMascot enthält eine Liste eindeutiger Schul-IDs (SchoolID) und das Maskottchen für die jeweilige Schule (Mascot).

+---------------------+
| SchoolID | Mascot   |
+---------------------+
| 50       | Jaguars  |
| 51       | Knights  |
| 52       | Lakers   |
| 53       | Mustangs |
+---------------------+

Sie können diese WITH-Klausel verwenden, um einen temporären Tabellennamen für die Beispiele in dieser Referenz zu emulieren:

WITH TeamMascot AS
 (SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
  SELECT 51, 'Knights' UNION ALL
  SELECT 52, 'Lakers' UNION ALL
  SELECT 53, 'Mustangs')
SELECT * FROM TeamMascot

SELECT-Liste

SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }]
    { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
        [ REPLACE ( expression [ AS ] column_name [, ...] ) ]
    | expression [ [ AS ] alias ] } [, ...]

Die SELECT-Liste bestimmt die Spalten, die von der Abfrage zurückgegeben werden. Ausdrücke in der SELECT-Liste können auf Spalten in einem der from_item-Elemente in der entsprechenden FROM-Klausel verweisen.

Jedes Element in der SELECT-Liste ist eines der folgenden:

• *
• expression
• expression.*

SELECT *

SELECT *, häufig als "Select Star" bezeichnet, erzeugt für jede Spalte eine Ausgabespalte, die nach Ausführung der vollständigen Abfrage sichtbar ist.

SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);

+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot    |
+-------+-----------+

SELECT expression

Bei Elementen in einer SELECT-Liste kann es sich um Ausdrücke handeln. Diese Ausdrücke ergeben einen einzelnen Wert und erzeugen eine Ausgabespalte mit einem optionalen expliziten alias.

Wenn der Ausdruck über keinen expliziten Alias verfügt, erhält er einen impliziten Alias gemäß den Regeln für implizite Aliasse, sofern möglich. Andernfalls ist die Spalte anonym und Sie können an anderer Stelle in der Abfrage nicht anhand des Namens darauf verweisen.

SELECT expression.*

Ein Element in einer SELECT-Liste kann auch die Form expression.* haben. Dies erzeugt eine Ausgabespalte für jede Spalte oder jedes Feld der obersten Ebene von expression. Der Ausdruck muss entweder ein Tabellenalias sein oder einen einzelnen Wert eines Datentyps mit Feldern ergeben, wie z. B. STRUCT.

Die folgende Abfrage erzeugt eine Ausgabespalte für jede Spalte in der Tabelle groceries mit dem Alias g.

WITH groceries AS
  (SELECT "milk" AS dairy,
   "eggs" AS protein,
   "bread" AS grain)
SELECT g.*
FROM groceries AS g;

+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk  | eggs    | bread |
+-------+---------+-------+

Weitere Beispiele:

WITH locations AS
  (SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
  UNION ALL
  SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
| Phoenix | Arizona    |
+---------+------------+

WITH locations AS
  (SELECT ARRAY<STRUCT<city STRING, state STRING>>[("Seattle", "Washington"),
    ("Phoenix", "Arizona")] AS location)
SELECT l.LOCATION[offset(0)].*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
+---------+------------+

Modifikatoren für *-Operator

SELECT * EXCEPT

Eine SELECT * EXCEPT-Anweisung gibt die Namen einer oder mehrerer Spalten an, die vom Ergebnis auszuschließen sind. Alle übereinstimmenden Spaltennamen werden in der Ausgabe ausgelassen.

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;

+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket  | 200      |
+-----------+----------+

Hinweis: SELECT * EXCEPT schließt keine Spalten aus, die keinen Namen haben.

SELECT * REPLACE

Eine SELECT * REPLACE-Anweisung gibt eine oder mehrere expression AS identifier-Klauseln an. Jede Kennzeichnung muss mit einem Spaltennamen der SELECT *-Anweisung übereinstimmen. In der Ausgabespaltenliste wird die Spalte, die mit der Kennzeichnung in einer REPLACE-Klausel übereinstimmt, durch den Ausdruck in dieser REPLACE-Klausel ersetzt.

Eine SELECT * REPLACE-Anweisung ändert nicht die Namen oder die Reihenfolge der Spalten. Sie kann jedoch den Wert und den Werttyp ändern.

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | widget    | 200      |
+----------+-----------+----------+

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | sprocket  | 100      |
+----------+-----------+----------+

Hinweis: SELECT * REPLACE ersetzt keine Spalten, die keinen Namen haben.

Umgang mit doppelten Zeilen

Sie können die Ergebnisse, die von einer SELECT-Abfrage zurückgegeben werden, folgendermaßen ändern.

SELECT DISTINCT

Eine SELECT DISTINCT-Anweisung verwirft doppelte Zeilen und gibt nur die verbleibenden Zeilen zurück. SELECT DISTINCT kann keine Spalten der folgenden Typen zurückgeben:

• STRUCT
• ARRAY

SELECT ALL

Eine SELECT ALL-Anweisung gibt alle Zeilen zurück, einschließlich doppelter Zeilen. SELECT ALL ist die Standardeinstellung von SELECT.

STRUCTs mit SELECT verwenden

• Abfragen, die einen STRUCT am Stammverzeichnis des Rückgabetyps zurückgeben, werden in Cloud Spanner APIs nicht unterstützt. Die folgende Abfrage wird beispielsweise nur als Unterabfragen unterstützt:

  SELECT STRUCT(1, 2) FROM Users;
  

• Die Rückgabe eines Arrays von Structs wird unterstützt. Die folgenden Abfragen werden in Cloud Spanner APIs beispielsweise unterstützt:

  SELECT ARRAY(SELECT STRUCT(1 AS A, 2 AS B)) FROM Users;
  

  SELECT ARRAY(SELECT AS STRUCT 1 AS a, 2 AS b) FROM Users;
  

• Abfrageformen, die einen ARRAY<STRUCT<...>>-Wert vom Typ NULL oder einen Wert vom Typ ARRAY<STRUCT<...>> mit einem Element, das NULL ist, zurückgeben können, werden jedoch in Cloud Spanner APIs nicht unterstützt. Die folgende Abfrage wird nur als Unterabfrage unterstützt:

  SELECT ARRAY(SELECT IF(STARTS_WITH(Users.username, "a"), NULL, STRUCT(1, 2)))
  FROM Users;
  

Hinweis: Die Logik in Cloud Spanner, die bestimmt, ob eine Abfrage ein Objekt zurückgeben kannNULL Array von Structs oderNULL Array von Struct-Elementen ist nichtabgeschlossen (im logischen Sinne vonabgeschlossen ). Das bedeutet, dass einige Abfragen, die eindeutig keine NULLs zurückgeben können, weiterhin abgelehnt werden und die Optimierung manchmal erforderlich ist, um eine unterstützte Abfrageform abzurufen. Die kleinsten problematischen Abfrageformen verwenden die Unterabfrage ARRAY(SELECT AS STRUCT ... ), um das Array der Strukturwerte zu erstellen.

Weitere Beispiele zum Abfragen von STRUCTs in einem ARRAY finden Sie unter STRUCT-Elemente in einem ARRAY abfragen.

Weitere Informationen finden Sie auch in den Hinweisen zur Verwendung von STRUCTs in Unterabfragen.

Wertetabellen

In Cloud Spanner SQL ist eine Wertetabelle eine Tabelle, deren Zeilentyp ein einzelner Wert ist. In einer regulären Tabelle besteht jede Zeile aus Spalten, die jeweils einen Namen und einen Typ haben. In einer Wertetabelle ist der Zeilentyp nur ein einzelner Wert und es gibt keine Spaltennamen.

In Cloud Spanner erfolgen Wertetabellen hauptsächlich als Ausgabe des Operators UNNEST oder einer Unterabfrage. Die WITH-Klausel führt eine Wertetabelle durch, wenn die verwendete Unterabfrage eine Wertetabelle erzeugt. Cloud Spanner unterstützt keine Wertetabellen als Basistabellen in Datenbankschemas und unterstützt keine Rückgabe von Werttabellen in Abfrageergebnissen. Daher werden Wertetabellen, die Abfragen erzeugen, nicht als Abfragen der obersten Ebene unterstützt.

In Kontexten, in denen eine Abfrage mit genau einer Spalte erwartet wird, kann stattdessen eine Wertetabellenabfrage verwendet werden. Für skalare Unterabfragen und Array-Unterabfragen (siehe Unterabfragen) ist normalerweise eine einspaltige Abfrage erforderlich. In Cloud Spanner SQL können sie jedoch auch mit einer Wertetabellenabfrage verwendet werden.

Eine Abfrage erstellt eine Wertetabelle, wenn sie SELECT AS und eine der folgenden Syntaxen verwendet:

SELECT AS STRUCT

SELECT AS STRUCT expr [[AS] struct_field_name1] [,...]

Diese Abfrage erzeugt eine Wertetabelle mit einem STRUCT-Zeilentyp. Dabei entsprechen die STRUCT-Feldnamen und -Typen den in der SELECT-Liste erzeugten Spaltennamen und -typen.

Beispiel:

SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)

SELECT AS STRUCT kann in einer skalaren oder Array-Unterabfrage verwendet werden, um einen einzelnen STRUCT-Typ zu erstellen, der mehrere Werte zusammenfasst. Skalare Unterabfragen und Array-Unterabfragen (siehe Unterabfragen) dürfen normalerweise nicht mehrere Spalten zurückgeben, können aber eine einzelne Spalte mit dem STRUCT-Typ zurückgeben.

Anonyme Spalten sind zulässig.

Beispiel:

SELECT AS STRUCT 1 x, 2, 3

Die obige Abfrage erzeugt STRUCT-Werte vom Typ STRUCT<int64 x, int64, int64>.. Das erste Feld hat den Namen x, das zweite und dritte Felder sind anonym.

Im obigen Beispiel wird das gleiche Ergebnis ausgegeben wie durch die Abfrage SELECT AS VALUE mit einem Struct Constructor:

SELECT AS VALUE STRUCT(1 AS x, 2, 3)

Doppelte Spalten sind zulässig.

Beispiel:

SELECT AS STRUCT 1 x, 2 y, 3 x

Die obige Abfrage erzeugt STRUCT-Werte vom Typ STRUCT<int64 x, int64 y, int64 x>.. Das erste und dritte Feld haben den gleichen Namen (x), das zweite Feld den Namen y.

Im obigen Beispiel wird das gleiche Ergebnis ausgegeben wie durch die Abfrage SELECT AS VALUE mit einem Struct Constructor:

SELECT AS VALUE STRUCT(1 AS x, 2 AS y, 3 AS x)

SELECT AS VALUE

SELECT AS VALUE erzeugt eine Wertetabelle aus jeder SELECT-Liste, die genau eine Spalte erzeugt. Anstelle einer Ausgabetabelle mit einer einzigen Spalte (möglicherweise mit einem Namen) wird eine Wertetabelle erzeugt, deren Zeilentyp dem Werttyp entspricht, der in der SELECT-Spalte erzeugt wurde. Jeder Alias, den die Spalte hatte, wird in der Wertetabelle verworfen.

Beispiel:

SELECT AS VALUE 1

Die obige Abfrage erzeugt eine Tabelle mit dem Zeilentyp INT64.

Beispiel:

SELECT AS VALUE STRUCT(1 AS a, 2 AS b) xyz

Die obige Abfrage erzeugt eine Tabelle mit dem Zeilentyp STRUCT<a int64, b int64>.

Beispiel:

SELECT AS VALUE v FROM (SELECT AS STRUCT 1 a, true b) v WHERE v.b

Bei einer Wertetabelle v als Eingabe filtert die obige Abfrage bestimmte Werte in der WHERE-Klausel heraus und erzeugt dann eine Wertetabelle mit genau demselben Wert wie in der Eingabetabelle. Wenn in der obigen Abfrage SELECT AS VALUE nicht verwendet wird, unterscheidet sich das Ausgabetabellenschema von dem Eingabetabellenschema, da die Ausgabetabelle eine reguläre Tabelle mit einer Spalte namens v sein würde, die 101}Eingabewert.

Aliasse

Unter Aliasse verwenden finden Sie Informationen zu Syntax und Sichtbarkeit für Aliasse in der Liste SELECT.

FROM-Klausel

FROM from_clause[, ...]

from_clause:
    from_item
    [ tablesample_operator ]

from_item:
    {
      table_name [ table_hint_expr ] [ as_alias ]
      | join_operation
      | ( query_expr ) [ table_hint_expr ] [ as_alias ]
      | field_path
      | unnest_operator
      | with_query_name [ table_hint_expr ] [ as_alias ]
    }

table_hint_expr:
    '@{' table_hint_key = table_hint_value '}'

table_hint_key:
    { FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION }

as_alias:
    [ AS ] alias

Die FROM-Klausel gibt die Tabelle bzw. die Tabellen an, aus denen Zeilen abgerufen werden sollen, und legt fest, wie diese Zeilen verknüpft werden sollen, um einen einzelnen Stream an Zeilen für die Verarbeitung in der restlichen Abfrage zu erzeugen.

tablesample_operator

Siehe TABLESAMPLE-Operator.

table_name

Der Name einer vorhandenen Tabelle.

SELECT * FROM Roster;

Tabellenhinweise

Die folgenden Hinweise werden für Tabellen unterstützt:

Hint-Taste	Mögliche Werte	Beschreibung
FORCE_INDEX	String. Der Name eines vorhandenen Indexes in der Datenbank oder _BASE_TABLE, damit die Basistabelle anstelle eines Indexes verwendet wird.	Verwenden Sie diesen Index anstelle der Basistabelle, wenn er auf den Namen eines Indexes festgelegt ist. Wenn der Index nicht alle benötigten Spalten bereitstellen kann, führen Sie eine Back-Join mit der Basistabelle durch. Wenn der String _BASE_TABLE eingestellt ist, verwenden Sie anstatt eines Indexes die Basistabelle für die Indexstrategie. Beachten Sie, dass dies der einzige gültige Wert ist, wenn FORCE_INDEX in einem Anweisungshinweis verwendet wird. Hinweis: FORCE_INDEX ist eigentlich eine Anweisung und kein Hinweis. Das bedeutet, dass ein Fehler angezeigt wird, wenn der Index nicht existiert.
GROUPBY_SCAN_OPTIMIZATION	TRUE FALSE	Die Group-by-Scan-Optimierung kann Abfragen beschleunigen, wenn sie GROUP BY oder SELECT DISTINCT verwenden. Dies kann angewendet werden, wenn die Gruppierungsschlüssel ein Präfix der zugrunde liegenden Tabelle oder des zugrunde liegenden Indexschlüssels bilden oder genau mit diesen übereinstimmen können und die Abfrage nur die erste Zeile jeder Gruppe erfordert. Diese Optimierung wird normalerweise angewendet, wenn das Optimierungsprogramm entscheidet, dass dadurch die Abfrage effizienter wird. Der Hinweis setzt diese Entscheidung außer Kraft. Wenn der Hinweis auf FALSE gesetzt ist, wird die Optimierung nicht berücksichtigt. Wenn der Hinweis auf TRUE gesetzt ist, wird die Optimierung angewendet, solange dies zulässig ist.

Im folgenden Beispiel wird gezeigt, wie beim Lesen aus einer Tabelle ein sekundärer Index verwendet wird. Dabei wird eine Indexanweisung in der Form @{FORCE_INDEX=index_name} an den Tabellennamen angehängt:

SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo
FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s
WHERE s.FirstName = "Catalina" AND s.LastName > "M";

Sie können mehrere Indexe in einer Abfrage berücksichtigen, auch wenn nur ein einzelner Index für jeden eindeutigen Tabellenverweis unterstützt wird. Beispiel:

SELECT s.SingerId, s.FirstName, s.LastName, s.SingerInfo, c.ConcertDate
FROM Singers@{FORCE_INDEX=SingersByFirstLastName} AS s JOIN
     Concerts@{FORCE_INDEX=ConcertsBySingerId} AS c ON s.SingerId = c.SingerId
WHERE s.FirstName = "Catalina" AND s.LastName > "M";

Weitere Informationen zu Indexanweisungen erhalten Sie im Abschnitt "Sekundärindexe".

join_operation

Siehe JOIN-Vorgang.

query_expr

( query_expr ) [ [ AS ] alias ] ist die Unterabfrage einer Tabelle.

field_path

In der FROM-Klausel ist field_path jeder Pfad, der in ein Feld innerhalb eines Datentyps aufgelöst wird. field_path kann beliebig tief in eine verschachtelte Datenstruktur führen.

Beispiele für gültige field_path-Werte:

SELECT * FROM T1 t1, t1.array_column;

SELECT * FROM T1 t1, t1.struct_column.array_field;

SELECT (SELECT ARRAY_AGG(c) FROM t1.array_column c) FROM T1 t1;

SELECT a.struct_field1 FROM T1 t1, t1.array_of_structs a;

SELECT (SELECT STRING_AGG(a.struct_field1) FROM t1.array_of_structs a) FROM T1 t1;

Feldpfade in der FROM-Klausel müssen mit einem Array-Feld enden. Außerdem dürfen Feldpfade keine Arrays vor dem Ende des Pfads enthalten. Der Pfad array_column.some_array.some_array_field ist beispielsweise ungültig, da er vor dem Ende des Pfads ein Array enthält.

Hinweis: Wenn der Pfad nur einen einzigen Namen hat, wird er als Tabelle interpretiert. Umschließen Sie zur Umgehung dieses Problems den Pfad mithilfe von UNNEST oder verwenden Sie den voll qualifizierten Pfad.

Hinweis: Wenn der Pfad mehr als einen Namen hat und dieser mit einem Feldnamen übereinstimmt, wird er als Feldname interpretiert. Sie sorgen dafür, dass der Pfad als Tabellenname interpretiert wird, wenn Sie den Pfad mit ` umschließen.

unnest_operator

Siehe UNNEST-Operator.

with_query_name

Die Abfragenamen in einer WITH-Klausel (siehe WITH-Klausel) verhalten sich wie Namen von temporären Tabellen, auf die Sie an beliebiger Stelle in der FROM-Klausel verweisen können. Im Beispiel unten sind subQ1 und subQ2 with_query_names.

Beispiel:

WITH
  subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
  subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;

Die WITH-Klausel blendet permanente Tabellen mit dem gleichen Namen während der Dauer der Abfrage aus, es sei denn, Sie qualifizieren den Tabellennamen. Beispiel:

db.Roster.

UNNEST-Operator

unnest_operator:
    {
      UNNEST( array_expression )
      | UNNEST( array_path )
      | array_path
    }
    [ table_hint_expr ]
    [ as_alias ]
    [ WITH OFFSET [ as_alias ] ]

Der Operator UNNEST nimmt ein ARRAY und gibt eine Tabelle mit einer Zeile je Element im ARRAY zurück. Sie können UNNEST aber auch außerhalb der FROM-Klausel mit dem IN-Operator verwenden.

Bei Eingabe-ARRAYs der meisten Elementtypen enthält die Ausgabe von UNNEST in der Regel eine Spalte. Diese eine Spalte hat einen optionalen alias, mit dem Sie an anderer Stelle in der Abfrage auf die Spalte verweisen können. ARRAYS mit folgenden Elementtypen geben mehrere Spalten zurück:

• STRUCT

UNNEST löst die Reihenfolge der Elemente im Eingabe-ARRAY auf. Verwenden Sie die optionale WITH OFFSET-Klausel, um eine zweite Spalte mit den Array-Elementindexen zurückzugeben (siehe unten).

Verschiedene Möglichkeiten zur Verwendung von UNNEST, einschließlich Konstruktion, vereinfachte Darstellung und Filterung, finden Sie unter Working with arrays.

UNNEST und STRUCTs

Für ein Eingabe-ARRAY mit STRUCT-Elementen gibt UNNEST eine Zeile für jedes Element STRUCT mit einer separaten Spalte für jedes Feld in dem Element STRUCT zurück. Der Alias für jede Spalte ist der Name des entsprechenden Felds im Element STRUCT.

Beispiel:

SELECT *
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')]);

+---+-----+
| x | y   |
+---+-----+
| 3 | bar |
| 1 | foo |
+---+-----+

Da der Operator UNNEST eine Wertetabelle zurückgibt, können Sie durch Angabe eines Alias für UNNEST eine Bereichsvariable definieren, auf die Sie an anderer Stelle in der Abfrage verweisen können. Wenn Sie auf eine Bereichsvariable in der Liste SELECT verweisen, gibt die Abfrage ein STRUCT-Element zurück, das alle Felder des ursprünglichen Elements STRUCT in der Eingabetabelle enthält.

Beispiel:

SELECT *, struct_value
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')])
       AS struct_value;

+---+-----+--------------+
| x | y   | struct_value |
+---+-----+--------------+
| 3 | bar | {3, bar}     |
| 1 | foo | {1, foo}     |
+---+-----+--------------+

Explizite und implizite UNNEST

Das Auflösen der Verschachtelung eines ARRAY kann explizit oder implizit erfolgen. Beim expliziten Auflösen einer Verschachtelung muss array_expression einen ARRAY-Wert zurückgeben. Es muss aber nicht in ein ARRAY aufgelöst werden. Das Schlüsselwort UNNEST ist erforderlich.

Beispiel:

SELECT * FROM UNNEST ([1, 2, 3]);

Beim impliziten Auflösen einer Verschachtelung muss array_path in ein ARRAY aufgelöst werden. Das Schlüsselwort UNNEST ist optional.

Beispiel:

SELECT x
FROM mytable AS t,
  t.struct_typed_column.array_typed_field1 AS x;

In diesem Szenario kann array_path beliebig tief in eine Datenstruktur führen. Allerdings muss das letzte Feld vom Typ ARRAY sein. Es kann kein vorheriges Feld im Ausdruck vom Typ ARRAY verwendet werden, da es nicht möglich ist, ein benanntes Feld aus einem ARRAY zu extrahieren.

UNNEST und NULLs

UNNEST verarbeitet den Wert NULL folgendermaßen:

• NULL und leere Arrays erzeugen null Zeilen.
• Ein Array, das NULL-Werte enthält, erzeugt Zeilen, die NULL-Werte enthalten.

Die optionale WITH OFFSET-Klausel gibt eine separate Spalte zurück, die den „Offset“-Wert (d. h., die Zählung beginnt bei null) für jede Zeile enthält, die von dem UNNEST-Vorgang erzeugt wird. Diese Spalte hat einen optionalen alias. Der Standardalias ist „offset“.

Beispiel:

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

Operator TABLESAMPLE

tablesample_clause:
    TABLESAMPLE sample_method (sample_size percent_or_rows )

sample_method:
    { BERNOULLI | RESERVOIR }

sample_size:
    numeric_value_expression

percent_or_rows:
    { PERCENT | ROWS }

partition_by:
    PARTITION BY partition_expression [, ...]

Beschreibung

Mit dem Operator TABLESAMPLE können Sie eine Stichprobe eines Datensatzes auswählen. Dieser Operator ist nützlich, wenn Sie mit Tabellen arbeiten, die große Datenmengen enthalten, und Sie keine genauen Antworten benötigen.

• sample_method: Wenn Sie den Operator TABLESAMPLE verwenden, müssen Sie den Stichprobenalgorithmus angeben:
  • BERNOULLI: Jede Zeile wird individuell mit der Wahrscheinlichkeit ausgewählt, die in der Klausel percent angegeben ist. Als Ergebnis erhalten Sie ungefähr N * percent/100 Zeilen.
  • RESERVOIR: Nimmt als Parameter eine tatsächliche Stichprobengröße K (ausgedrückt als Anzahl von Reihen). Wenn die Eingabe kleiner als K ist, wird die gesamte Eingabebeziehung ausgegeben. Ist die Eingabe dagegen größer als K, wird eine Stichprobe genau der Größe K ausgegeben, wobei jede Stichprobe der Größe K gleich wahrscheinlich ist.
• sample_size: Die Größe der Stichprobe.
• percent_or_rows: Beim Operator TABLESAMPLE müssen Sie ROWS oder PERCENT auswählen. Wenn Sie PERCENT wählen, muss der Wert zwischen 0 und 100 liegen. Bei ROWS muss der Wert größer oder gleich 0 sein.

Beispiele

In den folgenden Beispielen wird die Verwendung des Operators TABLESAMPLE veranschaulicht.

Auswahl aus einer Tabelle mit der Stichprobenmethode RESERVOIR:

SELECT MessageId
FROM Messages TABLESAMPLE RESERVOIR (100 ROWS);

Auswahl aus einer Tabelle mit der Stichprobenmethode BERNOULLI:

SELECT MessageId
FROM Messages TABLESAMPLE BERNOULLI (0.1 PERCENT);

Verwenden Sie TABLESAMPLE mit einer Unterabfrage:

SELECT Subject FROM
(SELECT MessageId, Subject FROM Messages WHERE ServerId="test")
TABLESAMPLE BERNOULLI(50 PERCENT)
WHERE MessageId > 3;

Verwenden Sie einen TABLESAMPLE-Vorgang mit einem Join zu einer anderen Tabelle.

SELECT S.Subject
FROM
(SELECT MessageId, ThreadId FROM Messages WHERE ServerId="test") AS R
TABLESAMPLE RESERVOIR(5 ROWS),
Threads AS S
WHERE S.ServerId="test" AND R.ThreadId = S.ThreadId;

JOIN-Vorgang

join_operation:
    { cross_join_operation | join_operation_with_condition }

cross_join_operation:
    from_item CROSS JOIN [ join_hint_expr ] from_item

join_operation_with_condition:
    from_item [ join_type ] [ join_method ] JOIN [ join_hint_expr ] from_item
    [ { on_clause | using_clause } ]

join_type:
    { [INNER] | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

join_method:
    { HASH }

join_hint_expr:
    '@{' join_hint_key = join_hint_value [, ...] '}'

join_hint_key:
    { FORCE_JOIN_ORDER | JOIN_METHOD }

on_clause:
    ON bool_expression

using_clause:
    USING ( join_column [, ...] )

Der JOIN-Vorgang führt zwei from_item-Elemente zusammen, damit die SELECT-Klausel diese Elemente als einzelne Quelle abfragen kann. Die Klauseln join_type und ON oder USING (eine "Join-Bedingung") geben an, wie Zeilen aus den beiden from_item-Elementen kombiniert oder verworfen werden, um eine einzelne Quelle zu bilden.

Alle JOIN-Vorgänge erfordern einen join_type. Wenn kein join_type mit einem JOIN-Vorgang angegeben wird, wird ein INNER JOIN ausgeführt.

Ein JOIN-Vorgang erfordert eine Join-Bedingung, es sei denn, eine der folgenden Bedingungen ist wahr:

• join_type ist CROSS.
• Bei einem oder beiden from_item-Elementen handelt es sich nicht um eine Tabelle, z. B. ein array_path oder field_path.

Join-Hinweise

Die folgenden Hinweise werden für JOIN unterstützt:

Hint-Taste	Mögliche Werte	Beschreibung
FORCE_JOIN_ORDER	TRUE FALSE (Standardeinstellung)	Wenn diese Option auf "true" festgelegt ist, verwenden Sie die in der Abfrage angegebene Join-Reihenfolge.
JOIN_METHOD	HASH_JOIN APPLY_JOIN	Wählen Sie bei der Implementierung eines logischen Joins eine bestimmte Alternative aus, die für die zugrundeliegende Join-Methode verwendet werden soll. Weitere Informationen finden Sie im Abschnitt "Join-Methoden". Für einen HASH-Join verwenden Sie entweder HASH JOIN oder JOIN@{JOIN_METHOD=HASH_JOIN}, aber nicht beides.
HASH_JOIN_BUILD_SIDE	BUILD_LEFT BUILD_RIGHT	Gibt an, welche Seite des Hash-Joins als Build-Seite verwendet wird. Kann nur mit JOIN_METHOD=HASH_JOIN verwendet werden
BATCH_MODE	TRUE (default) FALSE	Wird verwendet, um einen Batch-Join für einen zeilenweisen Apply-Join zu deaktivieren. Kann nur mit JOIN_METHOD=APPLY_JOIN verwendet werden.

Join-Methoden

Join-Methoden sind spezifische Implementierungen der verschiedenen logischen Join-Typen. Einige Join-Methoden sind nur für bestimmte Join-Typen verfügbar. Die Auswahl der zu verwendenden Join-Methode hängt von den Besonderheiten Ihrer Abfrage und der abgefragten Daten ab. Die beste Möglichkeit herauszufinden, ob eine bestimmte Join-Methode bei der Leistung Ihrer Abfrage hilfreich ist, besteht darin, die Methode auszuprobieren und den resultierenden Abfrageausführungsplan anzuzeigen. Weitere Informationen finden Sie unter Abfrageausführungsoperatoren, insbesondere in den Abschnitten zu den Operatoren "Apply" und "Hash Join".

Join-Methode	Beschreibung	Operanden
HASH_JOIN	Der Hash-Join-Operator erstellt eine Hash-Tabelle von einer Seite (der Build-Seite) und Prüfungen in der Hash-Tabelle für alle Elemente auf der anderen Seite (der Prüfungsseite).	Für verschiedene Join-Typen werden verschiedene Varianten verwendet. Zeigen Sie den Abfrage-Ausführungsplan für Ihre Abfrage an, um zu sehen, welche Variante verwendet wird. Weitere Informationen erhalten Sie im Abschnitt "Hash-Join-Operator".
APPLY_JOIN	Der Apply-Join-Operator ruft jedes Element von einer Seite (der Eingabeseite) ab und wertet die Unterabfrage auf der anderen Seite (der Kartenseite) unter Verwendung der Werte des Elements von der Eingabeseite aus.	Für verschiedene Join-Typen werden verschiedene Varianten verwendet. Cross Apply wird für den Inner Join verwendet und Outer Apply für Left Joins. Weitere Informationen finden Sie in den Abschnitten zu den Operatoren Cross Apply und Outer Apply.

[INNER] JOIN

Ein INNER JOIN (oder einfach nur JOIN) berechnet effektiv das kartesische Produkt der beiden from_item-Elemente und verwirft alle Zeilen, die nicht mit der Join-Bedingung übereinstimmen. "Effektiv" bedeutet, dass ein INNER JOIN implementiert werden kann, ohne tatsächlich das kartesische Produkt zu berechnen.

FROM A INNER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | k |     | 2 | b | 2 | k |
| 2 | b |     | 3 | m |     | 3 | c | 3 | m |
| 3 | c |     | 3 | n |     | 3 | c | 3 | n |
| 3 | d |     | 4 | p |     | 3 | d | 3 | m |
+-------+     +-------+     | 3 | d | 3 | n |
                            +---------------+

FROM A INNER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +-----------+
| x | y |  *  | x | z |  =  | x | y | z |
+-------+     +-------+     +-----------+
| 1 | a |     | 2 | k |     | 2 | b | k |
| 2 | b |     | 3 | m |     | 3 | c | m |
| 3 | c |     | 3 | n |     | 3 | c | n |
| 3 | d |     | 4 | p |     | 3 | d | m |
+-------+     +-------+     | 3 | d | n |
                            +-----------+

Beispiel

Diese Abfrage führt ein INNER JOIN für die Tabellen Roster und TeamMascot aus.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

CROSS JOIN

CROSS JOIN gibt das kartesische Produkt der beiden from_item-Elemente zurück. Anders gesagt: Jede Zeile aus dem ersten from_item-Element wird mit jeder Zeile aus dem zweiten from_item-Element kombiniert.

Wenn die Zeilen der beiden from_item-Elemente unabhängig sind, hat das Ergebnis M * N Zeilen, wenn ein from_item-Element M Zeilen und das andere N Zeilen umfasst. Dies gilt auch für den Fall, dass eines der from_item-Elemente null Zeilen enthält.

FROM A CROSS JOIN B

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+

Sie können korrelierte CROSS JOINs verwenden, um ARRAY-Spalten zu vereinfachen. In diesem Fall variieren die Zeilen des zweiten from_item-Elements für jede Zeile des ersten from_item-Elements.

FROM A CROSS JOIN A.y

Table A                    Result
+-------------------+      +-----------+
| w | x | y         |  ->  | w | x | y |
+-------------------+      +-----------+
| 1 | a | [P, Q]    |      | 1 | a | P |
| 2 | b | [R, S, T] |      | 1 | a | Q |
+-------------------+      | 2 | b | R |
                           | 2 | b | S |
                           | 2 | b | T |
                           +-----------+

CROSS JOINs können explizit so geschrieben werden:

FROM a CROSS JOIN b

Oder implizit als durch Kommas getrennter Cross-Join:

FROM a, b

Durch Kommas getrennte Cross Joins dürfen nicht in runde Klammern geschrieben werden:

FROM a CROSS JOIN (b, c)  // INVALID

Weitere Informationen zum Verhalten eines durch Komma getrennten Kreuzprodukts in einer Join-Sequenz finden Sie unter JOIN-Sequenzen.

Beispiele

Diese Abfrage führt einen expliziten CROSS JOIN für die Tabellen Roster und TeamMascot aus.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Adams      | Knights      |
| Adams      | Lakers       |
| Adams      | Mustangs     |
| Buchanan   | Jaguars      |
| Buchanan   | Knights      |
| Buchanan   | Lakers       |
| Buchanan   | Mustangs     |
| ...                       |
+---------------------------+

Diese Abfrage führt einen durch Kommas getrennten Cross Join durch, der dieselben Ergebnisse wie der explizite CROSS JOIN oben liefert:

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;

FULL [OUTER] JOIN

Ein FULL OUTER JOIN (oder einfach nur FULL JOIN) gibt alle Felder für alle Zeilen in beiden from_item-Elementen zurück, die mit der Join-Bedingung übereinstimmen.

FULL gibt an, dass alle Zeilen aus beiden from_item-Elementen zurückgegeben werden, selbst wenn sie die Join-Bedingung nicht erfüllen.

OUTER gibt Folgendes an: Wenn eine bestimmte Zeile aus einem from_item mit keiner Zeile im anderen from_item übereinstimmt, gibt die Zeile den Wert NULL für alle Spalten aus dem anderen from_item zurück.

FROM A FULL OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+

FROM A FULL OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

Beispiel

Diese Abfrage führt ein FULL JOIN für die Tabellen Roster und TeamMascot aus.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster FULL JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
| NULL       | Mustangs     |
+---------------------------+

LEFT [OUTER] JOIN

Das Ergebnis eines LEFT OUTER JOIN (oder einfach nur LEFT JOIN) für zwei from_item-Elemente behält immer alle Zeilen des from_item auf der linken Seite des JOIN-Vorgangs bei, selbst wenn keine Zeilen im from_item auf der rechten Seite dem JOIN-Prädikat entsprechen.

LEFT gibt an, dass alle Zeilen des linken from_item zurückgegeben werden. Wenn eine bestimmte Zeile des linken from_item mit keiner Zeile im rechten from_item übereinstimmt, gibt die Zeile für alle Spalten aus dem rechten from_item den Wert NULL zurück. Zeilen vom rechten from_item, die mit keiner Zeile im linken from_item übereinstimmen, werden verworfen.

FROM A LEFT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            +---------------------------+

FROM A LEFT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            +--------------------+

Beispiel

Diese Abfrage führt ein LEFT JOIN für die Tabellen Roster und TeamMascot aus.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster LEFT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
+---------------------------+

RIGHT [OUTER] JOIN

Das Ergebnis von RIGHT OUTER JOIN (oder einfach nur RIGHT JOIN) ist dem Ergebnis von LEFT OUTER JOIN ähnlich und verhält sich symmetrisch.

FROM A RIGHT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 2    | b    | 2    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | 3    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | 3    | m    |
+-------+     +-------+     | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+

FROM A RIGHT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 2    | b    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | m    |
+-------+     +-------+     | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

Beispiel

Diese Abfrage führt ein RIGHT JOIN für die Tabellen Roster und TeamMascot aus.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster RIGHT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| NULL       | Mustangs     |
+---------------------------+

ON-Klausel

Die ON-Klausel enthält einen bool_expression. Eine kombinierte Zeile (das Ergebnis aus dem Join von zwei Zeilen) stimmt mit der Join-Bedingung überein, wenn bool_expression den Wert TRUE zurückgibt.

FROM A JOIN B ON A.x = B.x

Table A   Table B   Result (A.x, B.x)
+---+     +---+     +-------+
| x |  *  | x |  =  | x | x |
+---+     +---+     +-------+
| 1 |     | 2 |     | 2 | 2 |
| 2 |     | 3 |     | 3 | 3 |
| 3 |     | 4 |     +-------+
+---+     +---+

Beispiel

Diese Abfrage führt ein INNER JOIN für die Tabellen Roster und TeamMascot aus.

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

USING-Klausel

Die USING-Klausel erfordert eine column_list einer oder mehrerer Spalten, die in beiden Eingabetabellen vorkommen. Sie führt einen Übereinstimmungsvergleich an dieser Spalte durch. Die Zeilen stimmen mit der Join-Bedingung überein, wenn der Übereinstimmungsvergleich den Wert TRUE zurückgibt.

FROM A JOIN B USING (x)

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

Beispiel

Diese Abfrage führt ein INNER JOIN für die Tabellen Roster und TeamMascot aus.

Diese Anweisung gibt die Zeilen aus Roster und TeamMascot zurück, wobei Roster.SchooldID mit TeamMascot.SchooldID identisch ist. Die Ergebnisse enthalten eine einzelne SchooldID-Spalte.

SELECT * FROM Roster INNER JOIN TeamMascot USING (SchoolID);

+----------------------------------------+
| SchoolID   | LastName   | Mascot       |
+----------------------------------------+
| 50         | Adams      | Jaguars      |
| 52         | Buchanan   | Lakers       |
| 52         | Coolidge   | Lakers       |
| 51         | Davis      | Knights      |
+----------------------------------------+

Äquivalenz von ON und USING

Die Schlüsselwörter ON und USING sind nicht äquivalent, aber ähnlich. ON gibt mehrere Spalten zurück, während USING eine zurückgibt.

FROM A JOIN B ON A.x = B.x
FROM A JOIN B USING (x)

Table A   Table B   Result ON     Result USING
+---+     +---+     +-------+     +---+
| x |  *  | x |  =  | x | x |     | x |
+---+     +---+     +-------+     +---+
| 1 |     | 2 |     | 2 | 2 |     | 2 |
| 2 |     | 3 |     | 3 | 3 |     | 3 |
| 3 |     | 4 |     +-------+     +---+
+---+     +---+

Obwohl ON und USING nicht äquivalent sind, geben sie dieselben Ergebnisse zurück, wenn Sie die zurückzugebenden Spalten angeben.

SELECT x FROM A JOIN B USING (x);
SELECT A.x FROM A JOIN B ON A.x = B.x;

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

JOIN-Sequenzen

Die FROM-Klausel kann mehrere JOIN-Vorgänge in einer Sequenz enthalten. JOINs sind von links nach rechts gebunden. Beispiel:

FROM A JOIN B USING (x) JOIN C USING (x)

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

Sie können auch Klammern verwenden, um JOINs zu gruppieren:

FROM ( (A JOIN B USING (x)) JOIN C USING (x) )

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

Mit Klammern können Sie JOINs so gruppieren, dass sie in einer anderen Reihenfolge gebunden sind:

FROM ( A JOIN (B JOIN C USING (x)) USING (x) )

-- B JOIN C USING (x)       = result_1
-- A JOIN result_1          = result_2
-- result_2                 = return value

Wenn durch Komma getrennte Kreuzprodukte in einer Abfrage mit einer JOIN-Sequenz vorhanden sind, werden diese von links nach rechts gruppiert wie andere JOIN-Typen auch:

FROM A JOIN B USING (x) JOIN C USING (x), D

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 CROSS JOIN D     = return value

Nach einem durch Kommas getrennten Join darf kein RIGHT JOIN oder FULL JOIN stehen:

FROM A, B RIGHT JOIN C ON TRUE // INVALID

FROM A, B FULL JOIN C ON TRUE  // INVALID

FROM A, B JOIN C ON TRUE       // VALID

WHERE-Klausel

WHERE bool_expression

Die Klausel WHERE filtert die Ergebnisse der Klausel FROM.

Es werden nur Zeilen einbezogen, deren bool_expression als TRUE ausgewertet wird. Zeilen, deren bool_expression als NULL oder FALSE ausgewertet wird, werden verworfen.

Die Auswertung einer Abfrage mit einer WHERE-Klausel wird in der Regel in dieser Reihenfolge ausgeführt:

• FROM
• WHERE
• GROUP BY und Aggregation
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

Die WHERE-Klausel kann nur auf Spalten verweisen, die über die FROM-Klausel verfügbar sind, und kann nicht auf SELECT-Listenaliasse verweisen.

Beispiele

Diese Abfrage gibt alle Zeilen aus der Tabelle Roster zurück, in denen die Spalte SchoolID den Wert 52 enthält:

SELECT * FROM Roster
WHERE SchoolID = 52;

bool_expression kann mehrere Unterbedingungen enthalten:

SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");

Ausdrücke in einem INNER JOIN haben einen entsprechenden Ausdruck in der WHERE-Klausel. Eine Abfrage mit INNER JOIN und ON hat beispielsweise einen entsprechenden Ausdruck mit CROSS JOINund WHERE. Die folgenden beiden Abfragen sind beispielsweise äquivalent:

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;

GROUP BY-Klausel

GROUP BY expression [, ...]

Die GROUP BY-Klausel fasst Zeilen in einer Tabelle mit nicht eindeutigen Werten für expression in der GROUP BY-Klausel zusammen. Wenn in der Quelltabelle mehrere Zeilen nicht eindeutige Werte für expression haben, werden diese mit der GROUP BY-Klausel zu einer einzelnen Zeile zusammengefasst. GROUP BY wird im Allgemeinen verwendet, wenn in der SELECT-Liste Aggregatfunktionen vorhanden sind oder Redundanzen in der Ausgabe ausgeschlossen werden sollen. Der Datentyp von expression muss gruppierbar sein.

Beispiel:

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;

Die GROUP BY-Klausel kann sich auf Ausdrucksnamen in der SELECT-Liste beziehen. Die GROUP BY-Klausel lässt auch die ordinalen Verweise auf Ausdrücke in der SELECT-Liste unter Verwendung von Ganzzahlen zu. Der Wert 1 verweist auf den ersten Ausdruck in der SELECT-Liste, der Wert 2 auf den zweiten Ausdruck usw. Sie können Ordinalzahlen und Ausdrucksnamen in der Ausdrucksliste kombinieren.

Beispiel:

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY LastName, FirstName;

Die Abfrage oben entspricht der folgenden Abfrage:

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY 2, FirstName;

GROUP BY-Klauseln können auch auf Aliasse verweisen. Wenn eine Abfrage Aliasse in der SELECT-Klausel enthält, überschreiben diese Aliasse die Namen in der entsprechenden FROM-Klausel.

Beispiel:

SELECT SUM(PointsScored), LastName as last_name
FROM PlayerStats
GROUP BY last_name;

HAVING-Klausel

HAVING bool_expression

Die Klausel HAVING filtert die Ergebnisse, die von GROUP BY oder der Aggregation generiert wurden. GROUP BY oder eine Aggregation muss in der Abfrage vorhanden sein. Wenn die Aggregation vorhanden ist, wird die HAVING-Klausel einmal für jede aggregierte Zeile in der Ergebnismenge ausgewertet.

Es werden nur Zeilen einbezogen, deren bool_expression als TRUE ausgewertet wird. Zeilen, deren bool_expression als NULL oder FALSE ausgewertet wird, werden verworfen.

Die Auswertung einer Abfrage mit einer HAVING-Klausel wird in der Regel in dieser Reihenfolge ausgeführt:

• FROM
• WHERE
• GROUP BY und Aggregation
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

Die HAVING-Klausel kann auf Spalten, die über die FROM-Klausel verfügbar sind, sowie auf Aliasse in der SELECT-Liste verweisen. Ausdrücke, auf die in der HAVING-Klausel verwiesen wird, müssen entweder in der GROUP BY-Klausel auftreten oder das Ergebnis einer Aggregatfunktion sein:

SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

Wenn eine Abfrage Aliasse in der SELECT-Klausel enthält, setzen diese Aliasse die Namen in einer FROM-Klausel außer Kraft.

SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;

Obligatorische Aggregation

Eine Aggregation muss nicht in der HAVING-Klausel selbst vorhanden sein, sie muss aber mindestens in einer der folgenden Formen vorliegen:

Aggregatfunktion in der SELECT-Liste

SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;

Aggregatfunktion in der HAVING-Klausel

SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

Aggregation in der SELECT-Liste und HAVING-Klausel

Wenn Aggregatfunktionen sowohl in der SELECT-Liste als auch der HAVING-Klausel vorhanden sind, müssen die Aggregatfunktionen und die Spalten, auf die sie verweisen, nicht miteinander übereinstimmen. Im folgenden Beispiel unterscheiden sich die beiden Aggregatfunktionen COUNT() und SUM() voneinander; sie verwenden auch unterschiedliche Spalten.

SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

ORDER BY-Klausel

ORDER BY expression
  [{ ASC | DESC }]
  [, ...]

Die ORDER BY-Klausel gibt eine Spalte oder einen Ausdruck als Sortierkriterium für die Ergebnismenge an. Wenn keine ORDER BY-Klausel vorhanden ist, ist die Reihenfolge der Ergebnisse einer Abfrage nicht festgelegt. Spaltenaliasse einer FROM-Klausel oder SELECT-Liste sind zulässig. Wenn eine Abfrage Aliasse in der SELECT-Klausel enthält, überschreiben diese Aliasse die Namen in der entsprechenden FROM-Klausel.

Optionale Klauseln

• ASC | DESC: sortiert die Ergebnisse in aufsteigender oder absteigender Reihenfolge von expression-Werten. ASC ist der Standardwert.

Beispiele

Verwenden Sie die Standardsortierreihenfolge (aufsteigend).

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true)
ORDER BY x;
+------+-------+
| x    | y     |
+------+-------+
| 1    | true  |
| 9    | true  |
+------+-------+

Verwenden Sie die absteigende Sortierreihenfolge.

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true)
ORDER BY x DESC;
+------+-------+
| x    | y     |
+------+-------+
| 9    | true  |
| 1    | true  |
+------+-------+

Eine Sortierung nach mehreren Spalten ist möglich. Im folgenden Beispiel wird die Ergebnismenge zuerst nach SchoolID und dann nach LastName sortiert:

SELECT LastName, PointsScored, OpponentID
FROM PlayerStats
ORDER BY SchoolID, LastName;

Die folgenden Regeln gelten beim Sortieren von Werten:

• NULL-Werte: In Bezug auf die ORDER BY-Klausel sind NULL-Werte der kleinstmögliche Wert. NULL-Werte werden also in ASC-Sortierungen als Erstes und in DESC-Sortierungen als Letztes angezeigt.
• Gleitkommadatentypen: Weitere Informationen zur Sortierung und Gruppierung für diese Datentypen finden Sie unter Gleitkommasemantik.

Wenn die ORDER BY-Klausel gemeinsam mit Set-Operatoren verwendet wird, gilt sie für die Ergebnismenge der gesamten Abfrage und nicht nur für die nächstgelegene SELECT-Anweisung. Aus diesem Grund kann es nützlich sein (obwohl nicht erforderlich), runde Klammern zu verwenden, um die Abgrenzung des Bereichs von ORDER BY anzuzeigen.

Die folgende Abfrage ohne runde Klammern:

SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot
ORDER BY SchoolID;

entspricht der folgenden Abfrage mit runden Klammern:

( SELECT * FROM Roster
  UNION ALL
  SELECT * FROM TeamMascot )
ORDER BY SchoolID;

Sie entspricht allerdings nicht der folgenden Abfrage, bei der die ORDER BY-Klausel nur für die zweite SELECT-Anweisung gilt:

SELECT * FROM Roster
UNION ALL
( SELECT * FROM TeamMascot
  ORDER BY SchoolID );

Sie können auch Ganzzahlliterale als Spaltenverweise in ORDER BY-Klauseln verwenden. Ein Ganzzahlliteral wird zur Ordnungszahl (z. B. Zählung beginnt bei 1) in der SELECT-Liste.

Die folgenden beiden Abfragen entsprechen beispielsweise einander:

SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY LastName;

SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY 2;

COLLATE

Mit der Klausel COLLATE können Sie verfeinern, wie Daten nach einer ORDER BY-Klausel sortiert werden sollen. Die Sortierung bezieht sich auf eine Reihe von Regeln, die bestimmen, wie STRINGs gemäß den Konventionen und Standards einer bestimmten Sprache, Region oder eines bestimmten Landes verglichen werden. Diese Regeln definieren möglicherweise die richtige Zeichenfolge mit Optionen zum Festlegen der Groß- und Kleinschreibung.

Hinweis: Sie können COLLATE nur für Spalten des Typs STRING verwenden.

Sie können Ihrer Anweisung wie dargestellt eine Sortierung hinzufügen:

SELECT ...
FROM ...
ORDER BY value COLLATE collation_string

Ein collation_string enthält einen collation_name und kann ein optionales collation_attribute als Suffix haben, das durch einen Doppelpunkt getrennt ist. Der collation_string ist ein Literal oder ein Parameter. Normalerweise besteht dieser Name aus zwei Buchstaben, die für die Sprache stehen. Optional können ein Unterstrich und zwei Buchstaben für die Region folgen, z. B. en_US. Diese Namen werden vom Common Locale Data Repository (CLDR) definiert. Eine Anweisung kann auch einen collation_name aus unicode enthalten. Dieser Wert bedeutet, dass die Anweisung Daten mit der Standard-Unicode-Sortierung zurückgeben soll.

Zusätzlich zu collation_name kann ein collation_string ein optionales collation_attribute als Suffix enthalten, das durch einen Doppelpunkt getrennt ist. Dieses Attribut gibt an, ob die Datenvergleiche die Groß- und Kleinschreibung berücksichtigen sollen. Zulässige Werte sind cs, wenn die Groß- und Kleinschreibung berücksichtigt, und ci, wenn die Groß- und Kleinschreibung nicht berücksichtigt werden soll. Wenn collation_attribute nicht angegeben ist, werden die CLDR-Standardwerte verwendet.

Beispiele

Sortierergebnisse, die Englisch (Kanada) verwenden:

SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_CA"

Sortierergebnisse, die einen Parameter verwenden:

#@collate_param = "arg_EG"
SELECT Place
FROM Locations
ORDER BY Place COLLATE @collate_param

Mehrere COLLATE-Klauseln in einer Anweisung verwenden:

SELECT APlace, BPlace, CPlace
FROM Locations
ORDER BY APlace COLLATE "en_US" ASC,
         BPlace COLLATE "ar_EG" DESC,
         CPlace COLLATE "en" DESC

Groß- und Kleinschreibung bei der Sortierung nicht berücksichtigen:

SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_US:ci"

Standardmäßige Unicode-Sortierung ohne Berücksichtigung von Groß- und Kleinschreibung:

SELECT Place
FROM Locations
ORDER BY Place COLLATE "unicode:ci"

Set-Operatoren

UNION { ALL | DISTINCT } | INTERSECT { ALL | DISTINCT } | EXCEPT { ALL | DISTINCT }

Beim Einrichten von Operatoren werden Ergebnisse aus zwei oder mehr Eingabeabfragen zu einer einzigen Ergebnismenge kombiniert. Sie müssen ALL oder DISTINCT angeben. Wenn Sie ALL angeben, werden alle Zeilen beibehalten. Wenn Sie DISTINCT angeben, werden doppelte Zeilen verworfen.

Wenn eine gegebene Zeile R genau m Mal in der ersten Eingabeabfrage und n Mal in der zweiten Eingabeabfrage vorhanden ist (m >= 0, n >= 0), geschieht Folgendes:

• Für UNION ALL wird R genau m + n-mal im Ergebnis angezeigt.
• Bei INTERSECT ALL wird R genau MIN(m, n) im Ergebnis angezeigt.
• Bei EXCEPT ALL wird R genau MAX(m - n, 0) im Ergebnis angezeigt.
• Bei UNION DISTINCT wird DISTINCT nach der Berechnung von UNION berechnet. Deshalb wird R genau einmal angezeigt.
• Bei INTERSECT DISTINCT wird DISTINCT berechnet, nachdem das obige Ergebnis berechnet wurde.
• Bei EXCEPT DISTINCT wird die Zeile R einmal in der Ausgabe angezeigt, wenn m > 0 und n = 0 ist.
• Bei mehr als zwei Eingabeabfragen werden die oben genannten Vorgänge verallgemeinert und die Ausgabe ist die gleiche, wie wenn die Eingaben inkrementell von links nach rechts kombiniert werden würden.

Es gelten die folgenden Regeln:

• Für andere Set-Operationen als UNION ALL müssen alle Spaltentypen den Gleichheitsvergleich unterstützen.
• Die Eingabeabfragen auf jeder Seite des Operators müssen die gleiche Anzahl an Spalten zurückgeben.
• Die Operatoren ordnen die Spalten, die von jeder Eingabeabfrage zurückgegeben werden, entsprechend den Positionen der Spalten in den jeweiligen SELECT-Listen paarweise an. Das heißt, dass die erste Spalte in der ersten Eingabeabfrage mit der ersten Spalte in der zweiten Eingabeabfrage paarweise angeordnet wird.
• In der Ergebnismenge werden immer die Spaltennamen aus der ersten Eingabeabfrage verwendet.
• In der Ergebnismenge werden immer die Obertypen der Eingabetypen in den entsprechenden Spalten verwendet, d. h. paarweise angeordnete Spalten müssen ebenfalls entweder den gleichen Datentyp oder einen gemeinsamen Obertyp haben.
• Trennen Sie unterschiedliche Set-Operationen durch Klammern. Set-Operationen wie UNION ALL und UNION DISTINCT sind in diesem Fall unterschiedlich. Wenn sich durch die Anweisung dieselbe Set-Operation nur wiederholt, sind keine Klammern erforderlich.

Beispiele:

query1 UNION ALL (query2 UNION DISTINCT query3)
query1 UNION ALL query2 UNION ALL query3

Ungültig:

query1 UNION ALL query2 UNION DISTINCT query3
query1 UNION ALL query2 INTERSECT ALL query3;  // INVALID.

UNION

Der Operator UNION kombiniert die Ergebnismengen von zwei oder mehr Eingabeabfragen, indem Spalten der Ergebnismenge von jeder Abfrage paarweise angeordnet und vertikal verkettet werden.

INTERSECT

Der Operator INTERSECT gibt Zeilen zurück, die in den Ergebnismengen der linken und rechten Eingabeabfragen gefunden werden. Anders als bei EXCEPT spielt die Positionierung der Eingabeabfragen (links oder rechts des Operators INTERSECT) hier keine Rolle.

EXCEPT

Der Operator EXCEPT gibt Zeilen aus der linken Eingabeabfrage zurück, die in der rechten Eingabeabfrage nicht vorhanden sind.

Beispiel:

SELECT * FROM UNNEST(ARRAY<int64>[1, 2, 3]) AS number
EXCEPT DISTINCT SELECT 1;

+--------+
| number |
+--------+
| 2      |
| 3      |
+--------+

LIMIT- und OFFSET-Klauseln

LIMIT count [ OFFSET skip_rows ]

LIMIT gibt einen nicht negativen count-Wert des Typs INT64 an und es werden nicht mehr als count-Zeilen zurückgegeben. LIMIT 0 gibt 0 Zeilen zurück.

Wenn eine Set-Operation vorliegt, wird LIMIT angewendet, nachdem die Set-Operation ausgewertet wurde.

OFFSET gibt eine nicht negative Anzahl von Zeilen an, die übersprungen werden sollen, bevor LIMIT angewendet wird. skip_rows ist vom Typ INT64.

Diese Klauseln akzeptieren nur Literal- oder Parameterwerte. Die von LIMIT und OFFSET zurückgegebenen Zeilen werden nur angegeben, wenn diese Operatoren nach ORDER BY verwendet werden.

Beispiele:

SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2

+---------+
| letter  |
+---------+
| a       |
| b       |
+---------+

SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1

+---------+
| letter  |
+---------+
| b       |
| c       |
| d       |
+---------+

WITH-Klausel

WITH with_clause

with_clause:
    with_subquery[, ...]

with_subquery:
    with_query_name AS ( query_expr )

Die Klausel WITH bindet die Ergebnisse einer oder mehrerer benannter Unterabfragen an temporäre Tabellennamen. Jeder eingeführte Tabellenname ist in nachfolgenden SELECT-Ausdrücken innerhalb desselben Abfrageausdrucks sichtbar. Dazu gehören die folgenden Arten von SELECT-Ausdrücken:

• Beliebige SELECT-Ausdrücke in nachfolgenden WITH-Bindungen
• SELECT-Ausdrücke der obersten Ebene im Abfrageausdruck auf beiden Seiten eines festgelegten Operators wie UNION
• SELECT-Ausdrücke in Unterabfragen innerhalb desselben Abfrageausdrucks

Beispiel:

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2

WITH wird in einer Unterabfrage nicht unterstützt. Dies gibt einen Fehler zurück:

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

Die WITH-Klausel wird in DML-Anweisungen nicht unterstützt.

Temporäre Tabellen, die von der WITH-Klausel definiert werden, werden im Arbeitsspeicher gespeichert. Cloud Spanner SQL weist allen von einer Abfrage erstellten temporären Tabellen dynamisch Speicher zu. Wenn die verfügbaren Ressourcen nicht ausreichen, schlägt die Abfrage fehl.

Aliase verwenden

Ein Alias ist ein temporärer Name für eine Tabelle, Spalte oder einen Ausdruck, die in einer Abfrage vorhanden sind. Sie können explizite Aliasse in der SELECT-Liste oder der FROM-Klausel einführen, oder Cloud Spanner SQL wird einen impliziten Alias für einige Ausdrücke ableiten. Ausdrücke ohne expliziten oder impliziten Alias sind anonym und die Abfrage kann darauf nicht mit dem Namen verweisen.

Explizite Aliasse

Sie können explizite Aliasse entweder in der FROM-Klausel oder der SELECT-Liste einfügen.

In einer FROM-Klausel können Sie explizite Aliasse für jedes Objekt einfügen, einschließlich Tabellen, Arrays, Unterabfragen und UNNEST-Klauseln. Verwenden Sie hierzu [AS] alias. Der Suchbegriff AS ist optional.

Beispiel:

SELECT s.FirstName, s2.SongName
FROM Singers AS s, (SELECT * FROM Songs) AS s2;

Sie können explizite Aliasse für jeden Ausdruck in der SELECT-Liste einfügen. Verwenden Sie hierzu [AS] alias. Der Suchbegriff AS ist optional.

Beispiel:

SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;

Implizite Aliasse

Wenn in der SELECT-Liste ein Ausdruck ohne explizites Alias vorhanden ist, weist Cloud Spanner SQL ein implizites Alias gemäß den folgenden Regeln zu. Es können mehrere Spalten mit demselben Alias in der SELECT-Liste vorkommen.

• Bei Kennungen ist der Alias die Kennung. Beispiel: SELECT abc impliziert AS abc.
• Bei Pfadausdrücken ist der Alias die letzte Kennung im Pfad. Beispiel: SELECT abc.def.ghi impliziert AS ghi.
• Beim Feldzugriff mit dem "Punkt"-Element als Feldzugriffsoperator ist der Alias der Feldname. Beispiel: SELECT (struct_function()).fname impliziert AS fname.

In allen anderen Fällen ist kein impliziter Alias vorhanden, d. h. die Spalte ist anonym und es kann darauf nicht mit dem Namen verwiesen werden. Die Daten dieser Spalte werden trotzdem zurückgegeben und die angezeigten Abfrageergebnisse weisen möglicherweise ein erzeugtes Label für diese Spalte auf. Dieses Label kann allerdings nicht als Alias verwendet werden.

In einer FROM-Klausel müssen from_item-Elemente keinen Alias haben. Es gelten die folgenden Regeln:

• Wenn ein Ausdruck vorhanden ist, der kein explizites Alias hat, weist Cloud Spanner SQL in den folgenden Fällen ein implizites Alias zu:
• Bei Kennungen ist der Alias die Kennung. Beispiel: FROM abc impliziert AS abc.
• Bei Pfadausdrücken ist der Alias die letzte Kennung im Pfad. Beispiel: FROM abc.def.ghi impliziert AS ghi.
• Die mit WITH OFFSET erstellte Spalte hat den impliziten Alias offset.
• Tabellen-Unterabfragen haben keine impliziten Aliasse.
• FROM UNNEST(x) hat keinen impliziten Alias.

Alias-Sichtbarkeit

Nach dem Einfügen eines expliziten Alias in eine Abfrage bestehen Einschränkungen in Bezug auf die Stellen, an denen Sie in der Abfrage auf dieses Alias verweisen können. Diese Einschränkungen in Bezug auf die Alias-Sichtbarkeit sind das Ergebnis der Namensbereichsdefinitionsregeln von Cloud Spanner SQL.

Sichtbarkeit in der FROM-Klausel

Cloud Spanner SQL verarbeitet Aliasse in einer FROM-Klausel von links nach rechts und Aliasse sind nur für nachfolgende Pfadausdrücke in einer FROM-Klausel sichtbar.

Beispiel:

Angenommen in der Tabelle Singers ist eine Spalte Concerts des Typs ARRAY vorhanden.

SELECT FirstName
FROM Singers AS s, s.Concerts;

Ungültig:

SELECT FirstName
FROM s.Concerts, Singers AS s;  // INVALID.

Aliasse in FROM-Klauseln sind nicht für Unterabfragen in derselben FROM-Klausel sichtbar. Unterabfragen in einer FROM-Klausel dürfen keine korrelierten Verweise auf andere Tabellen in derselben FROM-Klausel enthalten.

Ungültig:

SELECT FirstName
FROM Singers AS s, (SELECT (2020 - ReleaseDate) FROM s)  // INVALID.

Sie können jeden Spaltennamen einer Tabelle in der FROM-Klausel als Alias an jeder Stelle in der Abfrage verwenden – mit oder ohne Qualifizierung des Tabellennamens.

Beispiel:

SELECT FirstName, s.ReleaseDate
FROM Singers s WHERE ReleaseDate = 1975;

Wenn die FROM-Klausel einen expliziten Alias enthält, müssen Sie im Rest der Abfrage den expliziten Alias statt des impliziten Alias verwenden (siehe Implizite Aliasse). Ein Tabellenalias ist nützlich wegen seiner Kürze oder zur Vermeidung von Mehrdeutigkeit in Fällen wie Selbstverknüpfungen, in denen dieselbe Tabelle während der Abfrageverarbeitung mehrmals durchsucht wird.

Beispiel:

SELECT * FROM Singers as s, Songs as s2
ORDER BY s.LastName

Ungültig – ORDER BY verwendet nicht den Tabellenalias:

SELECT * FROM Singers as s, Songs as s2
ORDER BY Singers.LastName;  // INVALID.

Sichtbarkeit in der SELECT-Liste

Aliasse in der Liste SELECT sind nur für die folgenden Klauseln sichtbar:

• GROUP BY-Klausel
• ORDER BY-Klausel
• HAVING-Klausel

Beispiel:

SELECT LastName AS last, SingerID
FROM Singers
ORDER BY last;

Sichtbarkeit in der GROUP BY-, ORDER BY- und HAVING-Klausel

Diese drei Klauseln GROUP BY, ORDER BY und HAVING können nur auf die folgenden Werte verweisen:

• Tabellen in der FROM-Klausel und alle zugehörigen Spalten.
• Aliasse in der SELECT-Liste.

GROUP BY und ORDER BY können auch auf eine dritte Gruppe verweisen:

• Ganzzahlliterale, die auf Elemente in der SELECT-Liste verweisen. Die Ganzzahl 1 verweist auf das erste Element in der SELECT-Liste, 2 auf das zweite Element usw.

Beispiel:

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY 1
ORDER BY 2 DESC;

Die vorherige Abfrage ist äquivalent mit:

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY sid
ORDER BY s2id DESC;

Doppelte Aliasse

Eine SELECT-Liste oder Unterabfrage mit mehreren expliziten oder impliziten Aliassen desselben Namens ist zulässig, solange in der Abfrage nicht an anderer Stelle auf den Aliasnamen verwiesen wird, da der Verweis mehrdeutig wäre.

Beispiel:

SELECT 1 AS a, 2 AS a;

+---+---+
| a | a |
+---+---+
| 1 | 2 |
+---+---+

Mehrdeutige Aliasse

Cloud Spanner SQL gibt einen Fehler an, wenn der Zugriff auf einen Namen mehrdeutig ist, was bedeutet, dass er in mehr als ein eindeutiges Objekt in der Abfrage oder in einem Tabellenschema aufgelöst werden kann, einschließlich des Schemas einer Zieltabelle.

Beispiele:

Diese Abfrage enthält Spaltennamen, die zwischen den Tabellen miteinander in Konflikt stehen, da sowohl die Tabelle Singers als auch die Tabelle Songs eine Spalte mit dem Namen SingerID enthält:

SELECT SingerID
FROM Singers, Songs;

Diese Abfrage enthält Aliasse, die in der GROUP BY-Klausel mehrdeutig sind, da sie in der SELECT-Liste dupliziert sind:

SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;

Diese Abfrage enthält Aliasse, die in der SELECT-Liste und der FROM-Klausel mehrdeutig sind, da sie denselben Namen haben. Angenommen table hat die Spalten x, y und z. z ist vom Typ STRUCT und enthält die Felder v, w und x.

Beispiel:

SELECT x, z AS T
FROM table AS T
GROUP BY T.x;

Der Alias T ist mehrdeutig und erzeugt einen Fehler, weil T.x in der GROUP BY-Klausel entweder auf table.x oder auf table.z.x verweisen kann.

Ein Name ist in GROUP BY, ORDER BY oder HAVING nicht mehrdeutig, wenn er sowohl ein Spaltenname als auch ein Alias der SELECT-Liste ist. Voraussetzung hierfür ist, dass der Name in dasselbe zugrunde liegende Objekt aufgelöst wird.

Beispiel:

SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;

Der Alias BirthYear ist nicht mehrdeutig, da er in dieselbe zugrunde liegende Spalte Singers.BirthYear aufgelöst wird.

Implizite Aliasse

Wenn in der SELECT-Liste ein Ausdruck ohne explizites Alias vorhanden ist, weist Cloud Spanner SQL ein implizites Alias gemäß den folgenden Regeln zu. Es können mehrere Spalten mit demselben Alias in der SELECT-Liste vorkommen.

• Bei Kennungen ist der Alias die Kennung. Beispiel: SELECT abc impliziert AS abc.
• Bei Pfadausdrücken ist der Alias die letzte Kennung im Pfad. Beispiel: SELECT abc.def.ghi impliziert AS ghi.
• Beim Feldzugriff mit dem "Punkt"-Element als Feldzugriffsoperator ist der Alias der Feldname. Beispiel: SELECT (struct_function()).fname impliziert AS fname.

In allen anderen Fällen ist kein impliziter Alias vorhanden, d. h. die Spalte ist anonym und es kann darauf nicht mit dem Namen verwiesen werden. Die Daten dieser Spalte werden trotzdem zurückgegeben und die angezeigten Abfrageergebnisse weisen möglicherweise ein erzeugtes Label für diese Spalte auf. Dieses Label kann allerdings nicht als Alias verwendet werden.

In einer FROM-Klausel müssen from_item-Elemente keinen Alias haben. Es gelten die folgenden Regeln:

• Wenn ein Ausdruck vorhanden ist, der kein explizites Alias hat, weist Cloud Spanner SQL in den folgenden Fällen ein implizites Alias zu:
  • Bei Kennungen ist der Alias die Kennung. Beispiel: FROM abc impliziert AS abc.
  • Bei Pfadausdrücken ist der Alias die letzte Kennung im Pfad. Beispiel: FROM abc.def.ghi impliziert AS ghi.
  • Die mit WITH OFFSET erstellte Spalte hat den impliziten Alias offset.
• Tabellen-Unterabfragen haben keine impliziten Aliasse.
• FROM UNNEST(x) hat keinen impliziten Alias.

Bereichsvariablen

In Cloud Spanner SQL ist eine Bereichsvariable ein Tabellenausdrucksalias in der FROM-Klausel. Manchmal wird eine Bereichsvariable als table alias bezeichnet. Mit einer Bereichsvariable können Sie auf Zeilen verweisen, die über einen Tabellenausdruck gescannt werden. Ein Tabellenausdruck steht für ein Element in der FROM-Klausel. Als Rückgabe erhalten Sie dann eine Tabelle. Häufige Elemente, die dieser Ausdruck darstellen kann, sind Tabellen, Werttabellen, Unterabfragen, Joins und eingeklammerte Joins.

Im Allgemeinen liefert eine Bereichsvariable einen Verweis auf die Zeilen eines Tabellenausdrucks. Eine Bereichsvariable kann verwendet werden, um eine Spaltenreferenz zu qualifizieren und die zugehörige Tabelle eindeutig zu identifizieren, z. B. range_variable.column_1.

Wenn Sie auf eine einzelne Bereichsvariable verweisen, ohne ein Spaltensuffix anzugeben, ist das Ergebnis eines Tabellenausdrucks der Zeilentyp der zugehörigen Tabelle. Wertetabellen haben explizite Zeilentypen. Bei Bereichsvariablen, die sich auf Wertetabellen beziehen, ist der Ergebnistyp daher der Zeilentyp der Wertetabelle. Andere Tabellen haben keine expliziten Zeilentypen. Bei diesen Tabellen ist der Bereichsvariablentyp ein dynamisch definierter STRUCT-Typ, der alle Spalten der Tabelle enthält.

Beispiele

In diesen Beispielen wird die WITH-Klausel verwendet, um eine temporäre Tabelle mit dem Namen Grid zu emulieren. Diese Tabelle enthält die Spalten x und y. Eine Bereichsvariable mit dem Namen Coordinate verweist auf die aktuelle Zeile, während die Tabelle gescannt wird. Coordinate kann für den Zugriff auf die gesamte Zeile oder auf Spalten der Zeile verwendet werden.

Im folgenden Beispiel wird die Spalte x aus der Bereichsvariable Coordinate ausgewählt, wodurch dann die Spalte x aus der Tabelle Grid ausgewählt wird.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.x FROM Grid AS Coordinate;

+---+
| x |
+---+
| 1 |
+---+

Im folgenden Beispiel werden alle Spalten der Bereichsvariable Coordinate ausgewählt, wodurch dann alle Spalten der Tabelle Grid ausgewählt werden.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.* FROM Grid AS Coordinate;

+---+---+
| x | y |
+---+---+
| 1 | 2 |
+---+---+

Im folgenden Beispiel wird die Bereichsvariable Coordinate ausgewählt, die auf die Zeilen in Tabelle Grid verweist. Da Grid keine Wertetabelle ist, ist der Ergebnistyp von Coordinate ein STRUCT-Typ, der alle Spalten aus Grid enthält.

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate FROM Grid AS Coordinate;

+--------------+
| Coordinate   |
+--------------+
| {x: 1, y: 2} |
+--------------+

Anhang A: Beispieldaten

Diese Beispiele enthalten Anweisungen, die Abfragen für die Tabellen Roster und TeamMascot sowie PlayerStats ausführen.

GROUP BY-Klausel

Beispiel:

SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;

LastName	SUM
Adams	7
Buchanan	13
Coolidge	1

UNION

Der UNION-Operator kombiniert die Ergebnismenge von zwei oder mehreren SELECT-Anweisungen, indem Spalten der Ergebnismenge von jeder SELECT-Anweisung paarweise angeordnet und vertikal verkettet werden.

Beispiel:

SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;

Ergebnisse:

X	Y
Jaguars	50
Knights	51
Lakers	52
Mustangs	53
Adams	3
Buchanan	0
Coolidge	1
Adams	4
Buchanan	13

INTERSECT

Diese Abfrage gibt die Nachnamen zurück, die in Roster und PlayerStats vorhanden sind.

SELECT LastName
FROM Roster
INTERSECT ALL
SELECT LastName
FROM PlayerStats;

Ergebnisse:

LastName
Adams
Coolidge
Buchanan

EXCEPT

Die folgende Abfrage gibt die Nachnamen in Roster zurück, die nicht in PlayerStats vorhanden sind.

SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;

Ergebnisse:

LastName
Eisenhower
Davis

Wenn Sie die Reihenfolge der SELECT-Anweisungen umkehren, werden die Nachnamen unter "PlayerStats" zurückgegeben, die nicht unter "Roster" vorhanden sind:

SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;

Ergebnisse:

(empty)