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

Tabellen verknüpfen

nach oben

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.

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

nach oben

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

nach oben

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

nach oben

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

nach oben

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