Seitliche Verknüpfungen verwenden

In einer FROM-Klausel ermöglicht das LATERAL Konstrukt, auf Spalten aus einem Tabellenausdruck zu verweisen, der dieser Inline-Ansicht vorausgeht.

Wenn die Inline-Ansicht zum Beispiel eine Unterabfrage ist, kann die Unterabfrage Zeilen aus der Tabelle links von der Unterabfrage verarbeiten. Beispiel:

SELECT ...
  FROM left_hand_table_expression AS lhte,
    LATERAL (SELECT col_1 FROM table_2 AS t2 WHERE t2.col_1 = lhte.col_1);
Copy

Dieses Verhalten ähnelt in gewisser Weise einer korrelierten Unterabfrage. Die Unterabfrage nach dem LATERAL-Schlüsselwort ähnelt der korrelierten Unterabfrage selbst, und die left_hand_table_expression ähnelt der äußeren Abfrage. Ein Lateral Join kann im Gegensatz zu einer korrelierten Unterabfrage mehrere Zeilen zurückgeben, von denen jede mehrere Spalten haben kann.

Bei anderen Arten von Joins werden die Zeilen des linken Tabellenausdrucks nicht direkt zur Verarbeitung an den rechten Tabellenausdruck weitergegeben.

Eine häufige Verwendung für einen Lateral Join ist die Kombination mit einem Aufruf der Tabellenfunktion FLATTEN, um eine komplexe Datenstruktur zu verarbeiten, wie z. B. einen ARRAY-oder VARIANT-Datentyp, und die Werte zu extrahieren. Ein Beispiel dazu finden Sie unter LATERAL.

Im Gegensatz zur Ausgabe anderer Arten von Joins enthält die Ausgabe eines Lateral Join nur die Zeilen, die aus der Inline-Ansicht (der Unterabfrage) generiert wurden. Nachdem die Zeilen aus der Unterabfrage generiert wurden, werden sie nicht mit allen Zeilen aus der Tabelle auf der linken Seite gekreuzt verknüpft.

Unter diesem Thema:

Terminologie

Betrachten Sie das folgende Codefragment:

... FROM te1, LATERAL iv1 ...
Copy

Die linke Seite des Lateral Join ist ein Tabellenausdruck (te1). Die rechte Seite des Lateral Joins ist eine Inline-Ansicht (iv1).

  • Tabellenausdruck: In diesem Thema kann der Tabellenausdruck auf der linken Seite eines Lateral Join, wie beispielsweise der oben genannte Tabellenausdruck te1, fast jeder gültige Ausdruck sein, der zu einer Tabelle ausgewertet wird. Beispiel:

    • Eine Tabelle

    • Eine Ansicht

    • Eine Unterabfrage

    • Die Ausgabe einer Tabellenfunktion.

    • Das Ergebnis eines früheren Joins (eines Lateral Join oder einer anderen Art von Join).

  • Inline-Ansicht: In diesem Thema wird der Ausdruck auf der rechten Seite eines Lateral Join (in diesem Fall iv1) als „Inline-Ansicht“ bezeichnet. In diesem Zusammenhang kann eine gültige Inline-Ansicht eine der folgenden sein:

    • Eine Ansicht, die innerhalb der Anweisung definiert ist und nur für die Dauer der Anweisung gilt.

    • Eine Unterabfrage

    • Eine Tabellenfunktion: entweder eine integrierte Tabellenfunktion wie FLATTEN oder eine benutzerdefinierte Tabellenfunktion (UDTF).

    Die Inline-Ansicht kann keine Tabelle sein.

  • Cross Join: Unter diesem Thema bezieht sich der Begriff „Cross Join“ nicht nur auf explizite Cross Joins, sondern auch auf Inner Joins und Outer Joins, einschließlich aller Varianten (Natural Joins, Left/Right/Full Outer Joins usw.).

Eine Auffrischung zum Thema Joins

Ein Join ist ein zweistufiger Prozess. Zunächst stellt der Server zwei Zeilen zusammen, die sich in der Regel in verschiedenen Tabellen befinden und die fast immer in irgendeiner Weise miteinander verbunden sind. Zweitens fügt der Server die Spalten jeder Zeile des Paares zu einer einzigen Zeile zusammen.

Viele der Beispielabfragen verwenden die unten gezeigten Daten:

CREATE TABLE departments (department_id INTEGER, name VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, last_name VARCHAR,
  department_ID INTEGER, project_names ARRAY);

