FROM- und JOIN-Klauseln

Die FROM-Klausel legt die Quelle der Daten fest, auf denen der Rest der Abfrage arbeitet. Logisch ist die FROM-Klausel der Startpunkt der Abfrageausführung. Die FROM-Klausel kann eine einzelne Tabelle enthalten, eine Kombination mehrerer Tabellen, die mit JOIN-Klauseln verbunden werden, oder eine weitere SELECT-Abfrage in einem Unterabfrageknoten. DuckDB hat außerdem eine optionale FROM-zuerst-Syntax, mit der Sie auch ohne SELECT-Anweisung abfragen können.

Beispiele

Wählt alle Spalten aus der Tabelle tbl:

SELECT *
FROM tbl;

Wählt alle Spalten aus der Tabelle mit der FROM-zuerst-Syntax:

FROM tbl
SELECT *;

Wählt alle Spalten mit der FROM-zuerst-Syntax und ohne SELECT-Klausel:

FROM tbl;

Wählt alle Spalten aus der Tabelle tbl über den Alias tn:

SELECT tn.*
FROM tbl tn;

Verwendet einen Präfix-Alias:

SELECT tn.*
FROM tn: tbl;

Wählt alle Spalten aus der Tabelle tbl im Schema schema_name:

SELECT *
FROM schema_name.tbl;

Wählt die Spalte i aus der Tabellenfunktion range, wobei die erste Spalte der Range-Funktion in i umbenannt wird:

SELECT t.i
FROM range(100) AS t(i);

Wählt alle Spalten aus der CSV-Datei test.csv:

SELECT *
FROM 'test.csv';

Wählt alle Spalten aus einer Unterabfrage:

SELECT *
FROM (SELECT * FROM tbl);

Wählt die gesamte Zeile der Tabelle als Struct:

SELECT t
FROM t;

Wählt die gesamte Zeile der Unterabfrage als Struct (d. h. eine einzelne Spalte):

SELECT t
FROM (SELECT unnest(generate_series(41, 43)) AS x, 'hello' AS y) t;

Verbindet zwei Tabellen:

SELECT *
FROM tbl
JOIN other_table
  ON tbl.key = other_table.key;

Wählt eine 10-%-Stichprobe aus einer Tabelle:

SELECT *
FROM tbl
TABLESAMPLE 10%;

Wählt eine Stichprobe von 10 Zeilen aus einer Tabelle:

SELECT *
FROM tbl
TABLESAMPLE 10 ROWS;

Verwendet die FROM-zuerst-Syntax mit WHERE-Klausel und Aggregation:

FROM range(100) AS t(i)
SELECT sum(t.i)
WHERE i % 2 = 0;

Tabellenfunktionen

Einige Funktionen in DuckDB geben ganze Tabellen statt einzelner Werte zurück. Diese Funktionen heißen entsprechend Tabellenfunktionen und können mit einer FROM-Klausel wie gewöhnliche Tabellenverweise verwendet werden. Beispiele sind read_csv, read_parquet, range, generate_series, repeat, unnest und glob (einige der Beispiele hier können sowohl als skalare als auch als Tabellenfunktionen verwendet werden).

Zum Beispiel wird

SELECT *
FROM 'test.csv';

implizit in einen Aufruf der Tabellenfunktion read_csv übersetzt:

SELECT *
FROM read_csv('test.csv');

Alle Tabellenfunktionen unterstützen das Suffix WITH ORDINALITY, das die zurückgegebene Tabelle um eine ganzzahlige Spalte ordinality erweitert, die die erzeugten Zeilen ab 1 nummeriert.

SELECT *
FROM read_csv('test.csv') WITH ORDINALITY;

Beachten Sie, dass dasselbe Ergebnis mit der Fensterfunktion row_number erreicht werden könnte. Bei Joins erlaubt WITH ORDINALITY jedoch, eine Seite des Joins statt der endgültigen Ergebnismenge zu nummerieren, ohne auf Unterabfragen zurückgreifen zu müssen.

Joins

Joins sind eine grundlegende relationale Operation, um zwei Tabellen oder Relationen horizontal zu verbinden. Die Relationen heißen die linke und rechte Seite des Joins, je nachdem, wie sie in der Join-Klausel geschrieben sind. Jede Ergebniszeile hat die Spalten beider Relationen.

Ein Join verwendet eine Regel, um Zeilenpaare aus jeder Relation zuzuordnen. Oft ist das ein Prädikat, es gibt aber auch andere implizite Regeln, die angegeben werden können.

Outer Joins

Zeilen ohne Treffer können trotzdem zurückgegeben werden, wenn ein OUTER-Join angegeben ist. Outer Joins können eines von Folgendem sein:

