SELFPHP

SELFPHP-Druckversion
Original Adresse dieser Seite:
http://www.selfphp.de/praxisbuch/praxisbuchseite.php?site=276&group=48
© 2001-2017 E-Mail SELFPHP OHG, info@selfphp.de
© 2005-2017 E-Mail PHP5 Praxisbuch - Matthias Kannengiesser, m.kannengiesser@selfphp.de


Verknüpfte Tabellen (WHERE/JOIN)


Ein wichtiges Thema bei der Selektion von Daten aus der Datenbank ist die Verknüpfung von verschiedenen Tabellen für die Ausgabe. Das relationale Datenmodell bzw. die Anlage der Tabellen mit relationalen Verknüpfungen gewährleistet Ihnen eine effektive Datenhaltung. Wie an verschiedenen Stellen bereits erläutert wurde, sind diese relationalen Verknüpfungen zwischen verschiedenen Tabellen ein ständiger Begleiter bei der Datenbankarbeit. Auch bei SELECT-Abfragen über mehr als eine Tabelle ist es notwendig, diese relationalen Verknüpfungen handhaben zu können, da die Verknüpfung in den SELECT-Statements von Ihnen formuliert werden muss. Diese Verknüpfungen werden als Joins bezeichnet.

Sie können Tabellen auf zwei verschiedene Arten miteinander verknüpfen:

Einführungsbeispiel für Verknüpfungen

Um Verknüpfungen zu demonstrieren, fangen wir am besten mit einem einfachen Beispiel an. In diesem Beispiel liegen zwei Tabellen vor. Die Tabelle, die alle Produkte enthält, ist referenziell mit der Tabelle aller Anbieter verknüpft. Es handelt sich dabei also um eine 1:n-Beziehung.

Für dieses Beispiel soll nunmehr eine Tabelle erzeugt werden, die alle Anbieter und deren Produkte auflistet. In der Praxis wäre die Tabelle natürlich noch länger und würde beispielsweise Preisinformationen etc. enthalten. An dieser Stelle ist ein vereinfachtes Beispiel jedoch ausreichend, um Ihnen beim Thema Joins schnellere Erfolgserlebnisse zu ermöglichen.

Die zu erzeugende Tabelle hat dann folgendes Aussehen:

NameProdukt
Deutsche TelekomT-ISDN
Deutsche TelekomT-Net
Deutsche TelekomT-Mobile
VIAG InterkomCall by Call
VIAG InterkomPreselect
MobilcomCity Call
MobilcomCall by Call


Um das richtige Ergebnis zu erreichen, müssen Sie die Verknüpfungen in der Abfrage nachbilden und die sachliche Zuordnung »Produkt gehört zu Anbieter« darstellen.

Eine Möglichkeit, diese Abfrage zu formulieren, lautet:
SELECT anbieter.name, telprodukte.produkt FROM anbieter, telprodukte WHERE anbieter.id = telprodukte.ida;

Die Verknüpfung der Tabelle ist also in der WHERE-Bedingung dargestellt. Wenn Sie diese Abfrage ohne die WHERE-Bedingung formulieren, würde Ihnen MySQL eine Liste aller möglichen Kombinationen aus Anbietern und Produkten liefern.

Hinweis: Gelegentlich kann es vorkommen, dass die Schlüsselfelder der beteiligten Tabellen denselben Namen tragen. In diesem Fall werden Sie die Bedingung unter Angabe des jeweiligen Tabellennamens nach dem Muster Tabelle.Spalte präzisieren müssen.


Bezeichnung von Spalten

Die Verwendung des Sterns * als Ersatzeichen liefert sämtliche Spalten der verknüpften Tabelle. Das ist jedoch nur in seltenen Fällen erforderlich. Üblicherweise benötigen Sie nur eine bestimmte Anzahl von Spalten. In der SELECT-Anweisung sind dann die Spalten aufzulisten, die in der Ergebnistabelle erscheinen sollen. Grundsätzlich können Sie die Spalten wie gewohnt mit ihrem Namen auflisten, auch wenn sie unterschiedlichen Tabellen angehören:
SELECT kundenNr, Firma, Ort
FROM kunden, anbieter
WHERE kundenNr = KdNr

Dabei ist jedoch auf gleichnamige Spalten zu achten.

Um diese unterscheiden zu können, müssen Sie die Feldnamen gegebenenfalls mit dem vorangestellten Tabellennamen kennzeichnen:
SELECT kunden.kundenNr, kunden.Firma
anbieter.nr, anbieter.Firma, anbieter.Ort...

Eine solche Schreibweise erleichtert zudem die Lesbarkeit des Codes. Wollen Sie gleichnamige Felder ausgeben, ist hierfür ein Aliasname zu bestimmen, damit sich die Felder in der Ergebnistabelle unterscheiden lassen:
SELECT kunden.Firma AS Kfirma ...

