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.

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:

Seitliche Verknüpfung (Lateral Join)

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