Kategorien:: Abfragesyntax

Verwenden von Verknüpfungen (Joins)¶

Bei einer Verknüpfung werden Zeilen aus zwei Tabellen kombiniert, um eine neue kombinierte Zeile zu erstellen, die in der Abfrage verwendet werden kann.

Einführung¶

Verknüpfungen (Joins) sind nützlich, wenn die Daten in den Tabellen miteinander in Beziehung stehen. Beispielsweise kann eine Tabelle Informationen über Projekte enthalten, während eine andere Tabelle Informationen über die Mitarbeiter enthält, die an diesen Projekten arbeiten.

CREATE TABLE projects (
  project_id INT,
  project_name VARCHAR);

INSERT INTO projects VALUES
  (1000, 'COVID-19 Vaccine'),
  (1001, 'Malaria Vaccine'),
  (1002, 'NewProject');

CREATE TABLE employees (
  employee_id INT,
  employee_name VARCHAR,
  project_id INT);

INSERT INTO employees VALUES
  (10000001, 'Terry Smith', 1000),
  (10000002, 'Maria Inverness', 1000),
  (10000003, 'Pat Wang', 1001),
  (10000004, 'NewEmployee', NULL);

Copy

Fragen Sie die Tabellen ab, um die Daten anzuzeigen:

SELECT * FROM projects ORDER BY project_ID;

Copy

+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+

SELECT * FROM employees ORDER BY employee_ID;

Copy

+-------------+-----------------+------------+
| EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|-------------+-----------------+------------|
|    10000001 | Terry Smith     |       1000 |
|    10000002 | Maria Inverness |       1000 |
|    10000003 | Pat Wang        |       1001 |
|    10000004 | NewEmployee     |       NULL |
+-------------+-----------------+------------+

Die beiden verknüpften Tabellen haben in der Regel eine oder mehrere Spalten gemein, sodass die Zeilen einer Tabelle den entsprechenden Zeilen der anderen Tabelle zugeordnet werden können. So hat zum Beispiel in diesen Beispieltabellen jede Zeile der Projekttabelle eine eindeutige Projekt-ID-Nummer, und jede Zeile in der Mitarbeitertabelle enthält die ID-Nummer des Projekts, die dem Mitarbeiter derzeit zugeordnet ist.

Mit der Verknüpfungsoperation wird (explizit oder implizit) festgelegt, wie Zeilen der einen Tabelle mit den entsprechenden Zeilen der anderen Tabelle in Beziehung gesetzt werden sollen, typischerweise durch Bezugnahme auf eine oder mehrere gemeinsame Spalten (z. B. project_id). Beispielsweise verknüpft Folgendes die Tabelle projects und die Tabelle employees, die zuvor erstellt wurden:

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
  FROM projects AS p JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_ID, e.employee_ID;

Copy

+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |       1000 |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |       1000 |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |       1001 |
+------------+------------------+-------------+-----------------+------------+

Eine einzelne Verknüpfungsoperation kann zwar nur zwei Tabellen verknüpfen, aber Verknüpfungen können miteinander verkettet werden. Das Ergebnis einer Verknüpfung ist ein tabellenartiges Objekt, und dieses tabellenartige Objekt kann dann wiederum mit einem anderen tabellenartigen Objekt verknüpft werden. Konzeptionell ist die Idee ungefähr wie folgt (dies ist nicht die eigentliche Syntax):

table1 JOIN (table2 JOIN table3)

Copy

In diesem Pseudocode werden table2 und table3 zuerst verknüpft. Die Tabelle, die das Ergebnis dieser Verknüpfungsoperation ist, wird dann mit table1 verknüpft.

Verknüpfungen können nicht nur auf Tabellen, sondern auch auf andere tabellenartige Objekte angewendet werden. Sie können Folgendes verknüpfen:

Eine Tabelle
Eine Ansicht (materialisiert oder nicht materialisiert)
Ein Tabellenliteral
Ein Ausdruck, der als Ergebnis das Äquivalent einer Tabelle (mit einer oder mehreren Spalten und null oder mehreren Zeilen) liefert. Beispiel:
- Das von einer Tabellenfunktion zurückgegebene Resultset.
- Das von einer Unterabfrage zurückgegebene Resultset, das eine Tabelle zurückgibt.

Wenn unter diesem Thema auf das Verknüpfen einer Tabelle verwiesen wird, ist damit im Allgemeinen das Verknüpfen eines beliebigen tabellenartigen Objekts gemeint.

Bemerkung

Snowflake kann die Performance verbessern, indem unnötige Join-Verknüpfungen beseitigt werden. Weitere Informationen dazu finden Sie unter Erläuterungen zum Beseitigen redundanter Join-Verknüpfungen durch Snowflake.

Typen von Verknüpfungen (Joins)¶

Snowflake unterstützt die folgenden Typen von Verknüpfungen:

Innere Verknüpfung (Inner Join)
Äußerer Verknüpfung (Outer Join)
Kreuzverknüpfung (Cross Join)
Natürliche Verknüpfung (Natural Join)

