Outer Joins

Kapitel 5
Dr. Brigitte Mathiak
SQL DDL + DML
Lernziele

Fähigkeit zur praktischen Anwendung von
SQL-Anweisungen in den Bereichen:




Schema-Definitionen einschl. referentieller Integrität
Einfügen von Daten
Ändern von Daten
Löschen von Daten
Varianten von SQL kennen lernen
Datenbanken, WS 12/13 Kapitel 5: SQL2
2

SQL: Komponenten der vollständigen DB-Sprache
Datenmanipulation (Data Manipulation Language DML)
 Einfügen, Löschen und Ändern von individuellen Tupeln
In
und von Mengen von Tupeln
 Zuweisung von ganzen Relationen
diesem Kapitel
Datendefinition (Data Definition Language DDL)
 Definition von Wertebereichen, Attributen und Relationen
In
 Definition von verschiedenen Sichten auf Relationen
diesem Kapitel
Datenkontrolle
 Spezifikation von Bedingungen zur Zugriffskontrolle
 Spezifikation von Zusicherungen (assertions) zur semantischen
Integritätskontrolle
Datenabfragen
 Abbildung von Eingaberelationen durch Auswertung von Bedingungen
Das war Kapitel
auf die Ergebnisrelation
2
Kopplung mit einer Wirtssprache (z.B. C++)
 deskriptive Auswahl von Mengen von Tupeln
 sukzessive Bereitstellung einzelner Tupeln
Datenbanken, WS 12/13 Kapitel 5: SQL2
3

Beispiel: Universitätsschema
*
Studenten
MatrNr
Name
Semester
hören
*
*
voraussetzen
Vorlesungen
*
VorlNr
Titel
SWS
*
*
lesen
Prüfen
Note
1
1
Professoren
PersNr
Name
Rang
Raum
Datenbanken, WS 12/13 Kapitel 5: SQL2
Assistenten
arbeitenFür
1
*
PersNr
Name
Fachgebiet
4
Studenten
Professoren
Rang Raum MatrNr
Vorlesungen
Name
Semester
24002
Xenokrates
18
232
25403
Jonas
2127 Kopernikus C3
310
26120
2133
C3
52
26830
2134 Augustinus C3
309
2136
Curie
C4
36
2137
Kant
C4
7
PersNr
Name
2125
Sokrates
C4
226
2126
Russel
C4
Popper
voraussetzen
VorlNr
Titel
12
5001
Grundzüge
4
2137
Fichte
10
5041
Ethik
4
2125
Aristoxenos
8
5043
Erkenntnistheorie
3
2126
27550 Schopenhaue
r
6
5049
Mäeutik
2
2125
4052
Logik
4
2125
28106
3
5052
Wissenschaftstheori
e
3
2126
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
5022
Glaube und Wissen
2
2134
4630
Die 3 Kritiken
4
2137
Carnap
29120 Theophrastos
2
29555
2
Vorgänger Nachfolger
Feuerbach
hören
5001
5041
MatrNr
VorlNr
5001
5043
26120
5001
5001
5049
27550
5001
5041
5216
27550
4052
5043
5052
28106
5041
5041
5052
28106
5052
5052
5259
28106
5216
prüfen
29120
5001
MatrNr VorlNr PersNr Note
29120
5041
28106
5001
2126
1
29120
5049
25403
5041
2125
2
25403
5022
Datenbanken, WS 12/13 Kapitel 5: SQL2
SWS gelesenV
on
Assistenten
PerslNr
Name
Fachgebiet
Boss
3002
Platon
Ideenlehre
2125
3003
Aristoteles
Syllogistik
2125
3004
Wittgenstein
Sprachtheorie
2126
3005
Rhetikus
Planetenbewegung
2127
3006
Newton
Keplersche Gesetze
2127
3007
Spinoza
Gott und Natur
2126
5
Relationale Invarianten
Integritätsbedingungen des Relationenmodells
 Primärschlüsselbedingung (Entity-Integrität)
- Eindeutigkeit des Primärschlüssels
- keine Nullwerte!
 Referentielle Integrität:
