Was ist ein Self Join in SQL? Eine Erläuterung mit sieben Beispielen

Der Self-Join ist ein beliebter Spezialfall des SQL JOIN. Während die meisten JOINs zwei oder mehr Tabellen miteinander verknüpfen, um ihre Daten gemeinsam darzustellen, verknüpft ein Self-Join eine Tabelle mit sich selbst. Normalerweise wird eine Tabelle nur einmal innerhalb einer SQL-Abfrage mit sich selbst verknüpft, aber es ist auch möglich, dies mehrmals innerhalb derselben Abfrage zu tun.

Normalerweise speichert jede Tabelle in einer Datenbank eine bestimmte Art von Informationen. Daher gibt es in einer Datenbank oft Hunderte von Tabellen, die miteinander in Beziehung stehen. Daraus ergibt sich die Notwendigkeit von Joins. Mit dem Schlüsselwort JOIN können Sie verschiedene Tabellen über ihre gemeinsamen Spalten verknüpfen. Es ist auch möglich, eine Tabelle mit sich selbst zu verknüpfen, was als Self-Join bezeichnet wird. In diesem Artikel wird erläutert, was ein Self-Join ist, wie er funktioniert und wann Sie ihn in Ihren SQL-Abfragen benötigen.

Zum Üben von SQL JOIN, einschließlich Self-Joins, empfehle ich unseren interaktiven SQL-JOINs Kurs. Er enthält über 90 Übungen zu verschiedenen Arten von JOIN, darunter auch Abschnitte, die sich ausschließlich mit Self-Joins befassen.

Was ist ein Self-Join in SQL?

Beim Self-Join wird, wie der Name schon sagt, eine Tabelle mit sich selbst verknüpft. Um einen Self-Join zu verwenden, muss die Tabelle eine Spalte (X) enthalten, die als Primärschlüssel fungiert, und eine andere Spalte (Y), in der Werte gespeichert sind, die mit den Werten in Spalte X abgeglichen werden können. Die Werte der Spalten X und Y müssen für eine bestimmte Zeile nicht identisch sein. Der Wert in Spalte Y kann sogar null sein.

Schauen wir uns ein Beispiel an. Betrachten wir die Tabelle Mitarbeiter:

Jeder Mitarbeiter hat seine eigene Id, die unsere "Spalte X" ist. Für einen bestimmten Mitarbeiter (d.h. eine Zeile) enthält die Spalte Manager_ID die Id seines Vorgesetzten. Dies ist unsere "Spalte Y". Verfolgen wir die Mitarbeiter-Manager-Paare in dieser Tabelle anhand dieser Spalten:

• Der Vorgesetzte des Angestellten John Smith ist der Angestellte mit Id 3, d.h. Tom Lanon.
• Der Vorgesetzte des Mitarbeiters Jane Anderson ist der Mitarbeiter mit Id 3, d.h. Tom Lanon.
• Der Vorgesetzte des Angestellten Tom Lanon ist der Angestellte mit Id 4, d.h. Anne Connor.
• Die Mitarbeiterin Anne Connor hat keinen Vorgesetzten; ihre Manager_ID ist Null.
• Der Vorgesetzte des Angestellten Jeremy York ist der Angestellte mit Id 1, d.h. John Smith.

Diese Art der Tabellenstruktur ist in Hierarchien sehr verbreitet. Um nun den Namen des Vorgesetzten für jeden Mitarbeiter in derselben Zeile anzuzeigen, können wir die folgende Abfrage ausführen:

SELECT
	Mitarbeiter.Id,
    	Mitarbeiter.Vollstaendiger_Name,
    	Mitarbeiter.Manager_ID,
    	Manager.Vollstaendiger_Name as Name_des_Managers
FROM Mitarbeiter Mitarbeiter
JOIN Mitarbeiter Manager
ON Mitarbeiter.Manager_ID = Manager.Id

Diese liefert das folgende Ergebnis:

