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		Schmidt		089-3453345		schmidt@berlin.de
	4		Andreas		Brinckmann		0789-345343434		brinckmann2@freiburg
	5		Thomas		Müller		0789-345433		thomas@uni-freiburg.

Dies entspricht dem Orginal. Will man jetzt wissen, wie man alle Frauen gelistet bekommt und zusätzlich, welche, so vorhanden, ein Kind hat und wie, so vorhanden, der Name des Vaters lautet, kann man auch alle Daten der Tabellen Maenner,Kinder und Verwandschaft in einen contempary table spulen und sie dann über einen left join mit der Tabelle Frauen verbinden. Wir vereinen also erstmal die Tabellen Maenner,Kinder,Verwandschaft und übergeben das Ergebnis an einen temporary table. Das sieht dann so aus.

use testlauf;

create temporary table tutti (
KennungM int(3) NOT NULL,
VornameM char(30),
NameM char(30),
KennungK int(3) NOT NULL,
VornameK char(20) ,
NameK char(20),
IdMann int(3) NOT NULL,
IdFrau int(3) NOT NULL,
IdKind int(3) NOT NULL,
index(IdMann),index(IdFrau),index(IdKind),index(KennungM),index(KennungK));

insert into tutti
(KennungM,VornameM,NameM,KennungK,VornameK,NameK,IdMann,IdFrau,IdKind)
select Maenner.KennungM,Maenner.Vorname,Maenner.Name,Kinder.Name,Kinder.Vorname,Kinder.Name,
Verwandschaft.IdMann,Verwandschaft.IdFrau,Verwandschaft.IdKind from
Maenner,Kinder,Verwandschaft where Verwandschaft.IdKind=Kinder.KennungK and Verwandschaft.IdMann=Maenner.KennungM;

select VornameM as 'Vorname des Vaters',NameM as 'Name des Vaters',VornameK as 'Vorname des Kindes',NameK as 'Name des Kindes' ,IdFrau from tutti;

	Vorname des Vaters		Name des Vaters		Vorname des Kindes		Name des Kindes		IdFrau
	Kai		Schmidt		Thomas		Cantinflas		1
	Peter		Arrowsmith		Erich		Arrowsmith		5
	Kai		Schmidt		Andrea		Brinkmann		1
	Andreas		Brinckmann		Maria		Dinkelmann		5

Wir sehen uns also wieder mit dem alten Problem konfrontiert. Das Ergebnis stimmt nur fast. Da der Mann von Erika Dinkelmann, der Nummer 3, nicht existiert, wird der join nicht durchgeführt. Das heisst, die Id von Erika Dinkelmann ist verloren. Weiter geht auch das Kind Heinz Erhard verloren, die Nummer 6, weil dieses ja nicht in der Tabelle Verwandtschaft eingetragen ist. Von diesen Einschränkungen abgesehen, können wir jetzt noch einen left join über die Tabelle Frauen und die temporäre Tabelle tutti machen und wir erhalten ein fast richtiges Ergebnis. Das Skript sieht dann so aus.

use testlauf;

create temporary table tutti (
KennungM int(3) NOT NULL,
VornameM char(30),
NameM char(30),
KennungK int(3) NOT NULL,
VornameK char(20) ,
NameK char(20),
IdMann int(3) NOT NULL,
IdFrau int(3) NOT NULL,
IdKind int(3) NOT NULL,
index(IdMann),index(IdFrau),index(IdKind),index(KennungM),index(KennungK));

insert into tutti
(KennungM,VornameM,NameM,KennungK,VornameK,NameK,IdMann,IdFrau,IdKind)
select Maenner.KennungM,Maenner.Vorname,Maenner.Name,Kinder.Name,Kinder.Vorname,Kinder.Name,
Verwandschaft.IdMann,Verwandschaft.IdFrau,Verwandschaft.IdKind from
Maenner,Kinder,Verwandschaft where Verwandschaft.IdKind=Kinder.KennungK and Verwandschaft.IdMann=Maenner.KennungM;

select Frauen.Vorname as 'Vorname Mutter',Frauen.Name as 'Name Mutter',VornameM as 'Vorname Vater',
NameM as 'Name Vater',VornameK as 'Vorname Kind',NameK as 'Name Kind' from
Frauen left join tutti on (tutti.IdFrau=Frauen.KennungF);

Vorname Mutter

Name Mutter

Vorname Vater

Name Vater

Vorname Kind

Name Kind

Su

Smith

Kai

Schmidt

Thomas

Cantinflas

Su

Smith

Kai

Schmidt

Andrea

Brinkmann

Maria

Schulze

NULL

NULL

NULL

NULL

Erika

Dinkelman

NULL

NULL

NULL

NULL

Petra

Henkel

NULL

NULL

NULL

NULL

Inge

Hackel

Peter

Arrowsmith

Erich

Arrowsmith

Inge

Hackel

Andreas

Brinckmann

