Home / Tutorials / mysql Handbuch / joins


Tabellen verknüpfen
equi join
left join
left join über mehrere Tabellen
temporary table
self join

Tabellen verknüpfen

Denkt man an relationale Datenbanken, denkt man an die Tatsache, daß in relationalen Datenbanken die Daten in unterschiedlichen Tabellen gehalten werden, die über Primär- und Fremdschlüssel miteinander verknüpft sind. Mit Hilfe dieser Technik ist es möglich, Daten so zu strukturieren, dass Informationen nur einmal gespeichert werden. Wir sehen uns mit unserem alten Problem konfrontiert. Um irgendetwas zu testen, brauchen wir Testdaten. Alle Beispiele basieren auf diesen Tabellen.

Mann
KennungM Vorname Name Telefon email
1 Mario Cantinflas 040-345444 mario_cantinflas@aol.com
2 Peter Arrowsmith 030-345444 arrosmith@southbell.net
3 Kai Schmidt 089-3453345 schmidt@berlin.de
4 Andreas Brinckmann 0789-345343434 brinckmann2@freiburg-online.de
5 Thomas Müller 0789-345433 thomas@uni-freiburg.com

Frau
KennungF Vorname Name Telefon email
1 Su Smith 089-3452344 su@southbell.net
2 Maria Schulze 0767-34533456 maria@freiburg.net
3 Erika Dinkelman 089-235633443 erika@aol.de
4 Petra Henkel 087-3453433454 henkel@otto.com
5 Inge Hackel 0657-4564533 hackel@stuttgart.com
6 Bettina Schurwei 034-234524 b_schurwei@yahoo.de

Kind
KennungK Vorname Name
1 Erich Arrowsmith
2 Maria Dinkelmann
3 Andrea Brinkmann
4 Petra Hackel
5 Thomas Cantinflas
6 Hans Erhard

Verwandschaft
IdMann IdFrau IdKind
3 1 5
2 5 1
3 1 3
4 5 2
6 3 4

Unter Umständen ist es günstig, die Tabellen zu kopieren und auszudrucken, da die Beispiele dann leichter nachvollzogen werden können. Um die Beispiele nachvollziehen zu können, müssen wir diese vier Tabellen noch einrichten und die Daten einspulen. Das machen wir mit diesem kleinen Programm.

use testlauf;

create table Maenner (
KennungM int(3) NOT NULL,
Vorname char(30),
Name char(30),
Telefon char(50),
email char(20),
PRIMARY KEY(KennungM));

create table Frauen(
KennungF int(3) NOT NULL,
Vorname char(30),
Name char(30),
Telefon char(5),
email char(3),
PRIMARY KEY(KennungF));

create table Kinder(
KennungK int(3) NOT NULL,
Vorname char(20) ,
Name char(20),
PRIMARY KEY(KennungK));

create table Verwandschaft(
IdMann int(3) NOT NULL,
IdFrau int(3) NOT NULL,
IdKind int(3) NOT NULL,
index(IdMann),index(IdFrau),index(IdKind));


load data local infile 'c:/mysql_handbuch/Frauen.txt' into table Frauen Fields terminated by '||';

load data local infile 'c:/mysql_handbuch/Maenner.txt' into table Maenner Fields terminated by '||';

load data local infile 'c:/mysql_handbuch/Kinder.txt' into table Kinder Fields terminated by '||';

load data local infile 'c:/mysql_handbuch/Verwandschaft.txt' into table Verwandschaft Fields terminated by '||';

Dieses kann man dann mit diesem Befehl laufen lassen.

C:\mysql_handbuch>c:\mysql\bin\mysql.exe <initio.sql

Allerdings werden hierzu noch die drei Textdateien Frauen.txt, Maenner.txt, Kinder.txt und Verwandschaft.txt benötigt, welche man hier downloaden kann.

Theoretisch, kann man zwei Tabellen so miteinander verbinden, dass jede Zeile der einen Tabelle mit allen Zeilen der anderen Tabelle kombiniert wird. Praktisch ist dies jedoch ohne Relevanz. In diesem Falle würde es uns die Anzahl der möglichen Paare zeigen.

mysql> select Frauen.Vorname,Frauen.Name,Maenner.Vorname,Maenner.Name
-> from Frauen,Maenner limit 10;

