Ein illustrierter Leitfaden für den SQL-Self-Join

Was ist ein SQL-Self-Join und wie funktioniert er? Wann sollte er verwendet werden? Wir geben Antworten auf diese Fragen!

In SQL können wir Daten aus mehreren Tabellen kombinieren, indem wir einen JOIN Operator verwenden. JOIN hat mehrere Varianten; wir haben bereits CROSS JOIN, INNER JOIN und OUTER JOIN besprochen. In den meisten Fällen verbinden diese Operatoren Daten aus zwei oder mehr verschiedenen Tabellen. Sie können alle verschiedenen Arten von JOINs in unserem interaktiven SQL-JOINs Kurs üben. In diesem Artikel wird jedoch erklärt, wie man Datensätze aus ein und derselben Tabelle verknüpft.

Ein SQL-Self-Join verknüpft Daten aus derselben Tabelle. Mit anderen Worten, er verknüpft eine Tabelle mit sich selbst. Die Datensätze aus der Tabelle werden mit anderen Datensätzen aus derselben Tabelle abgeglichen. Warum sollten Sie das tun? Vielleicht müssen Sie einen Wert mit einem anderen Wert aus derselben Zeile vergleichen. Dies ist nur möglich, wenn Sie die Tabelle mit sich selbst verknüpfen und die Werte so vergleichen, als ob sie in zwei getrennten Datensätzen stünden.

Für diese Art von Abfrage gibt es keinen speziellen Operator. Stattdessen verwendet der SQL-Self-Join den INNER JOIN -Operator, einen der OUTER JOIN -Operatoren oder einen CROSS JOIN. Der Unterschied besteht darin, dass eine einzelne Tabelle sowohl als linke als auch als rechte Tabelle in der Verknüpfung aufgeführt wird.

Schauen wir uns ein Beispiel für einen SQL-Self-Join an, der auf der Tabelle employee basiert (siehe unten):

Die Tabelle employee speichert die ID-Nummern der Mitarbeiter, Vornamen, Nachnamen, Gehälter und die ID-Nummer des Vorgesetzten. Mit Ausnahme des Chefs (dessen ID = 1) hat jeder Mitarbeiter einen Vorgesetzten. Nur der Chef kann einen NULL-Wert in der Spalte manager_id haben.

Versuchen wir eine Abfrage, die den unmittelbaren Vorgesetzten eines jeden Mitarbeiters zurückgibt:

SELECT e.id, e.first_name, e.last_name,  e.salary, 
  m.first_name AS fname_boss, m.last_name AS lname_boss
FROM employee e
JOIN employee m ON  e.manager_id = m.id ;

Beachten Sie, dass wir die Tabelle employee sowohl als linke als auch als rechte Tabelle in der Verknüpfung verwendet haben. Um Daten aus derselben Tabelle zu verknüpfen, mussten wir dem Tabellennamen zwei Aliasnamen zuweisen.

Bei Self-Joins müssen Sie Aliasnamen verwenden. Da Sie Spalten aus derselben Tabelle verknüpfen, haben sie die gleichen Namen. Der Alias benennt die Spalten um, damit die Datenbankmaschine Ihre Abfrage ausführen kann. In der obigen Abfrage haben wir den Buchstaben e als Alias für die linke Tabelle. In diesem Fall verwenden wir die Tabelle employee für ihre Mitarbeiterdatensätze. Wir verwenden den Buchstaben m als Alias für die rechte Tabelle, die die Tabelle employee Tabelle für die Datensätze des Managers verwendet. Obwohl wir eine Tabelle verwenden, behandelt SQL sie als zwei verschiedene Tabellen.

Wir haben die Mitarbeiterdaten ausgewählt, indem wir die Spalten id, first_name und last_name in der e "Tabelle". Die Daten der Manager wurden durch Verweis auf die m "Tabelle". Beachten Sie, dass den Datensätzen der Mitarbeiter ein "e" und den Datensätzen der Manager ein "m" vorangestellt wurde. Wir konnten sie nun verknüpfen, weil wir eine geeignete Verknüpfungsbedingung verwendet haben: Die Spalte manager_id aus der Tabelle e Tabelle wurde mit der Spalte id aus der Tabelle m. Die Datensätze aus beiden Tabellen wurden mit dem Operator JOIN (oder INNER JOIN; denken Sie daran, dass das "INNER" impliziert ist) abgeglichen.

Ist Ihnen aufgefallen, dass der Chef nicht in den Ergebnissen angezeigt wird? Es gibt keine Übereinstimmung in der rechten Tabelle "manager", also konnten keine Daten über den Vorgesetzten des Chefs gefunden werden.

Die folgende Abbildung zeigt, wie die Datensätze bei der Ausführung dieser Abfrage abgeglichen werden.