Die Abfrage wählt die Spalten Id, Vollstaendiger_Name und Manager_ID aus der Tabelle "alias" Mitarbeiter. Sie wählt auch die Spalte Vollstaendiger_Name der Tabelle "alias" Manager und benennt diese Spalte als Name_des_Managers. Als Ergebnis wird jeder Mitarbeiter, der einen Vorgesetzten hat, zusammen mit der ID und dem Namen seines Vorgesetzten ausgegeben.

In dieser Abfrage wird die Mitarbeiter Tabelle mit sich selbst verbunden und hat zwei verschiedene Rollen:

• Rolle 1: Sie speichert die Mitarbeiterdaten (Alias Mitarbeiter).
• Rolle 2: Sie speichert die Daten des Managers (Alias Manager).

Auf diese Weise betrachten wir die beiden Kopien der Tabelle Mitarbeiter so, als ob es sich um zwei verschiedene Tabellen handelt, eine für die Mitarbeiter und eine für die Manager.

Weitere Informationen über das Konzept des Self Join finden Sie in unserem Artikel Eine illustrierte Anleitung zum SQL Self Join.

Tabellenalias in Self Join

Wenn in einer SQL-Abfrage mehr als einmal auf dieselbe Tabelle verwiesen wird, brauchen wir eine Möglichkeit, jeden Verweis von den anderen zu unterscheiden. Aus diesem Grund ist es wichtig, Aliasnamen zu verwenden, um jeden Verweis auf dieselbe Tabelle in einer SQL-Abfrage eindeutig zu identifizieren. Als gute Praxis sollten die Aliasnamen die Rolle der Tabelle für jeden spezifischen Verweis in einer Abfrage angeben.

In der folgenden Abfrage sind die Aliase in rot dargestellt. Sie können ihre Deklaration in den Klauseln FROM und JOIN sehen.

SELECT
	Mitarbeiter.Id,
    	Mitarbeiter.Vollstaendiger_Name,
    	Mitarbeiter.Manager_ID,
    	Manager.Vollstaendiger_Name as Name_des_Managers
FROM Mitarbeiter Mitarbeiter
JOIN Mitarbeiter Manager
ON Mitarbeiter.Manager_ID = Manager.Id

Das Schlüsselwort JOIN verbindet zwei Tabellen und wird in der Regel von einer ON oder USING Klausel gefolgt, die die gemeinsamen Spalten angibt, die für die Verknüpfung der beiden Tabellen verwendet werden. Hier sehen wir, dass die beiden Verweise auf die Tabelle Mitarbeiter durch die Bedingung verknüpft werden, dass die Manager_ID des Mitarbeiters mit der Mitarbeiter_ID des Managers übereinstimmt.

Beispiele

Gehen wir einige gängige Szenarien durch, in denen der Self-Join verwendet wird.

Szenario 1: Verarbeitung einer Hierarchie in SQL

Der Self-Join wird häufig bei der Verarbeitung einer Hierarchie verwendet. Wie wir bereits gesehen haben, ordnet eine Hierarchie eine Zeile in einer Tabelle einer anderen Zeile in derselben Tabelle zu. Sie können sich das so vorstellen, dass es Eltern- und Kindzeilen gibt.

Kehren wir zu dem Beispiel mit den Mitarbeitern und ihren Managern zurück. Hier ist wieder die Mitarbeiter Tabelle:

Und der Code, um jeden Mitarbeiter, der einen Vorgesetzten hat, mit dem Namen seines Vorgesetzten aufzulisten:

SELECT
	Mitarbeiter.Id,
    	Mitarbeiter.Vollstaendiger_Name,
    	Mitarbeiter.Manager_ID,
    	Manager.Vollstaendiger_Name as Name_des_Managers
FROM Mitarbeiter Mitarbeiter
JOIN Mitarbeiter Manager
ON Mitarbeiter.Manager_ID = Manager.Id

