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:
- Über eine WHERE-Bedingung oder
- über den SQL-Befehl JOIN.
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:
| Name | Produkt | | Deutsche Telekom | T-ISDN | | Deutsche Telekom | T-Net | | Deutsche Telekom | T-Mobile | | VIAG Interkom | Call by Call | | VIAG Interkom | Preselect | | Mobilcom | City Call | | Mobilcom | Call 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.
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:
- Inner Join
Gibt nur die Datensätze zurück, bei denen die Verknüpfungsbedingung übereinstimmt. Das Einführungsbeispiel ist ein solcher Inner Join. Zum Anbieter wurden diejenigen Produkte ausgewählt, die diesem Anbieter zugeordnet sind. Es wurden keine Datensätze von Anbietern ausgegeben, die keine Produkte anbieten, bzw. es sind keine Produkte ausgegeben worden, die keinem Anbieter zugeordnet sind. Beim Inner Join handelt es sich um die typische Form, die Sie beim Verknüpfen von Tabellen benötigen. - Outer Join
Gibt dieselben Datensätze wie ein Inner Join zurück. Allerdings werden hier alle Datensätze einer Tabelle ausgegeben, auch wenn keine korrespondierenden Datensätze in der jeweils anderen Tabelle vorhanden sind. In diesem Fall wird ein leerer Datensatz verknüpft. - Theta Join
Üblicherweise werden Tabellen über gleiche Datensätze in bestimmten Feldern verknüpft. Die Verknüpfungsbedingung muss aber nicht unbedingt mit dem Gleichheitsoperator gebildet werden. Auch Ungleichheit und Kleiner-/Größer-Vergleiche sind möglich. In diesem Fall spricht man von einem Theta Join.
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
| Name | Produkt | | Deutsche Telekom | T-ISDN | | Deutsche Telekom | T-Net | | Deutsche Telekom | T-Mobile | | VIAG Interkom | Call by Call | | VIAG Interkom | Preselect | | Mobilcom | City Call | | Mobilcom | Call 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:
- Ein Kurs kann von vielen Schülern besucht werden und ein Schüler kann viele Kurse besuchen.
- Ein Hotel kann von vielen Gästen bewohnt werden und ein Gast kann diverse Zimmer buchen.
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.
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:
| Vorname | Name | Bezeichnung | | Tanja | Biedorf | Englisch | | Manfred | Bohnmann | Englisch | | Manfred | Bohnmann | Mathe | | Manfred | Bohnmann | Deutsch | | Caroline | Kannengiesser | Deutsch | | Caroline | Kannengiesser | Mathe | | Bernd | Klein | Deutsch | | Jan | Kuhnert | Englisch | | Jan | Kuhnert | Mathe | | Susanne | Maier | Mathe | | Susanne | Maier | Deutsch | | Susanne | Maier | Englisch |
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
| name | bevdichte | name | bevdichte | | Taiwan | 50 | New York | 100 | | Japan | 60 | New York | 100 | | Frankreich | 80 | New York | 100 |
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
);
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.
| Name | Geschwister | Vater | | Michael Meier | Tanja Meier | Theo Meier | | Sandra Monk | Toni Monk | Manfred Monk | | Tina Schmidt | Susi Schmidt | Hans Schmidt | | Tanja Meier | Michael Meier | Theo Meier | | Susi Schmidt | Tina Schmidt | Hans Schmidt | | Toni Monk | Sandra Monk | Manfred 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.
|
SELFPHP
SELFPHP OHG, info@selfphp.de, Impressum, Kontakt