SQL Self-Join

SQL
Self-Join
Bei der Verbundbildung wird mehr als einmal Bezug auf eine Relation genommen
Prinzip der Verbundbildung
Select *
From Tabellenname T1, Tabellenname T2;
-- T1=T2
Anwendungsbeispiel: Finde die Namen aller Studenten, die an zwei Kursen teilnehmen, die zur gleichen Zeit
stattfinden
1. Schritt: Finde die SID aller Studenten, die an mehr als einem Kurs teilnehmen
Select Distinct B1.SNummer
From Besucht B1, Besucht B2
WHERE B1.SNummer=B2.SNummer AND B1.VName <> B2.VName;
2. Schritt: Finde die SID aller Studenten, die unterschiedliche Kurse besuchen, die zur gleichen Zeit
stattfinden
Select Distinct B1.SNummer
From Besucht B1, Besucht B2, Veranstaltung V1, Veranstaltung V2
Where B1.SNummer=B2.SNummer AND B1.VName <> B2.VName AND
B1.VName = V1.VName AND B2.VName = V2.VName AND V1.Beginnt_um=V2.Beginnt_um;
3. Schritt: Finde die Namen aller Studenten, die an zwei Kursen teilnehmen, die zur gleichen Zeit
stattfinden
Select Distinct S.SName
From Student S
Where S.SNummer IN (Select B1.SNummer
From Besucht B1, Besucht B2, Veranstaltung V1, Veranstaltung V2
Where B1.SNummer=B2.SNummer AND B1.VName <> B2.VName AND
B1.Vname = V1.VName AND B2.Vname = V2.VName AND V1.Beginnt_um=V2.Beginnt_um);
16. November 2001
Seite 1
Pfad- und Dateiangaben
SQL
Auswahloperatoren
IN Auswahloperator
Anwendung:
Spaltenname IN (SFW-Anweisung)
Aufbau einer Werteliste, in die jeder aus der Subauswahl resultierende Wert übernommen wird (nicht-korrelierte
Subquery)
Wert aus Spaltenname wird mit jedem Wert aus der entstandenen Werteliste verglichen
Vergleichsbedingung ist erfüllt, sobald der Wert von Spaltenname mit mindestens einem der Werte aus der
Subauswahl übereinstimmt
Liefert die Subauswahl eine leere Menge zurück, so ist die Auswahlbedingung nicht zutreffend
ANY Auswahloperator
Anwendung:
Spaltenname Vergleichsoperation ANY (SFW-Anweisung)
Aufbau einer Werteliste, in die jeder aus der Subauswahl resultierende Wert übernommen wird (nicht-korrelierte
Subquery)
Wert aus Spaltenname wird mit jedem Wert aus der entstandenen Werteliste verglichen
Ist die Vergleichsbedingung bei mindestens einem Wert aus der Subauswahl erfüllt, so wird aktueller Datensatz
Bestandteil des Ergebnisses
Liefert die Subauswahl eine leere Menge zurück, so ist die Auswahlbedingung nicht zutreffend
16. November 2001
Seite 2
Pfad- und Dateiangaben
SQL
Auswahloperatoren
ALL Auswahloperator
Anwendung:
Spaltenname Vergleichsoperation ALL (SFW-Anweisung)
Aufbau einer Werteliste, in die jeder aus der Subauswahl resultierende Wert übernommen wird (nicht-korrelierte
Subquery)
Wert aus Spaltenname wird mit jedem Wert aus der entstandenen Werteliste verglichen
Ist die Vergleichsbedingung bei allen Werten aus der Subauswahl erfüllt, so wird aktueller Datensatz Bestandteil des
Ergebnisses
Liefert die Subauswahl keinen Wert zurück, so triff die Auswahlbedingung zu
EXISTS
Anwendung:
EXISTS (SFW-Anweisung)
Liefert die Subauswahl ein Ergebnis, so triff die Auswahlbedingung zu (korrelierte Subquery – Subauswahl wird für
jede Tabellenzeile isoliert ausgewertet)
Liefert die Subauswahl keinen Wert zurück, so ist die Auswahlbedingung nicht erfüllt
16. November 2001
Seite 3
Pfad- und Dateiangaben
SQL
Auswahloperatoren
Division von Relationen – Anwendung von NOT EXISTS
Anwendungsbeispiel: Gesucht werden die Namen aller Studenten, die alle angebotenen Kurse/Veranstaltungen
besuchen
Gegeben:
Relation S –Student
Relation V – Veranstaltung
Relation B – besucht
Relation E - S Natural Join B
Gesucht: E / V
Effiziente Umsetzung in SQL:
1. Schritt: Finde die SID aller Studenten, die mindestens eine Veranstaltung besuchen
Select B.SNummer From Veranstaltung V, Besucht B
Where V.VName = B.VName;
2. Schritt: Finde die SID aller Studenten, die mindestens eine Veranstaltung nicht besuchen
Select B.SNummer From Besucht B Where not exists (
Select * From Veranstaltung V Where
V.VName = B.VName);
3. Schritt: Berechnung aller Studenten, die alle Veranstaltungen besuchen
Select Distinct S.SName
from Student S
Where not exists (Select B.SNummer From Besucht B
Where not exists (Select * From Veranstaltung V Where
V.VName = B.VName));
16. November 2001
Seite 4
Pfad- und Dateiangaben
SQL
Studenten-Datenbank
Student
besucht
SNUMMER SNAME
HAUPTFACH
SEMESTER SALTER
SNUMMER VNAME
------------------------------------------------------------------------------------------------------- ---------------------------------------------------------101
Karl
Biologie
5
25
101
Informatik I
102
Mayer
Mathematik
1
20
101
Biologie fuer Quereinsteiger
103
Schenk
Deutsch
14
30
102
Grundlagen der Mathematik
104
Grust
Informatik
10
34
104
Informatik I
105
Mainberger Biologie
4
22
104
Grundlagen der Mathematik
106
Conrad
Sport
1
18
104
Biologie fuer Quereinsteiger
105
Biologie fuer Quereinsteiger
105
Grundlagen der Mathematik
105
Biologie fuer Fortgeschrittene
105
Informatik I
Veranstaltung
VNAME
BEGINNT_U
RAUM
FID
------------------------------------------------------------------------------------------------------Grundlagen der Mathematik 10:00
R201
1
Informatik I
12:00
D301
1
Biologie fuer Quereinsteiger 08:00
F345
2
Biologie fuer Fortgeschrittene 08:00
F346
2
Fakultaet
FID
FNAME
---------------------------------------------1
Informatik
2
Biologie
3
Sport
16. November 2001
Seite 5
Pfad- und Dateiangaben