- Darstellung von Beziehungen durch Fremdschlüssel (foreign key):
Attribut, das in Bezug auf den Primärschlüssel einer anderen
(oder derselben) Relation definiert ist
- zugehöriger Primärschlüssel muss existieren, d.h. zu jedem Wert
(ungleich Null) eines Fremdschlüsselattributs einer Relation R2
muss ein gleicher Wert des Primärschlüssels in irgendeinem Tupel von
Relation R1 vorhanden sein
Datenbanken, WS 12/13 Kapitel 5: SQL2
6
Relationale Invarianten (1)
Fremdschlüssel und zugehöriger Primärschlüssel
tragen wichtige interrelationale Informationen
 sie sind auf dem gleichen Wertebereich definiert
 sie gestatten die Verknüpfung von Relationen mit Hilfe von
Relationenoperationen
Fremdschlüssel
 können Nullwerte aufweisen, wenn sie nicht Teil
eines Primärschlüssels sind.
 ein Fremdschlüssel ist „zusammengesetzt“, wenn der zugehörige
Primärschlüssel „zusammengesetzt“ ist
Eine Relation kann mehrere Fremdschlüssel besitzen, die die
gleiche oder verschiedene Relationen referenzieren
Zyklen sind möglich (geschlossener referentieller Pfad)
Eine Relation kann zugleich referenzierende und referenzierte
Relation sein („self-referencing table“).
Datenbanken, WS 12/13 Kapitel 5: SQL2
7
SQL: DDL Anweisungen
DDL-Spezifikation in SQL bei CREATE TABLE:
Professoren
Assistenten
PersNr
Name
Fachgebiet
Boss
PersNr
Name
Rang
Raum
3002
Platon
Ideenlehre
2125
2125
Sokrates
C4
226
3003
Aristoteles
Syllogistik
2125
2126
Russel
C4
232
3004
Wittgenstein
Sprachtheorie
2126
2127
Kopernikus
C3
310
3005
Rhetikus
Planetenbewegung
2127
2133
Popper
C3
52
3006
Newton
Keplersche Gesetze
2127
2134
Augustinus
C3
309
3007
Spinoza
Gott und Natur
2126
2136
Curie
C4
36
2137
Kant
C4
7
CREATE TABLE Assistenten
(PersNr INT PRIMARY KEY,
Name VARCHAR2 (100) NOT NULL,
Fachgebiet VARCHAR2(100),
Boss INT REFERENCES Professoren(PersNr));
Datenbanken, WS 12/13 Kapitel 5: SQL2
8
(Einfache) Datendefinition in SQL
CREATE TABLE <tablename>
( <columnname> <Datentyp> <Constraint>,
<columnname2> …, …
)
Wichtige Datentypen in Oracle:
varchar2 (n) für Strings variabler Länge
NUMBER(p,s) für Zahlen mit der angegebenen Genauigkeit
PLS_INTEGER für Integerwert (bessere Performanz)
blob oder raw für sehr große binäre Daten
clob für sehr große String-Attribute
date für Datumsangaben
xml für XML-Dokumente
…
Datenbanken, WS 12/13 Kapitel 5: SQL2
9
Constraints
Mehr zu Constraints im Kapitel Integritätsbedingungen hier nur
kurz die wichtigsten:
• PRIMARY KEY für den Primärschlüssel
• REFERENCES rel(col) für einen Fremdschlüssel, der auf die
Relation rel und die Spalte col zeigt
• UNIQUE wenn es keine Duplikate in der Spalte geben darf
(PRIMARY KEY ist automatisch UNIQUE)
• NOT NULL wenn Nullwerte nicht zugelassen sind
Datenbanken, WS 12/13 Kapitel 5: SQL2
10
Schemaänderungen
ALTER TABLE <table_name>
RENAME TO <new_table_name>;
Beispiel:
ALTER TABLE suppliers
RENAME TO vendors;
Statt RENAME TO gibt es auch eine Reihe anderer
Änderungsmöglichkeiten z.B.
• ADD <column> <Datentyp> <Constraint>
• MODIFY <column> <neuer_Datentyp> <neues_Constraint>
• DROP <column>
• RENAME COLUMN <column> TO <neuer_Name>
Datenbanken, WS 12/13 Kapitel 5: SQL2
11
Veränderung am Datenbestand
Einfügen von Tupeln:
MatrNr
Studenten
Name
Semester
29120
29555
28121
Theophrastos
Feuerbach
Archimedes
2
2
-
insert into Studenten (MatrNr, Name)
values (28121, 'Archimedes');
insert into hören
select MatrNr, VorlNr
Null-Wert
from Studenten, Vorlesungen
where Titel= 'Logik' ;
Datenbanken, WS 12/13 Kapitel 5: SQL2
12
Veränderungen am Datenbestand
Löschen von Tupeln
delete Studenten
where Semester > 13;
Verändern von Tupeln
update Studenten
set Semester = Semester + 1;
Datenbanken, WS 12/13 Kapitel 5: SQL2
13
Assistenten
Professoren
PersNr
Name
Fachgebiet
Boss
PersNr
Name
Rang
Raum
3002
Platon
Ideenlehre
2125
2125
Sokrates
C4
226
3003
Aristoteles
Syllogistik
2125
2126
Russel
C4
232
3004
Wittgenstein
Sprachtheorie
2126
2127
Kopernikus
C3
310
3005
Rhetikus
Planetenbewegung
2127
2133
Popper
C3
52
3006
Newton
Keplersche Gesetze
2127
2134
Augustinus
C3
309
3007
Spinoza
Gott und Natur
2126
2136
Curie
C4
36
2137
Kant
C4
7
CREATE TABLE Assistenten
(PersNr INT PRIMARY KEY,
Name VARCHAR2 (100) NOT NULL,
Fachgebiet VARCHAR2(100),
Boss INT REFERENCES Professoren(PersNr));
Aufgabe: Denken Sie sich eine Insert, eine Delete und eine
Update Anweisung aus, die vom System abgelehnt werden.
(Jeder für sich mit Zettel und Stift; 5 min)
Zusatzaufgabe: Denken Sie sich ein Alter Table aus, dass vom
System abgelehnt wird
Datenbanken, WS 12/13 Kapitel 5: SQL2
14
Bootstrapping
CREATE TABLE Sekretärin
(PersNr INT PRIMARY KEY, …
Boss INT REFERENCES Professor(PersNr));
CREATE TABLE Professor
(PersNr INT PRIMARY KEY, …
Sekräterin INT REFERENCES Sekretärin(PersNr));
Was geht da schief?
Welche Tabelle lege ich zuerst an?
Welche Daten füge ich zuerst ein?
Datenbanken, WS 12/13 Kapitel 5: SQL2
15
Bootstrapping (Lösungen)
•ALTER TABLE
MODIFY COLUMN
Boss INT REFERENCES Professor(PersNr);
•Bei der Datenmodellierung darauf achten, solche zyklischen
Abhängigkeiten zu vermeiden
•Transaktionen (s. spätere Kapitel)
Es werden mehrere Operationen gleichzeitig ausgeführt
•Insert into Sekretärin values (4, …, Null);
Insert into Professor values (7, …, 4);
Update Sekretärin Set Boss = 7 Where PersNr = 4 ;
Datenbanken, WS 12/13 Kapitel 5: SQL2
16
Varianten von SQL
Nicht alle Versionen von SQL sind identisch
Neben syntaktischen Varianten sind insbesondere die
verschiedenen Join Operatoren und Lösungen für das
Rekursionsproblem relevant.
Achtung:
Benutzen Sie in der Klausur ausschließlich Standardsyntax !!!
Datenbanken, WS 12/13 Kapitel 5: SQL2
17
Joins in SQL-92
cross join: Kreuzprodukt
select *
from R1, R2;
natural join: natürlicher Join
Join oder inner join: Theta-Join
left, right oder full outer join: äußerer Join
union join: Vereinigungs-Join (wird hier nicht vorgestellt)

