Wie man dieselbe Tabelle zweimal verknüpft

JOIN ist eine der häufigsten Anweisungen in SQL. Wie Sie vielleicht wissen, wird sie verwendet, um Daten aus zwei oder mehr Tabellen zu einem gemeinsamen Datensatz zu verbinden und zu kombinieren. In diesem Artikel gehe ich auf spezielle Arten von Joins ein, bei denen Sie dieselbe Tabelle zweimal kombinieren - einschließlich des Joins einer Tabelle mit sich selbst, auch bekannt als Self-Join. Wann und warum ist dies erforderlich? Wie schreibt man sie in SQL? Finden wir es heraus.

Verknüpfungen: Ein kurzer Überblick

Sie sind wahrscheinlich mit den Joins in SQL vertraut. Sie haben zwei Tabellen, A und B, und Sie kombinieren sie, indem Sie eine Spalte verwenden, die beiden gemeinsam ist. Hier ist ein Beispiel:

Wir haben zwei Tabellen: customer und citymit einer gemeinsamen Spalte namens city_id.

Wenn Sie nun die beiden Tabellen miteinander verbinden möchten, um die jeweiligen Städtenamen der Kunden zu erhalten, können Sie dies mit einer Verknüpfung wie der folgenden tun:

select customer.customer_id,
     customer.firstname,
     customer.lastname,
     customer.birthdate,
     customer.spouse_id,
     customer.city_id,
     city.name as city_name
from customer 
join city 
on customer.city_id = city.city_id;

In dieser JOIN Anweisung werden die Datensätze aus customer und city durch einen Schlüssel (city_id). Wir rufen alle 6 Spalten aus der Tabelle customer Tabelle und eine Spalte, name, aus der city Tabelle. Es gibt verschiedene Arten von Verknüpfungen in SQL; dieses Beispiel führt eine INNER JOIN durch.

Ich werde hier nicht näher auf die Syntax von JOIN eingehen. Um mehr zu erfahren, besuchen Sie unseren interaktiven Kurs über SQL-JOINs, den Sie auf LearnSQL.de.

Das Ergebnis dieses Joins ist eine Tabelle mit allen 6 Feldern aus der Tabelle customer Tabelle und einem zusätzlichen Feld aus der city Tabelle:

Dieselbe Tabelle mehrfach verknüpfen

Nach diesem kurzen Überblick wollen wir uns nun mit komplexeren Verknüpfungen befassen.

Manchmal müssen Sie dieselbe Tabelle mehrfach verknüpfen. Im Allgemeinen handelt es sich dabei um das Hinzufügen einer oder mehrerer Spalten zu einer Ergebnismenge aus derselben Tabelle, jedoch zu verschiedenen Datensätzen oder anhand verschiedener Spalten. Wir werden zwei solcher Szenarien untersuchen: die Verknüpfung einer Tabelle mit sich selbst und die Verknüpfung von Tabellen mit mehreren Beziehungen.

Selbst-Join: Verknüpfung einer Tabelle mit sich selbst

Ein Self-Join ist ein Spezialfall des Joins. Anstatt zwei verschiedene Tabellen zu verbinden, verbindet man eine Tabelle mit sich selbst. Warum sollten wir das tun?

In unserem obigen Beispiel wollten wir eine Spalte aus der Tabelle city Tabelle, den Namen der Stadt, in die customer Tabelle hinzufügen. Wir haben also zwei verschiedene Tabellen miteinander verbunden. Ein Self-Join würde zum Beispiel bedeuten, dass man die Tabelle customer Tabelle mit sich selbst verbinden.

Hier ist die customer Tabelle zur Erinnerung:

In der Spalte spouse_id wird die customer_id des Ehepartners des Kunden gespeichert. Beispielsweise sind die Kunden 1 und 2 (John und Mary) miteinander verheiratet, die Kunden 3 und 5 (Lisa und Tim) sind miteinander verheiratet und so weiter. Wir können den Vornamen und den Nachnamen des Ehepartners zu jedem Datensatz in der customer Tabelle hinzufügen. Dazu müssen wir einen Self-Join durchführen, d. h. die Tabelle customer Tabelle mit sich selbst verbinden:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.spouse_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust 
join customer spouse
on cust.spouse_id = spouse.customer_id;

Wenn Sie diesen Code ausführen, ist das Ergebnis das folgende:

Nachdem Sie nun ein Anwendungsbeispiel für Self-Joins gesehen haben, wollen wir uns die SQL-Syntax ansehen.

Self-Join-Syntax

Die Syntax für den Self-Join ist sehr ähnlich wie bei allen anderen Arten von Joins. Hier ist der Code aus unserem Self-Join-Beispiel:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.spouse_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust
join customer spouse
on cust.spouse_id = spouse.customer_id;

Es handelt sich um eine JOIN -Anweisung, in der die customer Tabelle zweimal verwendet wird.

Die Informationen über den Ehepartner, wie z. B. sein Name, werden in derselben Tabelle wie ein separater Kunde mit seiner eigenen customer_id gespeichert. Da spouse_id die customer_id des Ehepartners enthält, müssen wir die Tabelle mit sich selbst verknüpfen, um den Namen des Ehepartners zu erhalten.

