Home - SQL Befehle - Datenmanipulation - Daten abfragen www.sqldocu.com
zurück vor



Abfragen über mehrere Tabellen (Joins)


Beschreibung
top
Abfragen über mehrer Tabellen werden JOINS genannt. Man spricht auch davon, Tabellen zu verknüpfen. Einige JOINS können mit Hilfe des Befehls SELECT gebildet werden. Viele RDBMS bieten neben dieser Möglichkeit den Befehl JOIN.
In der Mengenlehre stellen JOINS die Vereinigung von Mengen dar.
Ein kurzes Beispiel:
TabelleName: captainKlein
Spalte1 hat den Namen commandant
Spalte2 hat den Namen nr_schiff

Spaltenname commandant nr_schiff
Reihe 1 James Kirk 1
Reihe 2 Jean-Luc Picard 2

TabelleName: raumschiffKlein
Spalte1 hat den Namen schiffsname
Spalte2 hat den Namen nr_schiff

Spaltenname nr_schiff schiffsname
Reihe 1 1 U.S.S. Enterprise
Reihe 2 2 U.S.S. Stargazer



SELECT T1.*,T2.* FROM capitainKlein T1, raumschiffKlein T2

Der oben angeführte Befehl stellt einen JOIN (Syntax wird etwas später erklärt) dar, und bildet das kartesische Produkt der Tabellen captainKlein und raumschiffKlein. Die Ergebnistabelle ist daher wesentlich größer als die 2 "verknüften" Tabellen.

Ergebnistabelle:
T1.commander T1.nr_schiff T2.nr_schiff T2.schiffsname
James Kirk 1 1 U.S.S. Enterprise
Jean-Luc Picard 2 1 U.S.S. Enterprise
James Kirk 1 2 U.S.S. Stargazer
Jean-Luc Picard 2 2 U.S.S. Stargazer

Das Ergebnis macht so keinen Sinn. Würden die Tabellen aus 3 Reihen bestehen so hätte wir eine Ergebnistabelle mit 9 (3*3) Reihen. Erst durch Verwendung von WHERE Bedingungen (Bildung einer Schnittmengen in der Mengenlehre) erzielt man sinnvolle Ergebnisse.

SELECT  T1.commander, T2.schiffsname
FROM  capitainKlein T1, raumschiffKlein T2
WHERE  T1.nr_schiff = T2.nr_schiff

Ergebnistabelle:
T1.commander T2.schiffsname
James Kirk U.S.S. Enterprise
Jean-Luc Picard U.S.S. Stargazer

Der Befehl JOIN ist im ANSI Standard enthalten. Anschliessend wird der Syntax dargestellt. Alle Optionen werden von den RDBMS nicht unterstützt. In Zukunft ist zu erwarten, dass die Mehrheit RDBMS den Syntax immer vollständiger interpretieren.



Syntax
top
Die meisten Joins lassen sich mit Hilfe des SELECT Befehls formulieren. Daher folgen 2 Syntax Beschreibungen.

 Syntax mit SELECT
Hinweis:
Beschreibung enthält nicht alle Optionen (viele fehlen)!
Die Funktionalität wird jedoch weitgehend abgedeckt.
Beispiele:
SELECT T1.Spalte1, T2.Spalte4 FROM Tabelle1 T1, Tabelle2 T2
SELECT T1.Spalte1, T2.* FROM Tabelle1 T1, Tabelle2 T2
SELECT T1.Spalte1, T2.Spalte4 FROM Tabelle1 T1, Tabelle2 T2 WHERE T1.Spalte2=T2.Spalte2

 Syntax mit JOIN
Hinweis:
Alle oben angeführten Optionen entsprechen dem ANSI Standard. Im Moment wird der beschriebene Syntax nicht vollständig von RDBMS unterstützt. Dies wird sich in Zukunft sicher ändern. Wir gehen auf diejenigen Optionen ein, die von nahezu allen RDBMS interpretiert werden.
Beispiele:
SELECT T1.Spalte1, T2.Spalte4 FROM Tabelle1 T1 INNER JOIN Tabelle2 T2 ON T1.Spalte2=T2.Spalte2

| ... trennt Syntaxmöglichkeiten (entweder oder)
[] ... Syntax innerhalb dieser Klammern ist optional
optional ... Syntax in dieser Zeile ist optional

Beispiel Tabelle:
top
Diese Tabellen werden verwendet um SQL besser darstellen zu können.


TabelleName: captain
Die Tabelle captain besteht aus 2 Spalten und 5 Reihen
Spalte1 hat den Namen name.
Spalte2 hat den Namen nr_schiff

