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

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.

SELECT * FROM projects ORDER BY project_ID;
+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+
Copy
SELECT * FROM employees ORDER BY employee_ID;
+-------------+-----------------+------------+
| EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|-------------+-----------------+------------|
|    10000001 | Terry Smith     | 1000       |
|    10000002 | Maria Inverness | 1000       |
|    10000003 | Pat Wang        | 1001       |
|    10000004 | NewEmployee     | NULL       |
+-------------+-----------------+------------+
Copy

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

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)
Copy

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 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 Details 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)

Innere Verknüpfung (Inner Join)

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

Im folgenden Beispiel wird eine innere Verknüpfung verwendet:

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

In diesem Beispiel enthält die Ausgabetabelle zwei Spalten mit dem Namen „Project_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 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;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
+------------------+-----------------+
Copy

Das Projekt mit dem Namen „NewProject“ ist in dieser Ausgabe enthalten, obwohl es in der Mitarbeitertabelle keine übereinstimmende Zeile. 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;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NULL             | NewEmployee     |
+------------------+-----------------+
Copy

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;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
| NULL             | NewEmployee     |
+------------------+-----------------+
Copy

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 (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
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| 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     |
+------------------+-----------------+
Copy

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 CROSS JOIN employees AS e
    WHERE e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+
Copy

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;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+
Copy

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. In den oben gezeigten Mitarbeiter- und Projekttabellen haben beide Tabellen Spalten 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 werden alle gemeinsamen Spalten in der ON-Klausel verwendet. Wenn Sie z. B. zwei Tabellen haben, die jeweils Spalten mit den Namen „city“ und „state“ haben, dann würde eine natürliche Verknüpfung die folgende ON-Klausel konstruieren:

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

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 Spalte „project_ID“ alles bis auf ein Kopie weggelassen wird:

SELECT *
    FROM projects NATURAL JOIN employees
    ORDER BY employee_ID;
+------------+------------------+-------------+-----------------+
| 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        |
+------------+------------------+-------------+-----------------+
Copy

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 ON-Unterklausel in 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.

Sprache: Deutsch