Inner Join

Anfrage:
welcher Professor bietet welche Vorlesungen an?
SELECT p.Name, v.Name
FROM Professoren p
JOIN Vorlesungen v ON p.PersNr = v.gelesenVon ;
SELECT p.Name, v.Name
FROM Professoren p, Vorlesungen v
WHERE p.PersNr = v.gelesenVon ;
Datenbanken, SS 12 Kapitel 2: SQL Anfragen
19
Natural Join

Anfrage: Welcher Student hört welche Vorlesung?
SELECT Studenten.Name, Vorlesungen.Name
FROM Studenten
NATURAL JOIN hören
NATURAL JOIN Vorlesungen ;
SELECT s.Name, v.Name
FROM Studenten s, hören h, Vorlesungen v
WHERE
s.MatrNr = h.MatrNr AND
h.VorlNr = v.vorlNr ;
Datenbanken, SS 12 Kapitel 2: SQL Anfragen
20
Cross Join

Anfrage:
alle Paare "Professor – Student" :
SELECT Professoren.Name, Studenten.Name
FROM Professoren CROSS JOIN Studenten ;
SELECT p.Name, s.Name
FROM Professoren p, Studenten s ;
Datenbanken, SS 12 Kapitel 2: SQL Anfragen
21
Outer Joins: Left Outer Join