Hier ist das Ergebnis, wenn Sie den Code ausführen:

Diese Abfrage verwendet die Standard-Verknüpfung, auch bekannt als INNER JOIN. Weitere Informationen über INNER JOIN finden Sie in unserem Artikel Ein illustrierter Leitfaden zum SQL INNER JOIN.

Wenn wir alle Mitarbeiter auflisten möchten, unabhängig davon, ob sie Manager haben oder nicht, können wir stattdessen LEFT OUTER JOIN verwenden. Die folgende Abfrage tut dies:

SELECT
	Mitarbeiter.Id,
    	Mitarbeiter.Vollstaendiger_Name,
    	Mitarbeiter.Manager_ID,
    	Manager.Vollstaendiger_Name as Name_des_Managers
FROM Mitarbeiter Mitarbeiter
LEFT OUTER JOIN Mitarbeiter Manager
ON Mitarbeiter.Manager_ID = Manager.Id

Wenn Sie diese Abfrage ausführen, erhalten Sie das folgende Ergebnis:

Der Unterschied zwischen JOIN und LEFT OUTER JOIN wird deutlich, wenn wir dies mit dem Ergebnis der vorherigen SQL-Abfrage vergleichen. Im Ergebnis der (inneren) Abfrage JOIN sind nur die Mitarbeiter mit Führungskräften enthalten. Im Gegensatz dazu liefert die Abfrage LEFT OUTER JOIN alle Mitarbeiter, mit oder ohne Manager. Um mehr über OUTER JOIN zu erfahren, lesen Sie bitte unseren Artikel Eine illustrierte Anleitung zum SQL OUTER JOIN.

Ein weiteres Beispiel für eine Hierarchie ist die Beziehung zwischen Eltern und ihren Kindern. Betrachten Sie die Mensch Tabelle, die hier gezeigt wird:

In der folgenden Abfrage werden die Kinder ihren jeweiligen Eltern zugewiesen, indem die Mensch Tabelle mit sich selbst verknüpft:

SELECT
	Kind.Id as Kind_ID,
    	Kind.Vorname as Vorname_des_Kindes,
    	Kind.Alter as Alter_des_Kindes,
    	Kind.ElternId,
    	Elternteil.Vorname as ElternteilVorname,
    	Elternteil.age as ElternteilAlter
FROM Mensch Kind
INNER JOIN Mensch Elternteil
ON Kind.ElternId = Elternteil.Id

Hier ist das Ergebnis dieser Abfrage:

Das Ergebnis der Abfrage enthält nur die Kinder, die Eltern haben. Wie im Beispiel der Mitarbeiter-Manager-Hierarchie könnten wir einen LEFT OUTER JOIN verwenden, um alle Zeilen aus der Tabelle "alias" Kind einzubeziehen.

Hier ist ein weiteres Beispiel für eine Hierarchie. Betrachten Sie die Tabelle Kategorie die unten gezeigt wird:

Weisen wir jeder Kategorie eine übergeordnete Kategorie zu, wo immer dies möglich ist. Hier ist eine Abfrage, die das tut:

SELECT
	Kategorie.Id,
	Kategorie.Anzahl,
    	Kategorie.Kategorie,
    	Kategorie.Uebergeordnete_KategorieId,
    	ElternKategorie.Kategorie as Uebergeordnete_Kategorie
FROM Kategorie Kategorie
JOIN Kategorie ElternKategorie
ON Kategorie.Uebergeordnete_KategorieId = ElternKategorie.Id

Und hier ist das Ergebnis:

Die ersten vier Spalten des obigen Ergebnisses stammen aus dem Verweis auf die Tabelle "alias" Kategorie. Die letzte Spalte stammt aus der Tabelle "alias" ElternKategorie und enthält den Namen der übergeordneten Kategorie, die mit der entsprechenden Id übereinstimmt.