Maria

Dinkelmann

Bettina

Schurwei

NULL

NULL

NULL

NULL

In Abhängigkeit von der konkreten Situation ist dieses Verfahren machbar und schneller als der left join über vier Tabellen. In diesem konkreten Fall ergibt sich jedoch ein Fehler, da Erika Dinkelmann ja ein Kind hat, ihre Kennung aber beim ersten equi join in die virtuelle Tabelle aber verloren geht und sie somit nicht mehr erscheint. Wir könnten das flicken, indem wir auch beim Einspeisen der Daten in die virtuelle Tabelle einen left join machen. Das sieht dann so aus.

use testlauf;

create temporary table tutti (
KennungM int(3) NOT NULL,
VornameM char(30),
NameM char(30),
KennungK int(3) NOT NULL,
VornameK char(20) ,
NameK char(20),
IdMann int(3) NOT NULL,
IdFrau int(3) NOT NULL,
IdKind int(3) NOT NULL,
index(IdMann),index(IdFrau),index(IdKind),index(KennungM),index(KennungK));

insert into tutti
(KennungM,VornameM,NameM,KennungK,VornameK,NameK,IdMann,IdFrau,IdKind)
select Maenner.KennungM,Maenner.Vorname,Maenner.Name,Kinder.Name,Kinder.Vorname,Kinder.Name,
Verwandschaft.IdMann,Verwandschaft.IdFrau,Verwandschaft.IdKind from
Verwandschaft left join Maenner on(Verwandschaft.IdMann=Maenner.KennungM)
left join Kinder on (Verwandschaft.IdKind=Kinder.KennungK);

select Frauen.Vorname as 'Vorname Mutter',Frauen.Name as 'Name Mutter',VornameM as 'Vorname Vater',
NameM as 'Name Vater',VornameK as 'Vorname Kind',NameK as 'Name Kind' from
Frauen left join tutti on (tutti.IdFrau=Frauen.KennungF);

Vorname Mutter

Name Mutter

Vorname Vater

Name Vater

Vorname Kind

Name Kind

Su

Smith

Kai

Schmidt

Thomas

Cantinflas

Su

Smith

Kai

Schmidt

Andrea

Brinkmann

Maria

Schulze

NULL

NULL

NULL

NULL

Erika

Dinkelman

NULL

NULL

Petra

Hackel

Petra

Henkel

NULL

NULL

NULL

NULL

Inge

Hackel

Peter

Arrowsmith

Erich

Arrowsmith

Inge

Hackel

Andreas

Brinckmann

Maria

Dinkelmann

Bettina

Schurwei

NULL

NULL

NULL

NULL

Auch dies kann unter Umständen schneller sein, als alle Tabellen auf einen Schlag miteinander zu verbinden. Abgesehen davon, ist es sinnvoll, sich mit temporary tables vertraut zu machen, weil es immer wieder Situationen gibt, die mit temporary tables geschickter beherrscht werden können.

self join

nach oben

Herausfinden, ob die Mutter von Andrea Brinkmann noch ein Kind hat.
Diese Aufgabe ist einfach zu lösen, wenn man sie aufteilt in mehrere Schritte. Schwierig, wenn man es auf einen Schlag machen will. Zuerst eine Lösung in mehreren Schritten. Die sieht so aus.

use testlauf;

select @KennungMutter:=Verwandschaft.IdFrau from Verwandschaft,Kinder where Verwandschaft.IdKind=Kinder.KennungK
and Kinder.Name="Brinkmann" and Kinder.Vorname="Andrea";

select Frauen.Vorname as 'Vorname der Mutter', Frauen.Name as 'Name der Mutter',Kinder.Vorname as 'Vorname des Kindes',Kinder.Name as 'Name des Kindes'
from Verwandschaft,Frauen,Kinder where Verwandschaft.IdKind=Kinder.KennungK and Verwandschaft.IdFrau=Frauen.KennungF and Frauen.KennungF=@KennungMutter;

Das Resultat sieht so aus und ist, wie man anhand der Tabellen zu Beginn des Kapitels nachprüfen kann, richtig.

	@KennungMutter:=Verwandschaft.IdFrau
	1

	Vorname der Mutter		Name der Mutter		Vorname des Kindes		Name des Kindes
	Su		Smith		Thomas		Cantinflas
	Su		Smith		Andrea		Brinkmann

Merkwürdig ist hierbei vielleicht die Zuweisung an die Variable @KennungMutter (@KennungMutter:=Verwandschaft.IdFrau). Mit dieser Variablen können wir uns im zweiten select Statement das zweite Kind rausfischen. Schwieriger wird es, wenn man das Problem auf einen Schlag lösen will. Das sieht dann so aus.

use testlauf;

select Frauen2.KennungF, Frauen2.Vorname as 'Vorname der Mutter', Frauen2.Name as 'Name der Mutter',
Kinder2.Vorname as 'Vorname des Kindes',Kinder2.Name as 'Name des Kindes'

