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.

Unter diesem Thema:

Einführung

Beim Verknüpfen zweier Tabellen stehen die Daten in den beiden Tabellen auf bestimmte Weise zueinander in Beziehung. Beispielsweise kann eine Tabelle Informationen über Projekte enthalten, während eine andere Tabelle Informationen über die Mitarbeiter enthält, die an diesen Projekten arbeiten.

+------------+------------------+
| Project_ID | Project_Name     |
+------------+------------------+
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+

+-------------+------------------+------------+
| Employee_ID | Employee_Name    | Project_ID |
+-------------+------------------+------------+
|    10000001 | Terry Smith      |       1000 |
|    10000002 | Maria Inverness  |       1000 |
|    10000003 | Pat Wang         |       1001 |
|    99999999 | 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 kann zum Beispiel jede Zeile der Projekttabelle eine eindeutige Projekt-ID enthalten, und jede Zeile in der Mitarbeitertabelle kann die Projekt-ID enthalten, 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 die gemeinsamen Spalten (wie die Spalte mit den Projekt-IDs). Beispielsweise werden im Folgenden die Projekttabelle und die Mitarbeitertabelle miteinander verknüpft:

SELECT *
    FROM projects AS p JOIN employees AS e
        ON e.project_ID = p.project_ID;

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 table 3)

In diesem Pseudocode werden zuerst „table2“ und „table3“ 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 diese Thema auf das Verknüpfen einer Tabelle verwiesen wird, ist damit im Allgemeinen das Verknüpfen eines beliebigen tabellenartigen Objekts gemeint.

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)

Innere Verknüpfung (Inner Join)

Jede Zeile der einen Tabelle wird mit der/den passende(n) Zeile(n) der anderen Tabelle gepaart.

Angenommen, Sie verwenden die oben gezeigten Tabellen in der folgenden Abfrage:

SELECT *
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_id = p.project_id;

Das Ergebnis würde ungefähr wie folgt aussehen:

+-------------+------------------+------------+------------+------------------+
| Employee_ID | Employee_Name    | Project_ID | Project_ID | Project_name     |
+-------------+------------------+------------+------------+------------------+
|    10000001 | Terry Smith      |       1000 |       1000 | COVID-19 Vaccine |
|    10000002 | Maria Inverness  |       1001 |       1001 | Malaria Vaccine  |
|    10000003 | Pat Wang         |       1001 |       1001 | Malaria Vaccine  |
+-------------+------------------+------------+------------+------------------+

In diesem Beispiel enthält die Ausgabetabelle zwei Spalten mit dem Namen „Projekt_ID“. Eine „Project_ID“-Spalte stammt aus der Projekttabelle und die andere aus der Mitarbeitertabelle. Für jede Zeile in der Ausgabetabelle 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 der Verknüpfungsbedingung entsprechen. In diesem Beispiel gibt es keine Zeile für das neue Projekt (dem noch keine Mitarbeiter zugeordnet sind) und den neuen Mitarbeiter (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 würde eine linke äußere Verknüpfung (Left Outer Join) zwischen Projekten und Mitarbeitern alle Projekte auflisten, 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;

Das Ergebnis würde ungefähr wie folgt aussehen:

+------------------+------------------+
| Project_name     | Employee_Name    |
+------------------+------------------+
| COVID-19 Vaccine | Terry Smith      |
| Malaria Vaccine  | Maria Inverness  |
| Malaria Vaccine  | Pat Wang         |
| New Project      | NULL             |
+------------------+------------------+

Das Projekt mit dem Namen „New Project“ ist in dieser Ausgabe enthalten, obwohl es in der Mitarbeitertabelle keine entsprechende Zeile gibt. Da es keine passenden Mitarbeiternamen für das Projekt mit dem Namen „New Project“ gibt, wird der Mitarbeitername auf NULL gesetzt.

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

Eine vollständige äußere Verknüpfung listet alle Projekte und alle Mitarbeiter auf. Die Ausgabe würde ungefähr so aussehen:

+------------------+------------------+
| Project_name     | Employee_Name    |
+------------------+------------------+
| COVID-19 Vaccine | Terry Smith      |
| Malaria Vaccine  | Maria Inverness  |
| Malaria Vaccine  | Pat Wang         |
| New Project      | NULL             |
| NULL             | NewEmployee      |
+------------------+------------------+

Kreuzverknüpfung (Cross Join)

Bei einer Kreuzverknüpfung wird jede Zeile in der ersten Tabelle mit jeder Zeile in der zweiten Tabelle kombiniert, wodurch jede mögliche Kombination von Zeilen entsteht (auch „Kartesisches Produkt“ genannt). 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):

SELECT p.project_name, e.employee_name
    FROM projects AS p CROSS JOIN employees AS e;

Beachten Sie, dass diese Abfrage keine ON-Klausel und keinen Filter enthält.

Die Ausgabe einer Kreuzverknüpfung kann durch Anwendung eines Filters in der WHERE-Klausel sinnvoller gestaltet werden:

SELECT p.project_name, e.employee_name
    FROM projects AS p INNER JOIN employees AS e
    WHERE e.project_ID = p.project_ID;

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;

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 vs. 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 „table2“ kommen, für Zeilen von „table1“, 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 wirklich einer äußeren Verknüpfung.

Natürliche Verknüpfung (Natural Join)

Eine natürliche Verknüpfung wird verwendet, wenn zwei Tabellen Spalten enthalten, die denselben Namen haben und in denen die Daten in diesen Spalten übereinstimmen. Nehmen wir beispielsweise zwei Tabellen, die jeweils eine Spalte mit dem Namen „city“ und eine Spalte mit dem Namen „province“ enthalten. Eine natürliche Verknüpfung konstruiert implizit die ON-Klausel ON table2.city = table1.city AND table2.province = table1.province.

Eine natürliche Verknüpfung beinhaltet automatisch nur eine Kopie jeder dieser Spalten in der Ausgabe.

Beispielsweise würde die folgende Abfrage einen natürliche Verknüpfung erzeugen, die alle Spalten der beiden Tabellen enthält, mit der Ausnahme, dass redundante Informationen zu „city“ und „province“ aus einer der beiden Tabellen weggelassen werden:

SELECT *
    FROM geographic_data_by_city_and_province NATURAL JOIN demographic_data_by_city_and_province;

Eine natürliche Verknüpfung kann mit einer äußeren Verknüpfung kombiniert werden.

Eine natürliche Verknüpfung kann nicht mit einer ON-Klausel 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 den Operator JOIN in der Unterklausel ON der FROM-Klausel.

  • Verwenden Sie von WHERE mit der FROM-Klausel.

Snowflake empfiehlt die Verwendung der FROM-Klausel. Diese Syntax ist flexibler. Und die Spezifizierung des Prädikats in der ON-Klausel 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.