Im PHP-Skript greifen Sie über diese Namen auf die betreffenden Spalten zu.


Verknüpfung mehrerer Tabellen über WHERE

Sind mehr als zwei Tabellen zu verknüpfen, wird die Abfrage um die entsprechenden Tabellen und WHERE-Bedingungen erweitert.

Die Syntax lautet dann wie folgt:
SELECT <Feldliste> FROM Tabelle_1, Tabelle_2, ..., Tabelle_n WHERE Tabelle_i.Spaltenname Tabelle_j.Spaltenname AND Tabelle_m.Spaltenname;
Wobei i,j,m für den jeweiligen Tabellennamen steht und nach FROM aufgelistet sein muss.{PSP}Relationsalgebra

Die Verknüpfung von Tabellen unterliegt grundsätzlich der Relationsalgebra. Dahinter verbirgt sich nichts anderes als das Erzeugen neuer Relationen auf der Basis vorhandener Relationen. Joins sind dabei die Verbundmenge aus zwei oder mehr Relationen. Je nach Formulierung der Verknüpfung wird das Ergebnis ausgegeben.

Über den Typ der Verknüpfungsart werden verschiedene Joins unterschieden:


Left und Right Join

Hieraus resultieren dann die Begriffe Left und Right Join, je nachdem, von welcher der beiden Tabellen alle Datensätze ausgegeben werden. Bezogen auf unser Beispiel ist ein Outer Join eine Abfrage, bei der auch dann alle Anbieter ausgegeben werden, wenn ihnen keine Produkte zugeordnet sind.

Um Tabellen miteinander verknüpfen zu können, müssen die Felder, über die die Tabellen verknüpft werden, über einen kompatiblen Datentyp verfügen. Im Einführungsbeispiel wurden die Tabellen über die ID verknüpft, die jeweils als INTEGER definiert sind.


Verknüpfung über JOIN-Syntax

Beim Einführungsbeispiel handelte es sich um die alte SQL-Methode zur Realisierung von Verknüpfungen mit WHERE.
SELECT anbieter.name, telprodukte.produkt FROM anbieter, telprodukte WHERE anbieter.id = telprodukte.ida;

Die Verknüpfung innerhalb von Abfragen zwischen Tabellen kann jedoch, außer über die oben gezeigte WHERE-Bedingung, auch ANSI-SQL-92-konform über das Schlüsselwort JOIN und die Angabe der Verknüpfungsbedingung deklariert werden. Mit einem LEFT JOIN sieht die oben genannte Abfrage wie folgt aus:
SELECT anbieter.name, telprodukte.produkt FROM anbieter LEFT JOIN telprodukte ON anbieter.id = telprodukte.ida;

Ausgabe
NameProdukt
Deutsche TelekomT-ISDN
Deutsche TelekomT-Net
Deutsche TelekomT-Mobile
VIAG InterkomCall by Call
VIAG InterkomPreselect
MobilcomCity Call
MobilcomCall by Call

Die allgemeine Syntax hinter dem FROM des SELECT-Befehls lautet:
<Tabellenrefenz>, JOIN [ON <Verknüpfungsbedingung>] WHERE [<Suchbedingung>]

Verknüpfungsabfragen können beliebig komplex werden. Die Definition umfangreicher JOIN-Abfragen kann durchaus einige Zeit in Anspruch nehmen.

Die JOIN-Syntax soll nochmals an einem weiteren Beispiel gezeigt werden, einem Belegungsplan für die Kursbelegung in Hochschulen oder die Belegung von Zimmern in einem Hotel. Dies sind in der Regel n:m-Beziehungen. Das heißt für die Beispiele:



Bild 7.5: Kursbelegung, Schüler und Kurse unter Kontrolle in MySQLCC

Tipp: Sie sollten eine Testdatenbank mit dem Namen testbank für die folgenden Beispiele anlegen.
{PSP}Die Tabellendefinitionen hierfür sehen wie folgt aus:

CREATE TABLE kurse (
  ID int(11) NOT NULL auto_increment,
  Bezeichnung varchar(50) default NULL,
  PRIMARY KEY (ID)
);
Daten
INSERT INTO kurse VALUES (1, 'Deutsch');
INSERT INTO kurse VALUES (2, 'Mathe');
INSERT INTO kurse VALUES (3, 'Englisch');
INSERT INTO kurse VALUES (4, 'Latein');
INSERT INTO kurse VALUES (5, 'Informatik');
INSERT INTO kurse VALUES (6, 'Biologie');
INSERT INTO kurse VALUES (7, 'Physik');

und