Bemerkung

Snowflake unterstützt auch ASOF JOIN zur Analyse von Zeitreihendaten. Weitere Informationen dazu finden Sie unter ASOF JOIN und Analysieren von Zeitreihendaten.

Innere Verknüpfung (Inner Join)¶

Bei einer inneren Verknüpfung wird jede Zeile der einen Tabelle mit den übereinstimmenden Zeilen der anderen Tabelle gepaart.

Das folgende Beispiel zeigt eine innere Verknüpfung:

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
  FROM projects AS p INNER JOIN employees AS e
    ON e.project_id = p.project_id
  ORDER BY p.project_ID, e.employee_ID;

Copy

+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |       1000 |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |       1000 |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |       1001 |
+------------+------------------+-------------+-----------------+------------+

In diesem Beispiel enthält die Ausgabe zwei Spalten mit dem Namen PROJECT_ID. Eine Spalte des Typs PROJECT_ID stammt aus der Tabelle projects und die andere stammt aus der Tabelle employees. Für jede Zeile in der Ausgabe stimmen die Werte in den beiden PROJECT_ID-Spalten überein, weil in der Abfrage e.project_id = p.project_id angegeben ist.

Die Ausgabe enthält nur gültige Paare (d. h. Zeilen, die die Verknüpfungsbedingung erfüllen). In diesem Beispiel gibt es keine Zeile für das Projekt NewProject (dem noch keine Mitarbeiter zugeordnet sind) und dem Mitarbeiter NewEmployee (der noch keinem Projekt zugeordnet ist).

Äußerer Verknüpfung (Outer Join)¶

Eine äußere Verknüpfung listet alle Zeilen der angegebenen Tabelle auf, auch wenn diese Zeilen keine Übereinstimmung in der anderen Tabelle haben. Beispielsweise listet eine linke äußere Verknüpfung (Left Outer Join) zwischen Projekten und Mitarbeitern alle Projekte auf, einschließlich der Projekte, denen noch kein Mitarbeiter zugeordnet ist.

SELECT p.project_name, e.employee_name
  FROM projects AS p LEFT OUTER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_name, e.employee_name;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
+------------------+-----------------+

Das Projekt mit dem Namen NewProject ist in dieser Ausgabe enthalten, obwohl es in der employees-Tabelle keine entsprechende Zeile gibt. Da es keine übereinstimmenden Mitarbeiternamen für das Projekt mit dem Namen NewProject gibt, wird der Mitarbeitername auf NULL gesetzt.

Bei einer rechten äußeren Verknüpfung (Right Outer Join) werden alle Mitarbeiter (unabhängig vom Projekt) aufgelistet.

SELECT p.project_name, e.employee_name
  FROM projects AS p RIGHT OUTER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_name, e.employee_name;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NULL             | NewEmployee     |
+------------------+-----------------+

Eine vollständige äußere Verknüpfung (Full Outer Join) listet alle Projekte und alle Mitarbeiter auf.

SELECT p.project_name, e.employee_name
  FROM projects AS p FULL OUTER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_name, e.employee_name;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
| NULL             | NewEmployee     |
+------------------+-----------------+

Kreuzverknüpfung (Cross Join)¶

Bei einer Kreuzverknüpfung wird jede Zeile der ersten Tabelle mit jeder Zeile der zweiten Tabelle kombiniert, wodurch alle möglichen Kombinationen von Zeilen erstellt werden. Dies wird als Kartesisches Produkt bezeichnet. Da die meisten Ergebniszeilen Teile von Zeilen enthalten, die eigentlich nicht miteinander in Beziehung stehen, ist eine Kreuzverknüpfung an sich selten sinnvoll. Tatsächlich sind Kreuzverknüpfungen in der Regel das Ergebnis eines versehentlichen Weglassens der Verknüpfungsbedingung.

Das Ergebnis einer Kreuzverknüpfung kann sehr groß und teuer sein. Wenn die erste Tabelle N Zeilen und die zweite Tabelle M Zeilen enthält, dann ist das Ergebnis N × M Zeilen. Beispiel: Wenn die erste Tabelle 100 Zeilen und die zweite Tabelle 1.000 Zeilen enthält, dann enthält das Resultset 100.000 Zeilen.

Die folgende Abfrage zeigt eine Kreuzverknüpfung (Cross Join):

Bemerkung

Diese Abfrage enthält keine ON-Klausel und keinen Filter.

SELECT p.project_name, e.employee_name
  FROM projects AS p CROSS JOIN employees AS e
  ORDER BY p.project_ID, e.employee_ID;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Pat Wang        |
| COVID-19 Vaccine | NewEmployee     |
| Malaria Vaccine  | Terry Smith     |
| Malaria Vaccine  | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
| Malaria Vaccine  | NewEmployee     |
| NewProject       | Terry Smith     |
| NewProject       | Maria Inverness |
| NewProject       | Pat Wang        |
| NewProject       | NewEmployee     |
+------------------+-----------------+

