JOINs mit SQL-Beispielen aus der Praxis lernen

Mit der JOIN-Anweisung können Sie mit Daten arbeiten, die in mehreren Tabellen gespeichert sind. In diesem Artikel führe ich Sie anhand von SQL-Beispielen aus der Praxis durch das Thema JOIN-Klauseln.

Stellen Sie sich vor, Sie könnten jeweils nur mit einer Datenbanktabelle arbeiten. Zum Glück müssen wir uns darüber keine Sorgen machen. Sobald Sie die Anweisung JOIN gelernt haben, können Sie damit beginnen, Daten miteinander zu verknüpfen. In diesem Artikel werde ich anhand von SQL-Beispielen aus der Praxis erläutern, wie JOINs verwendet wird, wie die einzelnen Typen von JOIN funktionieren und wann die einzelnen Typen verwendet werden. Außerdem gebe ich Ihnen einige Tipps, die Ihnen helfen werden, häufige Fehler bei der Verwendung von JOINs zu vermeiden.

Was ist die SQL JOIN-Klausel?

Die JOIN Klausel ermöglicht es uns, die Spalten von zwei oder mehr Tabellen auf der Grundlage gemeinsamer Spaltenwerte zu kombinieren. Ich werde sie anhand von SQL-Echtzeitbeispielen erklären. Hier ist das erste Beispiel: Nehmen wir an, wir haben eine Tabelle students, die die Namen der Schüler, ihre jeweiligen Benutzernamen und eine ID-Nummer enthält. Außerdem haben wir eine Tabelle "Kommentare", in der alle comments gespeichert werden, die Schüler in einem Forum gepostet haben. Hier sind die beiden Tabellen.

Fügen wir einige Testdaten hinzu:

INSERT INTO `students` VALUES
(1,'Jhon','jj2005'),(2,'Albert','salbert'),(3,'Mathew','powermath'),
(4,'Lisa','lisabbc'),(5,'Sandy','imsandyw'),(6,'Tamara','tamy21');

INSERT INTO 'comments' VALUES 
(1,'jj2005','Awesome!'),(2,'jj2005','This is great :)'),
(3,'powermath','Hmmm...'),(4,'imsandyw','Let\'s wait a moment'),
(5,'lisabbc','Sure thing'),(6,'lisabbc','wow!'),
(7,'lisabbc','lol :) :) :)');

Wie Sie sehen können, haben beide Tabellen die Spalte forum_username gemeinsam. Daher kann diese Spalte in einer JOIN -Anweisung verwendet werden, um die beiden Tabellen miteinander zu verknüpfen.

Wenn wir zum Beispiel den tatsächlichen Namen des Schülers für jeden Kommentar im Forum wissen wollen, würden wir diese JOIN-Abfrage schreiben:

SELECT students.name, comments.forum_username, comments.comment
FROM students 
INNER JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

Die Ergebnisse würden wie folgt aussehen:

Hinweis: Ich habe absichtlich forum_username verwendet, um das Konzept JOIN zu veranschaulichen, aber in der Praxis würden Sie den Primärschlüssel (in diesem Fall die Spalte id aus der Tabelle students ) verwenden, um Tabellen zu verknüpfen.

Kennenlernen der JOIN-Typen

Es gibt mehrere Arten von JOINs. Lassen Sie uns diese kurz durchgehen:

INNER JOIN: Dieser JOIN gibt Datensätze zurück, die in beiden Tabellen auf der Grundlage des Verknüpfungsprädikats (das nach dem Schlüsselwort ON steht) übereinstimmen. Dies ist dieselbe JOIN, die wir im vorangegangenen Beispiel verwendet haben. Das Schlüsselwort INNER ist optional.

LEFT [OUTER] JOIN: Dies gibt alle Datensätze aus der linken Tabelle (d.h. der Tabelle, die Sie zuerst in JOIN aufführen) und nur passende Datensätze aus der rechten (d.h. zweiten) Tabelle zurück. Das Schlüsselwort OUTER ist optional.

Zurück zu unseren SQL-Beispielen aus der realen Welt: Im Fall des Studentenforums wäre dies folgendermaßen:

SELECT students.name, comments.forum_username, comments.comment
FROM students 
LEFT JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