Analysieren wir nun die Daten in der Ausgabetabelle:

In unserer Tabelle hat die JOIN keinen Datensatz für John Watson zurückgegeben. Er hat keinen Bezeichner, der auf die ID seines Vorgesetzten verweist, also ist er der Chef. Wir müssten eine LEFT JOIN anstelle einer INNER JOIN verwenden, wenn wir Daten für alle Mitarbeiter, einschließlich des Chefs, zurückgeben wollten. In diesem Fall würden auch Datensätze aus der linken Tabelle angezeigt werden, die in der rechten Tabelle nicht übereinstimmen:

SELECT e.id, e.first_name, e.last_name,  e.salary, m.first_name 
   AS fname_boss  , m.last_name  AS lname_boss
FROM employee e
LEFT JOIN employee m ON  e.manager_id = m.id ;

Unser interaktiver SQL-JOINs Kurs ist ein ganzer Abschnitt dem Üben von Self-Joins gewidmet.

Wann sollte man Self Join in SQL verwenden?

Self-Joins werden häufig in den folgenden Bereichen verwendet:

• Hierarchische Beziehungen
• Sequentielle Beziehungen
• Graphische Daten

Wir werden uns jeden dieser Bereiche einzeln ansehen.

Hierarchische Daten

Die Verarbeitung hierarchischer Daten ist eine der häufigsten Anwendungen des SQL-Self-Joins. Dies geschieht, wenn es eine zusätzliche Spalte gibt, die auf einen Bezeichner in derselben Tabelle verweist, wie z. B. in unserer employee Tabelle. In unserem Fall verweist die Spalte manager_id auf die Spalte id (hat den gleichen Wert wie diese).

Das oben genannte Beispiel (die Beziehung zwischen Mitarbeiter und Manager) ist nicht die einzige Situation, in der hierarchische Daten verwendet werden. A Stückliste für ein Auto weist eine ähnliche Struktur auf. Jedes Auto besteht aus mehreren Komponenten, wie z. B. dem Motor, dem Bremssystem und dem elektrischen System. Jede dieser Komponenten besteht aus kleineren Teilen. Das elektrische System eines Autos kann in seine Bestandteile zerlegt werden, z. B. in die Batterie und die Lichtmaschine (die wiederum in noch mehr Autoteile zerlegt werden können). Dies bedeutet, dass Autoteile eine Gruppe hierarchischer Daten darstellen. Die Tabelle car Tabelle zeigt einige Daten zu Autoteilen.

Ein weiteres Beispiel für hierarchische Daten ist die Eltern-Kind-Beziehung. Durch die Speicherung dieser Beziehung können wir eine einzige Tabelle verwenden, um einen ganzen Stammbaum zu speichern. Mit einer SQL-Selbstverknüpfung können wir dann leicht Daten über die Vorfahren einer bestimmten Person abrufen.

Die folgende Tabelle kann uns helfen, die ältesten Vorfahren in einer Familie schnell zu identifizieren. Diese Personen haben keine Vorfahrendaten in ihren Datensätzen, d. h. sie bilden die Wurzel des Stammbaums.

Die Tabelle person Tabelle veranschaulicht dies:

Die nachstehende Abfrage ruft den Vor- und Nachnamen jeder Person zusammen mit dem Vor- und Nachnamen der Mutter und des Vaters ab.

SELECT c.first_name, c.last_name, m.first_name AS fname_mother, m.last_name  AS lname_mother
FROM family c
LEFT JOIN person m ON  c.mather_id = m.id 
LEFT JOIN person f ON  c.father_id = f.id ;

Beachten Sie, dass wir dreimal eine Selbstverknüpfung verwenden mussten (d. h. auf drei "Tabellen" oder Aliasen), um die Daten sowohl über den Vater als auch über die Mutter zu erhalten. John Watson, Anne Brown und Jacob Miller haben keine Daten, die auf ihre Vorfahren verweisen.

In den oben beschriebenen Beispielen für hierarchische Strukturen werden die Daten nach dem Prinzip der übergeordneten und untergeordneten Datensätze gespeichert. Dies ermöglicht es uns, Daten als Baumstruktur darzustellen. Sie werden mehr über die Verarbeitung von Baumstrukturen in SQL erfahren, wenn Sie sich weiter mit relationalen Datenbanken beschäftigen.

Sequentielle Daten

Sequentielle Daten können auch von der Verwendung einer SQL-Selbstverknüpfung profitieren. Nehmen wir zum Beispiel an, Sie haben Datensätze, die die aufeinanderfolgenden Schritte beschreiben, die für die Zubereitung eines Gerichts erforderlich sind. Alle Schritte können in einer einzigen Tabelle untergebracht werden. Ihre Reihenfolge wird anhand der Spalten bestimmt, die auf die IDs der vorherigen und nächsten Datensätze in derselben Tabelle verweisen.

