FULL, INNER, OUTER JOIN

SQL Arbeitsblatt 3
Mit Hilfe von JOINS können nicht nur zwei, sondern auch sehr viel mehr Tabellen
zusammengefügt werden. In unserer Mitarbeiter-Telefonliste sollen Nachname, Vorname, Art des
Telefonanschlusses und die Telefonnummer erscheinen.
1. Schauen Sie sich mit Hilfe der SELECT *-Anweisung die Tabellen t_mitarbeiter, t_tele und
t_art an. Führen Sie folgende Anweisung durch und erklären Sie diese!
SELECT m.vorname, m.nachname
a.bezeichnung, t.bezeichnung
FROM t_mitarbeiter m, t_tele t, t_art a
WHERE (m.nummer = t.mitarbeiter) AND (a.nummer = t.art)
Wähle Nachname und Vorname aus t_mitarbeiter, bezeichnung (Art des Telefonanschlusses) aus
t_art und bezeichnung (Telefonnummer) aus t_tele. Gib Daten aus, bei der die Nummer aus
t_mitarbeiter der Nummer (mitarbeiter) aus t_tele entspricht und bei der die Art (Nummer) aus
t_tele gleich der Nummer aus t_art ist.
3. Erstellen Sie den obigen JOIN mit Hilfe von ON-Klauseln. Beachten Sie, dass – im Gegensatz
zur WHERE-Klausel – mehrere ON-Klauseln erlaubt sind.
SELECT m.vorname, m.nachname, a.bezeichnung, t.bezeichnung FROM t_mitarbeiter m INNER
JOIN t_tele t ON m.nummer = t.mitarbeiter INNER JOIN t_art a ON a.nummer = t.art
Die Verwendung von INNER JOINS erhöht im Gegensatz zur WHERE-Klausel die Übersichtlichkeit
der Abfrage!
4. Kennzeichen des bisher bekannten INNER JOIN (EQUI JOIN) ist es, dass nur diejenigen
Datensätze aufgenommen werden, bei denen zu allen beteiligten Tabellen Datensätze
vorhanden sind. Es soll eine vollständige Mitarbeiterliste erstellt werden, in der auch die
Telefonnummern der Mitarbeiter (soweit bekannt) aufgenommen werden. Analysieren Sie
hierzu die folgende SQL-Anweisung!
SELECT m.vorname, m.nachname, t.bezeichnung
FROM t_mitarbeiter m
LEFT OUTER JOIN t_tele t ON m.nummer = t.mitarbeiter
Bei einem LEFT OUTER JOIN werden alle Datensätze der Tabelle auf der linken Seite verwendet,
hier also t_mitarbeiter. Diese werden dann, soweit möglich, mit den Datensätzen der rechten
Seite, hier t_tele, verknüpft.
5. Die Existenz eines LEFT OUTER JOIN legt die Vermutung nahe, dass es auch einen RIGHT
OUTER JOIN gibt. Was macht folgende Anweisung?
SELECT m.vorname, m.nachname, t.bezeichnung
FROM t_mitarbeiter m
RIGHT OUTER JOIN t_tele t ON m.nummer = t.mitarbeiter
Hier werden Telefonnummer angezeigt und den Mitarbeitern zugeordnet. Es würden auch
Telefonnummern angezeigt, zu denen kein passender Mitarbeiter existiert.
6. Prinzipiell kann man auch INNER und OUTER JOIN mischen. Man muss allerdings damit
rechnen, nicht das gewünschte Ergebnis zu erhalten. Analysieren Sie hierzu folgende
SELECT-Anweisung! Welche Folge hat die Verwendung des INNER JOIN?
SELECT m.vorname, m.nachname, t.bezeichnung, a.bezeichnung
FROM t_mitarbeiter m
LEFT OUTER JOIN t_tele t ON m.nummer = t.mitarbeiter
INNER JOIN t_art a ON t.art = a.nummer
Die Verwendung des INNER JOIN führt dazu, dass nur die Mitarbeiter mit Telefon angezeigt werden.
Müller-Tiemann, Cuno-BK I
D:\75901504.doc
Seite 1 von 2
SQL Arbeitsblatt 3
7. Verändern Sie die Tabellenreihenfolge so, dass alle Mitarbeiter sowie die Telefonnummer und
die Art des Telfonanschlusses (wenn vorhanden) angegeben werden!
SELECT m.vorname, m.nachname, t.bezeichnung, a.bezeichnung
FROM t_tele t
INNER JOIN t_art a ON t.art = a.nummer
RIGHT OUTER JOIN t_mitarbeiter m ON m.nummer = t.mitarbeiter
8. Analysieren Sie folgenden SQL-Anweisungen!
SELECT m.vorname, m.nachname, t.bezeichnung, a.bezeichnung
FROM t_mitarbeiter m
LEFT OUTER JOIN (t_tele t INNER JOIN t_art a ON t.art = a.nummer)
ON m.nummer = t.mitarbeiter
SELECT m.vorname, m.nachname, t.bezeichnung, a.bezeichnung
FROM t_mitarbeiter m
LEFT OUTER JOIN t_tele t ON m.nummer = t.mitarbeiter
LEFT OUTER JOIN t_art a ON t.art = a.nummer
9. Stellen Sie obige SQL-Anweisung so um, dass nur RIGHT OUTER JOINS verwendet werden!
SELECT m.vorname, m.nachname, t.bezeichnung, a.bezeichnung
FROM t_art a
RIGHT OUTER JOIN t_tele t ON t.art = a.nummer
RIGHT OUTER JOIN t.mitarbeiter m ON m.nummer = t.mitarbeiter
10. Bei einem FULL OUTER JOIN werden alle Datensätze der beteiligten Tabellen angezeigt. Wo
es möglich ist, werden Verknüpfungen vorgenommen. Das folgende Beispiel zeigt einen FULL
OUTER JOIN. Die Anweisung funktioniert, ist jedoch nicht sinnvoll. Warum?
SELECT m.vorname, m.nachname, t.bezeichnung
FROM t_mitarbeiter m
FULL OUTER JOIN t_tele t
ON m.nummer = t.mitarbeiter
Die Anweisung würde nicht nur Mitarbeiter ohne Telefon, sondern auch Telefonnummern ohne
Mitarbeiter auflisten.
11. Ein SELF JOIN ist ein JOIN, bei dem die Tabelle mit sich selbst verknüpft wird. Das setzt dann
voraus, dass für ein und dieselbe Tabelle zwei verschieden Aliase verwendet werden. Eine
sinnvolle Anwendung ergibt sich aus folgendem Beispiel: Die Tabelle t_mitarbeiter enthält die
Spalte vorgesetzter, in welcher die Personennummer des jeweils direkten Vorgesetzen
gespeichert ist. Wie wird ermittelt, wer sich hinter der Nummer verbirgt?
SELECT m.vorname, m.nachname, v.vorname || „ „ || v.nachname AS
vorgesetzter
FROM t_mitarbeiter m, t_mitarbeiter v WHERE m.vorgesetzter = v.nummer
Wer sich hinter der Nummer verbirgt, wird anhand der Tabelle t_mitarbeiter ermittelt.
12. Handelt es sich bei diesem SELF JOIN um einen INNER JOIN, einen LEFT OUTER JOIN,
einen RIGHT OUTER JOIN oder um einen FULL OUTER JOIN? Schreiben Sie den SELF
JOIN so um, dass keine WHERE-Klausel, sondern eine ON-Klausel verwendet wird!
Es handelt sich um einen INNER JOIN, weil OUTER JOINS explizit so genannt werden müssen,
bei OUTER JOINs keine WHERE-Klausel erlaubt ist.
SELECT m.vorname, m.nachname, v.vorname
|| “ “ || v.nachname AS vorgesetzter
FROM t_mitarbeiter m INNER JOIN t_mitarbeiter v
ON m.vorgesetzter = v.nummer
Müller-Tiemann, Cuno-BK I
D:\75901504.doc
Seite 2 von 2