Spaltenname name nr_schiff
Reihe 1 James Kirk 1
Reihe 2 Jean-Luc Picard 2
Reihe 3 Kathryn Janeway 3
Reihe 4 Commodore Decker 4
Reihe 5 Thomas Ramart 6


TabelleName: raumschiff
Die Tabelle raumschiff besteht aus 3 Spalten und 5 Reihen
Spalte1 hat den Namen nr_schiff.
Spalte2 hat den Namen name
Spalte3 hat den Namen status
  status=0 —> Raumschiff einsatzbereit
  status=1 —> Raumschiff in Reparatur

Spaltename nr_schiff name status
Reihe 1 1 U.S.S. Enterprise 1
Reihe 2 2 U.S.S. Stargazer 0
Reihe 3 3 U.S.S. Voyager 1
Reihe 4 4 U.S.S. Constellation 0
Reihe 5 5 U.S.S. Phoenix 1


TabelleName: baujahr
Die Tabelle baujahr besteht aus 2 Spalten und 5 Reihen
Spalte1 hat den Namen nr_schiff.
Spalte2 hat den Namen jahr

Spaltename nr_schiff jahr
Reihe 1 1 2221
Reihe 2 2 2325
Reihe 3 3 2050
Reihe 4 4 2014
Reihe 5 5 2201


INNER JOIN
top
SELECT  T1.name, T2.name  FROM  captain T1, raumschiff T2

Er wird am häufigsten verwendet . Wie beschrieben bildet ein Join zunächst das kartesiche Produkt aus den selektierten Spalten der verwendeten Tabellen. Meistens werden nun mit Hilfe einer Bedingung (WHERE) die gewünschten Reihen (Datensätze) ausgfiltert. In diesem Fall spricht man von INNER JOIN. Wird in der WHERE Bedingung das Gleichheitszeichen (=) verwendet, so bezeichnet man die Anweisung als EQUI JOIN genauer INNER EQUI JOIN, wird das Ungleichheitszeichen (<> oder !=) verwendet, spricht man von NON EQUI JOIN genauer INNER NON EQUI JOIN.

Ergebnistabelle: Da die Tabellen captain und raumschiff jeweils aus 5 Reihen bestehen enthält die Ergebnistabelle 25 Reihen. Ein nahezu identes Beispiel wird im Abschnitt Beschreibung mit entsprechender Ergebnistabelle beschrieben.


EQUI JOIN (genauer: INNER EQUI JOIN)
top
SELECT  T1.name, T2.name  FROM  captain T1, raumschiff T2
WHERE  T1.nr_schiff = T2.nr_schiff

SELECT  T1.name, T2.name  FROM  captain T1  INNER JOIN  raumschiff T2
ON  T1.nr_schiff = T2.nr_schiff

Beide Abfragen liefern das selbe Ergebnis. In der ersten Zeile der SQL Abfragen wird das kartesische Produkt (siehe Beschreibung) gebildet (besteht aus 25 Reihen). In der zweiten Zeile filtern wir durch die WHERE/ON Bedingung die gewünschten Zeilen aus dem kartesischen Produkt heraus. Wird das Gleichheitszeichen (=) in der WHERE Bedingung verwendet spricht man von EQUI JOIN. Als Ergebnis erhalten wir eine Tabelle in der jede Zeile den Commandanten und sein Raumschiff enthält.

Ergebnistabelle:
James Kirk U.S.S. Enterprise
Jean-Luc Picard U.S.S. Stargazer
Kathryn Janeway U.S.S. Voyager
Commodore Decker U.S.S. Constellation
Jean-Luc Picard U.S.S. Stargazer

Hinweis: Tatsächlich bildet ein RDBMS nicht das gesammte kartesische Produkt. Dies würde zu langen Abfragezeiten führen, und der benötige Arbeitspeicher würde in manchen Fällen sämtliche Kapazitäten überschreiten.

Schau dir auf jeden Fall die Beschreibung von OUTER JOIN an. Erst wenn dir der Unterschied zwischen INNER und OUTER JOIN klar ist, hast du die JOINS mit ihren Vorteilen richtig verstanden :)

Tabelle captain und raumschiff anzeigen
PHP Syntax     PERL Syntax


NON EQUI JOIN (genauer: INNER NON EQUI JOIN)
top
SELECT  T1.name, COUNT(T2.name)  FROM  captain T1, raumschiff T2
WHERE  T1.nr_schiff <> T2.nr_schiff
GROUP BY T1.name

SELECT  T1.name, COUNT(T2.name)  FROM  captain T1 INNER JOIN raumschiff T2
ON  T1.nr_schiff <> T2.nr_schiff
GROUP BY T1.name

