Abfragesyntax in Standard-SQL

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

SQL-Syntax

query_statement:
    query_expr

query_expr:
    [ WITH with_query_name AS ( query_expr ) [, ...] ]
    { 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_item [, ...] ] [ WHERE bool_expression ] [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ] [ HAVING bool_expression ] [ WINDOW named_window_expression AS { named_window | ( [ window_definition ] ) } [, ...] ] set_op: UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT from_item: { table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ AS ] alias ] } join: from_item [ join_type ] JOIN from_item [ { ON bool_expression | USING ( join_column [, ...] ) } ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

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.

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            |
| Davis      | 52         | 4            |
| Eisenhower | 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 |
+---------+------------+

SELECT-Modifizierer

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 * 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      |
+-----------+----------+

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      |
+----------+-----------+----------+

SELECT ALL

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

Wertetabellen

Eine Wertetabelle in BigQuery ist 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.

Hinweis: In BigQuery kann eine Abfrage nur eine Wertetabelle mit dem Typ STRUCT zurückgeben.

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 BigQuery können jedoch auch Wertetabellenabfragen verwendet werden.

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

SELECT AS STRUCT

SELECT AS STRUCT expr1 [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 t.f1, t.f2 WHERE t.f3=true)
FROM
  Table t

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.

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 STRUCT(1 a, 2 b) xyz FROM Table;

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

Aliasse

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

FROM-Klausel

from_item: {
    table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ]  |
    join |
    ( query_expr ) [ [ AS ] alias ] |
    field_path |
    { UNNEST( array_expression ) | UNNEST( array_path ) | array_path }
        [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] |
    with_query_name [ [ 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.

table_name

Der (optional qualifizierte) Name einer vorhandenen Tabelle.

SELECT * FROM Roster;
SELECT * FROM dataset.Roster;
SELECT * FROM project.dataset.Roster;

FOR SYSTEM_TIME AS OF

FOR SYSTEM_TIME AS OF verweist auf die historischen Versionen der Tabellendefinition und Zeilen, die zu timestamp_expression aktuell waren.

Beschränkungen:

Die Quelltabelle in der FROM-Klausel, die FOR SYSTEM_TIME AS OF enthält, darf nicht Folgendes sein:

  • Ein ARRAY-Scan, einschließlich eines vereinfacht dargestellten Arrays oder der Ausgabe des Operators UNNEST
  • Ein allgemeiner Tabellenausdruck, der durch eine WITH-Klausel definiert wird

timestamp_expression muss ein konstanter Ausdruck sein. Er darf Folgendes nicht enthalten:

  • Unterabfragen
  • Korrelierte Referenzen (Verweise auf Spalten einer Tabelle, die auf einer höheren Ebene der Abfrageanweisung erscheinen, z. B. in der Liste SELECT)
  • Benutzerdefinierte Funktionen (UDFs)

Der Wert von timestamp_expression darf nicht in die folgenden Bereiche fallen:

  • Nach dem aktuellen Zeitstempel (in der Zukunft)
  • Mehr als sieben Tage vor dem aktuellen Zeitstempel

Eine einzelne Abfrageanweisung darf nicht zu mehr als einem Zeitpunkt, einschließlich der aktuellen Zeit, auf eine einzelne Tabelle verweisen. Das heißt, eine Abfrage kann zum selben Zeitstempel mehrmals auf eine Tabelle verweisen, jedoch nicht auf die aktuelle und eine alte Version oder zwei verschieden alte Versionen.

Beispiele:

Die folgende Abfrage gibt eine alte Version der Tabelle von vor einer Stunde zurück.

SELECT *
FROM t
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

Die folgende Abfrage gibt eine alte Version der Tabelle zu einem absoluten Zeitpunkt zurück.

SELECT *
FROM t
  FOR SYSTEM_TIME AS OF '2017-01-01 10:00:00-07:00';

Die folgende Abfrage gibt einen Fehler zurück, da timestamp_expression einen korrelierten Verweis auf eine Spalte in der enthaltenden Abfrage umfasst.

SELECT *
FROM t1
WHERE t1.a IN (SELECT t2.a
               FROM t2 FOR SYSTEM_TIME AS OF t1.timestamp_column);

Die folgenden Vorgänge zeigen den Zugriff auf eine frühere Version der Tabelle, bevor die Tabelle ersetzt wird.

DECLARE before_replace_timestamp TIMESTAMP;

-- Create table books.
CREATE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;

-- Get current timestamp before table replacement.
SET before_replace_timestamp = CURRENT_TIMESTAMP();

-- Replace table with different schema(title and release_date).
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, DATE '1603-01-01' release_date;

-- This query returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF before_replace_timestamp;

Die folgenden Vorgänge zeigen den Zugriff auf eine frühere Version der Tabelle vor einem DML-Job.

DECLARE JOB_START_TIMESTAMP TIMESTAMP;

-- Create table books.
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;

-- Insert two rows into the books.
INSERT books (title, author)
VALUES('The Great Gatsby', 'F. Scott Fizgerald'),
      ('War and Peace', 'Leo Tolstoy');

SELECT * FROM books;

SET JOB_START_TIMESTAMP = (
  SELECT start_time
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
  WHERE job_type="QUERY"
    AND statement_type="INSERT"
  ORDER BY start_time DESC
  LIMIT 1
 );

-- This query only returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF JOB_START_TIMESTAMP;

Join

Siehe JOIN-Typen.

select

( select ) [ [ 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.

UNNEST

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).

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}     |
+---+-----+--------------+

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 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;

Weitere Informationen zum Verwenden von UNNEST, einschließlich Konstruktion, Vereinfachung und Filterung, finden Sie unter Arrays topic.

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:

dataset.Roster oder project.dataset.Roster.

Aliasse

Unter Aliasse verwenden finden Sie weitere Informationen zur Syntax und Sichtbarkeit von Aliassen in FROM-Klauseln.

JOIN-Typen

join:
    from_item [ join_type ] JOIN from_item
    [ ON bool_expression | USING ( join_column [, ...] ) ]

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

Die JOIN-Klausel 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-Klauseln erfordern einen join_type.

Eine JOIN-Klausel 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.

[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 der JOIN-Klausel 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-Klauseln 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 werden:

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

Sie können Klammern für aufeinanderfolgende ON- und USING-Klauseln auch weglassen, sofern keine durch Kommas getrennten Joins vorhanden sind:

FROM A JOIN B JOIN C JOIN D USING (w) ON B.x = C.y ON A.z = B.x

Wenn die Klausel durch Kommas getrennte Joins enthält, müssen Sie Klammern verwenden:

FROM A, B JOIN C JOIN D ON C.x = D.y ON B.z = C.x    // INVALID
FROM A, B JOIN (C JOIN D ON C.x = D.y) ON B.z = C.x  // VALID

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

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 WHERE-Klausel filtert Zeilen durch Auswerten jeder Zeile in Bezug auf bool_expression und verwirft alle Zeilen, die nicht den Wert TRUE zurückgeben (d. h. Zeilen, die den Wert FALSE oder NULL zurückgeben).

Beispiel:

SELECT * FROM Roster
WHERE SchoolID = 52;

bool_expression kann mehrere Unterbedingungen enthalten.

Beispiel:

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

Sie können auf keine Spaltenaliasse der SELECT-Liste in der WHERE-Klausel verweisen.

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.

Beispiel: Die folgende Abfrage:

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

entspricht:

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

GROUP BY-Klausel

GROUP BY { expression [, ...] | ROLLUP ( 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;

GROUP BY ROLLUP gibt die Ergebnisse von GROUP BY für Präfixe der Ausdrücke in der ROLLUP-Liste zurück, die jeweils als Gruppierungssatz bezeichnet werden. Für die ROLLUP-Liste (a, b, c) lauten die Gruppierungssätze (a, b, c), (a, b), (a), (). Bei der Auswertung der Ergebnisse von GROUP BY für einen bestimmten Gruppierungssatz betrachtet GROUP BY ROLLUP Ausdrücke, die nicht im Gruppierungssatz enthalten sind, als Ausdrücke mit einem Wert von NULL. Eine SELECT-Anweisung wie

SELECT a, b, SUM(c) FROM Input GROUP BY ROLLUP(a, b);

verwendet die Rollup-Liste (a, b). Das Ergebnis enthält die Ergebnisse von GROUP BY für die Gruppierungssätze (a, b), (a) und (), die alle Zeilen enthalten. Das gibt dieselben Zeilen so zurück:

SELECT NULL, NULL, SUM(c) FROM Input               UNION ALL
SELECT a,    NULL, SUM(c) FROM Input GROUP BY a    UNION ALL
SELECT a,    b,    SUM(c) FROM Input GROUP BY a, b;

Dies ermöglicht die Berechnung von Aggregaten für die Gruppierungssätze, die durch die Ausdrücke in der ROLLUP-Liste definiert sind, und die Präfixe dieser Liste.

Beispiel:

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(day);

Die obige Abfrage gibt eine Zeile für jeden Tag zusätzlich zu der Zusammenfassung der Summe über alle Tage aus, wie durch ein NULL-Tag angezeigt wird:

+------+-------+
| day  | total |
+------+-------+
| NULL | 39.77 |
|    1 | 23.54 |
|    2 |  9.99 |
|    3 |  6.24 |
+------+-------+

Beispiel:

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  sku,
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;

Die obige Abfrage gibt Zeilen zurück, die nach folgenden Gruppierungssätzen zusammengefasst sind:

  • Artikelnummer und Tag
  • Artikelnummer (Tag ist NULL)
  • Leerer Gruppierungssatz (Tag und Artikelnummer sind NULL)

Die Summen für diese Gruppierungssätze entsprechen der Summe für jede einzelne Kombination aus Artikelnummer und Tag, der Summe für jede Artikelnummer an allen Tagen und der Gesamtsumme:

+------+------+-------+
| sku  | day  | total |
+------+------+-------+
| NULL | NULL | 39.77 |
|  123 | NULL | 28.97 |
|  123 |    1 | 18.98 |
|  123 |    2 |  9.99 |
|  456 | NULL |  8.81 |
|  456 |    1 |  4.56 |
|  456 |    3 |  4.25 |
|  789 |    3 |  1.99 |
|  789 | NULL |  1.99 |
+------+------+-------+

HAVING-Klausel

HAVING bool_expression

Die HAVING-Klausel ist der WHERE-Klausel ähnlich: Sie filtert Zeilen, die nicht den Wert TRUE zurückgeben, wenn sie in Bezug auf bool_expression ausgewertet werden.

Wie auch bei der WHERE-Klausel kann bool_expression ein Ausdruck sein, der einen booleschen Wert zurückgibt und mehrere Unterbedingungen enthalten kann.

Die HAVING-Klausel unterscheidet sich von der WHERE-Klausel in Folgendem:

  • Die HAVING-Klausel erfordert, dass GROUP BY oder eine Aggregation in der Abfrage vorhanden ist.
  • Die HAVING-Klausel tritt nach GROUP BY und der Aggregation und vor ORDER BY auf. Dies bedeutet, dass die HAVING-Klausel einmal für jede aggregierte Zeile in der Ergebnismenge ausgewertet wird. Dies unterscheidet sich von der WHERE-Klausel, die vor GROUP BY und der Aggregation ausgewertet wird.

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 }]
  [{ NULLS FIRST | NULLS LAST }]
  [, ...]

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

  • NULLS FIRST | NULLS LAST:
    • NULLS FIRST: sortiert Nullwerte vor Nicht-Nullwerten
    • NULLS LAST: sortiert Nullwerte nach Nicht-Nullwerten
  • ASC | DESC: sortiert die Ergebnisse in aufsteigender oder absteigender Reihenfolge von expression-Werten. ASC ist der Standardwert. Wenn keine Null-Folge mit NULLS FIRST oder NULLS LAST angegeben ist:
    • NULLS FIRST wird standardmäßig angewendet, wenn die Sortierfolge aufsteigend ist.
    • NULLS LAST wird standardmäßig angewendet, wenn die Sortierfolge absteigend ist.

Beispiele

Verwenden Sie die Standardsortierreihenfolge (aufsteigend).

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

Verwenden Sie die Standardsortierfolge (aufsteigend), geben Sie Nullwerte aber zuletzt zurück.

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

Verwenden Sie die absteigende Sortierreihenfolge.

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

Verwenden Sie eine absteigende Sortierfolge, geben Sie Nullwerte aber zuerst zurück.

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x DESC NULLS FIRST;
+------+-------+
| x    | y     |
+------+-------+
| NULL | false |
| 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;

WINDOW-Klausel

WINDOW named_window_expression [, ...]

named_window_expression:
  named_window AS { named_window | ( [ window_specification ] ) }

Eine WINDOW-Klausel definiert eine Liste mit benannten Fenstern. Ein benanntes Fenster stellt eine Gruppe von Zeilen in einer Tabelle dar, für die eine Analysefunktion verwendet werden soll. Ein benanntes Fenster kann mit einer Fensterspezifikation definiert werden oder auf ein anderes benanntes Fenster verweisen. Wenn auf ein anderes benanntes Fenster verwiesen wird, muss die Definition des referenzierten Fensters vor dem referenzierenden Fenster stehen.

Beispiele

Diese Beispiele verweisen auf eine Tabelle mit dem Namen Produce. Sie geben alle dasselbe Ergebnis zurück. Achten Sie auf die vielen unterschiedlichen Möglichkeiten, wie Sie benannte Fenster kombinieren und in der OVER-Klausel einer Analysefunktion verwenden können.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (d) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  d AS (c)
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (c ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS b

Set-Operatoren

UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT 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:

  • Bei UNION ALL wird R genau m + n Mal 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

Die WITH-Klausel enthält eine oder mehrere Unterabfragen, die jedes Mal ausgeführt werden, wenn eine nachfolgende SELECT-Anweisung auf sie verweist. Jede Klausel oder Unterabfrage kann auf Unterabfragen verweisen, die Sie in der WITH-Klausel definieren. Dies umfasst alle SELECT-Anweisungen auf beiden Seiten eines Mengenoperators, z. B. UNION.

Die WITH-Klausel ist hauptsächlich für die Lesbarkeit von Bedeutung, da BigQuery das Ergebnis der Abfragen in der WITH-Klausel nicht berücksichtigt. Kommt eine Abfrage in mehreren WITH-Klauseln vor, wird sie in jeder Klausel ausgeführt.

Beispiel:

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

Verwenden Sie WITH, um komplexere Abfragen in eine WITH SELECT-Anweisung und WITH-Klauseln aufzuteilen. Dabei ist es die bessere Alternative, keine verschachtelten Tabellenunterabfragen zu schreiben. Beispiel:

WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2)

Es folgen die Bereichsregeln für WITH-Klauseln:

  • Aliasse sind bereichsbezogen, sodass Aliasse, die in einer WITH-Klausel eingefügt werden, nur in den späteren Unterabfragen in derselben WITH-Klausel sowie in der Abfrage unter der WITH-Klausel sichtbar sind.
  • Aliasse, die in derselben WITH-Klausel eingefügt werden, dürfen nur einmal vorkommen, aber derselbe Alias kann in mehreren WITH-Klauseln in derselben Abfrage verwendet werden. Der lokale Alias setzt alle äußeren Aliasse an allen Stellen außer Kraft, an denen der lokale Alias sichtbar ist.
  • Unterabfragen mit Alias in einer WITH-Klausel können nie korreliert werden. Es sind keine Spalten außerhalb der Abfrage sichtbar. Die einzigen sichtbaren Namen von außerhalb sind andere WITH-Aliasse, die bereits in derselben WITH-Klausel eingeführt wurden.

Das folgende Beispiel ist eine Anweisung mit Aliassen in WITH-Unterabfragen:

WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q3 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q1 AS (SELECT * FROM q1),  # q1 (in the query) resolves to my_query
        q4 AS (SELECT * FROM q1)   # q1 resolves to the WITH subquery
                                   # on the previous line.
    SELECT * FROM q1)  # q1 resolves to the third inner WITH subquery.

WITH RECURSIVE wird nicht unterstützt.

Aliasse 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 FROM-Klausel einführen oder BigQuery leitet einen impliziten Alias für bestimmte Ausdrücke ab. 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 vorhanden ist, der keinen expliziten Alias hat, weist BigQuery entsprechend den folgenden Regeln einen impliziten Alias 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 keinen expliziten Alias hat, weist BigQuery in den folgenden Fällen einen impliziten 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 BigQuery.

Sichtbarkeit in der FROM-Klausel

BigQuery 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.

Wenn eine SELECT-Liste oberster Ebene doppelte Spaltennamen enthält und keine Zieltabelle angegeben ist, werden alle doppelten Spalten, mit Ausnahme der ersten, automatisch umbenannt, um sie eindeutig zu machen. Die umbenannten Spalten werden im Abfrageergebnis angezeigt.

Beispiel:

SELECT 1 AS a, 2 AS a;

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

Doppelte Spaltennamen in Tabellen- oder Ansichtsdefinitionen werden nicht unterstützt. Die folgenden Anweisungen mit Abfragen, die doppelte Spaltennamen enthalten, schlagen fehl:

CREATE TABLE my_dataset.my_table AS (SELECT 1 AS a, 2 AS a);
CREATE VIEW my_dataset.my_view AS (SELECT 1 AS a, 2 AS a);

Mehrdeutige Aliasse

BigQuery 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 vorhanden ist, der keinen expliziten Alias hat, weist BigQuery entsprechend den folgenden Regeln einen impliziten Alias 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 keinen expliziten Alias hat, weist BigQuery in den folgenden Fällen einen impliziten 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 BigQuery ist eine Bereichsvariable ein Alias für einen Tabellenausdruck 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 DISTINCT
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)