Vorname Name Vorname Name
Su Smith Mario Cantinflas
Maria Schulze Mario Cantinflas
Erika Dinkelman Mario Cantinflas
Petra Henkel Mario Cantinflas
Inge Hackel Mario Cantinflas
Bettina Schurwei Mario Cantinflas
Su Smith Peter Arrowsmith
Maria Schulze Peter Arrowsmith
Erika Dinkelman Peter Arrowsmith
Petra Henkel Peter Arrowsmith

10 rows in set (0.00 sec)
mysql>

Wie deutlich zu erkennen, ist das Ergebnis sinnlos. Vorname und Nachname müssen hier mit der Tabelle angegeben werden, da mysql sonst irritiert ist, da es sowohl in der Tabelle Maenner als auch in der Tabelle Frauen die Spalten Vorname, Name gibt. Sinnvoll lassen sich Tabellen nur mit einer where Bedingung verknüpfen. Um Tabellen miteinander verknüpfen zu können, muss jede Tabelle über einen Primärschlüssel verfügen. Dieser Primärschlüssel muss nicht expressis verbis deklariert werden, allerdings sind Konflikte vorprogrammiert, wenn dies nicht geschieht. Die Deklaration der Spalte als Primärschlüssel, die die einzelnen Zeilen genau beschreibt, hat den Vorteil, dass es keine zwei Zeilen mit dem gleichen Primärschlüssel geben kann, weil mysql streikt, wenn man dies versucht. Als Alternative hätte sich noch unique angeboten. Der Unterschied zwischen primary key und unique ist der, dass bei einem primary key alle Einträge in der Spalte NOT NULL sein müssen.

mysql> create table test10(
-> Name char(60),
-> Vorname char(70),
-> unique(Name));
Query OK, 0 rows affected (0.00 sec)

mysql> create table test11(
-> Name char(60),
-> Vorname char(70),
-> primary key(Name));
ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a
key, use UNIQUE instead
mysql>

Wie deutlich zu erkennen, kann man bei unique auf die Angabe von NOT NULL verzichten, bei primary key nicht. Wer den Index einfach mit index definiert, der muss selber dafür sorgen, dass der entsprechende Eintrag eindeutig ist. Davon abgesehen sollte aber irgendein Index, aus den genannten Gründen am besten primary key aber auch deshalb beim Primarschlüssel angegeben werden, weil sonst die Suche nach dem entsprechenden Datensatz sequentiell verläuft. Das heisst, wenn zwei Millionen Datensätze in der Datenbank sind und der 999999zigste gesucht wird, braucht es eben 999999 Schritte um ihn zu finden. Bei der binary search werden die Daten zusätzlich in einer Art Baumstruktur abgelegt, was dramatisch schneller ist.

equi join

Um herauszufinden, wieviele Kinder jede Frau hat, muss man einen join über die Tabelle Frauen und die Tabelle Verwandtschaft machen.

mysql> select count(IdFrau),Vorname,Name from Verwandschaft,Frauen
-> where Frauen.KennungF=Verwandschaft.IdFrau group by
-> IdFrau;

count(IdFrau) Vorname Name
2 Su Smith
1 Erika Dinkelman
2 Inge Hackel

3 rows in set (0.05 sec)
mysql>

Wie deutlich zu erkennen, ist das Ergebnis richtig. Su Smith hat die Kennung 1. Damit ist ersichtlich, dass Thomas Cantinflas (5) und Andrea Brinkmann (3) ihre Kinder sind. Will man die Namen der Kinder mitgeliefert bekommen, muss man einen join über drei Tabellen machen.

mysql> select Frauen.Vorname,Frauen.Name,Kinder.Vorname,Kinder.Name from
-> Frauen,Kinder,Verwandschaft where Frauen.KennungF=Verwandschaft.IdFrau
-> and Kinder.KennungK=Verwandschaft.IdKind;

Vorname Name Vorname Name
Su Smith Thomas Cantinflas
Inge Hackel Erich Arrowsmith
Su Smith Andrea Brinkmann
Inge Hackel Maria Dinkelmann
Erika Dinkelman Petra Hackel

5 rows in set (0.17 sec)
mysql>