Sie können sich einen Self-Join als eine Verknüpfung zwischen zwei Kopien der gleichen Tabelle vorstellen. Für jeden Datensatz mit einem Nicht-Null-Wert in spouse_id suchen wir nach dem Wert in customer_id, der damit übereinstimmt. Wenn wir eine Übereinstimmung finden, werden die Spalten firstname und lastname der resultierenden Tabelle hinzugefügt.

Bei einem Self-Join sind Tabellen-Aliase erforderlich. Ohne sie funktioniert der Code nicht, da er nicht weiß, auf welche Kopie der Tabelle Sie sich beziehen. Hier verwende ich die Aliasnamen cust und spouse.

Direkt vor dem Schlüsselwort FROM wählen wir die Spalten aus, die wir in der Ergebnistabelle behalten wollen. Für den Abruf der Spalten müssen wir die Tabellenalias verwenden (cust.firstname, cust.lastname, spouse.firstname, usw.). Wir behalten fünf Spalten aus der Tabelle customer Tabelle und fügen aus derselben Tabelle zwei Spalten hinzu, die den Namen des Ehepartners enthalten.

Dies ist eine innere Verknüpfung, aber Sie können jede Art von Verknüpfung verwenden: LEFT JOIN, RIGHT JOIN, CROSS JOIN, usw. In unserem Beispiel haben wir eine innere Verknüpfung, die nur die übereinstimmenden Datensätze zurückgibt, d. h. es werden nur die Kunden mit Ehepartnern zurückgegeben. Da Donna keinen Ehepartner in der Tabelle hat, wird Donna nicht in den resultierenden Datensatz aufgenommen.

Wenn Sie mehr über Joins erfahren möchten, empfehle ich Ihnen unseren Artikel "Wie man SQL JOINs lernt."Es gibt auch einen interaktiven SQL-JOINs Kurs auf unserer LearnSQL.de Plattform.

Beispiele für den Self Join

Das obige Beispiel ist nur ein Beispiel. Wenn Sie anfangen, SQL täglich zu verwenden, werden Sie häufig auf Self-Joins stoßen.

Ein häufiger Anwendungsfall für den Self-Join ist, wenn es eine Hierarchie zwischen den Datensätzen in einer Tabelle gibt. Diese Art von Datenstruktur wird als Baumstruktur bezeichnet, und Sie müssen die Tabelle oft mit sich selbst in SQL verknüpfen. Hier sind einige Beispiele.

Beispiel 1: Mitarbeiterhierarchie

Jeder Mitarbeiter hat einen Vorgesetzten, und ein Vorgesetzter wiederum hat seinen Vorgesetzten, alle in derselben Tabelle. Wenn Sie jedem Datensatz die entsprechenden Managerinformationen hinzufügen möchten, müssen Sie einen Self-Join durchführen. Dieses Beispiel wird in dem Artikel "Ein illustrierter Leitfaden für den SQL Self Join" behandelt.

Beispiel 2: Abteilungshierarchie

Jede Abteilung in einem Unternehmen hat eine übergeordnete Abteilung: Die Data Science-Abteilung ist beispielsweise der IT-Abteilung untergeordnet, die IT-Abteilung dem Business Support, und der Business Support dem Vorstand.

Betrachten Sie die folgende Tabelle, department, die wie folgt aussieht:

Wenn Sie nun den Namen der übergeordneten Abteilung an jede Abteilung anhängen möchten, müssen Sie einen Self-Join schreiben:

select c.*,
   p.name as parent_name
from department c 
left join department p 
on c.parent_department_id=p.department_id;

Innerhalb dieser SELECT verknüpfen wir die department Tabelle mit sich selbst, um den Namen der übergeordneten Abteilung als zusätzliches Feld zu erhalten. Beachten Sie, dass der Datensatz, dessen department_id gleich 1 ist, keine übergeordnete Abteilung hat (parent_department_id ist NULL; er ist nicht ausgefüllt). Das liegt daran, dass der Vorstand an der Spitze der Baumstruktur steht. Da wir diesen Datensatz im Ergebnis anzeigen wollen, verwenden wir LEFT JOIN und nicht INNER JOIN.

Wenn Sie diesen Code ausführen, sieht die resultierende Tabelle wie folgt aus:

Sie können leicht erkennen, zu welcher übergeordneten Abteilung die einzelnen Abteilungen gehören: Die IT-Abteilung ist der Verwaltung untergeordnet, die Verwaltung ist dem Vorstand untergeordnet, usw.

Beispiel 3: Kategoriehierarchie

Nehmen Sie die beliebten Webseiten für Kleinanzeigen, auf denen Sie alles von Immobilien bis hin zu verschiedenen Produkten und Dienstleistungen mieten, kaufen oder verkaufen können. Um eine Anzeige aufzugeben, wählen Sie eine Kategorie und eine Unterkategorie für Ihre Anzeige. Wenn Sie zum Beispiel Immobilien verkaufen, wählen Sie eine Unterkategorie wie Haus, Wohnung oder Grundstück.