INSERT INTO departments (department_ID, name) VALUES
  (1, 'Engineering'),
  (2, 'Support');
INSERT INTO employees (employee_ID, last_name, department_ID) VALUES
  (101, 'Richards', 1),
  (102, 'Paulson',  1),
  (103, 'Johnson',  2);
Copy

Hier ist eine einfacher Inner Join (dies ist keine Lateral Join):

SELECT *
  FROM departments AS d, employees AS e
  WHERE e.department_ID = d.department_ID
  ORDER BY employee_ID;
Copy
+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME        | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
|             1 | Engineering |         101 | Richards  |             1 | NULL          |
|             1 | Engineering |         102 | Paulson   |             1 | NULL          |
|             2 | Support     |         103 | Johnson   |             2 | NULL          |
+---------------+-------------+-------------+-----------+---------------+---------------+

Wie Sie sehen können, werden die Zeilen anhand der übereinstimmenden Abteilungs-IDs gepaart.

Der Join nimmt die Spalten aus zwei entsprechenden („gepaarten“) Eingabezeilen und erzeugt eine Ausgabezeile, die alle Spalten aus beiden Eingabezeilen enthält. (Natürlich können Sie durch Ändern der SELECT-Liste die Spalten ändern; im einfachsten Fall werden jedoch alle Eingabespalten in die Ausgabe übernommen.)

Bei einem Lateral Join werden Zeilen anders gepaart. Die zweite Hälfte des Prozesses, der „Join“ von gepaarten Zeilen, ist jedoch ähnlich: Die Ausgabezeile enthält (fast immer) eine oder mehrere Spalten aus jedem Mitglied des Paares von Eingabezeilen.

Wie ein Lateral Join Zeilen paart

Ein Lateral Join verhält sich anders als andere Arten von Joins. Ein lateraler Join verhält sich so, als hätte der Server eine Schleife ausgeführt, die der folgenden ähnelt:

for each row in left_hand_table LHT:
  execute right_hand_subquery RHS using the values from the current row in the LHT
Copy

Dieser Abschnitt konzentriert sich auf den Teil des Prozesses, der sich bei Lateral Joins unterscheidet, nämlich das „Pairing“.

Das LATERAL-Konstrukt ermöglicht eine Inline-Ansicht auf der rechten Seite des Lateral Join, um auf Spalten aus einer Tabellenausdruck zu verweisen, die sich außerhalb der Ansicht befindet. (In dem Beispiel unten ist die „Inline-Ansicht“ eigentlich eine Unterabfrage)

SELECT *
  FROM departments AS d,
    LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
  ORDER BY employee_ID;
Copy
+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME        | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
|             1 | Engineering |         101 | Richards  |             1 | NULL          |
|             1 | Engineering |         102 | Paulson   |             1 | NULL          |
|             2 | Support     |         103 | Johnson   |             2 | NULL          |
+---------------+-------------+-------------+-----------+---------------+---------------+

In diesem Beispiel bezieht sich die WHERE-Klausel in der Unterabfrage auf der rechten Seite auf einen Wert aus der Tabelle auf der linken Seite.

Die Unterschiede zwischen einem Lateral Join und einem Cross Join sind viel größer als nur der Zugriff auf Spalten. In den nächsten Abschnitten werden diese beiden Arten von Verknüpfungen gegenübergestellt, beginnend mit dem traditionellen Cross Join.

Ein Cross Join kombiniert jede Zeile der Tabelle auf der linken Seite mit jeder Zeile der Tabelle auf der rechten Seite. Das Ergebnis ist ein kartesisches Produkt.

Konzeptionell ähnelt eine Cross Join einer verschachtelten Schleife, wie im folgenden Pseudocode dargestellt:

for each row in left_hand_table LHT:
  for each row in right_hand_table RHT:
    concatenate the columns of the RHT to the columns of the LHT
Copy

Wenn die Tabelle auf der linken Seite n Zeilen und die Tabelle auf der rechten Seite m* Zeilen hat, hat das Ergebnis des Cross Join n x m Zeilen. Wenn zum Beispiel die Tabelle auf der linken Seite 1000 Zeilen und die Tabelle auf der rechten Seite 100 Zeilen hat, ist das Ergebnis des Inner Joins 100.000 Zeilen. Das ist genau das, was man von verschachtelten Schleifen erwarten würde: Wenn die äußere Schleife 1000-ma ausgeführt wird und die innere Schleife 100-mal pro Iteration der äußeren Schleife, wird die innerste Anweisung 100.000-mal ausgeführt. (Natürlich schreiben SQL-Programmierer selten reine Cross-Joins ohne Join-Bedingungen in der FROM-Klausel oder WHERE-Klausel.)