left join

Bei einem normalen, equi join, werden nur die Zeilen in den verschiedenen Tabellen verknüpft, die über einen Primär und Fremdschlüssel einander zugeordnet werden können. Das ist meistens sinnvoll, manchmal aber auch nicht. Man kann sich zum Beispiel eine Ausgabe wünschen, die alle Frauen mit Kinder aufblendet, zusätzlich aber noch all die Frauen, die keine Kinder haben. Man vergleiche dieses Programm mit dem oberen. Aus tiptechnischen Gründen, lösen wir es über einen batchfile aus.
Dies ist das Programm

use testlauf;

select
Frauen.Vorname as 'Vorname',Frauen.Name as 'Name', Verwandschaft.IdKind as 'Kennung des Kindes'
from
Frauen
left join Verwandschaft on (Frauen.KennungF=Verwandschaft.IdFrau);

So lösen wir es aus

C:\>c:\mysql\bin\mysql -t <c:\mysql_handbuch\testlauf2.sql >c:\ergebnis2.txt

Vorname Name Kennung des Kindes
Su Smith 5
Su Smith 3
Maria Schulze NULL
Erika Dinkelman 4
Petra Henkel NULL
Inge Hackel 1
Inge Hackel 2
Bettina Schurwei NULL


Das Ergebnis der Abfrage wird in die Datei ergebnis2.txt gedruckt. Wir sehen, dass der left join von der Tabelle die vor inner join steht, alles zeigt. Wir sehen also, welche Frau keine Kinder hat. Wir hätten uns über diese Methode auch von vorneherein nur die Frauen anzeigen lassen können, die keine Kinder haben.

use testlauf;

select
Frauen.Vorname as 'Vorname',Frauen.Name as 'Name', Verwandschaft.IdKind as 'Kennung des Kindes'
from
Frauen
left join Verwandschaft on (Frauen.KennungF=Verwandschaft.IdFrau) where Verwandschaft.IdKind is NULL;

Vorname Name Kennung des Kindes
Maria Schulze NULL
Petra Henkel NULL
Bettina Schurwei NULL
left join über mehrere Tabellen

Etwas schwieriger ist ein left join über mehr als zwei Tabellen. Nehmen wir an, wir wollen alle Frauen aufgelistet bekommen und, so weit vorhanden, den Namen des Kindes. Wir müssen dann einen left join über drei Tabellen machen: Frauen, Verwandschaft und Kinder. Das sieht dann so aus.

use testlauf;

select
Frauen.Vorname as 'Vorname',Frauen.Name as 'Name', Verwandschaft.IdKind as 'Kennung des Kindes',Kinder.Vorname as 'Vorname des Kindes',Kinder.Name as 'Kinder.Name'
from
Frauen
left join Verwandschaft on (Frauen.KennungF=Verwandschaft.IdFrau)
left join Kinder on (Kinder.KennungK=Verwandschaft.IdKind)

Vorname Name Kennung des
Kindes
Vorname des
Kindes
Kinder.Name
Su Smith 5 Thomas Cantinflas
Su Smith 3 Andrea Brinkmann
Maria Schulze NULL NULL NULL
Erika Dinkelman 4 Petra Hackel
Petra Henkel NULL NULL NULL
Inge Hackel 1 Erich Arrowsmith
Inge Hackel 2 Maria Dinkelmann
Bettina Schurwei NULL NULL NULL

Dass das überhaupt funktionniert verblüfft. Tatsächlich besteht überhaupt keine Verbindung zwischen der Tabelle Frauen und der Tabelle Kinder. Offensichtlich ist mysql in der Lage, selber die Schlüssel zu finden, mit denen man die Tabellenverbinden kann. Einleuchtender wäre diese Schreibweise.

use testlauf;

select
Frauen.Vorname as 'Vorname',Frauen.Name as 'Name', Verwandschaft.IdKind as 'Kennung des Kindes',Kinder.Vorname as 'Vorname des Kindes',Kinder.Name as 'Kinder.Name'
from
Frauen
left join Verwandschaft on (Frauen.KennungF=Verwandschaft.IdFrau)
left join Kinder on (Kinder.KennungK=Verwandschaft.IdKind and Verwandschaft.IdFrau=Frauen.KennungF);

