Werbung einblenden Werbung ausblenden


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 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

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.

1 Peter Müller Berlin Am Hechtacker 12 mueller@wessnich.de business 30000
2 Erika Kunstig München Geibenstaig 13 kunstig@yahoo.de business 40000
3 Werner Sauerbier Freiburg Mooswaldalle 34 sauerbier@gmx.de business 4000
4 Maria Hopfendahl Berlin Kurfüstenstrasse 45 Hopf@hotmail.com consumer 5000
5 Heiner Müller Bremen Lichtergasse 12 heiner@mueller.com consumer 6000
6 Hans Kohl Düsseldorf Am Markt 45 hansiKohl@freenet.de consumer 70000
7 Werner Glottertal Coburg Am Fels 45 wernerglott@wessnich.de consumer 90000
8 Hans Lützelschwab Freiburg Am Hertweg 7 lützelschwab@freiburg.com business 20000
9 Peter Brueg Freiburg Reischstrasse 34 peter-brueg@freenet.de consumer 5000
10 Erika Lachfeld Freiburg Krozinger Strasse 12 erika_lachfeld@freiburg.net business 4000

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