Eine Lateral Join verknüpft Datensätze auf ganz andere Weise. Hier ist der Pseudocode für die Implementierung eines Lateral Join:

for each row in left_hand_table LHT:
  execute right_hand_subquery RHS using the values from the LHT row,
    and concatenate LHT columns to RHS columns
Copy

Der Lateral Join hat nur eine Schleife, nicht zwei verschachtelte Schleifen, was die Ausgabe verändert.

Beim Cross Join betrug die Ausgabe 100.000 Zeilen. Bei einem Lateral Join mit derselben Tabelle mit 1000 Zeilen auf der linken Seite und Verwendung einer rechten Inline-Ansicht (z. B. einer Unterabfrage), die pro Eingabezeile eine Ausgabezeile ausgibt, umfasst die Ausgabe des Lateral Join 1000 Zeilen und nicht 100.000 Zeilen.

Sie können sich einen Lateral Join wie folgt vorstellen: Für jede Eingabezeile aus der linken Tabelle erzeugt die Inline-Ansicht auf der rechten Seite 0 oder mehr Zeilen. Jede dieser Ausgabezeilen aus der Unterabfrage wird dann mit der Eingabezeile verbunden (nicht mit der gesamten Tabelle auf der linken Seite), um eine Zeile zu erzeugen, die die aus der Unterabfrage ausgewählten Spalten und die Spalten aus der LHT-Eingabezeile enthält.

Die Inline-Ansicht auf der rechten Seite eines Lateral Join erzeugt nicht unbedingt genau eine Ausgabezeile für jede Eingabezeile. Für jede Eingabezeile kann die Ausgabe auf der rechten Seite 0 Zeilen, 1 Zeile oder mehrere Zeilen umfassen. Jede dieser Ausgabezeilen wird mit den Spalten der ursprünglichen Eingabezeile verknüpft.

Wenn die Unterabfrage nicht genau eine Ausgabezeile für jede Eingabezeile erzeugt, erzeugt der Lateral Join nicht unbedingt genau so viele Zeilen, wie in der linken Tabelle vorhanden sind. Wenn die linke Tabelle 1000 Zeilen hat und die Inline-Ansicht 2 Ausgabezeilen für jede Eingabezeile erzeugt, ist das Ergebnis des Lateral Join 2000 Zeilen.

In jedem der bisherigen Beispiele für Lateral Joins gab es keine ON-Klausel oder WHERE-Klausel in der äußeren Abfrage, um Datensätze zu paaren. Die Zuordnung (falls vorhanden) erfolgt durch die Inline-Ansicht auf der Grundlage der einzelnen Zeile, die an die Inline-Ansicht übergeben wurde. Dies ist relativ klar, wenn die Inline-Ansicht eine Unterabfrage mit einer WHERE-Klausel ist. In anderen Fällen ist es nicht unbedingt so offensichtlich, z. B. wenn der rechte Ausdruck eine Tabellenfunktion und keine Unterabfrage ist. (Ein späteres Beispiel zeigt einen Ausdruck auf der rechten Seite, der anstelle einer Unterabfrage die Tabellenfunktion FLATTEN verwendet.)

Leser, die mit korrelierten Unterabfragen oder mit Joins von Tabellenfunktionen vertraut sind, finden die folgenden Vergleiche möglicherweise hilfreich, um zu verstehen, wie sich Lateral Joins von Cross Joins unterscheiden. Leser, die nicht mit korrelierten Unterabfragen oder Funktionen zum Verbinden von Tabellen vertraut sind, können diese Abschnitte auslassen.

Ähnlichkeiten zwischen korrelierten Unterabfragen und Lateral Joins

Ein Lateral Join ist ähnlich wie eine korrelierte Unterabfrage:

  • Bei einer korrelierten Unterabfrage wird die Unterabfrage für jede Zeile in der äußeren Abfrage einmal ausgeführt.

  • Bei einem Lateral Join wird die rechte Unterabfrage (Inline-Ansicht) einmal für jede Zeile im linken Tabellenausdruck ausgeführt.