Beide Abfragen liefern das selbe Ergebnis. In der ersten Zeile der SQL AbfrageN wird das kartesische Produkt (siehe Beschreibung) gebildet (besteht aus 25 Reihen). In den nächsten Zeile filtern wir durch die WHERE/ON Bedingung die gewünschten Zeilen aus dem kartesischen Produkt heraus und gruppieren sie anschliessend. Wird das Ungleichheitszeichen (<>) in der WHERE Bedingung verwendet spricht man von NON EQUI JOIN. Als Ergebnis erhalten wir eine Liste, in der jede Zeile den Namen eines Commandanten enthält und die Anzahl der Raumschiffe die er nicht befehligt.


Ergebnistabelle:
James Kirk 4
Jean-Luc Picard 4
Kathryn Janeway 4
Commodore Decker 4
Thomas Ramart 5

Hinweis: Tatsächlich bildet ein RDBMS nicht das gesammte kartesische Produkt. Dies würde zu langen Abfragezeiten führen, und der benötige Arbeitspeicher würde in manchen Fällen sämtliche Kapazitäten überschreiten.

Schau dir auf jeden Fall die Beschreibung von OUTER JOIN an. Erst wenn dir der Unterschied zwischen INNER und OUTER JOIN klar ist, hast du die JOINS mit ihren Vorteilen richtig verstanden :)

Tabelle captain und raumschiff anzeigen
PHP Syntax     PERL Syntax


OUTER JOIN
top
Wir bilden den INNER EQUI JOIN
SELECT  T1.name, T2.name  FROM  captain T1, raumschiff T2
WHERE  T1.nr_schiff = T2.nr_schiff

(siehe INNER EQUI JOIN). Wir erhalten folgendes Resultat.

Ergebnistabelle (INNER EQUI JOIN):
James Kirk U.S.S. Enterprise
Jean-Luc Picard U.S.S. Stargazer
Kathryn Janeway U.S.S. Voyager
Commodore Decker U.S.S. Constellation


Vergleichen wir die Ergebnistabelle mit der Tabelle captain, stellen wir fest, dass Captain Thomas Ramart in der Ergenistabelle fehlt. Warum? Der Datensatz von Thomas Ramart verweist auf ein Raumschiff mit nr_schiff=6. Die Tabelle raumschiff enthält keinen Eintrag mit dieser Nummer. Somit ist T1.nr_schiff = T2.nr_schiff für den Datensatz von Captain Thomas Ramart nie erfüllt und fehlt in der Ergebnistabelle. Das gleiche gilt für den Datensatz U.S.S. Phoenix aus der Tabelle raumschiff.
OUTER JOINS zeigen im Gegensatz zu den INNER JOINS auch die Datensätze an, die durch die WHERE Bedingung ausgefiltert wurden.
Beachte: Es werden nicht zusätzlich die ausgefilterten Elemente des kartisischen Produktes angezeigt, sondern die Datensätze der verknüpften Tabellen, die in der Ergebnistabelle nicht vorkommen.

Ergebnistabelle ( FULL OUTER JOIN):
James Kirk U.S.S. Enterprise
Jean-Luc Picard U.S.S. Stargazer
Kathryn Janeway U.S.S. Voyager
Commodore Decker U.S.S. Constellation
Thomas Ramart NULL
NULL U.S.S. Phoenix

Tabelle captain und raumschiff anzeigen


FULL OUTER JOIN
top
SELECT  T1.name, T2.name  FROM  captain T1  FULL OUTER JOIN  raumschiff T2
ON  T1.nr_schiff = T2.nr_schiff

Beschreibung siehen OUTER JOIN

Ergebnistabelle:
James Kirk U.S.S. Enterprise
Jean-Luc Picard U.S.S. Stargazer
Kathryn Janeway U.S.S. Voyager
Commodore Decker U.S.S. Constellation
Thomas Ramart NULL
NULL U.S.S. Phoenix

Hinweis: FULL OUTER JOIN ist Bestandteil des ANSI Standards. Jedoch wird er von den meisten RDBMS noch nicht unterstützt.

Tabelle captain und raumschiff anzeigen
PHP Syntax     PERL Syntax


LEFT JOIN (genauer:  LEFT OUTER JOIN)
top
SELECT  T1.name, T2.name  FROM  captain T1  LEFT JOIN  raumschiff T2
ON  T1.nr_schiff = T2.nr_schiff

Es sind immer 2 Tabellen bei einem LEFT JOIN beteiligt (durch Klammerung gelingt es beliebig viele Tabellen zu verknüpfen, siehe Joins mit mehreren Tabellen). Die Tabelle die links vom Join steht ist die MainTabelle, die rechts davon die JoinTabelle.
LEFT JOIN verhält sich gleich wie FULL OUTER JOIN (siehe OUTER JOIN) mit dem Unterschied:
Datensätze die durch die WHERE Bedingung ausgefiltert werden und sich in der JoinTabelle befinden werden in der Ergebnistabelle nicht aufgelistet.
In unserem Bespiel ist captain die MainTabelle und raumschiff die JoinTabelle. Datensätze von raumschiff die durch die WHERE Bedingung ausgefiltert wurden, scheinen in der Ergebnistabelle nicht auf.