from Kinder as Kinder1,Kinder as Kinder2,Verwandschaft as Verwandschaft1,Verwandschaft as Verwandschaft2,
Frauen as Frauen1, Frauen as Frauen2
where Kinder1.KennungK=Verwandschaft1.IdKind and Verwandschaft1.IdFrau=Frauen1.KennungF and Kinder1.Name="Brinkmann"
and Kinder2.KennungK=Verwandschaft2.IdKind and Verwandschaft2.IdFrau=Frauen1.KennungF and Frauen1.KennungF=Frauen2.KennungF;

	KennungF		Vorname der Mutter		Name der Mutter		Vorname des Kindes		Name des Kindes
	1		Su		Smith		Thomas		Cantinflas
	1		Su		Smith		Andrea		Brinkmann

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

Das Ergebnis steht wieder in ergebnis2.txt.

Was, wie man anhand der zu Beginn des Kapitels stehenden Tabellen ablesen kann, richtig ist. Wie deutlich zu sehen, ist ein self-join über mehrere Tabellen ziemlich kompliziert. Weiter stellt sich die Frage, ob er bei größeren Datenmengen überhaupt durchgeführt werden kann, da die Tabellen, die als Zwischenprodukte generiert werden, riesig sind. Unter Umständen ist es also günstiger, man macht es in zwei Stufen.

Differenz des aktuellen Wertes vom Durchschnitt berechnen
Denkbar sind Probleme, bei denen zuerst ein Wert aus der Gesamttabelle ermittelt werden muss und anschliessend dieser Wert mit Werten in den einzelnen Zeilen verglichen wird. Man sollte dieses Problem nicht verwechseln, mit einem Problem, bei der Wert, gegen den verglichen wird, bereits bekannt ist. Wir brauchen also, das ist ja immer unser Problem, eine Tabelle, bei der sich dieses Problem stellt. Wir verwenden unsere Tabelle aus dem Kapitel Daten aus einem Flatfile in Tabellen importieren. Sie hat, zur Erinnerung, folgendes Aussehen.

mysql> select Name,Ort,Kategorie,Umsatz from testerone;

	Name		Ort		Kategorie		Umsatz
	Peter Müller		Berlin		business		30000
	Erika Kunstig		München		business		40000
	Werner Sauerbier		Freiburg		business		4000
	Maria Hopfendahl		Berlin		consumer		5000
	Heiner Müller		Bremen		consumer		6000
	Hans Kohl		Düsseldorf		consumer		70000
	Werner Glottertal		Coburg		consumer		90000
	Hans Lützelschwab		Freiburg		business		20000
	Peter Brueg		Freiburg		consumer		5000
	Erika Lachfeld		Freiburg		business		4000

10 rows in set (0.06 sec)
mysql>

Angenommen, wir wollen jetzt den durchschnittlichen Umsatz berechnen und anschliessend ermitteln, um wieviel die einzelnen Kunden von diesem Umsatz nach oben oder nach unter abweichen. Natürlich kann man dies wieder in zwei Schritten tun, was ja auch einfacher ist.

mysql> select @durchschnitt:=avg(Umsatz) from testerone;

	@durchschnitt:=avg(Umsatz)
	27400.0000

1 row in set (0.00 sec)

mysql> select Name,Umsatz,@durchschnitt as 'Durchschnitt',
-> @durchschnitt-Umsatz as 'Differenz' from testerone;

10 rows in set (0.00 sec)
mysql>

So weit so einfach. Will man das Problem auf einen Schlag lösen, muss ein self-join durchgeführt werden. Wir schreiben wieder einen batch file.

use testlauf;

select testerone1.Name,testerone1.Umsatz,avg(testerone2.Umsatz) as 'Durchschnitt',
avg(testerone2.Umsatz)-testerone1.Umsatz as 'Differenz'
from testerone as testerone1,testerone as testerone2 group by(testerone1.Name);

Den wir so auslösen.

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

	Name		Umsatz		Durchschnitt		Differenz
	Erika Kunstig		40000		27400.0000		-12600.0000
	Erika Lachfeld		4000		27400.0000		23400 .0000
	Hans Kohl		70000		27400.0000		-42600.0000
	Hans Lützelschwab		20000		27400.0000		7400.0000
	Heiner Müller		6000		27400.0000		21400.0000
	Maria Hopfendahl		5000		27400.0000		22400.0000
	Peter Brueg		5000		27400.0000		22400.0000
	Peter Müller		30000		27400.0000		-2600.0000
	Werner Glottertal		90000		27400.0000		-62600.0000
	Werner Sauerbier		4000		27400.0000		23400.0000

Wir erhalten dann eine Datei ergebnis2.txt, die das gleiche Ergebnis wie oben zeigt, wenn auch in anderer Reihenfolge.

vorhergehendes Kapitel	nach oben	nächstes Kapitel