JOIN¶

Syntax¶

Verwenden Sie eine der folgenden Optionen:

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

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

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

Parameter¶

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 (Join) 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. (Sie können auch ein Komma verwenden, um eine innere Verknüpfung anzugeben. Ein Beispiel dazu finden Sie im Beispielabschnitt). 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.) Beispiele dazu finden Sie im Beispielabschnitt. Ein NATURAL JOIN kann mit einem OUTER JOIN kombiniert werden. Ein NATURAL JOIN kann nicht mit einer ON condition-Klausel kombiniert werden, da die JOIN-Bedingung bereits impliziert ist. Sie können jedoch eine WHERE-Klausel verwenden, um die Ergebnisse zu filtern.

Das DIRECTED -Schlüsselwort gibt eine gezielte Verknüpfung an, die die Join-Reihenfolge der Tabellen erzwingt. Die erste oder linke Tabelle wird vor der zweiten oder rechten Tabelle durchsucht. Beispiel: o1 INNER DIRECTED JOIN o2 scannt die Tabelle o1 vor der Tabelle o2. Gezielte Verknüpfungen sind in den folgenden Situationen nützlich:

• Sie migrieren Workloads mit Richtlinien für die Verknüpfungsreihenfolge nach Snowflake.

• Sie möchten die Leistung verbessern, indem Sie Verknüpfungstabellen in einer bestimmten Reihenfolge scannen.

Bemerkung

Die gezielte Verknüpfung ist ein Vorschau-Feature, das für alle Konten verfügbar ist.

Standard: INNER JOIN

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

Wenn das Schlüsselwort DIRECTED hinzugefügt wird, ist der Verknüpfungstyp – zum Beispiel INNER oder OUTER – erforderlich.

Siehe auch:

• LATERAL

• ASOF JOIN

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; da die Verknüpfungsspalten impliziert sind.

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 der Spalten, die die beiden Tabellen, die verbunden werden sollen, gemeinsam haben. Diese Spalten werden als Verknüpfungsspalten 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 SELECT-Schlüsselwort) * 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 im Beispielabschnitt.

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 angeben, außer in einer SQL UDTF.

    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 angeben, außer in einer SQL UDTF.

    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 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 JOIN-Schlüsselworts 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. Erstellen Sie diese Tabellen und fügen Sie Daten ein:

CREATE TABLE t1 (col1 INTEGER);

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

CREATE TABLE t2 (col1 INTEGER);

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

Die folgenden Beispiele führen Abfragen mit Joins aus:

• Führen Sie eine Abfrage mit einer inneren Verknüpfung aus

• Führen Sie eine Abfrage mit einer linken äußeren Verknüpfung aus

• Führen Sie eine Abfrage mit einer rechten äußeren Verknüpfung aus

• Führen Sie eine Abfrage mit einer vollständigen äußeren Verknüpfung aus

• Ausführen einer Abfrage mit einer Kreuzverknüpfung

• Ausführen einer Abfrage mit einer natürlichen Verknüpfung

• Ausführen einer Abfrage, die Verknüpfungen in der FROM-Klausel kombiniert

• Ausführen von Abfragen mit Joins, die die USING-Klausel verwenden

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

SELECT t1.col1, t2.col1
  FROM t1 INNER DIRECTED JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

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

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

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

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

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

CREATE OR REPLACE TABLE d1 (
  id NUMBER,
  name VARCHAR);

INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');

CREATE OR REPLACE TABLE d2 (
  id NUMBER,
  value VARCHAR);

INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');

SELECT *
  FROM d1 NATURAL INNER JOIN d2
  ORDER BY id;

+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

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

CREATE TABLE t3 (col1 INTEGER);

INSERT INTO t3 (col1) VALUES
  (2),
  (6);

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

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

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

+--------+
| USERID |
|--------|
| a      |
+--------+

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