CREATE TABLE studenten (
  ID int(11) NOT NULL auto_increment,
  Vorname varchar(50) default NULL,
  Name varchar(50) default NULL,
  PRIMARY KEY (ID)
);
Daten
INSERT INTO studenten VALUES (1, 'Bernd', 'Klein');
INSERT INTO studenten VALUES (2, 'Caroline', 'Kannengiesser');
INSERT INTO studenten VALUES (3, 'Manfred', 'Bohnmann');
INSERT INTO studenten VALUES (4, 'Susanne', 'Maier');
INSERT INTO studenten VALUES (5, 'Jan', 'Kuhnert');
INSERT INTO studenten VALUES (6, 'Tanja', 'Biedorf');

Dies sind die beiden Tabellen für die Kurse und die Studenten. Aufgrund der n:m-Beziehung wird im relationalen Datenmodell eine zusätzliche Tabelle Kursbelegung benötigt, die die Zuordnung von Studenten und Kursen beinhaltet. Die Tabellendefinition, einschließlich der Fremdschlüssel, sieht so aus:

CREATE TABLE Kursbelegung (
  Kurse_ID INT,
  Studenten_ID INT,
  FOREIGN KEY (Kurse_id) REFERENCES Kurse(ID),
  FOREIGN KEY (Studenten_id) REFERENCES Studenten(ID)
);
Daten
INSERT INTO kursbelegung VALUES (1,1);
INSERT INTO kursbelegung VALUES (1,2);
INSERT INTO kursbelegung VALUES (1,3);
INSERT INTO kursbelegung VALUES (1,4);
INSERT INTO kursbelegung VALUES (2,2);
INSERT INTO kursbelegung VALUES (2,3);
INSERT INTO kursbelegung VALUES (2,4);
INSERT INTO kursbelegung VALUES (2,5);
INSERT INTO kursbelegung VALUES (3,3);
INSERT INTO kursbelegung VALUES (3,4);
INSERT INTO kursbelegung VALUES (3,5);
INSERT INTO kursbelegung VALUES (3,6);

Um jetzt eine Liste zu erhalten, die alle Studenten mit ihren belegten Kursen auflistet, sind alle Tabellen miteinander zu verknüpfen:

SELECT Studenten.Vorname, Studenten.Name, kurse.bezeichnung
FROM kursbelegung
INNER JOIN kurse ON kurse.ID=kursbelegung.Kurse_ID
INNER JOIN Studenten ON Studenten.ID=kursbelegung.Studenten_ID
ORDER BY Studenten.Name;

In diesem Fall werden also zwei Verknüpfungen in einer Abfrage realisiert, nämlich die Verknüpfung zwischen den Tabellen kursbelegung und Studenten sowie zwischen den Tabellen kursbelegung und Kurse. Das Ergebnis dieser Abfrage sieht wie folgt aus:

VornameNameBezeichnung
TanjaBiedorfEnglisch
ManfredBohnmannEnglisch
ManfredBohnmannMathe
ManfredBohnmannDeutsch
CarolineKannengiesserDeutsch
CarolineKannengiesserMathe
BerndKleinDeutsch
JanKuhnertEnglisch
JanKuhnertMathe
SusanneMaierMathe
SusanneMaierDeutsch
SusanneMaierEnglisch
{PSP}Wer macht was an der Uni?

Theta Join – Joins auf der Basis von Vergleichen

Inner Joins können auch mit Vergleichen durchgeführt werden, die nicht das Gleichheitszeichen beinhalten. Es kann beispielsweise eine Verknüpfung zwischen Tabellen erzeugt werden, die einen Vergleich benötigen.

Gegeben sind beispielsweise zwei Tabellen, die die Bevölkerungsdichte von Ländern und Städten enthalten.

Die Tabellen haben folgendes Aussehen:

CREATE TABLE Stadt (
  id int auto_increment PRIMARY KEY,
  name varchar(50) ,
  bevdichte float
);



Bild 7.6: Tabellen der Länder und Städte – samt Vergleich


Daten

INSERT INTO 'stadt' VALUES (1, 'New York', 100);
INSERT INTO 'stadt' VALUES (2, 'Berlin', 75);

und

CREATE TABLE Laender (
  id int auto_increment PRIMARY KEY,
  name varchar(50),
  bevdichte float
);


Daten

INSERT INTO laender VALUES (1, 'Taiwan', 50);
INSERT INTO laender VALUES (2, 'Japan', 60);
INSERT INTO laender VALUES (3, 'USA', 120);
INSERT INTO laender VALUES (4, 'China', 140);
INSERT INTO laender VALUES (5, 'Frankreich', 80);

Ermittelt werden sollen jetzt alle Länder, die eine geringere Bevölkerungsdichte als die Stadt New York aufweisen. Die Abfrage hierfür lautet:
SELECT l.name,l.bevdichte, st.name, st.bevdichte FROM laender as l INNER JOIN Stadt as st ON st.bevdichte>l.bevdichte AND st.name='New York';