Diese LEFT JOIN würde einen Datensatz für alle Studenten zurückgeben - einschließlich Tamara und Albert, die keine Kommentare haben und in den Ergebnissen einer INNER JOIN nicht aufgelistet wären. Beachten Sie auch, dass in diesem LEFT JOIN Beispiel die Spaltenergebnisse von Tamara und Albert comment NULL sein werden.

RIGHT [OUTER] JOIN: Dies ist die Umkehrung des LEFT JOIN; er gibt alle Datensätze aus der rechten (zweiten) Tabelle zurück und nur diejenigen, die eine Übereinstimmung aus der linken (ersten) Tabelle haben.

Eine Abfrage, die dem vorherigen Beispiel ähnelt, würde wie folgt aussehen:

SELECT 
  students.name,
  comments.forum_username,
  comments.comment
FROM students 
RIGHT JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

FULL [OUTER] JOIN: Dies ist im Wesentlichen die Kombination aus einer LEFT JOIN und einer RIGHT JOIN. Die Ergebnismenge enthält alle Zeilen aus beiden Tabellen, wobei die Spalten mit Tabellenwerten aufgefüllt werden, wenn dies möglich ist, oder mit NULLen, wenn es keine Übereinstimmung in der Gegentabelle gibt. Dies ist eine JOIN, die Sie im wirklichen Leben nicht sehr oft benutzen werden. Hinweis: In MySQL gibt es diese Anweisung nicht, aber ein ähnliches Ergebnis kann mit UNION von LEFT JOIN und RIGHT JOIN erzielt werden.

Bei unseren SQL-Beispielen aus der realen Welt sollten Sie beachten, dass wir in der folgenden FULL JOIN -Klausel das ON-Schlüsselwort angeben, wie wir es in LEFT oder RIGHT JOINs tun:

SELECT *
FROM students 
FULL OUTER JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

CROSS JOIN: Dies ist ein weiterer Join-Typ, den Sie nicht allzu oft verwenden werden - in diesem Fall, weil er das kartesische Produkt beider Tabellen abruft. Im Grunde erhalten Sie damit die Kombination aller Datensätze aus beiden Tabellen. CROSS JOIN wendet kein Prädikat an (es gibt kein ON Schlüsselwort), aber es ist dennoch möglich, Zeilen mit WHERE zu filtern. Dadurch könnte die Ergebnismenge einer INNER JOIN entsprechen. In MySQL sind JOIN, CROSS JOIN und INNER JOIN syntaktische Äquivalente, d. h. sie können einander ersetzen.

Im Folgenden gibt es keine ON Klausel, um die Ergebnisse zu filtern. Alle möglichen Kombinationen aus beiden Tabellen werden in der Ergebnismenge angezeigt:

SELECT *
FROM students 
CROSS JOIN comments 
ORDER BY students.name ASC;

Sie können mehr über die Arten von SQL-JOINs auf unserem YouTube-Kanal - We Learn SQL - erfahren. Vergessen Sie nicht, auf abonnieren zu klicken.

JOINs in SQL - Beispiele in Echtzeit

Die Anzahl der Szenarien, die einen JOIN erfordern, ist endlos, aber einige Szenarien treten häufiger auf. Anstatt das typische table1/table2-Beispiel durchzugehen, möchte ich Ihnen lieber SQL-Beispiele aus der Praxis vorstellen. Anhand dieser Beispiele können wir uns einige praktische Tipps holen.

Die Beziehung zwischen Großvater, Vater und Sohn

Ein häufiges Echtzeit-Szenario befasst sich mit Daten, die dieser Art von Beziehung folgen. Zum Beispiel befindet sich eine user in einer city, die zu einer state gehört. Die Tabellen (mit mehr oder weniger Spalten) sehen etwa so aus:

Fügen wir einige Daten hinzu:

INSERT INTO `states` VALUES (3,'California'),(2,'Florida'),(1,'Nevada');

INSERT INTO `cities` VALUES (1,1,'Miami'),(2,1,'Orlando'),
(3,2,'Las Vegas'),(4,2,'Coyote Springs');

INSERT INTO `users` VALUES (1,1,'Jhon','Doe'),(2,1,'Albert','Thomson'),
(3,2,'Robert','Ford'),(4,3,'Samantha','Simpson');

