Kategorien:: Abfragesyntax

JOIN¶

Bei einer JOIN-Operation werden Zeilen aus zwei Tabellen (oder anderen tabellenartige Quellen, z. B. Ansichten oder Tabellenfunktionen) kombiniert, um eine neue kombinierte Zeile zu erstellen, die in der Abfrage verwendet werden kann. Eine Erläuterung des JOIN-Konzepts finden Sie unter Verwenden von Verknüpfungen (Joins).

Unter diesem Thema wird die Verwendung des JOIN-Konstrukts in der FROM-Klausel beschrieben. Mit der JOIN-Unterklausel wird (explizit oder implizit) festgelegt, wie Zeilen in einer Tabelle mit den entsprechenden Zeilen einer anderen Tabelle in Beziehung gesetzt werden sollen. Ein spezielles Konstrukt ist ASOF JOIN, das verwendet wird, um Zeitreihendaten über Zeitstempelspalten zu verknüpfen, wenn ihre Werte eng aufeinander folgen, einander vorausgehen oder genau übereinstimmen.

Obwohl zum Verknüpfen von Tabellen die Verwendung von JOIN mit der Unterklausel ON der FROM-Klausel empfohlen wird, können Tabellen alternativ auch mit der WHERE-Klausel verknüpft werden. Weitere Informationen dazu finden Sie in der Dokumentation zur WHERE-Klausel.

Syntax¶

Verwenden Sie eine der folgenden Optionen:

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]

Copy

SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
[ ... ]

Copy

