Bewertung: 5 / 5

Stern aktivStern aktivStern aktivStern aktivStern aktiv
 
SQL ist nicht ganz einfach zu verstehen, speziell wenn es darum geht mehrere Tabellen mit joins zusammenzufassen bzw zu verknüpfen. Im Folgenden wird dem SQL Einsteiger sehr einfach mit Venn Diagrammen sowie einfachen SQL queries erklärt  wie die verschiedenen SQL joins funktionieren. Dazu werden zwei Beispieltabellen mit Daten benutzt und Joins beschrieben sowie deren Eingabedaten und Ergebnisse. Dateien zum Erstellen der Beispieldaten sowie die SQL queries können downloaded werden. Die Beispiele sind für mySQL und DB2.
 
Auf SQL durch Beispiele lernen befinden sich weitere Beispiele für komplexere Joins sowie andere SQL Funktionen wie views, count, max, subselect uvm.
 
sql_innerjoin.jpg sql_leftouterjoin.jpg sql_fullouterjoin.jpg sql_leftouterjoinminusintersection.jpg sql_fullouterjoinminusintersection.jpg

 

 
Zur Einführung werden Venn Diagramme benutzt. Im streng mathematischen Sinne benutzt man Venn Diagramme nur für Mengen und deren Operationen wie Vereinigung, Schnitt, Differenz usw, aber sie sind auch sehr hilfreich um sql joins zu erklären und zu verstehen.
 
Genaugenommen gibt es vier verschiedene sql joins die man verstehen muss:
 
1) inner join
2) left/right outer join
3) full outer join
4) karthesisches Produkt
 
Wenn man Venn Diagramme benutzt um die Joins zu erklären gibt es noch zwei weitere Joins, die Entsprechungen in den Venn Diagrammen haben und das Verständnis von Joins fördern.
 
5) Left outer join where null
6) Full outer joinwhere null
 
In den folgenden Venn Diagrammen findet man immer zwei Tabellen TabelleA und TabelleB, die jeweils Zeilen enthalten, in denen Namen und weitere Informationen stehen. Der Join wird immer über die Namen gemacht, d.h. es werden Zeilen in beiden Tabellen gesucht, die gleiche Namen haben und deren Joinergebnisse betrachtet.
 

 

Bei den folgenden Beispielen enthalten die Tabellen A und B folgende Spalten und Inhalt:
Jede Tabelle hat einen Schlüssel id, eine Spalte name sowie eine Spalte nation mit der Nationalität der Person sowie eine Spalte table, die entweder A oder B enthält um zu kennzeichnen, in welcher Tabelle die Zeilenstehen, also Tabelle A oder Tabelle B. 
 
Die sql Befehle um die Tabelle anzulegen und zu füllen können hier für mySQL und hier für DB2 runtergeladen werden.  Die Queries um die Joins über die Tabelle auszuführen befinden sich hier für mySQL und hier für DB2 zum download.
 
Tabelle A:
 
mysql> select * from TableA;
+----+-----------+---------+-------+
| id | name      | nation  | table |
+----+-----------+---------+-------+
|  1 | Albert    | Germany | A     |
|  2 | Elizabeth | England | A     |
|  3 | Paul      | Spain   | A     |
|  4 | Julia     | Denmark | A     |
+----+-----------+---------+-------+
                                                                                                                                
 
Tabelle B:
mysql> select * from TableB;
+----+--------+--------+-------+
| id | name   | nation | table |
+----+--------+--------+-------+
|  1 | Marc   | China  | B     |
|  2 | Albert | France | B     |
|  3 | Peter  | Japan  | B     |
|  4 | Paul   | Poland | B     |
+----+--------+--------+-------+
 

 
1) Inner join
 
Bei einem Inner join werden alle Zeilen aus der Tabelle A und der Tabelle B zusammengeführt, wo die Join Spaltenelemente gleich sind.
 
mysql> select * from TableA inner join TableB on TableA.name = TableB.name;
+----+--------+---------+-------+----+--------+--------+-------+
| id | name   | nation  | table | id | name   | nation | table |
+----+--------+---------+-------+----+--------+--------+-------+
|  1 | Albert | Germany | A     |  2 | Albert | France | B     |
|  3 | Paul   | Spain   | A     |  4 | Paul   | Poland | B     |
+----+--------+---------+-------+----+--------+--------+-------+
 
Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A und B zusammengefügt hat, wo jeweils der Name gleich ist, d.h. Albert und Paul.
 