Veranschaulichen wir dies anhand der instruction Tabelle:

Wie Sie sehen, machen diese Anweisungen in der aktuellen Reihenfolge keinen Sinn. Wenn wir jedoch eine SQL-Selbstverknüpfung verwenden, um die sequentielle Beziehung für jeden Schritt des Rezepts darzustellen, erhalten wir:

1. Heizen Sie den Ofen auf 220 Grad C vor.
2. Vier Kartoffeln schälen.
3. Schneiden Sie die Kartoffeln in Scheiben.
4. Die Kartoffelscheiben mit Öl beträufeln.
5. Im vorgeheizten Backofen 20 Minuten backen.
6. Die heißen Scheiben mit Salz und Pfeffer würzen.

Es gibt auch leckere Ofenpommes!

Diagramme

Self join SQL kann auch verwendet werden, um die für Graphen benötigten Beziehungen darzustellen. Ein Graph ist eine Struktur, die aus Knoten besteht, die durch Kanten (Beziehungen) miteinander verbunden sind. Ein Beispiel für einen Graphen ist das Straßennetz zwischen mehreren Städten.

Werfen Sie einen Blick auf die folgende Zeichnung.

Dieses Diagramm stellt fünf Städte dar, die miteinander verbunden sind. Jeder Pfeil zeigt eine Straße von einer Stadt zur anderen. In diesem Fall sind die Städte die Knoten und die Straßen zwischen ihnen sind die Kanten. Wir verwenden zwei Tabellen, um diese Daten zu speichern. Die Tabelle city Tabelle speichert die ID-Nummer und den Namen der einzelnen Städte. Die Tabelle route Tabelle enthält die ID-Nummer der Route, die Startstadt (Spalte from_city_id ) und die Zielstadt (Spalte to_city_id ).

Hier ist die Tabelle "Stadt":

Und dies ist die Tabelle "Route":

Wir können einen SQL-Self-Join auf die Tabelle city zusammen mit einer INNER JOIN der beiden Tabellen verwenden, um herauszufinden, welche Routen zwischen Städten existieren.

Werfen Sie einen Blick auf die Abfrage.

SELECT c1.name AS from_city, c2.name AS to_city 
FROM city c1
JOIN route r ON c1.id = r.from_city_id
JOIN city c2  ON c2.id = r.to_city_id ;

Die Spalten city und route wurden über die Spalte id aus city und die Spalte from_city_id aus route. Zu diesem Zeitpunkt konnten wir nur den Namen der Startstadt abrufen. Um den Namen der Zielstadt abzurufen, verwendeten wir einen Self-Join auf die city Tabelle. Diesmal verglichen wir die Spalte id aus der Tabelle aliased city Tabelle mit der Spalte to_city_id in der route Tabelle.

Hier ist das Ergebnis:

Sie können auch einen SQL-Self-Join in rekursiven Unterabfragen verwenden, die Graphen speichern. Weitere Informationen zu diesem Thema finden Sie in unserem neuen Kurs "Rekursive Abfragen".

SQL Self Join verwenden, um doppelte Werte zu finden

Self-Joins können auch verwendet werden, um doppelte Werte in einer Tabelle zu identifizieren. Lassen Sie uns eine Beispieltabelle namens color:

Jeder Datensatz in der Tabelle ist aufgrund der Spalte id, die immer eindeutig sein muss, unterschiedlich. Dies verhindert jedoch nicht, dass zwei Zeilen denselben Farbnamen speichern. Wir wollen solche Fälle identifizieren und die IDs der doppelten Farbnamen finden. Versuchen wir dies:

SELECT c1.id AS id1, c1.name  AS color1, c2.id AS id2, c2.name AS color2
FROM color c1
JOIN color c2 ON c1.name = c2.name AND c1.id < c2.id   ; 

Wir waren in der Lage, doppelte Farbnamen zu finden, weil wir Datensätze auf der Grundlage des Farbnamens selbst gejoint haben. Die zweite Bedingung wird verwendet, um identische Datensätze aus beiden Tabellen sowie gleiche Paare von Datensätzen in umgekehrter Reihenfolge zu überspringen.

Werfen Sie einen Blick auf das Abfrageergebnis:

Es ist nun leicht zu erkennen, dass es doppelte Werte für Blau und Gelb gibt.

Mehr erfahren

Wenn Sie mehr über JOINs erfahren möchten, als wir in dieser illustrierten SQL-Self-Join-Anleitung behandelt haben, besuchen Sie unseren SQL-JOINs Kurs. Denken Sie daran, die beste Art zu lernenSQL-JOINs - oder irgendetwas anderes - ist, wenn man es selbst tut!