Ergebnistabelle:
James Kirk U.S.S. Enterprise
Jean-Luc Picard U.S.S. Stargazer
Kathryn Janeway U.S.S. Voyager
Commodore Decker U.S.S. Constellation
Thomas Ramart NULL

Tabelle captain und raumschiff anzeigen
PHP Syntax     PERL Syntax


RIGHT JOIN (genauer:  RIGHT OUTER JOIN)
top
SELECT  T1.name, T2.name  FROM  captain T1  RIGHT JOIN  raumschiff T2
ON  T1.nr_schiff = T2.nr_schiff

Es sind immer 2 Tabellen bei einem RIGHT JOIN beteiligt (durch Klammerung gelingt es beliebig viele Tabellen zu verknüpfen, siehe Joins mit mehreren Tabellen). Die Tabelle die rechts vom Join steht ist die MainTabelle, die links davon die JoinTabelle.
RIGHT JOIN verhält sich gleich wie FULL OUTER JOIN (siehe OUTER JOIN) mit dem Unterschied:
Datensätze die durch die WHERE Bedingung ausgefiltert werden und sich in der JoinTabelle befinden werden in der Ergebnistabelle nicht aufgelistet.
In unserem Bespiel ist raumschiff die MainTabelle und captain die JoinTabelle. Datensätze der Tabelle captain die durch die WHERE Bedingung ausgefiltert wurden, scheinen in der Ergebnistabelle nicht auf.

Ergebnistabelle:
James Kirk U.S.S. Enterprise
Jean-Luc Picard U.S.S. Stargazer
Kathryn Janeway U.S.S. Voyager
Commodore Decker U.S.S. Constellation
NULL U.S.S. Phoenix

Hinweis: Werden die Tabellen vertauscht so liefert LEFT JOIN das gleiche Ergebnis.
SELECT T2.name, T1.name FROM raumschiff T1 LEFT JOIN captain T2 ON T2.nr_schiff=T1.nr_schiff
MySQL empfielt aus diesem Grund nur LEFT JOIN zu verwenden.


Tabelle captain und raumschiff anzeigen
PHP Syntax     PERL Syntax


JOINS VERKNÜPFEN
top
SELECT T1.name, T2.name, T3.jahr FROM captain T1, raumschiff T2, baujahr T3
WHERE T1.nr_schiff = T2.nr_schiff AND
T3.nr_schiff = T2.nr_schiff

SELECT T1.name, T2.name, T3.jahr FROM captain T1 INNER JOIN raumschiff T2
ON T1.nr_schiff = T2.nr_schiff
INNER JOIN baujahr T3
ON T3.nr_schiff = T2.nr_schiff

Beide Abfragen liefern das selbe Ergebnis. Auf diese Weise können theoretisch beliebig viele Tabellen verknüpft werden. Jedes RDBMS besitzt eine maximale Anzahl von Tabellen die in einen JOIN verwendet werden können. Diese Grenze ist so hoch, dass sie normalerweise nicht erreicht wird.

Ergebnistabelle:
James Kirk U.S.S. Enterprise 2221
Jean-Luc Picard U.S.S. Stargazer 2325
Kathryn Janeway U.S.S. Voyager 2050
Commodore Decker U.S.S. Constellation 2014

Tabelle captain und raumschiff anzeigen
PHP Syntax     PERL Syntax


UNION
top
SELECT nr_schiff, name FROM captain UNION
SELECT nr_schiff,name FROM raumschiff T2 ORDER BY nr_schiff

Die Ergebnisse der beiden SELECT Anweisungen werden durch UNION in einer Ergebnistabelle vereint. Deshalb müssen die SELECT Anweisungen die selbe Anzahl von Spalten liefern. Zudem sollten die Spalten vom gleichen Datentyp sein (in unserem Beispiel: erste Reihe INTEGER zweite Reihe VARCHAR)

Ergebnistabelle:
1 James Kirk
1 U.S.S. Enterprise
2 Jean-Luc Picard
2 U.S.S. Stargazer
3 Kathryn Janeway
3 U.S.S. Voyager
4 Commodore Decker
4 U.S.S. Constellation
5 U.S.S. Phoenix
6 Thomas Ramart

Hinweis: Der Befehl ist ANSI Standard, wird jedoch von MySQL erst ab Beta Version 4 unterstützt.

Tabelle captain und raumschiff anzeigen
PHP Syntax     PERL Syntax


top