sql_innerjoin.jpg
 

2) Left outer join
 
Bei einem left outer join werden alle Zeilen aus der Tabelle A und der Tabelle B zusammengeführt, wo die Join Spaltenelemente gleich sind sowie alle Elemente aus der linken Tabelle, die in diesem Falle Tabelle A ist.
 
mysql> select * from TableA left outer join TableB on TableA.name = TableB.name;
+----+-----------+---------+-------+------+--------+--------+-------+
| id | name      | nation  | table | id   | name   | nation | table |
+----+-----------+---------+-------+------+--------+--------+-------+
|  1 | Albert    | Germany | A     |    2 | Albert | France | B     |
|  2 | Elizabeth | England | A     | NULL | NULL   | NULL   | NULL  |
|  3 | Paul      | Spain   | A     |    4 | Paul   | Poland | B     |
|  4 | Julia     | Denmark | A     | NULL | NULL   | NULL   | NULL  |
+----+-----------+---------+-------+------+--------+--------+-------+
 
Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A und B zusammengefügt hat, wo jeweils der Name gleich ist, d.h. Albert und Paul aber auch die Elemente aus der Tabelle A, wo es keinen gleichen Namen in Tabelle B gibt (Elizabeth und Julia). Diese Elemente sind daran zu erkennen, dass der Teil der Ergebnisspalten, wo die Teile aus der Tabelle B stehen mit NULL gefüllt sind.
 
sql_leftouterjoin.jpg
 

   
3) Full outer join
 
Bei einem full outer join werden alle Zeilen aus der Tabelle A und der Tabelle B zusammengeführt, wo die Join Spaltenelemente gleich sind sowie alle Elemente aus den beiden Tabellen, diekeine gleichen Elemente haben.
 
mysql: (Da mysql kein full outer join anbietet wird der full outer join über eine Vereinigung von dem left und right outer join vorgenommen, der dasselbe Ergebnis liefert)
 
mysql> select * from TableA left join TableB on TableA.name = TableB.name
    -> UNION
    -> select * from TableA right join TableB on TableA.name = TableB.name;
 
DB2: (Richtiger full join)
 
select * from TableA full join TableB on TableA.name = TableB.name; 
+------+-----------+---------+-------+------+--------+--------+-------+
| id   | name      | nation  | table | id   | name   | nation | table |
+------+-----------+---------+-------+------+--------+--------+-------+
|    1 | Albert    | Germany | A     |    2 | Albert | France | B     |
|    2 | Elizabeth | England | A     | NULL | NULL   | NULL   | NULL  |
|    3 | Paul      | Spain   | A     |    4 | Paul   | Poland | B     |
|    4 | Julia     | Denmark | A     | NULL | NULL   | NULL   | NULL  |
| NULL | NULL      | NULL    | NULL  |    1 | Marc   | China  | B     |
| NULL | NULL      | NULL    | NULL  |    3 | Peter  | Japan  | B     |
+------+-----------+---------+-------+------+--------+--------+-------+
 
Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A und B zusammengefügt hat, wo jeweils der Name gleich ist, d.h. Albert und Paul aber auch die Elemente aus der Tabelle A, wo es keinen gleichen Namen in Tabelle B gibt (Elizabeth und Julia) sowie die Elemente aus Tabelle B, wo es kein gleichen Namen in Tabelle A gibt (Marc und Peter). Diese Elemente sind daran zu erkennen, dass der Teil der Ergebnisspalten, wo die Teile aus der anderen Tabelle nicht existieren mit NULL gefüllt sind.Bei dem Zusammenfügen (UNION) der beiden Tabellen ist zu berücksichtigen, dass dabei eigentlich die Zeilen, die gleiche Namen in beiden Tabellen haben, in der Ergebniszeile doppelt auftreten, da sie sowohl im left als auch right join enthalten sind. Allerdings entfernt die UNION diese doppelten Elemente. Im folgenden Beispiel wird durch UNION ALL diese Entfernung verhindert. Die Zeilen mit Albert und Paul tauchen zweimal auf.
 