Was zum gleichen Ergebnis führt, aber nachvollziehbarer ist. Zuerst wird die Tabelle links von left join komplett aufgebaut, dann werden die Tabellen Frauen und Verwandschaft miteinander verknüpft und über Frauen.KennungF=Verwandschaft.IdFrau an die entsprechenden Zeilen der Tabelle Frauen angefügt. Anschliessend werden die Tabellen Kinder und Verwandschaft miteinander verknüpft und über Verwandschaft.IdFrau und Frauen.KennungF an die Tabelle Frauen angefügt. Auf diese Art und Weise ließe sich auch ermitteln, welches Kind einen Vater hat.

use testlauf;

select
Frauen.Vorname as 'Vorname',Frauen.Name as 'Name',Kinder.Name as 'Name des Kindes', Kinder.Vorname as 'Vorname des Kindes',Maenner.Name as 'Name Mann'
from
Frauen
left join Verwandschaft on (Frauen.KennungF=Verwandschaft.IdFrau)
left join Kinder on (Kinder.KennungK=Verwandschaft.IdKind)
left join Maenner on (Maenner.KennungM=Verwandschaft.IdMann);

Zur Erinnerung: Dieses Programm kann man mit diesem Befehl von der Kommando Ebene aus auslösen.

C:\>c:\mysql\bin\mysql -t <c:\mysql_handbuch\testlauf2.sql >c:\ergebnis2.txt

Vorname Name Name des Kindes Vorname des
Kindes
Name Mann
Su Smith Cantinflas Thomas Schmidt
Su Smith Brinkmann Andrea Schmidt
Maria Schulze NULL NULL NULL
Erika Dinkelman Hackel Petra NULL
Petra Henkel NULL NULL NULL
Inge Hackel Arrowsmith Erich Arrowsmith
Inge Hackel Dinkelmann Maria Dinkelmann
Bettina Schurwei NULL NULL NULL

Wir erhalten also vier Kinder, die tatsächlich einen Vater haben. Eigentlich haben wir aber, siehe Tabellen zu Beginn des Kapitels, 6 Kinder. Der Unterschied ist durch zwei Tatsachen bedingt. Erstens, hat das 6. Kind, der Hans Erhard, keine Verknüpfung mit der Tabelle Verwandschaft. Zweitens verweist IdMann=6 in der Tabelle Verwandschaft auf keine Zeile in der Tabelle Maenner. Auch hier können wir uns wieder über die Tatsache wundern, dass es überhaupt funktionniert. Nachvollziehbarer wäre diese Schreibweise.

use testlauf;

select
Frauen.Vorname as 'Vorname',Frauen.Name as 'Name',Kinder.Name as 'Name des Kindes', Kinder.Vorname as 'Vorname des Kindes',Maenner.Name as 'Name Mann' from
Frauen
left join Verwandschaft on (Frauen.KennungF=Verwandschaft.IdFrau)
left join Kinder on (Kinder.KennungK=Verwandschaft.IdKind and Verwandschaft.IdFrau=Frauen.KennungF)
left join Maenner on (Maenner.KennungM=Verwandschaft.IdMann and Verwandschaft.IdMann=Maenner.KennungM);

Was aber, aus den oben genannten Gründen, zum gleichen Ergebnis führt, wenn es auch nachvollziehbarer ist. Mit dieser Methode könnte man auch direkt die Kinder finden, die keinen Vater haben.

use testlauf;

select
Frauen.Vorname as 'Vorname',Frauen.Name as 'Name',Kinder.Name as 'Name des Kindes', Kinder.Vorname as 'Vorname des Kindes',Maenner.Name as 'Name Mann'
from
Frauen
left join Verwandschaft on (Frauen.KennungF=Verwandschaft.IdFrau)
left join Kinder on (Kinder.KennungK=Verwandschaft.IdKind and Verwandschaft.IdFrau=Frauen.KennungF)
left join Maenner on (Maenner.KennungM=Verwandschaft.IdMann and Verwandschaft.IdMann=Maenner.KennungM)
where Maenner.Name is NULL and Kinder.Name is NOT NULL;

Vorname Name Name des Kindes Vorname des
Kindes
Name Mann
Erika Dinkelman Hackel Petra NULL