Korrelierte Unterabfragen und Lateral Joins sind jedoch nicht dasselbe. Ein Unterschied besteht darin, dass bei einem Lateral Join die Unterabfrage mehr als eine Ausgabezeile pro Eingabezeile erzeugen kann und jede Ausgabezeile mehrere Spalten enthalten kann. Korrelierte Unterabfragen geben nur eine Ausgabezeile pro Eingabezeile zurück, und jede Ausgabezeile darf nur eine Spalte enthalten.

Ähnlichkeiten zwischen Funktionen zum Verknüpfen von Tabellen und Lateral Joins

Ein Lateral Join ist vergleichbar mit einem „Join“ zwischen einer Tabelle und einer benutzerdefinierten Tabellenfunktion (UDTF). Betrachten Sie beispielsweise die folgende SQL-Anweisung:

SELECT *
  FROM t1, TABLE(udtf2(t1.col1))
  ...
  ;
Copy

Der Pseudocode für die Implementierung dem Join zwischen der Tabelle und der UDTF lautet:

for each row in left_hand_table LHT:
  udtf2(row) -- that is, call udtf2() with the value(s) from the LHT row.
Copy

Dies ist im Wesentlichen identisch mit dem Code für die Implementierung eines Lateral Join:

for each row in left_hand_table LHT:
  execute right_hand_subquery RHS using the values from the LHT row
Copy

Beispiel: Verwendung einer Lateral Join mit der Tabellenfunktion FLATTEN

Lateral Joins werden häufig mit der integrierten Tabellenfunktion FLATTEN verwendet. Die FLATTEN-Funktion wird häufig mit Datentypen verwendet, die mehrere Werte speichern können (wie ARRAY, VARIANT und OBJECT). Ein Array beispielsweise enthält normalerweise mehrere Werte. In ähnlicher Weise kann eine VARIANT-Spalte einen JSON-Datenwert enthalten, der ein Wörterbuch (Hashwert) oder eine Liste enthalten kann. (Und das wiederum könnte andere Werte enthalten.)

Sie können ARRAY-Werte wie folgt erstellen:

UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'UDFs')
  WHERE employee_ID = 101;
UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'Lateral Joins')
  WHERE employee_ID = 102;
Copy

Die Funktion FLATTEN kann Werte aus diesen Werten extrahieren. Die Funktion nimmt einen einzelnen Ausdruck vom Typ VARIANT, OBJECT oder ARRAY und extrahiert die Werte aus diesem Ausdruck in eine Reihe von Zeilen (0 oder mehr Zeilen, von denen jede 1 oder mehr Spalten enthält). Dieser Satz von Zeilen entspricht einer Ansicht oder einer Tabelle. Diese Ansicht existiert nur für die Dauer der Anweisung, in der sie definiert ist, daher wird sie üblicherweise als „Inline-Ansicht“ bezeichnet.

Das folgende Beispiel verwendet FLATTEN, um Werte aus einem Array zu extrahieren (ohne Verwendung eines Lateral Join):

SELECT index, value AS project_name
  FROM TABLE(FLATTEN(INPUT => ARRAY_CONSTRUCT('project1', 'project2')));
Copy
+-------+--------------+
| INDEX | PROJECT_NAME |
|-------+--------------|
|     0 | "project1"   |
|     1 | "project2"   |
+-------+--------------+

Die von FLATTEN erzeugte Inline-Ansicht kann (muss aber nicht) mit dem Schlüsselwort LATERAL verwendet werden. Beispiel:

SELECT * FROM table1, LATERAL FLATTEN(...);
Copy

Wenn Sie das LATERAL-Schlüsselwort verwenden, kann die Inline-Ansicht einen Verweis auf Spalten in einer Tabelle enthalten, die ihr vorausgeht:

SELECT emp.employee_ID, emp.last_name, index, value AS project_name
  FROM employees AS emp,
    LATERAL FLATTEN(INPUT => emp.project_names) AS proj_names
  ORDER BY employee_ID;
Copy
+-------------+-----------+-------+----------------------+
| EMPLOYEE_ID | LAST_NAME | INDEX | PROJECT_NAME         |
|-------------+-----------+-------+----------------------|
|         101 | Richards  |     0 | "Materialized Views" |
|         101 | Richards  |     1 | "UDFs"               |
|         102 | Paulson   |     0 | "Materialized Views" |
|         102 | Paulson   |     1 | "Lateral Joins"      |
+-------------+-----------+-------+----------------------+
Sprache: Deutsch