mysql> select * from TableA left join TableB on TableA.name = TableB.name  UNION ALL  select * from TableA right join TableB on TableA.name = TableB.name;
+------+-----------+---------+-------+------+--------+--------+-------+
| id   | name      | nation  | table | id   | name   | nation | table |
+------+-----------+---------+-------+------+--------+--------+-------+
|    1 | Albert    | Germany | A     |    2 | Albert | France | B     |
|    2 | Elizabeth | England | A     | NULL | NULL   | NULL   | NULL  |
|    3 | Paul      | Spain   | A     |    4 | Paul   | Poland | B     |
|    4 | Julia     | Denmark | A     | NULL | NULL   | NULL   | NULL  |
| NULL | NULL      | NULL    | NULL  |    1 | Marc   | China  | B     |
|    1 | Albert    | Germany | A     |    2 | Albert | France | B     |
| NULL | NULL      | NULL    | NULL  |    3 | Peter  | Japan  | B     |
|    3 | Paul      | Spain   | A     |    4 | Paul   | Poland | B     |
+------+-----------+---------+-------+------+--------+--------+-------+
 
sql_fullouterjoin.jpg

 

 
4) Cross join - Karthesisches Produkt
 
In diesem Falle gibt es keine Join Bedingung. Deshalb werden  bei diesem join jeweils alle Zeilen der linken Tabelle mit den jeweiligen Zeilen der rechten Tabelle  zusammengeführt. D.h. die Ergebnistabelle enthält immer (Anzahl Zeilen TabelleA) * (Anzahl Zeilen TabelleB) Ergebniszeilen. Diese kann bei großen Ausgangstabellen sehr schnell riesige Ergebnistabellen ergeben deren Berechnung sehr zeit- und speicher aufwendig ist und sollte deshalb vermieden werden.
 
mysql> select * from TableA join TableB;
+----+-----------+---------+-------+----+--------+--------+-------+
| id | name      | nation  | table | id | name   | nation | table |
+----+-----------+---------+-------+----+--------+--------+-------+
|  1 | Albert    | Germany | A     |  1 | Marc   | China  | B     |
|  2 | Elizabeth | England | A     |  1 | Marc   | China  | B     |
|  3 | Paul      | Spain   | A     |  1 | Marc   | China  | B     |
|  4 | Julia     | Denmark | A     |  1 | Marc   | China  | B     |
|  1 | Albert    | Germany | A     |  2 | Albert | France | B     |
|  2 | Elizabeth | England | A     |  2 | Albert | France | B     |
|  3 | Paul      | Spain   | A     |  2 | Albert | France | B     |
|  4 | Julia     | Denmark | A     |  2 | Albert | France | B     |
|  1 | Albert    | Germany | A     |  3 | Peter  | Japan  | B     |
|  2 | Elizabeth | England | A     |  3 | Peter  | Japan  | B     |
|  3 | Paul      | Spain   | A     |  3 | Peter  | Japan  | B     |
|  4 | Julia     | Denmark | A     |  3 | Peter  | Japan  | B     |
|  1 | Albert    | Germany | A     |  4 | Paul   | Poland | B     |
|  2 | Elizabeth | England | A     |  4 | Paul   | Poland | B     |
|  3 | Paul      | Spain   | A     |  4 | Paul   | Poland | B     |
|  4 | Julia     | Denmark | A     |  4 | Paul   | Poland | B     |
+----+-----------+---------+-------+----+--------+--------+-------+ 

Man sieht also, dass das Ergebnis alle Zeilen aus der Tabelle A jeweils mit allen Elementen der Tabelle B zusammengefügt hat. Dabei tauchen keine Spalten mit NULL auf.
 
Eine Darstellung als Venn Diagramm gibt es dafür nicht.     
 

 
5) Left outer join where null
 
Bei diesem join werden alle Zeilen aus der Tabelle A mit der Tabelle B zusammengeführt, wo es keine gleichen Elemente gibt. Es entspricht also auch dem left outer join wo der inner join entfernt wurde.
 
mysql> select * from TableA left outer join TableB on TableA.name = TableB.name
    -> WHERE TableB.id is null;