SELECT p.PersNr, p.Name, f.PersNr, f.Note,
f.MatrNr, s.MatrNr, s.Name
FROM Professoren p LEFT OUTER JOIN
( prüfen f LEFT OUTER JOIN Studenten s
ON f.MatrNr = s.MatrNr )
ON p.PersNr = f.PersNr ;
p.PersN
f.PersN f.Not f.MatrN
p.Name
r
r
e
r
2126
Russel
2126
1
28106
Sokrate
2125
2125
2
25403
s
s.Matr
Nr
28106
25403
s.Name
Carnap
Jonas
2137
Kant
2137
2
27550
2136
Curie
Ø
Ø
Ø
Ø
Ø
…
…
…
…
…
…
…
Datenbanken, SS 12 Kapitel 2: SQL Anfragen
27550 Schopenhauer
22
Outer Joins: Right Outer Join

SELECT p.PersNr, p.Name, f.PersNr, f.Note,
f.MatrNr, s.MatrNr, s.Name
FROM Professoren p RIGHT OUTER JOIN
(prüfen f RIGHT OUTER JOIN
Studenten s ON f.MatrNr = p.MatrNr)
ON p.PersNr = f.PersNr ;
p.PersN
r
p.Name
f.PersNr
f.Note
f.MatrN
r
s.MatrNr
s.Name
2126
Russel
2126
1
28106
28106
Carnap
2125
Sokrates
2125
2
25403
25403
Jonas
2137
Kant
2137
2
27550
27550
Schopenhau
er
Ø
Ø
Ø
Ø
Ø
26120
Fichte
…
…
…
…
…
…
…
Datenbanken, SS 12 Kapitel 2: SQL Anfragen
23
Outer Joins: Full Outer Join

SELECT p.PersNr, p.Name, f.PersNr, f.Note,
f.MatrNr, s.MatrNr, s.Name
FROM Professoren p FULL OUTER JOIN
(prüfen f FULL OUTER JOIN Studenten s
ON f.MatrNr = s.MatrNr)
ON p.PersNr = f.PersNr ;
p.PersN
r
2126
p.Name
f.PersNr
f.Note
f.MatrNr
s.MatrNr
s.Name
Russel
Sokrate
s
2126
1
28106
28106
Carnap
2125
2
25403
25403
Jonas
2137
Kant
2137
2
27550
27550
Schopenhauer
Ø
Ø
Ø
Ø
Ø
26120
Fichte
…
…
…
…
…
…
…
2136
Curie
Ø
Ø
Ø
Ø
Ø
…
…
…
…
…
…
…
2125
Datenbanken, SS 12 Kapitel 2: SQL Anfragen
24
Outer Joins: Oracle-Syntax