Ergebnis
namebevdichtenamebevdichte
Taiwan50New York100
Japan60New York100
Frankreich80New York100


Und noch ein Fallbeispiel. Gegeben sind zwei Tabellen, die die Fläche von Ländern und Bundesländern enthalten. Die Tabellen haben folgendes Aussehen:

CREATE TABLE bundeslaender (
  id int auto_increment PRIMARY KEY,
  name varchar(50) ,
  flaeche float
);

und

CREATE TABLE laender (
  id int auto_increment PRIMARY KEY,
  name varchar(50),
  flaeche float
);
{PSP}Ermittelt werden sollen jetzt alle Länder, die kleiner sind als das Bundesland Bayern. Die Abfrage hierfür lautet:
SELECT l.name,l.flaeche, bl.name,bl.flaeche FROM laender as l INNER JOIN bundeslaender as bl ON bl.flaeche>l.flaeche AND bl.name='Bayern';


Self Join

Verbindungen müssen nicht nur zwischen verschiedenen Tabellen bestehen. Tabellen können auch als SELF JOIN mit sich selbst verbunden werden. Ein Beispiel wäre ein Stammbaum, der Personen und deren Väter enthält.

Um jetzt herauszufinden, welche Personen Geschwister sind, kann ein Self Join verwendet werden. Das folgende Beispiel beschreibt dies:

CREATE TABLE stammbaum (
  name varchar(50),
  Vater varchar(50)
);



Bild 7.7: Den Stammbaum genau durchleuchten – wer gehört zu wem?


Daten

INSERT INTO stammbaum VALUES ('Tanja Meier', 'Theo Meier');
INSERT INTO stammbaum VALUES ('Toni Monk', 'Manfred Monk');
INSERT INTO stammbaum VALUES ('Susi Schmidt', 'Hans Schmidt');
INSERT INTO stammbaum VALUES ('Michael Meier', 'Theo Meier');
INSERT INTO stammbaum VALUES ('Tina Schmidt', 'Hans Schmidt');
INSERT INTO stammbaum VALUES ('Sandra Monk', 'Manfred Monk');

Die SQL-Abfrage hierfür lautet:
SELECT s1.name,s2.name AS Geschwister, s1.Vater FROM stammbaum AS s1 INNER JOIN stammbaum AS s2 USING(Vater) WHERE s1.name<>s2.name;

Im Ergebnis werden dann zu jeder Person die Geschwister ermittelt.

NameGeschwisterVater
Michael MeierTanja MeierTheo Meier
Sandra MonkToni MonkManfred Monk
Tina SchmidtSusi SchmidtHans Schmidt
Tanja MeierMichael MeierTheo Meier
Susi SchmidtTina SchmidtHans Schmidt
Toni MonkSandra MonkManfred Monk


Hinweis: Wie Sie sehen, kann auch bei der Verwendung von JOIN- und ON-Klauseln noch eine zusätzliche WHERE-Klausel enthalten sein. Die Bedingung kann sich dabei auf die linke, die rechte oder beide Tabellen beziehen. Es sind somit auch zusammengesetzte Bedingungen möglich.


Outer Join

Beim Outer Join werden sämtliche Datensätze einer der beteiligten Tabellen angezeigt. Ob es sich dabei um die linke oder rechte Tabelle handelt, bestimmen Sie mit den Schlüsselwörtern LEFT und RIGHT. Das folgende Beispiel zeigt sämtliche Datensätze der linken Tabelle an, in diesem Falls der Tabelle kunden:
SELECT * FROM kunden
LEFT OUTER JOIN anbieter ON kundenNr = KdNr

Wenn für einen Datensatz der linken Tabelle kunden keine Datensätze in der rechten Tabelle anbieter enthalten sind, werden in den betreffenden Spalten NULL-Werte ausgegeben. In der Regel können Sie auf das Schlüsselwort OUTER sogar verzichten.
SELECT * FROM kunden
LEFT JOIN anbieter ON kundenNr = KdNr

Damit ist dennoch ein Outer Join gemeint. Eine LEFT-JOIN-Verknüpfung werden Sie recht häufig nutzen können. Sie entspricht der Tabellenbeziehung 1:n, welche Sie bereits kennengelernt haben. Wesentlich seltener dürfte ein RIGHT-JOIN sein. Dies würde bedeuten, dass in der rechten Tabelle Datensätze enthalten sind, denen in der linken keine Datensätze entsprechen. Bei der Struktur der Beispieltabelle kann dies durchaus vorkommen. Zwar wäre ein Anbieter ohne Kunden arm dran, aber das soll es bekanntermaßen ja auch geben.