+----+-----------+---------+-------+------+------+--------+-------+
| id | name      | nation  | table | id   | name | nation | table |
+----+-----------+---------+-------+------+------+--------+-------+
|  2 | Elizabeth | England | A     | NULL | NULL | NULL   | NULL  |
|  4 | Julia     | Denmark | A     | NULL | NULL | NULL   | NULL  |
+----+-----------+---------+-------+------+------+--------+-------+

Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A und B zusammengefügt hat, wo jeweils für Namen aus der linken Tabelle A keine Namen in der Tabelle B existieren. Da ein Join vorgenommen wird tauchen Spalten der Tabelle B im Ergebnis auf, die aber alle mit NULL gefüllt sind.
 
sql_leftouterjoinminusintersection.jpg
 

 
6) Full outer join where null
 
Bei diesem join werden alle Zeilen der beiden Tabellen  zusammengeführt, wo es keine gleichen Elemente in der anderen Tabelle gibt. Er entspricht also dem full outer join wo der inner join entfernt wurde.
 
mysql> select * from TableA left join TableB on TableA.name = TableB.name where TableB.id is null
    -> UNION
    -> select * from TableA right join TableB on TableA.name = TableB.name where TableA.id is null;
+------+-----------+---------+-------+------+-------+--------+-------+
| id   | name      | nation  | table | id   | name  | nation | table |
+------+-----------+---------+-------+------+-------+--------+-------+
|    2 | Elizabeth | England | A     | NULL | NULL  | NULL   | NULL  |
|    4 | Julia     | Denmark | A     | NULL | NULL  | NULL   | NULL  |
| NULL | NULL      | NULL    | NULL  |    1 | Marc  | China  | B     |
| NULL | NULL      | NULL    | NULL  |    3 | Peter | Japan  | B     |
+------+-----------+---------+-------+------+-------+--------+-------+

Man sieht also, dass das Ergebnis die beiden Zeilen aus der Tabelle A und B zusammengefügt hat, wo jeweils für Namen aus der linken Tabelle A keine Namen in der Tabelle B existieren und für Namen aus der rechten Tabelle B keine Namen in der linken Tabelle A existieren. Da ein Join vorgenommen wird tauchen Spalten der Tabelle A und B im Ergebnis auf, die aber alle mit NULL gefüllt sind.
 
sql_fullouterjoinminusintersection.jpg
 
 
References: 
 
Kommentar schreiben

*** Hinweis ***