Anfrage:
welche Studenten hören welche Vorlesungen
SELECT *
FROM Studenten
LEFT OUTER JOIN hören
USING (MatrNr)
SELECT *
FROM Studenten s, hören h
WHERE
s.MatrNr = h.MatrNr (+)
Datenbanken, SS 12 Kapitel 2: SQL Anfragen
25
Äußere Joins
select p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr,
s.MatrNr, s.Name
from Professoren p left outer join
(prüfen f left outer join Studenten s
on f.MatrNr= s.MatrNr)
on p.PersNr=f.PersNr;
PersNr p.Name f.PersNr
2126 Russel
2126
2125 Sokrates 2125
2137
Kant
2137
2136
Curie
-
f.Note
1
2
2
-

f.MatrNr s.MatrNr s.Name
28106
28106
Carnap
25403
25403
Jonas
27550
27550 Schopenhauer
-
Äußere Joins

select p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr,
s.MatrNr, s.Name
from Professoren p right outer join
(prüfen f right outer join Studenten s on
f.MatrNr= s.MatrNr)
on p.PersNr=f.PersNr;
PersNr p.Name f.PersNr f.Note f.MatrNr s.MatrNr s.Name
2126 Russel
2126
1
28106
28106
Carnap
2125 Sokrates 2125
2
25403
25403
Jonas
2137
Kant
2137
2
27550
27550 Schopenhauer
26120
Fichte

Äußere Joins
select p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr,
s.MatrNr, s.Name
from Professoren p full outer join
(prüfen f full outer join Studenten s on
f.MatrNr= s.MatrNr)
on p.PersNr=f.PersNr;
p.PersNr p.Name f.PersNr
2126 Russel
2126
2125 Sokrates 2125
2137
Kant
2137
f.Note
1
2
2
-
-
-
-
2136
Curie
-
-
f.MatrNr s.MatrNr s.Name
28106
28106
Carnap
25403
25403
Jonas
27550
27550 Schopenhauer
26120
Fichte
-
-
-

Rekursion
select Vorgänger
from voraussetzen, Vorlesungen
where Nachfolger= VorlNr and
Titel= `Der Wiener Kreis´

Der Wiener Kreis
Wissenschaftstheorie
Erkenntnistheorie
Bioethik
Ethik
Grundzüge
Mäeutik
Rekursion
select v1.Vorgänger
from voraussetzen v1, voraussetzen v2, Vorlesungen v
where v1.Nachfolger= v2.Vorgänger and
v2.Nachfolger= v.VorlNr and
v.Titel=`Der Wiener Kreis´

Vorgänger des „Wiener Kreises“
der Tiefe n
select v1.Vorgänger
Wollen wir das wirklich?
from voraussetzen v1

voraussetzen vn_minus_1
voraussetzen vn,
Vorlesungen v
where v1.Nachfolger= v2.Vorgänger and