SELECT ...
FROM <object_ref1> [
                     {
                       | NATURAL [ { LEFT | RIGHT | FULL } [ OUTER ] ]
                       | CROSS
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

Copy

object_ref1 und object_ref2

Jede Objektreferenz ist eine Tabelle oder eine tabellenartige Datenquelle.

JOIN

Verwenden Sie das Schlüsselwort JOIN, um anzugeben, dass die Tabellen verknüpft werden sollen. Kombinieren Sie JOIN mit anderen verknüpfungsbezogenen Schlüsselwörtern (z. B. INNER oder OUTER), um den Typ der Verknüpfung festzulegen.

Die Semantik von Verknüpfungen lautet wie folgt (der Kürze wegen werden unter diesem Thema o1 und o2 für object_ref1 und object_ref2 verwendet):

Verknüpfungstyp	Semantik
`o1 INNER JOIN o2`	Bei jeder Zeile von `o1` wird für jede Zeile von `o2`, die die Unterklausel `ON condition` erfüllt, eine Zeile erzeugt. (Beachten Sie, dass Sie auch ein Komma verwenden können, um eine innere Verknüpfung anzugeben. Ein Beispiel dazu finden Sie im Beispielbereich unten.) Wenn Sie `INNER JOIN` ohne die `ON`-Klausel verwenden (oder wenn Sie ein Komma ohne eine `WHERE`-Klausel verwenden), ist das Ergebnis dasselbe wie bei Verwendung von `CROSS JOIN`: ein kartesisches Produkt (jede Zeile von `o1` gepaart mit jeder Zeile von `o2`).
`o1 LEFT OUTER JOIN o2`	Das Ergebnis der inneren Verknüpfung wird für jede Zeile von `o1`, die keine Übereinstimmungen in `o2` aufweist, um eine Zeile erweitert. Die Ergebnisspalten, die Bezug auf `o2` nehmen, enthalten Null.
`o1 RIGHT OUTER JOIN o2`	Das Ergebnis der inneren Verknüpfung wird für jede Zeile von `o2`, die keine Übereinstimmungen in `o1` aufweist, um eine Zeile erweitert. Die Ergebnisspalten, die Bezug auf `o1` nehmen, enthalten Null.
`o1 FULL OUTER JOIN o2`	Gibt alle verknüpften Zeilen zurück, plus eine Zeile für jede Zeile ohne Entsprechung auf der linken Seite (rechts erweitert um Nullen), plus eine Zeile für jede Zeile ohne Entsprechung auf der rechten Seite (links erweitert um Nullen).
`o1 CROSS JOIN o2`	Für jede mögliche Kombination von Zeilen aus `o1` und `o2` (d. h. Kartesisches Produkt) enthält die verknüpfte Tabelle eine Zeile, die aus allen Spalten in `o1` gefolgt von allen Spalten in `o2` besteht. Ein `CROSS JOIN` kann nicht mit einer `ON condition`-Klausel kombiniert werden. Sie können jedoch eine `WHERE`-Klausel verwenden, um die Ergebnisse zu filtern.
`o1 NATURAL JOIN o2`	Ein `NATURAL JOIN` ist identisch mit einem expliziten `JOIN` für die gemeinsamen Spalten der beiden Tabellen, mit der Ausnahme, dass die gemeinsamen Spalten nur einmal in der Ausgabe enthalten sind. (Bei einer natürlichen Verknüpfung wird davon ausgegangen, dass Spalten mit dem gleichen Namen, die sich jedoch in verschiedenen Tabellen befinden, übereinstimmende Daten enthalten.) Im Beispielabschnitt unten finden Sie einige Beispiele. Ein `NATURAL JOIN` kann mit einem `OUTER JOIN` kombiniert werden. Ein `NATURAL JOIN` kann nicht mit einer `ON condition`-Bedingungsklausel kombiniert werden, da die `JOIN`-Bedingung bereits impliziert ist. Sie können jedoch eine `WHERE`-Klausel verwenden, um die Ergebnisse zu filtern.

Siehe auch:

Standard: INNER JOIN

Wenn das Wort JOIN ohne Angabe von INNER oder OUTER verwendet wird, dann wird JOIN als innere Verknüpfung interpretiert.

ON condition

Ein boolescher Ausdruck, der die Zeilen auf beiden Seiten des JOIN definiert, die als Übereinstimmung gelten. Beispiel:

ON object_ref2.id_number = object_ref1.id_number

Copy

Bedingungen werden in der Dokumentation zur WHERE-Klausel näher erläutert.

Die ON-Klausel ist bei CROSS JOIN nicht zulässig.

Die Klausel ON ist bei NATURAL JOIN unnötig (und unzulässig); die Verknüpfungsspalten sind implizit.

Bei anderen Verknüpfungen ist die ON-Klausel optional. Wenn Sie die ON-Klausel jedoch weglassen, erhalten Sie ein kartesisches Produkt (jede Zeile von object_ref1 gepaart mit jeder Zeile von object_ref2). Ein kartesisches Produkt kann eine sehr große Ausgabe erzeugen, die fast ausschließlich aus Zeilenpaaren besteht, die nicht wirklich miteinander zu tun haben. Dies erfordert viele Ressourcen und ist eine häufige Quelle für Benutzerfehler.

USING( <column_list> )

Eine Liste von Spalten, die zwischen den beiden Tabellen, die verbunden werden sollen, gemeinsam sind. Diese Spalten werden als Join-Spalten verwendet. Die Spalten müssen in jeder der zu verbindenden Tabellen denselben Namen und dieselbe Bedeutung haben.

Angenommen, die SQL-Anweisung enthält Folgendes:

... o1 JOIN o2
    USING (key_column)

Copy

In diesem einfachen Fall wäre dies äquivalent zu:

... o1 JOIN o2
    ON o2.key_column = o1.key_column

Copy

In der Standard-JOIN-Syntax wird „*“ für die Projektionsliste (die Liste der Spalten und anderer Ausdrücke nach dem Schlüsselwort SELECT) verwendet. Dies führt dazu, dass die Abfrage genau einmal den Wert von key_column zurückgibt. Die Spalten werden in der folgenden Reihenfolge zurückgegeben:

Die Spalten in der USING-Klausel in der angegebenen Reihenfolge.
Die linken Tabellenspalten, die nicht in der USING-Klausel angegeben sind.
Die rechten Tabellenspalten, die nicht in der USING-Klausel angegeben sind.

Beispiele für standardmäßige und nicht standardmäßige Verwendung finden Sie in den folgenden Beispielen.

Nutzungshinweise¶

Die folgenden Einschränkungen gelten für Tabellenfunktionen außer SQL-UDTFs:

Sie können die Klauseln ON, USING oder NATURAL JOIN nicht in einer lateralen Tabellenfunktion (außer einer SQL-UDTF) angeben.

Folgende Syntax ist beispielsweise nicht zulässig:

SELECT ... FROM my_table
  JOIN TABLE(FLATTEN(input=>[col_a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  INNER JOIN TABLE(FLATTEN(input=>[col_a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  JOIN TABLE(my_js_udtf(col_a))
  ON ... ;

Copy

SELECT ... FROM my_table
  INNER JOIN TABLE(my_js_udtf(col_a))
  ON ... ;

Copy

Sie können die Klauseln ON, USING oder NATURAL JOIN nicht in einem äußeren lateralen Join zu einer Tabellenfunktion (außer einer SQL-UDTF) angeben.

Folgende Syntax ist beispielsweise nicht zulässig:

SELECT ... FROM my_table
  LEFT JOIN TABLE(FLATTEN(input=>[a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  FULL JOIN TABLE(FLATTEN(input=>[a]))
  ON ... ;

Copy

SELECT ... FROM my_table
  LEFT JOIN TABLE(my_js_udtf(a))
  ON ... ;

Copy

SELECT ... FROM my_table
  FULL JOIN TABLE(my_js_udtf(a))
  ON ... ;

Copy

Die Verwendung der obigen Syntax führt zu folgendem Fehler:

000002 (0A000): Unsupported feature
  'lateral table function called with OUTER JOIN syntax
   or a join predicate (ON clause)'

Diese Einschränkungen gelten nicht, wenn Sie anstelle des Schlüsselworts JOIN ein Komma verwenden:
```
SELECT ... FROM my_table,
  TABLE(FLATTEN(input=>[col_a]))
  ON ... ;
```
Copy

Beispiele¶

In vielen der JOIN-Beispiele werden zwei Tabellen genutzt: t1 und t2. Die Tabellen und zugehörige Daten werden wie folgt erstellt:

CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);

Copy

INSERT INTO t1 (col1) VALUES 
   (2),
   (3),
   (4);
INSERT INTO t2 (col1) VALUES 
   (1),
   (2),
   (2),
   (3);

Copy

Innere Verknüpfung:

SELECT t1.col1, t2.col1
    FROM t1 INNER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Copy

Dies zeigt eine linke äußere Verknüpfung. Beachten Sie den Wert NULL für die Zeile in Tabelle t1, für die in Tabelle t2 keine übereinstimmende Zeile vorhanden ist.

SELECT t1.col1, t2.col1
    FROM t1 LEFT OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
+------+------+

Copy

Dies zeigt eine rechte äußere Verknüpfung. Beachten Sie den Wert NULL für die Zeile in Tabelle t1, für die in Tabelle t2 keine übereinstimmende Zeile vorhanden ist.

SELECT t1.col1, t2.col1
    FROM t1 RIGHT OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
| NULL |    1 |
+------+------+

Copy

Dies zeigt eine vollständige äußere Verknüpfung. Beachten Sie, dass die Ausgabe zwei Zeilen mit NULL-Werten enthält, da jede Tabelle eine Zeile hat, für die in der anderen Tabelle keine übereinstimmende Zeile vorhanden ist:

SELECT t1.col1, t2.col1
    FROM t1 FULL OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
| NULL |    1 |
+------+------+

Copy

Dies ist ein Beispiel für eine Kreuzverknüpfung (Cross Join), mit der ein kartesisches Produkt erstellt wird. Beachten Sie, dass die Kreuzverknüpfung keine ON-Klausel enthält.

SELECT t1.col1, t2.col1
    FROM t1 CROSS JOIN t2
    ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    1 |
|    2 |    2 |
|    2 |    2 |
|    2 |    3 |
|    3 |    1 |
|    3 |    2 |
|    3 |    2 |
|    3 |    3 |
|    4 |    1 |
|    4 |    2 |
|    4 |    2 |
|    4 |    3 |
+------+------+

Copy

Eine Kreuzverknüpfung kann durch eine WHERE-Klausel gefiltert werden, wie im folgenden Beispiel gezeigt:

SELECT t1.col1, t2.col1
    FROM t1 CROSS JOIN t2
    WHERE t2.col1 = t1.col1
    ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Copy

Dies ist ein Beispiel für eine natürliche Verknüpfung. Dies erzeugt dieselbe Ausgabe wie die entsprechende innere Verknüpfung, außer dass keine zweite Kopie der Join-Spalte enthalten ist:

CREATE OR REPLACE TABLE d1 (
  id number,
  name string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D1 successfully created. |
+--------------------------------+
INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
CREATE OR REPLACE TABLE d2 (
  id number,
  value string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D2 successfully created. |
+--------------------------------+
INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
SELECT *
    FROM d1 NATURAL INNER JOIN d2
    ORDER BY id;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

Copy

Natürliche Verknüpfungen können mit äußeren Verknüpfungen kombiniert werden. Beispiel:

SELECT *
  FROM d1 NATURAL FULL OUTER JOIN d2
  ORDER BY ID;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
|  4 | c    | NULL  |
|  5 | NULL | zz    |
+----+------+-------+

Copy

Verknüpfungen können in der FROM-Klausel miteinander kombiniert werden. Mit dem folgenden Code wird zuerst eine dritte Tabelle erstellt, und dann werden in der FROM-Klausel zwei JOINs verkettet:

CREATE TABLE t3 (col1 INTEGER);
INSERT INTO t3 (col1) VALUES 
   (2),
   (6);

Copy

SELECT t1.*, t2.*, t3.*
  FROM t1
     LEFT OUTER JOIN t2 ON (t1.col1 = t2.col1)
     RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1)
  ORDER BY t1.col1;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
| NULL | NULL |    6 |
+------+------+------+

Copy

In einer solchen Abfrage werden die Ergebnisse basierend auf den Verknüpfungen von links nach rechts bestimmt (wobei der Optimierer die Verknüpfungen neu anordnen kann, wenn eine andere Verknüpfungsreihenfolge das gleiche Ergebnis liefert). Wenn die rechte äußere Verknüpfung vor der linken äußeren Verknüpfung erfolgen soll, kann die Abfrage wie folgt geschrieben werden:

SELECT t1.*, t2.*, t3.*
  FROM t1
     LEFT OUTER JOIN
     (t2 RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1))
     ON (t1.col1 = t2.col1)
  ORDER BY t1.col1;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
+------+------+------+

Copy

Die beiden folgenden Beispiele zeigen die standardmäßige (ISO 9075) und die nicht standardmäßige Verwendung der USING-Klausel. Beide werden von Snowflake unterstützt.

Dieses erste Beispiel zeigt die übliche Verwendung. Konkret enthält die Projektionsliste „*“. Obwohl die Beispielabfrage zwei Tabellen verbindet, und jede Tabelle über eine Spalte verfügt, und die Abfrage nach allen Spalten fragt, enthält die Ausgabe nur eine und nicht zwei Spalten.

WITH
    l AS (
         SELECT 'a' AS userid
         ),
    r AS (
         SELECT 'b' AS userid
         )
  SELECT *
    FROM l LEFT JOIN r USING(userid)
;
+--------+
| USERID |
|--------|
| a      |
+--------+

Copy

Das folgende Beispiel zeigt eine nicht standardmäßige Verwendung. Die Projektionsliste enthält etwas anderes als „*“. Die Ausgabe enthält zwei Spalten, wobei die zweite Spalte entweder einen Wert aus der zweiten Tabelle oder NULL enthält.

WITH
    l AS (
         SELECT 'a' AS userid
       ),
    r AS (
         SELECT 'b' AS userid
         )
  SELECT l.userid as UI_L,
         r.userid as UI_R
    FROM l LEFT JOIN r USING(userid)
;
+------+------+
| UI_L | UI_R |
|------+------|
| a    | NULL |
+------+------+

Copy