LEFT (Alle Zeilen der linken Relation erscheinen mindestens einmal)
RIGHT (Alle Zeilen der rechten Relation erscheinen mindestens einmal)
FULL (Alle Zeilen beider Relationen erscheinen mindestens einmal)

Ein Join, der nicht OUTER ist, ist INNER (nur Zeilen, die gepaart werden, werden zurückgegeben).

Wenn eine ungepaarte Zeile zurückgegeben wird, werden die Attribute der anderen Tabelle auf NULL gesetzt.

Kreuzprodukt-Joins (kartesisches Produkt)

Die einfachste Join-Art ist ein CROSS JOIN. Für diese Join-Art gibt es keine Bedingungen, und sie gibt einfach alle möglichen Paare zurück.

Gibt alle Zeilenpaare zurück:

SELECT a.*, b.*
FROM a
CROSS JOIN b;

Das entspricht dem Weglassen der JOIN-Klausel:

SELECT a.*, b.*
FROM a, b;

Bedingte Joins

Die meisten Joins werden durch ein Prädikat angegeben, das Attribute der einen Seite mit Attributen der anderen Seite verbindet. Die Bedingungen können explizit mit einer ON-Klausel am Join angegeben werden (klarer) oder implizit durch die WHERE-Klausel (altmodisch).

Wir verwenden die Tabellen l_regions und l_nations aus dem TPC-H-Schema:

CREATE TABLE l_regions (
    r_regionkey INTEGER NOT NULL PRIMARY KEY,
    r_name      CHAR(25) NOT NULL,
    r_comment   VARCHAR(152)
);

CREATE TABLE l_nations (
    n_nationkey INTEGER NOT NULL PRIMARY KEY,
    n_name      CHAR(25) NOT NULL,
    n_regionkey INTEGER NOT NULL,
    n_comment   VARCHAR(152),
    FOREIGN KEY (n_regionkey) REFERENCES l_regions(r_regionkey)
);

Gibt die Regionen zu den Nationen zurück:

SELECT n.*, r.*
FROM l_nations n
JOIN l_regions r ON (n_regionkey = r_regionkey);

Haben die Spalten denselben Namen und sollen sie gleich sein, kann die einfachere USING-Syntax verwendet werden:

CREATE TABLE l_regions (regionkey INTEGER NOT NULL PRIMARY KEY,
                        name      CHAR(25) NOT NULL,
                        comment   VARCHAR(152));

CREATE TABLE l_nations (nationkey INTEGER NOT NULL PRIMARY KEY,
                        name      CHAR(25) NOT NULL,
                        regionkey INTEGER NOT NULL,
                        comment   VARCHAR(152),
                        FOREIGN KEY (regionkey) REFERENCES l_regions(regionkey));

Gibt die Regionen zu den Nationen zurück:

SELECT n.*, r.*
FROM l_nations n
JOIN l_regions r USING (regionkey);

Die Ausdrücke müssen keine Gleichheiten sein – jedes Prädikat ist erlaubt:

Gibt die Job-Paare zurück, bei denen einer länger gelaufen ist, aber weniger gekostet hat:

SELECT s1.t_id, s2.t_id
FROM west s1, west s2
WHERE s1.time > s2.time
  AND s1.cost < s2.cost;

Natural Joins

Natural Joins verbinden zwei Tabellen anhand von Attributen, die denselben Namen haben.

Nehmen Sie das folgende Beispiel mit Städten, Flughafencodes und Flughafennamen. Beachten Sie, dass beide Tabellen absichtlich unvollständig sind, d. h. sie haben nicht in der anderen Tabelle ein passendes Paar.

CREATE TABLE city_airport (city_name VARCHAR, iata VARCHAR);
CREATE TABLE airport_names (iata VARCHAR, airport_name VARCHAR);
INSERT INTO city_airport VALUES
    ('Amsterdam', 'AMS'),
    ('Rotterdam', 'RTM'),
    ('Eindhoven', 'EIN'),
    ('Groningen', 'GRQ');
INSERT INTO airport_names VALUES
    ('AMS', 'Amsterdam Airport Schiphol'),
    ('RTM', 'Rotterdam The Hague Airport'),
    ('MST', 'Maastricht Aachen Airport');

Um die Tabellen über ihre gemeinsamen IATA-Attribute zu verbinden, führen Sie aus:

SELECT *
FROM city_airport
NATURAL JOIN airport_names;

Das erzeugt folgendes Ergebnis:

city_name	iata	airport_name
Amsterdam	AMS	Amsterdam Airport Schiphol
Rotterdam	RTM	Rotterdam The Hague Airport