Kommentare sind erwünscht. Aber um lästige Spamposts abweisen zu können gibt es ein paar Dinge die zu beachten sind:
  1. Kommentare mit dem Text http werden sofort zurückgewiesen mit der Meldung Sie sind nicht berechtigt den Tag zu verwenden. zz
  2. Kommentare werden manuell überprüft und es dauert deshalb in der Regel einen Tag bis sie veröffentlicht werden.

    Kommentare   
    #16 SwaggyBoy2 2023-06-19 08:55
    zitiere HP Drucker:
    Sorry, aber: Der fliegende Hund soll bestimmt witzig sein! Es stört massig und ist einfach albern! (NICHT witzig)


    zitiere framp:
    Moin HP Drucker,

    das ist kein fliegender Hund sondern eine Erdmännchenfigur welche eine rote Weihnachtsmütze auf dem Kopf hat und auf meiner Terrasse vor mehreren Jahren eingeschneit wurde. Ich habe sie dann fotografiert und seitdem wackelt sie jedes Jahr zur Weihnachtszeit über diese Webseite :-) Es ist nicht witzig und nicht albern sondern der momentanen Adventszeit angemessen.

    Wenn es Dich stört sehe ich folgende Alternative:

    Du wartest bis zum 27.12. Dann ist das WeihnachtsErdmännchen wieder für ein Jahr verschwunden :lol:

    Cu framp


    es ist witzig... ;-) 8) isabodohjiasbdijsadasd
    Zitieren
    #15 M. E. 2021-11-21 18:23
    Endlich mal eine Erklärung, die mit Beispielen unterlegt und dadurch verständlich ist!
    SQL Joins habe ich nie richtig verstanden, jetzt weiß ich endlich, was ich mit den Grafiken anfangen soll! (Die sind ohne Erklärung nämlich nicht wirklich hilfreich)
    Zitieren
    #14 John Lorenz Moser 2021-11-11 08:24
    :lol: Gut Erklärt
    Finde es sehr gut erläutert und sehr gut beschreiben besonders auch durch die Bilder

    weiter so ;-)
    Zitieren
    #13 thomas 2020-01-07 17:03
    Super Erklärung und einfach verständlich - danke!!
    Zitieren
    #12 framp 2018-12-04 19:15
    Moin HP Drucker,

    das ist kein fliegender Hund sondern eine Erdmännchenfigur welche eine rote Weihnachtsmütze auf dem Kopf hat und auf meiner Terrasse vor mehreren Jahren eingeschneit wurde. Ich habe sie dann fotografiert und seitdem wackelt sie jedes Jahr zur Weihnachtszeit über diese Webseite :-) Es ist nicht witzig und nicht albern sondern der momentanen Adventszeit angemessen.

    Wenn es Dich stört sehe ich folgende Alternative:

    Du wartest bis zum 27.12. Dann ist das WeihnachtsErdmännchen wieder für ein Jahr verschwunden :lol:

    Cu framp
    Zitieren
    #11 HP Drucker 2018-12-04 09:53
    Sorry, aber: Der fliegende Hund soll bestimmt witzig sein! Es stört massig und ist einfach albern! (NICHT witzig)
    Zitieren
    -1 #10 Birni 2017-10-12 10:40
    WOW. Danke vielmals. Vorallem die Grafiken haben mir geholfen. Die leichte Transparenz erläutert die Joins besser als alle mir bis jetzt bekannten Grafiken. :D
    Zitieren
    -5 #9 Peter 2017-05-29 09:42
    Danke endlich verstanden
    Zitieren
    -1 #8 Vera 2017-02-09 18:19
    Genial, habe es endlich verstanden, vielen Dank
    Zitieren
    +1 #7 framp 2016-08-26 14:23
    Moin Erik,

    auf dieser Seite habe ich versucht mit Venn Diagrammen den Einstieg in die Welt der SQL Joins Einsteigern zu erleichtern.

    Fuer mehrere Tabellen sind Venn Diagramme nicht mehr geeignet. Wer mit >2 Tabellen arbeitet ist kein Einsteiger mehr und gehoert nicht mehr zur Zielgruppe dieser Seite :-) Es gibt aber diverse andere Seiten im Netz die bei der Beantwortung Deiner Frage helfen.

    Cu framp
    Zitieren
    -6 #6 Erik Lorenz 2016-08-26 08:36
    Moin,

    die Erklärungen sind sehr gut und echt hilfreich. Aber wie verhält es sich, wenn ich nicht 2 sondern 5 Tabellen mit einander verbinden möchte und die Abfrage sich über mehrere Referenzen erstreckt? Wie baut man einen solchen Join auf?
    Zitieren
    #5 Tobias 2016-03-11 15:21
    Danke, hat mir bei einen Problem weiter geholfen!
    Zitieren
    -6 #4 Swagboy 2015-04-24 10:13
    Super mega geil :lol: :lol: :lol:
    Zitieren
    #3 i 2014-10-17 10:28
    Sehr gute Erklärung die auch ohne viel Vorwissen verstanden werden kann. Vielen Dank!!!
    Zitieren
    -6 #2 framp 2014-02-05 19:57
    Im streng mathematischen Sinn ist die Anwendung von Venndiagrammen nich korrekt. Aber ich persoenlich finde diese Darstellung hilfreich um die Joins zu verstehen. Und es freut mich dass es Dir auch so geht.
    Zitieren
    -1 #1 Walter 2014-02-05 09:52
    Sehr nice gemacht. Beschaeftige mich eher selten mit SQL im Rahmen meiner Hobby-Programmierkunst, und erkenne bloss so nach langer Zeit ganz langsam die Sphaeren datenbankteschnischer Funktionalitaet.

    Ich sehe diese Diagramme zum ersten mal, komme mir im Moment ein wenig an den Film Tron erinnert vor, wo das Programm den Schlitten von einem Leitstrahl auf den anderen lenkt. :-*

    Greets
    Zitieren