Wir haben eine Tabelle namens category die Informationen über diese Kategorien und Unterkategorien sowie deren Beziehungen enthält. Die Beziehungen zwischen den Kategorien und Unterkategorien in dieser Tabelle werden in einer Eltern-Kind-Struktur wie dieser gespeichert:

Alle Kategorien und Unterkategorien befinden sich in dieser einen Tabelle. Wenn Sie nun jedem Datensatz Informationen über die übergeordnete Kategorie hinzufügen möchten, müssen Sie einen Self-Join durchführen - verbinden Sie diese Tabelle mit sich selbst:

select subcategory.*,
    main.category_name as parent_name
from category subcategory 
left join category main 
on subcategory.parent_category_id = main.category_id;

Hier ist das Ergebnis der Ausführung dieser SQL-Anweisung:

Mehrere Beziehungen zwischen zwei Tabellen

Neben dem Self-Join gibt es Situationen, in denen Sie dieselbe Tabelle mehr als einmal verknüpfen müssen. Eine davon ist, wenn Sie mehrere Beziehungen zwischen zwei verschiedenen Tabellen haben. In diesem Fall verbinden Sie dieselbe Tabelle zweimal, aber normalerweise mit einer anderen Tabelle und nicht unbedingt mit sich selbst.

Angenommen, die Tabelle customer zwei Felder hat, die Städte-IDs enthalten. Dies ist üblich, wenn Sie für jeden Kunden zwei verschiedene Städte haben, z. B. die Stadt des Wohnsitzes (residence_city_id) und die Stadt der Postanschrift, an die die Bescheide geschickt werden sollen (notice_city_id):

Wir haben auch city die die Stadt-ID (city_id) und den Namen der Stadt (name) enthält, wie zuvor gesehen und unten zur Erinnerung dargestellt:

Wenn Sie nun die Namen der Städte anzeigen möchten, müssen Sie die Tabelle city Tabelle zweimal verbinden:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.residence_city_id,
      cust.notice_city_id,
      residence_city.name as residence_city_name,
      notice_city.name as notice_city_name
from customer cust 
join city residence_city 
on cust.residence_city_id=residence_city.city_id
join city notice_city 
on cust.notice_city_id=notice_city.city_id;

Schauen wir uns an, was in diesem Code passiert. Zuerst verbinden wir customer und city mit residence_city_id als Schlüssel. Wir erhalten residence_city_name durch Abgleich mit city_id in der city Tabelle. Eine zweite Verknüpfung wird durchgeführt zwischen customer und city durchgeführt, um notice_city_name zu erhalten. Der hier verwendete Schlüssel ist notice_city_id, der auch mit city_id in der Tabelle city Tabelle übereinstimmt.

Wir verwenden Tabellen-Aliase cust für customer, residence_city für die erste Kopie von city um den Namen des Wohnortes zu erhalten, und notice_city für die zweite Kopie von city, um den Namen der Stadt zu erhalten. Wir verwenden die Aliase, um die Spalten in der resultierenden Tabelle zu definieren. Aliase werden auch während der Verknüpfung verwendet, um die Schlüsselspalten zu definieren. Auch hier sind Aliasnamen erforderlich, um die beiden Kopien von city.

Wenn Sie diesen Code ausführen, erhalten Sie das folgende Ergebnis:

Wir haben jetzt zwei zusätzliche Spalten mit den entsprechenden Städtenamen.

Wir verwenden hier einfache (d. h. innere) Joins, aber Sie können bei Bedarf jede Art von Join verwenden. Wenn Sie mit SQL-Joins noch nicht vertraut sind und mehr über die verschiedenen Arten von Joins lesen möchten, empfehle ich Ihnen die Artikel "Wie lerne ich Joins" und "Wie übe ich Joins", die diese Themen behandeln. Wenn Sie es vorziehen, durch das Ansehen von Videos zu lernen. Ich empfehle die Episode, die sich mit Joins befasst.

Zweimalige Verknüpfung derselben Tabelle

In diesem Artikel haben wir erörtert, wann Sie dieselbe Tabelle in SQL zweimal verknüpfen müssen, und haben uns einige häufige Anwendungsfälle angesehen. Wir haben erklärt, wie man das macht und wie die SQL-Syntax aussieht. Self-Joins mit hierarchischen Daten und mehrere Beziehungen zwischen zwei Tabellen sind nur zwei der Situationen, in denen Sie dieselbe Tabelle zweimal verknüpfen müssen. Es gibt noch weitere Fälle, bei denen es im Allgemeinen darum geht, eine oder mehrere Spalten aus derselben Tabelle in derselben Spalte zu einer Ergebnismenge hinzuzufügen.

Wenn Sie mehr über Joins, einschließlich Self-Joins, erfahren möchten, empfehle ich Ihnen unseren interaktiven Kurs SQL Joins auf unserer LearnSQL.de Plattform. Wenn es um SQL geht, ist es wichtig, zu üben; unser Kurs ist genau dafür gedacht!