vn_minus_1.Nachfolger= vn.Vorgänger and
vn.Nachfolger = v.VorlNr and
v.Titel= `Der Wiener Kreis´


Grundproblem: Transitive Hülle
transA,B(R)= {(a,b)  k  IN (1, ..., k  R (
1.A= 2.B 

k-1.A= k.B 
1.A= a 
k.B= b))}

Der Wiener Kreis
Wissenschaftstheorie
Erkenntnistheorie
Bioethik
Ethik
Grundzüge
Mäeutik

Die connect by-Klausel (Oracle)
select Titel
from Vorlesungen
where VorlNr in (select Vorgänger
Grundzüge
Ethik
Erkenntnistheorie
Wissenschaftstheorie
from voraussetzen
connect by Nachfolger=prior Vorgänger
start with Nachfolger= (select VorlNr
from Vorlesungen
where Titel= `Der
Wiener Kreis´));
Rekursion in DB2/SQL99:
gleiche Anfrage
with TransVorl (Vorg, Nachf)
as (select Vorgänger, Nachfolger from voraussetzen
union all
select t.Vorg, v.Nachfolger
from TransVorl t, voraussetzen v
where t.Nachf= v.Vorgänger)
select Titel from Vorlesungen where VorlNr in
(select Vorg from TransVorl where Nachf in
(select VorlNr from Vorlesungen
where Titel= `Der Wiener Kreis´) )


zuerst wird eine temporäre Sicht TransVorl mit der withKlausel angelegt
Diese Sicht TransVorl ist rekursiv definiert, da sie selbst in
der Definition vorkommt
Aus dieser Sicht werden dann die gewünschten Tupel
extrahiert
Ergebnis ist natürlich wie gehabt
Fazit Rekursion/transitive Hülle

In SQL nur mühsam lösbar
Vorhandene Lösungen sind technologieabhängig
Die praktisch beste Lösung ist fast immer das Ändern der
Datenstruktur oder das Anlegen von Hilfstabellen
voraussetzen_rec
5001
5216
Vorgänger Nachfolger
5001
5052
5001
5041
5001
5043
5001
5043
5001
5052
5001
5049
5043
5041
5216
5041
5043
5052
5041
5052
5052
5259
Datenbanken, SS 12 Kapitel 2: SQL Anfragen
Achtung!
5259
Solche Datenstrukturen
5259
sind oft schwer aktuell zu
halten -> Datenintegrität
39
Syntaktische Beschreibung einer Select Anweisung
"Grobsyntax":
select_block { { UNION | INTERSECT | EXCEPT } [ALL]
select_block ...}
[ORDER BY result_column [ASC | DESC]
{, result_column [ASC | DESC] …}
mit select_block ::=
SELECT [ALL | DISTINCT]
{column | {expression [AS result_column]}}
{, {column | {expression [AS result_column]}} …}
FROM table [correlation_var] {, table [correlation_var] …}
[WHERE search_condition]
[GROUP BY column {, column …} [HAVING search_condition] ]
Datenbanken, SS 12 Kapitel 2: SQL Anfragen
40
Typische Select-Pattern
Paare von gleichartigen Objekten
(z.B. Welche Paare von Studenten haben …?)
SELECT a1.name, a2.name FROM a a1, a a2
WHERE a1.ID < a2.ID AND ….
Verhindert, dass Paare
zweimal vorkommen
Aggregation (z.B. Wer hört wie viele Vorlesungen?)
SELECT a.name, aggr(b.ID) FROM a,b
WHERE a.ID=b.Fkey GROUP BY a.name
Choosing group
(z.B. Wer hört mehr als zwei Vorlesungen?)
SELECT a.name FROM a,b
WHERE a.ID=b.Fkey GROUP BY a.name HAVING …
Datenbanken, WS 12/13 Kapitel 5: SQL2
41
Doppelte Aggregation
Double aggregation (
z.B. Wie viele Vorlesungen hören Studenten im Durchschnitt?)
Warum doppelt?
Ich muss erst die Vorlesungen zählen und dann den
Durchschnitt bestimmen.
Einfachste Variante:
SELECT aggr1(aggr2(b.ID))
a,b WHERE a.ID=b.Fkey
Leider nicht FROM
durch den
GROUP BY a.ID
Standard vorgesehen!!!
System unabhängig:
SELECT aggr1(t.temp) FROM
(SELECT aggr2 AS temp FROM a,b WHERE a.ID=b.Fkey
GROUP BY a.ID) t
Datenbanken, WS 12/13 Kapitel 5: SQL2
42
Choice-by-aggregate
(z.B. Welche Vorlesungen haben die meisten SWS?)
SELECT a.name, x FROM a WHERE x = (
SELECT aggr(x) FROM a)
Allerdings müssen beide a exakt identisch sein (inkl. WHERE,
GROUP BY und HAVING Klauseln), sonst kann es zu einem
Mismatch kommen. Bei komplexen a kann es sich daher
lohnen eine View anzulegen (Kap. 8)
Datenbanken, WS 12/13 Kapitel 5: SQL2
43
Constraints and Views...
..to be continued.
Datenbanken, WS 12/13 Kapitel 5: SQL2
44