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

Joins are useful when the data in the tables is related. For example, one table might hold information about projects, and one table might hold information about employees working on those projects.

SELECT * FROM projects ORDER BY project_ID;
+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+
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       |
+-------------+-----------------+------------+

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

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)

An inner join pairs each row in one table with the matching row(s) in the other table.

The example below uses an inner join:

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

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.

The output includes only valid pairs (i.e. rows that match the join condition). In this example there is no row for the project named „NewProject“ (which has no employees assigned yet) or the employee named „NewEmployee“ (who hasn’t been assigned to any projects yet).

Äußerer Verknüpfung (Outer Join)

An outer join lists all rows in the specified table, even if those rows have no match in the other table. For example, a left outer join between projects and employees lists all projects, including projects that do not yet have any employee assigned.

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

The project named „NewProject“ is included in this output even though there is no matching row in the employees table. Because there are no matching employee names for the project named „NewProject“, the employee name is set to NULL.

A right outer join lists all employees (regardless of project).

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

A full outer join lists all projects and all employees.

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

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

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

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

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)

A natural join is used when two tables contain columns that have the same name and in which the data in those columns corresponds. In the employees and projects tables shown above, both tables have columns named „project_ID“. A natural join implicitly constructs the ON clause: ON projects.project_ID = employees.project_ID.

If two tables have multiple columns in common, then all the common columns are used in the ON clause. For example, if you had two tables that each had columns named „city“ and „province“, then a natural join would construct the following ON clause:

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

The output of a natural join includes only one copy of each of the shared columns. For example, the following query produces a natural join containing all columns in the two tables, except that it omits all but one copy of the redundant project_ID column:

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

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 recommends using the ON sub-clause in the FROM clause. The syntax is more flexible. And specifying the predicate in the ON clause avoids the problem of accidentally filtering rows with NULLs when using a WHERE clause to specify the join condition for an outer join.