Die Tabelle Kategorie hat zwei verschiedene Rollen, die durch die beiden separaten Verweise gekennzeichnet sind. Die Spalte Uebergeordnete_KategorieId aus der Tabelle "alias" Kategorie wird mit Id aus der Tabelle "alias" ElternKategorie abgeglichen. Die Klausel ON legt fest, dass Uebergeordnete_KategorieId aus Kategorie gleich sein muss wie Id von ElternKategorie, um die entsprechenden Zeilen zu verbinden.

Szenario 2: Auflistung von Paaren innerhalb einer Tabelle

Sie können einen Self-Join verwenden, um Zeilenpaare auf der Grundlage der Bedingung in der ON-Klausel zu erzeugen. Beginnen wir mit einem einfachen Beispiel, das alle möglichen Paare unter den Kollegen erzeugt. Betrachten Sie die folgende Tabelle Kollegen:

Angenommen, wir müssen alle möglichen Paare unter den Kollegen generieren, damit jeder eine Chance hat, mit jedem anderen am Einführungsabend der Firma zu sprechen. Hier ist der SQL-Code:

SELECT
	Teammitglied1.Vollstaendiger_Name as Teammitglied1Vollstaendiger_Name,
	Teammitglied1.Alter as Teammitglied1Alter,
	Teammitglied2.Vollstaendiger_Name as Teammitglied2Vollstaendiger_Name,
	Teammitglied2.Alter as Teammitglied2Alter
FROM Kollegen Teammitglied1
CROSS JOIN Kollegen Teammitglied2
ON Teammitglied1.Vollstaendiger_Name <> Teammitglied2.Vollstaendiger_Name

Und hier ist das Ergebnis:

Das Ergebnis bringt jede Person mit jeder einzelnen Person in der Tabelle zusammen. Da wir nicht wollen, dass jemand mit sich selbst gepaart wird, haben wir die ON Klausel Bedingung Teammitglied1.Vollstaendiger_Name <> Teammitglied2.Vollstaendiger_Name. Dies bedeutet, dass jede Person mit drei anderen Kollegen gepaart wird, da es bei dieser Veranstaltung vier Kollegen gibt.

Schauen wir uns nun ein etwas komplizierteres Beispiel an. Betrachten Sie die unten gezeigte Tabelle Mensch. Wir wollen jeder Person alle Vorfahren zuordnen, soweit die Daten dies zulassen. Wobei eine Person ein Vorfahre ist, wenn sie einen höheren Id Wert hat.

Unten ist die Mensch Tabelle, die in diesem Beispiel verwendet wird.

Lassen Sie uns alle Nachkommen-Ahnen-Paare in der obigen Tabelle finden. Hier ist der SQL-Code:

SELECT
	Nachkomme.Id,
    	Nachkomme.Vorname,
    	Nachkomme.Alter,
    	Nachkomme.ElternId,
    	Vorfahre.Id as Vorfahre_Id,
    	Vorfahre.Vorname as Vorname_des_Vorfahren,
    	Vorfahre.Alter as VorfahreAlter
FROM Mensch Nachkomme
LEFT JOIN Mensch Vorfahre
ON Nachkomme.ElternId <= Vorfahre.Id

Und das Ergebnis:

Durch Angabe der ON-Klauselbedingung Nachkomme.ElternId <= Vorfahre.Id finden wir alle Vorfahren jeder Person in der Tabelle, sofern sie vorhanden sind. Andernfalls gibt die Abfrage null für die Vorfahreninformationen zurück.

Die ersten vier Spalten werden aus der Tabelle "alias" Nachkomme entnommen, die Informationen über die Person enthält, für die die Vorfahren gesucht werden. Die letzten drei Spalten stammen aus der Tabelle "alias" Vorfahre und enthalten Details über jeden Vorfahren.

Szenario 3: Self Join in Kombination mit einer anderen Tabelle

In SQL ist es möglich, einen Self-Join in Kombination mit einer oder mehreren anderen Tabellen durchzuführen. Dies ist zwar kein reiner Self-Join, kommt aber in der Praxis sehr häufig vor.