Beachten Sie, dass nur Zeilen, in denen dasselbe Attribut iata in beiden Tabellen vorhanden war, ins Ergebnis aufgenommen wurden.

Wir können diese Abfrage auch mit der gewöhnlichen JOIN-Klausel und dem Schlüsselwort USING ausdrücken:

SELECT *
FROM city_airport
JOIN airport_names
USING (iata);

Semi- und Anti-Joins

Semi-Joins geben Zeilen der linken Tabelle zurück, die mindestens einen Treffer in der rechten Tabelle haben. Anti-Joins geben Zeilen der linken Tabelle zurück, die keine Treffer in der rechten Tabelle haben. Bei einem Semi- oder Anti-Join hat das Ergebnis nie mehr Zeilen als die linke Tabelle. Semi-Joins entsprechen der Logik der Anweisung mit dem Operator IN. Anti-Joins entsprechen der Logik des Operators NOT IN, außer dass Anti-Joins NULL-Werte der rechten Tabelle ignorieren.

Semi-Join-Beispiel

Gibt eine Liste von Stadt–Flughafencode-Paaren aus der Tabelle city_airport zurück, bei denen der Flughafenname in der Tabelle airport_names verfügbar ist:

SELECT *
FROM city_airport
SEMI JOIN airport_names
    USING (iata);

city_name	iata
Amsterdam	AMS
Rotterdam	RTM

Diese Abfrage entspricht:

SELECT *
FROM city_airport
WHERE iata IN (SELECT iata FROM airport_names);

Anti-Join-Beispiel

Gibt eine Liste von Stadt–Flughafencode-Paaren aus der Tabelle city_airport zurück, bei denen der Flughafenname in der Tabelle airport_names nicht verfügbar ist:

SELECT *
FROM city_airport
ANTI JOIN airport_names
    USING (iata);

city_name	iata
Eindhoven	EIN
Groningen	GRQ

Diese Abfrage entspricht:

SELECT *
FROM city_airport
WHERE iata NOT IN (SELECT iata FROM airport_names WHERE iata IS NOT NULL);

Laterale Joins

Das Schlüsselwort LATERAL erlaubt Unterabfragen in der FROM-Klausel, auf vorherige Unterabfragen zu verweisen. Dieses Merkmal heißt auch lateraler Join.

SELECT *
FROM range(3) t(i), LATERAL (SELECT i + 1) t2(j);

i	j
0	1
2	3
1	2

Laterale Joins sind eine Verallgemeinerung korrelierter Unterabfragen, da sie mehrere Werte pro Eingabewert statt nur eines einzelnen Werts zurückgeben können.

SELECT *
FROM
    generate_series(0, 1) t(i),
    LATERAL (SELECT i + 10 UNION ALL SELECT i + 100) t2(j);

i	j
0	10
1	11
0	100
1	101

Es kann hilfreich sein, sich LATERAL als Schleife vorzustellen, in der wir die Zeilen der ersten Unterabfrage durchlaufen und sie als Eingabe für die zweite (LATERAL-)Unterabfrage verwenden. In den Beispielen oben durchlaufen wir die Tabelle t und verweisen aus der Definition der Tabelle t2 auf ihre Spalte i. Die Zeilen von t2 bilden die Spalte j im Ergebnis.

Es ist möglich, auf mehrere Attribute aus der LATERAL-Unterabfrage zu verweisen. Mit der Tabelle aus dem ersten Beispiel:

CREATE TABLE t1 AS
    SELECT *
    FROM range(3) t(i), LATERAL (SELECT i + 1) t2(j);

SELECT *
    FROM t1, LATERAL (SELECT i + j) t2(k)
    ORDER BY ALL;

i	j	k
0	1	1
1	2	3
2	3	5

DuckDB erkennt, wann LATERAL-Joins verwendet werden sollten, sodass die Verwendung des Schlüsselworts LATERAL optional ist.

Positionale Joins

Bei Data Frames oder anderen eingebetteten Tabellen derselben Größe können die Zeilen eine natürliche Entsprechung anhand ihrer physischen Reihenfolge haben. In Skriptsprachen lässt sich das leicht mit einer Schleife ausdrücken:

for (i = 0; i < n; i++) {
    f(t1.a[i], t2.b[i]);
}

In Standard-SQL ist das schwer auszudrücken, weil relationale Tabellen nicht geordnet sind; importierte Tabellen wie Data Frames oder Dateien auf der Festplatte (etwa CSVs oder Parquet-Dateien) haben jedoch eine natürliche Ordnung.

Sie über diese Ordnung zu verbinden, heißt positionaler Join:

CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (s VARCHAR);

INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES ('a'), ('b');