Um die vollständige Liste der Nutzer in einer bestimmten Stadt und einem bestimmten Bundesland zu erhalten, müssen wir die Tabelle des Sohnes (User) mit seinem Vater (City) und seinem Großvater (State) verbinden.

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id;

Wir haben bereits ein paar nützliche Tipps:

• Die Spalten, die zur Verknüpfung von Tabellen verwendet werden, sollten indiziert werden, um die Leistung zu verbessern.
• Wenn die Spalten, die die Tabellen verbinden (wie im ersten Beispiel), auf den Primärschlüssel der Bezugstabelle verweisen, handelt es sich um Fremdschlüssel. In diesem Fall ist es besser, diese Beziehung als Teil der Tabellendefinition einzubeziehen; dies erhöht die Leistung. In MySQL können Sie einen Fremdschlüssel Benutzer/Stadt wie folgt erstellen:

  ALTER TABLE `users` 
  ADD INDEX `fk_city_idx` (`city_id` ASC);
  ALTER TABLE `users` 
  ADD CONSTRAINT `fk_city`
    FOREIGN KEY (`city_id`)
    REFERENCES `cities` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
  

  Dadurch haben Sie den zusätzlichen Vorteil, dass die Integritätsprüfung von der Engine durchgeführt wird, wenn Daten in diesen Tabellen aktualisiert oder gelöscht werden.

Nehmen wir an, wir wollen alle Benutzer in einem bestimmten Zustand finden. Sie fügen der Abfrage eine Filterbedingung hinzu, wie unten gezeigt:

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id
  AND State.statename = 'Nevada';

Sie können auch eine implizite Verknüpfung verwenden (fett gedruckt), etwa so:

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM
   users User,
   cities City,
   states State
WHERE User.city_id = City.id
  AND City.state_id = State.id
  AND State.statename = 'Nevada';

Ich würde jedoch vorschlagen, dass Sie die JOIN explizit schreiben und die Verknüpfungskriterien und die Filterbedingungen getrennt halten:

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id
WHERE State.statename = 'Nevada';

Ein paar wichtige Konzepte:

• Wir können sehen, wie die Tabelle son (user) anhand der Bedingungen gefiltert wird, die in der Tabelle Großvater (state) Tabelle. Mit anderen Worten: Die Benutzer basieren auf einem bestimmten Zustand. In ähnlicher Weise hätten wir die Ergebnisse auf der Grundlage der Tabelle Vater (city) gefiltert und eine Liste von Nutzern auf der Grundlage einer bestimmten Stadt erhalten.
• Als Faustregel gilt, dass die JOIN -Prädikate (die Bedingungen nach dem ON -Schlüsselwort) nur für die Join-Beziehung verwendet werden sollten. Lassen Sie den Rest der Filterbedingungen innerhalb des WHERE Abschnitts. Dies vereinfacht die Lesbarkeit der Abfrage und die zukünftige Wartung des Codes.

Umgekehrt könnten wir Zustände auf der Grundlage bestimmter Kriterien zurückgeben. So könnten wir beispielsweise Zustände abrufen, zu denen mindestens ein Nutzer gehört:

SELECT 
  DISTINCT State.statename
FROM states State
INNER JOIN cities City
  ON City.state_id = State.id
INNER JOIN users User
  ON User.city_id = City.id

Was lernen wir daraus?

• Die INNER JOIN entfernt alle "unbenutzten" oder nicht übereinstimmenden Datensätze (Datensätze ohne Übereinstimmung auf beiden Seiten der JOIN).
• DISTINCT filtert doppelte Datensätze heraus. Da es für einen Zustand mehrere Benutzer geben kann, würden wir ohne DISTINCT so viele wiederholte Zustände erhalten, wie Benutzer zu diesem Zustand gehören. (Derselbe Filtereffekt könnte auch mit GROUP BY erzielt werden).
• Eine weitere Filterung könnte durch das Hinzufügen von WHERE Bedingungen erreicht werden.

Die Many-to-Many-Beziehung

Möchten Sie weitere SQL-Echtzeitbeispiele? Ein weiteres typisches Szenario für JOINist, wenn Datensätze in einer "Many-to-Many" oder N-to-N-Beziehung zueinander stehen. Angenommen, Sie haben ein System, in dem Sie Abzeichen erstellen, die an Benutzer vergeben werden. In diesem Fall hat ein Benutzer Abzeichen und gleichzeitig hat ein Abzeichen Benutzer. Für diese Beziehungen wird eine dritte Tabelle benötigt, die die Primärschlüssel von users und badges verbindet. Sie würde etwa so aussehen:

Fügen wir einige Beispieldaten hinzu:

INSERT INTO `badges` VALUES 
(1,'gold heart',100),(2,'silver heart',50),(3,'metal heart',10),(4,'star',5),(5,'wood',2),(6,'dust',1);
INSERT INTO `users` VALUES (1,'Robert','Williams'),(2,'Anthony','McPeters'),(3,'Tania','Krugger'),(4,'JJ','Richards'),(5,'Katy','Thomas');
INSERT INTO `badges_users` VALUES
(1,1,1),(2,4,1),(3,4,2),(4,4,2),(5,4,2),(6,3,3),(7,3,3),(8,2,4);

Wie kann eine JOIN alle Benutzer mit ihren jeweiligen Abzeichen abrufen?

SELECT 
  User.first_name,
  User.last_name,
  BU.user_id,
  BU.badge_id,
  Badge.badge_name,
  Badge.badge_points
FROM users User
LEFT JOIN badges_users B
  ON User.id = BU.user_id
LEFT JOIN badges Badge
  ON BU.badge_id = Badge.id
ORDER BY Badge.badge_points DESC

Hier sind einige Dinge, die Sie sich bei dieser Art von Abfrage merken sollten:

• Wir haben hier absichtlich LEFT JOIN verwendet, weil es Benutzer anzeigt, die überhaupt kein Abzeichen haben. Hätten wir eine INNER JOIN oder eine implizite innere Verknüpfung verwendet (indem wir die Gleichheit der IDs in einer WHERE gesetzt hätten), dann würden Benutzer, die keine Ausweise haben, nicht in den Ergebnissen enthalten sein. Wenn Sie diese Benutzer ausschließen möchten, sollten Sie eine INNER JOIN verwenden.
• Die Verwendung einer LEFT JOIN bedeutet auch, dass nicht verwendete Ausweise nicht aufgelistet werden; wir konzentrieren uns auf die Benutzer und nicht auf die Ausweise.
• Denken Sie schließlich daran, die Zwischentabelle ordnungsgemäß zu indizieren (badges_users). Alle ihre Fremdschlüssel sollten definiert sein.

Nehmen wir nun alle Abzeichen, die mindestens einen Benutzer haben. Anders ausgedrückt, sind dies Abzeichen, die mindestens einmal verwendet wurden. Die Abfrage würde lauten:

SELECT DISTINCT Badge.badge_name
FROM badges Badge
LEFT JOIN badges_users BadgeUser
  ON Badge.id = BadgeUser.badge_id

Und wenn wir alle nicht verwendeten Abzeichen abfragen wollten, würde die Abfrage lauten:

SELECT Badge.badge_name, Badge.badge_points
FROM badges Badge
LEFT JOIN badges_users BadgeUser
  ON Badge.id = BadgeUser.badge_id
WHERE BadgeUser.badge_id IS NULL

Beachten Sie das:

• Es ist nicht immer erforderlich, alle drei Tabellen zu verknüpfen. Durch die Verknüpfung mit der Zwischentabelle (badges_users), die die Verweise auf die Tabellen user und badge enthält, können wir unsere Abfragen trotzdem erfolgreich durchführen.
• Die Zwischentabelle kann auch dazu verwendet werden, zusätzliche Informationen zu speichern. Zum Beispiel könnte sie den Zeitstempelwert speichern, wann ein Benutzer ein bestimmtes Abzeichen erhalten hat.

Versuchen Sie es selbst

Sie werden wahrscheinlich täglich mit solchen Situationen konfrontiert, wenn Sie mit Tabellen arbeiten, die zusammenhängende Daten enthalten. Ich empfehle Ihnen dringend, die obigen SQL-Beispiele in der Praxis zu prüfen und sie mit den tatsächlichen Daten zu testen. Auf diese Weise werden Sie ein besseres Verständnis der Konzepte erlangen.

Wenn Sie Kommentare zu unseren SQL-Beispielen aus der Praxis oder eigene Ideen für andere JOIN-Klauseln aus der Praxis haben, teilen Sie sie bitte mit uns, damit wir alle weiter lernen können!