Sie können die Ausgabe einer Kreuzverknüpfung durch Anwendung eines Filters in der WHERE-Klausel sinnvoller gestalten:

SELECT p.project_name, e.employee_name
  FROM projects AS p CROSS JOIN employees AS e
  WHERE e.project_ID = p.project_ID
  ORDER BY p.project_ID, e.employee_ID;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

Das Ergebnis dieser Kreuzverknüpfung mit Filter ist das gleiche wie das Ergebnis der folgenden inneren Verknüpfung (Inner Join):

SELECT p.project_name, e.employee_name
  FROM projects AS p INNER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_ID, e.employee_ID;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

Wichtig

Obwohl die beiden Abfragen in diesem Beispiel die gleiche Ausgabe erzeugen, wenn sie die gleiche Bedingung (e.project_id = p.project_id) in verschiedenen Klauseln (WHERE und FROM ... ON ...) verwenden, ist es möglich, Paare von Abfragen zu konstruieren, die die gleiche Bedingung verwenden, aber nicht die gleiche Ausgabe erzeugen.

Die häufigsten Beispiele betreffen äußere Verknüpfungen (Outer Joins). Wenn Sie table1 LEFT OUTER JOIN table2 ausführen, dann enthalten die Spalten, die aus table1 kommen, für Zeilen von table2, die keine Übereinstimmung haben, den Wert NULL. Ein Filter wie WHERE table2.ID = table1.ID filtert Zeilen heraus, in denen entweder table2.id oder table1.id einen NULL-Wert enthält. Bei Verwendung einer expliziten äußeren Verknüpfung in der FROM ... ON ...-Klausel werden Zeilen mit NULL-Werten jedoch nicht herausfiltert. Mit anderen Worten, eine äußere Verknüpfung mit einem Filter entspricht nicht einer äußeren Verknüpfung.

Natürliche Verknüpfung (Natural Join)¶

Eine natürliche Verknüpfung verknüpft zwei Tabellen über Spalten, die denselben Namen und kompatible Datentypen haben. Sowohl die Tabelle employees als auch die Tabelle projects, die zuvor erstellt wurden, haben eine Spalte mit dem Namen project_ID. Eine natürliche Verknüpfung konstruiert die ON-Klausel ON projects.project_ID = employees.project_ID.

Wenn zwei Tabellen mehrere Spalten gemeinsam haben, dann verwendet eine natürliche Verknüpfung alle gemeinsamen Spalten in der konstruierten ON-Klausel . Wenn beispielsweise zwei Tabellen jeweils Spalten mit den Namen city und province haben, erstellt eine natürliche Verknüpfung die folgende ON-Klausel:

ON table2.city = table1.city AND table2.province = table1.province

Copy

Die Ausgabe einer natürlichen Verknüpfung enthält nur eine Kopie jeder der gemeinsamen Spalten. Beispielsweise erzeugt die folgende Abfrage eine natürliche Verknüpfung, die alle Spalten der beiden Tabellen enthält, in der aber von der redundanten project_id-Spalten alles bis auf ein Kopie weggelassen wird:

SELECT *
  FROM projects NATURAL JOIN employees
  ORDER BY employee_id;

Copy

+------------+------------------+-------------+-----------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   |
|------------+------------------+-------------+-----------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |
+------------+------------------+-------------+-----------------+

Sie können eine natürliche Verknüpfung mit einer äußeren Verknüpfung kombinieren.

Die ON-Klausel einer natürlichen Verknüpfung kann nicht kombiniert werden, da die Verknüpfungsbedingung bereits impliziert ist. Sie können jedoch eine WHERE-Klausel verwenden, um die Ergebnisse einer natürlichen Verknüpfung zu filtern.

Implementierung von Verknüpfungen¶

Syntaktisch gibt es zwei Möglichkeiten, Tabellen zu verknüpfen:

Verwenden Sie die JOIN-Unterklausel in der ON-Unterklausel der FROM-Klausel.
Verwenden Sie die WHERE-Klausel mit der FROM-Klausel.

Snowflake empfiehlt die Verwendung der ON-Unterklausel in der FROM-Klausel, da die Syntax flexibler ist. Und die Spezifizierung des Prädikats in der ON-Unterklausel vermeidet das Problem des versehentlichen Filterns von Zeilen mit NULL-Werten, wenn Sie mit einer WHERE-Klausel die Verknüpfungsbedingung für eine äußere Verknüpfung spezifizieren möchten.

Darüber hinaus können Sie das Schlüsselwort DIRECTED verwenden, um die Verknüpfungsreihenfolge der Tabellen zu erzwingen. Wenn Sie dieses Schlüsselwort angeben, wird die erste oder linke Tabelle vor der zweiten oder rechten Tabelle gescannt. Beispiel: o1 INNER DIRECTED JOIN o2 scannt die Tabelle o1 vor der Tabelle o2. Wenn das Schlüsselwort DIRECTED hinzugefügt wird, ist der Verknüpfungstyp – zum Beispiel INNER oder OUTER – erforderlich. Weitere Informationen dazu finden Sie unter JOIN.

Bemerkung

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