SELECT *
FROM t1
POSITIONAL JOIN t2;

x	s
1	a
2	b
3	NULL

Positionale Joins sind immer FULL OUTER-Joins, d. h. die Ergebnistabelle hat die Länge der längeren Eingabetabelle, und fehlende Einträge werden mit NULL-Werten gefüllt.

As-Of-Joins

Eine häufige Operation bei temporalen oder ähnlich geordneten Daten ist, das nächste (erste) Ereignis in einer Referenztabelle zu finden (etwa Preise). Das heißt As-Of-Join:

Hängt Preise an Aktienhandelsgeschäfte:

SELECT t.*, p.price
FROM trades t
ASOF JOIN prices p
       ON t.symbol = p.symbol AND t.when >= p.when;

Der ASOF-Join verlangt mindestens eine Ungleichheitsbedingung auf dem Ordnungsfeld. Die Ungleichheit kann jede Ungleichheitsbedingung sein (>=, >, <=, <) auf jedem Datentyp, die häufigste Form ist jedoch >= auf einem temporalen Typ. Alle anderen Bedingungen müssen Gleichheiten sein (oder NOT DISTINCT). Das bedeutet, dass die Links/Rechts-Reihenfolge der Tabellen maßgeblich ist.

ASOF verbindet jede linke Zeile mit höchstens einer rechten Zeile. Er kann als OUTER-Join angegeben werden, um ungepaarte Zeilen zu finden (z. B. Handelsgeschäfte ohne Preise oder Preise ohne Handelsgeschäfte).

Hängt Preise oder NULLs an Aktienhandelsgeschäfte:

SELECT *
FROM trades t
ASOF LEFT JOIN prices p
            ON t.symbol = p.symbol
           AND t.when >= p.when;

ASOF-Joins können Join-Bedingungen auf übereinstimmenden Spaltennamen auch mit der USING-Syntax angeben, aber das letzte Attribut in der Liste muss die Ungleichheit sein, die größer oder gleich (>=) sein wird:

SELECT *
FROM trades t
ASOF JOIN prices p USING (symbol, "when");

Gibt symbol, trades.when, price zurück (aber NICHT prices.when):

Wenn Sie USING so mit einem SELECT * kombinieren, gibt die Abfrage die Spaltenwerte der linken Seite (Probe) für die Treffer zurück, nicht die der rechten Seite (Build). Um im Beispiel die Zeiten von prices zu erhalten, müssen Sie die Spalten explizit auflisten:

SELECT t.symbol, t.when AS trade_when, p.when AS price_when, price
FROM trades t
ASOF LEFT JOIN prices p USING (symbol, "when");

Self-Joins

DuckDB erlaubt Self-Joins für alle Join-Arten. Beachten Sie, dass Tabellen Aliase brauchen; dieselbe Tabelle ohne Aliase führt zu einem Fehler:

CREATE TABLE t (x INTEGER);
SELECT * FROM t JOIN t USING(x);

Binder Error:
Duplicate alias "t" in query!

Das Hinzufügen der Aliase erlaubt der Abfrage, erfolgreich geparst zu werden:

SELECT * FROM t AS t1 JOIN t AS t2 USING(x);

Kurzschreibweisen in der `JOIN`-Klausel

Sie können Spaltennamen in der JOIN-Klausel angeben:

CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (y INTEGER);
INSERT INTO t1 VALUES (1), (2), (4);
INSERT INTO t2 VALUES (2), (3);
SELECT * FROM t1 NATURAL JOIN t2 t2(x);

x
2

Sie können in der JOIN-Klausel auch die VALUES-Klausel verwenden:

SELECT * FROM t1 NATURAL JOIN (VALUES (2), (4)) _(x);

x
2
4

`FROM`-zuerst-Syntax

Das SQL von DuckDB unterstützt die FROM-zuerst-Syntax, d. h. Sie können die FROM-Klausel vor die SELECT-Klausel setzen oder die SELECT-Klausel ganz weglassen. Wir verwenden das folgende Beispiel zur Veranschaulichung:

CREATE TABLE tbl AS
    SELECT *
    FROM (VALUES ('a'), ('b')) t1(s), range(1, 3) t2(i);

`FROM`-zuerst-Syntax mit `SELECT`-Klausel

Die folgende Anweisung zeigt die Verwendung der FROM-zuerst-Syntax:

FROM tbl
SELECT i, s;

Das entspricht:

SELECT i, s
FROM tbl;

i	s
1	a
2	a
1	b
2	b

`FROM`-zuerst-Syntax ohne `SELECT`-Klausel

Die folgende Anweisung zeigt die Verwendung der optionalen SELECT-Klausel:

FROM tbl;