Kategorien:

Abfragesyntax

JOIN

Eine JOIN-Klausel ist eine Unterklausel einer FROM-Klausel.

Bei einer JOIN-Operation werden Zeilen aus zwei Tabellen (oder anderen Quellen, z. B. Ansichten oder Tabellenfunktionen) kombiniert, um eine neue kombinierte Zeile zu erstellen, die in der Abfrage verwendet werden kann.

Typischerweise sind die Daten in den beiden Tabellen auf irgendeine Weise miteinander verbunden. Beispielsweise kann eine Tabelle Informationen über Projekte enthalten, während eine andere Tabelle Informationen über die Mitarbeiter enthält, die an diesen Projekten arbeiten.

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

Mit der JOIN-Klausel wird (explizit oder implizit) festgelegt, wie Zeilen in einer Tabelle mit den entsprechenden Zeilen in der anderen Tabelle in Beziehung gesetzt werden sollen, typischerweise durch Bezugnahme auf die gemeinsamen Spalten (z. B. Projekt-ID).

Siehe auch:

Seitliche Verknüpfung (Lateral Join)

Syntax

Verwenden Sie eine der folgenden Optionen:

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]
SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
[ ... ]
SELECT ...
FROM <object_ref1> [
                     {
                       | NATURAL [ { LEFT | RIGHT | FULL } [ OUTER ] ]
                       | CROSS
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

JOIN

Die Semantik von Verknüpfungen lautet wie folgt (der Kürze wegen werden unter diesem Thema o1 und o2 für Objektreferenz1 und Objektreferenz2 verwendet):

Verknüpfungstyp

Semantik

o1 INNER JOIN o2

Bei jeder Zeile von o1 wird für jede Zeile von o2, die die Unterklausel ON Bedingung erfüllt, eine Zeile erzeugt. (Beachten Sie, dass Sie auch ein Komma verwenden können, um eine innere Verknüpfung anzugeben. Ein Beispiel dazu finden Sie im Beispielbereich unten.) Wenn Sie INNER JOIN ohne die ON-Klausel verwenden (oder wenn Sie ein Komma ohne eine WHERE-Klausel verwenden), ist das Ergebnis dasselbe wie bei Verwendung von CROSS JOIN: ein kartesisches Produkt (jede Zeile von o1 gepaart mit jeder Zeile von o2).

o1 LEFT OUTER JOIN o2

Das Ergebnis der inneren Verknüpfung wird für jede Zeile von o1, die keine Übereinstimmungen in o2 aufweist, um eine Zeile erweitert. Die Ergebnisspalten, die auf o2 verweisen, enthalten null.

o1 RIGHT OUTER JOIN o2

Das Ergebnis der inneren Verknüpfung wird für jede Zeile von o2, die keine Übereinstimmungen in o1 aufweist, um eine Zeile erweitert. Die Ergebnisspalten, die auf o1 verweisen, enthalten null.

o1 FULL OUTER JOIN o2

Gibt alle verknüpften Zeilen zurück, plus eine Zeile für jede Zeile ohne Entsprechung auf der linken Seite (rechts erweitert um Nullen), plus eine Zeile für jede Zeile ohne Entsprechung auf der rechten Seite (links erweitert um Nullen).

o1 CROSS JOIN o2

Für jede mögliche Kombination von Zeilen aus o1 und o2 (d. h. kartesisches Produkt) enthält die verknüpfte Tabelle eine Zeile, die aus allen Spalten in o1 gefolgt von allen Spalten in o2 besteht. Ein CROSS JOIN kann nicht mit einer ON Bedingung-Klausel kombiniert werden. Sie können jedoch eine WHERE-Klausel verwenden, um die Ergebnisse zu filtern.

o1 NATURAL JOIN o2

Ein NATURAL JOIN ist identisch mit einem expliziten JOIN für die gemeinsamen Spalten der beiden Tabellen, mit der Ausnahme, dass die gemeinsamen Spalten nur einmal in der Ausgabe enthalten sind. (Bei einer natürlichen Verknüpfung wird davon ausgegangen, dass Spalten mit dem gleichen Namen, die sich jedoch in verschiedenen Tabellen befinden, übereinstimmende Daten enthalten.) Im Beispielabschnitt unten finden Sie einige Beispiele. Ein NATURAL JOIN kann mit einem OUTER JOIN kombiniert werden. Ein NATURAL JOIN kann nicht mit einer ON Bedingung-Klausel kombiniert werden, da die JOIN-Bedingung bereits impliziert ist. Sie können jedoch eine WHERE-Klausel verwenden, um die Ergebnisse zu filtern.

Standard: INNER JOIN

Wenn das Wort JOIN ohne Angabe von INNER oder OUTER verwendet wird, dann wird die JOIN zu einer inneren Verknüpfung.

ON Bedingung

Ein boolescher Ausdruck, der die Zeilen auf den beiden Seiten der JOIN definiert, die als Übereinstimmung gelten. Beispiel:

ON object_ref2.id_number = object_ref1.id_number

Bedingungen werden in der WHERE-Klausel näher erläutert.

Die ON-Klausel ist bei CROSS JOIN nicht zulässig.

Die Klausel ON ist bei NATURAL JOIN unnötig (und unzulässig); die Verknüpfungsspalten sind implizit.

Bei anderen Verknüpfungen ist die ON-Klausel optional. Wenn Sie die ON-Klausel jedoch weglassen, erhalten Sie ein kartesisches Produkt (jede Zeile von Objektreferenz1 gepaart mit jeder Zeile von Objektreferenz2). Ein kartesisches Produkt kann eine sehr große Ausgabe erzeugen, die fast ausschließlich aus Zeilenpaaren besteht, die nicht wirklich miteinander zu tun haben. Dies erfordert viele Ressourcen und ist eine häufige Quelle für Benutzerfehler.

USING( <column_list> )

Eine Liste von Spalten, die zwischen den beiden Tabellen, die verbunden werden sollen, gemeinsam sind. Diese Spalten werden als Join-Spalten verwendet. Die Spalten müssen in jeder der zu verbindenden Tabellen denselben Namen und dieselbe Bedeutung haben.

Angenommen, die SQL-Anweisung enthält Folgendes:

... o1 JOIN o2
    USING (key_column)

In diesem einfachen Fall wäre dies äquivalent zu:

... o1 JOIN o2
    ON o2.key_column = o1.key_column

Zur korrekten Verwendung der USING-Klausel sollte die Projektionsliste (die Liste der Spalten und anderen Ausdrücke nach dem Schlüsselwort SELECT) „*“ sein. Auf diese Weise kann der Server die Schlüsselspalte genau einmal zurückgeben. Dies ist die Standardmethode für die Verwendung der USING-Klausel. Beispiele für standardmäßige und nicht standardmäßige Verwendung finden Sie in den folgenden Beispielen.

Beispiele

In vielen der JOIN-Beispiele werden zwei Tabellen genutzt: t1 und t2. Die Tabellen und zugehörige Daten werden wie folgt erstellt:

CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t1 (col1) VALUES 
   (2),
   (3),
   (4);
INSERT INTO t2 (col1) VALUES 
   (1),
   (2),
   (2),
   (3);

Innere Verknüpfung:

SELECT t1.col1, t2.col1 FROM t1 INNER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Dies zeigt eine linke äußere Verknüpfung. Beachten Sie den Wert NULL für die Zeile in Tabelle t1, für die in Tabelle t2 keine übereinstimmende Zeile vorhanden ist.

SELECT t1.col1, t2.col1 FROM t1 LEFT OUTER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
+------+------+

Dies zeigt eine rechte äußere Verknüpfung. Beachten Sie den Wert NULL für die Zeile in Tabelle t1, für die in Tabelle t2 keine übereinstimmende Zeile vorhanden ist.

SELECT t1.col1, t2.col1 FROM t1 RIGHT OUTER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
| NULL |    1 |
+------+------+

Dies zeigt eine vollständige äußere Verknüpfung. Beachten Sie, dass die Ausgabe zwei Zeilen mit NULL-Werten enthält, da jede Tabelle eine Zeile hat, für die in der anderen Tabelle keine übereinstimmende Zeile vorhanden ist:

SELECT t1.col1, t2.col1 FROM t1 FULL OUTER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
| NULL |    1 |
+------+------+

Dies ist ein Beispiel für eine Kreuzverknüpfung (Cross Join), mit der das kartesische Produkt erstellt wird. Beachten Sie, dass die Kreuzverknüpfung keine ON-Klausel enthält.

SELECT t1.col1, t2.col1 FROM t1 CROSS JOIN t2 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    1 |
|    2 |    2 |
|    2 |    2 |
|    2 |    3 |
|    3 |    1 |
|    3 |    2 |
|    3 |    2 |
|    3 |    3 |
|    4 |    1 |
|    4 |    2 |
|    4 |    2 |
|    4 |    3 |
+------+------+

Eine Kreuzverknüpfung kann durch eine WHERE-Klausel gefiltert werden, wie im folgenden Beispiel gezeigt:

SELECT t1.col1, t2.col1 FROM t1 CROSS JOIN t2 WHERE t2.col1 = t1.col1 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Dies ist ein Beispiel für eine natürliche Verknüpfung. Dies erzeugt dieselbe Ausgabe wie die entsprechende innere Verknüpfung, außer dass wir keine zweite Kopie der Join-Spalte erhalten:

CREATE OR REPLACE TABLE d1 (
  id number,
  name string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D1 successfully created. |
+--------------------------------+
INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
CREATE OR REPLACE TABLE d2 (
  id number,
  value string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D2 successfully created. |
+--------------------------------+
INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
SELECT * FROM d1 NATURAL INNER JOIN d2 ORDER BY id;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

Natürliche Verknüpfungen können mit äußeren Verknüpfungen kombiniert werden, z. B.:

SELECT *
  FROM d1 NATURAL FULL OUTER JOIN d2
  ORDER BY ID;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
|  4 | c    | NULL  |
|  5 | NULL | zz    |
+----+------+-------+

Der Kommaoperator ist ältere Syntax für INNER JOIN. Wenn anstelle von INNER JOIN ein Komma verwendet wird, wird die Filterbedingung in der WHERE-Klausel und nicht in der ON-Klausel festgelegt. Die folgenden beiden Anweisungen sind gleichwertig:

Neuerer (bevorzugter) Stil:

SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Älterer Stil:

SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t2.col1 = t1.col1 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Verknüpfungen können in der FROM-Klausel miteinander kombiniert werden. Die folgende Abfrage zeigt zwei in der FROM-Klausel verkettete Verknüpfungen:

SELECT t1.*, t2.*, t3.*
  FROM t1
     LEFT OUTER JOIN t2 ON (t1.col1 = t2.col1)
     RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1)
  ORDER BY t1.col1;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
| NULL | NULL |    6 |
+------+------+------+

In einer solchen Abfrage werden die Ergebnisse basierend auf den Verknüpfungen von links nach rechts bestimmt (wobei der Optimierer die Verknüpfungen neu anordnen kann, wenn eine andere Verknüpfungsreihenfolge das gleiche Ergebnis liefert). Wenn die rechte äußere Verknüpfung vor der linken äußeren Verknüpfung erfolgen soll, kann die Abfrage wie folgt geschrieben werden:

SELECT t1.*, t2.*, t3.*
  FROM t1
     LEFT OUTER JOIN
     (t2 RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1))
     ON (t1.col1 = t2.col1)
  ORDER BY t1.col1;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
+------+------+------+

Die beiden folgenden Beispiele zeigen die standardmäßige und nicht standardmäßige Verwendung der USING-Klausel.

Dieses erste Beispiel zeigt die korrekte Verwendung. Insbesondere enthält die Projektionsliste ausschließlich „*“. Obwohl die Abfrage zwei Tabellen verbindet, und jede Tabelle über eine Spalte verfügt, und die Abfrage nach allen Spalten fragt, enthält die Ausgabe nur eine und nicht zwei Spalten.

WITH 
    l AS (
         SELECT 'a' AS userid
         ),
    r AS (
         SELECT 'b' AS userid
         )
  SELECT * 
    FROM l LEFT JOIN r USING(userid)
;
+--------+
| USERID |
|--------|
| a      |
+--------+

Das folgende Beispiel zeigt die nicht standardmäßige Verwendung. Die Projektionsliste enthält etwas anderes als „*“. Da die Verwendung nicht standardmäßig ist, enthält die Ausgabe zwei Spalten mit dem Namen „userid“. Das zweite Vorkommen (von dem Sie möglicherweise erwarten, dass es einen Wert aus der Tabelle „r“ enthält) enthält einen Wert, der nicht in der Tabelle enthalten ist (der Wert „a“ steht nicht in der Tabelle „r“).

WITH 
    l AS (
         SELECT 'a' AS userid
       ),
    r AS (
         SELECT 'b' AS userid
         )
  SELECT l.userid as UI_L,
         r.userid as UI_R  -- Incorrect usage!
    FROM l LEFT JOIN r USING(userid)
;
+------+------+
| UI_L | UI_R |
|------+------|
| a    | a    |
+------+------+