Ein Beispiel aus der Praxis sind die Fluginformationen auf Flughäfen, die stündlich eine enorme Datenmenge enthalten. Nehmen wir an, wir wollen nach einer Flugidentifikationsnummer zusammen mit den Details zu den Abflug- und Zielflughäfen suchen. Betrachten Sie die folgenden Tabellen:

Tabelle Flughafen:

Tabelle Flug:

Hier ist zu beachten, dass die Spalte FlughafenId der Tabelle Flughafen Tabelle der Fremdschlüssel zu den Spalten StartFlughafenId und ZielFlughafenId der Tabelle Flug Tabelle ist. Wir verbinden die Flughafen Tabelle mit der Flug Tabelle zwei separate Male wie folgt:

• Im ersten JOIN übernimmt Flughafen die Rolle der Tabelle mit den Startflughäfen.
• Im zweiten JOIN übernimmt Flughafen die Rolle der Tabelle mit den Zielflughäfen.

Die Abfrage sieht wie folgt aus:

SELECT
	Flug.FlugId,
    	Flug.FlugzeugId,
    	Flug.StartFlughafenId,
    	startFlughafen.Land as StartFlughafenLand,
    	startFlughafen.Stadt as StartFlughafenStadt,
    	Flug.ZielFlughafenId,
    	endFlughafen.Land as EndFlughafenLand,
    	endFlughafen.Stadt as EndFlughafenStadt
FROM Flug Flug
JOIN Flughafen startFlughafen
ON Flug.StartFlughafenId = startFlughafen.FlughafenId
JOIN Flughafen endFlughafen
ON Flug.ZielFlughafenId = endFlughafen.FlughafenId

Und das Ergebnis der Abfrage sieht wie folgt aus:

Lassen Sie uns das Ergebnis analysieren. Die ersten drei Spalten stammen aus einem einfachen SELECT der Flug-Tabelle. Die nächsten beiden Spalten stammen aus Flughafen in der Rolle der Startflughafentabelle. Die Zeilen werden auf der Grundlage von FlughafenId und StartFlughafenId aus den Tabellen Flughafen und Flug Tabellen abgeglichen. Es folgt eine Spalte aus der Tabelle Flug. Die letzten beiden Spalten stammen aus Flughafen, diesmal in der Rolle der Tabelle des Zielflughafens. Die Zeilen werden auf der Grundlage von FlughafenId und ZielFlughafenId aus den Tabellen Flughafen und Flug abgeglichen.

Sind Sie immer noch etwas verwirrt über all die JOINs? Es gibt noch viele weitere Artikel, in denen Sie sich Hilfe holen können. Ich empfehle insbesondere den Artikel Wie man SQL-JOINs lernt. Und wenn Sie anfangen möchten zu üben, besuchen Sie bitte unseren Artikel Wie man SQL-JOINs übt.

Self Join: Ein Spezialfall des Join

Wie wir gesehen haben, ist Self-Join ein wichtiger Spezialfall des Joins. Wir haben Beispiele für verschiedene Anwendungen des Self-Joins gesehen, darunter die Verarbeitung einer Hierarchie in einer Tabelle und die Paarung von Zeilen innerhalb einer Tabelle. Wir können dieselbe Tabelle mehrfach verknüpfen, aber es ist wichtig, jedem Verweis einen Alias zu geben, der seine Rolle angibt. Diese Tabellenaliase werden verwendet, um Spalten aus dieser einzelnen Tabelle auf der Grundlage der Rolle, für die sie referenziert werden, abzurufen.

Joins sind ein wesentlicher Bestandteil von SQL und eine sehr hilfreiche, häufig verwendete Funktion zur Kombination verschiedener Tabellen. Sie sind allgegenwärtig - sehen Sie sich unbedingt unseren Kurs über SQL-JOINs um dieses mächtige Werkzeug zu beherrschen!