Was korrekt ist. Denn die Tochter von Erika Dinkelmann hat zwar bei IdMann in der Tabelle Verwandschaft einen Eintrag, dieser zeigt aber auf keine Zeile in der Tabelle Maenner. Nun haben wir ja noch ein Kind, dass keinen Eintrag in der Tabelle Verwandschaft hat. Es stellt sich also die Frage, wie kann man alle Kinder auflisten und die dazugehörigen Väter und Mütter. Das geht so.

use testlauf;

select
Kinder.Vorname as 'Vorname des Kindes',Kinder.Name as 'Name des Kindes',Frauen.Name as 'Name der Mutter',
Frauen.Vorname as 'Vorname des Mutter',Maenner.Vorname as 'Vorname des Vaters',Maenner.Name as 'Vorname des Vaters'
from
Kinder
left join Verwandschaft on (Kinder.KennungK=Verwandschaft.IdKind)
left join Frauen on (Kinder.KennungK=Verwandschaft.IdKind and Verwandschaft.IdFrau=Frauen.KennungF)
left join Maenner on (Maenner.KennungM=Verwandschaft.IdMann and Verwandschaft.IdMann=Maenner.KennungM);

Als Ergebnis erhalten wir das.

Vorname des Kindes Name des Kindes Name der Mutter Vorname des Mutter Vorname des Vaters Vorname des Vaters
Erich Arrowsmith Hackel Inge Peter Arrowsmith
Maria Dinkelmann Hackel Inge Andreas Brinckmann
Andrea Brinkmann Smith Su Kai Schmidt
Petra Hackel Dinkelman Erika NULL NULL
Thomas Cantinflas Smith Su Kai Schmidt
Hans Erhard NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL

Was die Verhätnisse richtig wiederspiegelt. Der Hans Erhard ist in der Tabelle Verwandschaft nicht gelistet und der Vater von Petra Hackel, die Nummer 6, ist in der Tabelle Maenner nicht vorhanden. joins lassen sich also über beliebig viele Tabellen ausführen. Es stellt sich nur die Frage, ob die Datenbank nicht irgendwann zusammenbricht. By the way, sind die Fremd- und Primärschlüssel nicht indiziert (primary key, index, unique) bricht sie sofort zusammen. Unter Umständen ist es aber für die Performance günstiger, man arbeitet mit einem temporary table.

temporary table

Anstatt mehrer Tabellen über einen join zusammenzuführen, könnte man auch einen table im Speicher des Rechners generieren, Zwischenergebnisse da rein schreiben und weitere Abfragen auf diesen Table durchführen. Das ist unter Umständen schneller als ein join. Ein einfaches Beispiel für einen temporary table sieht so aus.

use testlauf;

create temporary table MaennerT (
KennungM int(3) NOT NULL,
Vorname char(30),
Name char(30),
Telefon char(50),
email char(20),
PRIMARY KEY(KennungM));
insert into MaennerT(KennungM,Vorname,Name,Telefon,email) values (14,'Heiner','Müller','0567-34544','heiner@berlin.de');
select * from MaennerT;

KennungM Vorname Name Telefon email
14 Heiner Müller 0567-34544 heiner@berlin.de

Der temporary table MaennerT wird sofort nach Beendigung des Programms wieder gelöscht. Das heisst er liegt nur virtuell vor. Wie bereits oben mehrfach beschrieben, ist das Programm so aufzurufen.

C:\>c:\mysql\bin\mysql -t <c:\mysql_handbuch\testlauf2.sql >c:\ergebnis2.txt

Wir können auch auf einen Schlag die komplette Datenbank Maenner in den contemporary table spiegeln.

use testlauf;

create temporary table MaennerT (
KennungM int(3) NOT NULL,
Vorname char(30),
Name char(30),
Telefon char(50),
email char(20),
PRIMARY KEY(KennungM));

insert into MaennerT (KennungM,Vorname,Name,Telefon,email) select KennungM,Vorname,Name,Telefon,email from Maenner;

select * from MaennerT;

KennungM Vorname Name Telefon email
1 Mario Cantinflas 040-345444 mario_cantinflas@aol
2 Peter Arrowsmith 030-345444 arrosmith@southbell.
3 Kai