Folien zu SQL

Grundlagen von SQL
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Grundlegende Eigenschaften von SQL
!  Natürlichsprachlich
!  Deklarativ
!  Mengenorientiert
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Abstraktion
In SQL-Anweisungen steht die Frage „Was?“ und nicht die
Frage „Wie?“ im Mittelpunkt. So ist auch ein hohes Maß an
Datenunabhängigkeit gesichert.
SQL-Anweisungen können vom RDBMS ohne Eingriff des
Anwenders ausgeführt werden:
„... it is likely that more SQL programs are executed in
parallel than in any other programming language. However,
most SQL programmers do not write explicitly parallel
code ...“
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Abschlossenheit
Die select-Anweisung ist abgeschlossen:
Der Operand und das Ergebnis sind beide Tabellen.
Somit ist auch der folgende Code syntaktisch korrekt
create table personen(
id int generated always as identity primary key,
name varchar(20),
freund char(1) check (freund in ('J','N'))
);
select name
from (select id, name
from personen
where freund='J‘)
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Die Bestandteile von SQL
Data Data Definition Language (DDL):
Anweisungen, die mit create, drop und alter eingeleitet
werden.
Data Control Language (DCL):
Die Anweisungen grant und revoke vergeben und
entziehen Rechte
Data Manipulation Language (DML):
Die Anweisungen update, delete, insert und select.
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Die update-Anweisung
update personen
set name = upper(name)
where freund = 'J'
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Die delete-Anweisung
delete from personen
where freund!='J'
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Die insert-Anweisung
insert into personen(name, freund)
values ('Tick', 'N'),('Trick', 'N'),('Track', 'N‘)
insert into freunde
select id, name from personen
where freund ='J‘;
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Die insert-Anweisung
Die letzte Variante ermöglicht eine rasche Erzeugung von
Massendaten:
create table big(
id int generated always as identity primary key,
name varchar(20)
);
insert into big(name) values('Donald');
Was passiert, wenn die folgende Anweisung mehrfach
ausgeführt wird?
insert into big(name)
select b1.name from big b1, big b2
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Codds 7. Regel: Mengenorientiertes Einfügen, Ändern
und Löschen von Daten
Ein RDBMS muss mengenorientierte Operationen zum
Einfügen, Ändern und Löschen von Daten zur Verfügung
stellen. Das bedeutet, dass das RDBMS die Daten der
Datenbank in Form von Mengen bereitstellt, die aus
mehreren Datensätzen aus verschiedenen Tabellen
zusammengesetzt sind. Insbesondere ermöglicht ein
RDBMS es, derartige Mengen zu ändern, zu löschen oder
einzufügen.
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Der Standard
SQL ist über die International Standards Organisation (ISO)
standardisiert. Die Hersteller orientieren sich an diesem
Standard.
Überraschend ist es, dass es namhafte RDBMS gibt, bei
denen trotzdem
!  Blockkommentare unbekannt sind;
!  Schlüsselworte groß geschrieben werden müssen;
!  Primärschlüssel nur in Verbindung mit einem expliziten
not null definiert werden können.
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Alles klar?
!  SQL ist natürlichsprachlich.
!  Die SQL-Anweisungen sind deklarativ: Nicht die Frage
„Wie?“, sondern die Frage „Was?“ steht im Vordergrund.
!  Abfragen sind in SQL abgeschlossen. Das Ergebnis einer
select-Anweisung kann in anderen SQL-Anweisungen
wie eine Tabelle genutzt werden.
!  Die SQL-Anweisungen werden in die drei Kategorien
DDL, DCL und DML unterteilt.
!  SQL ist standardisiert; die Hersteller der RDBMS
orientieren sich am Standard.
Prof. Dr. Stefan Böcker – SQL: Grundlagen von SQL
Einfache select-Anweisungen
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
Die Projektion
In einer select-Anweisung können die Spaltennamen in
der genannten Projektion ausgewählt
werden.
10.1 Viele Möglichkeiten, um Spalten zu beschreiben
Tabelle 10.2: Ergebnis der Abfrage aus Listing 10.1
select titel, preis
from alben
titel
preis
Gespenster Geschichten
Asterix der Gallier
Asterix und Kleopatra
Asterix als Legionär
Die Trabantenstadt
Der große Graben
Der geheimnisvolle Stern
Tim und der Haifischsee
Das Kriminalmuseum
Das Meisterwerk
1.20
2.80
2.80
3.00
3.80
5.00
null
null
8.80
8.80
abhängig, mit dem wir auf die Datenbank zugreifen. In einigen S
wird null als Text angezeigt, andere verwenden dazu eine leere
Strich.
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
Wir werden noch mit Abfragen arbeiten, in denen wir es mit Dat
Tabellen zu tun haben. Dann ist es gelegentlich erforderlich, z
Die Projektion
Alternativ kann zusätzlich zum Spaltennamen noch der
Tabellennamen qualifiziert werden.
select alben.titel, alben.preis
from alben
Mit * werden werden verkürzt alle Spalten gewählt:
select *
from alben
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
Mit Spalten Rechnen
174
10 Einfache selec
In der Projektion können auch arithmetische Ausdrücke
Die Preise
in derpassiert:
Datenbank bleiben unverändert, sie werden nur
auftauchen. Beachten Sie, was mit
null
le 10.3 – erhöht dargestellt.
Tabelle 10.3: Ergebnis der Abfrage aus Listing 10.4
select titel, preis*1.05
from alben
titel
preis * 1.05
Gespenster Geschichten
Asterix der Gallier
Asterix und Kleopatra
Asterix als Legionär
Die Trabantenstadt
Der große Graben
Der geheimnisvolle Stern
Tim und der Haifischsee
Das Kriminalmuseum
Das Meisterwerk
1.2600
2.9400
2.9400
3.1500
3.9900
5.2500
null
null
9.2400
9.2400
In select-Anweisungen können wir in der Projektion arithmetis
verwenden. Wie wir auch dem Ergebnis der Abfrage aus Listing 1
ist das Resultat einer Operation gleich null, wenn einer der Oper
Im Ergebnis sehen wir auch, dass der Text preis * 1.05 als Spa
für den erhöhten Preis vom RDBMS verwendet wird. Wenn wir n
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
in der folgenden Anweisung einen Spaltennamen vergeben, küm
RDBMS darum.
Aliasing
Mit dem Schlüsselwort as können Sie Spalten und Tabellen
unter einem anderen Namen ansprechen.
as kann sich auch aus dem Kontext ergeben.
select titel, preis*1.05 as neuerpreis
from alben
select a.jahr
from alben a
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
from alben
Dubletten eliminieren Im Ergebnis (siehe Tabelle 10.5) fin
Tabelle 10.5: Ergebnis der Abfrage
jahr
select distinct jahr
from alben
1985
1986
1968
1972
1973
1974
1980
null
Das Schlüsselwort distinct kan
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
Dubletten eliminieren
176
select distinct reihe, jahr
from alben
where reihe='Asterix'
In den Beispieldaten gibt es zwei Aster
jektion aus Listing 10.8 berücksichtigt n
Tabelle 10.6: Ergebnis der Abfrage aus L
Obwohl doppelt vorhanden, ist
(Asterix, 1968) nur einmal im
Ergebnis vorhanden.
reihe
jahr
Asterix
Asterix
Asterix
Asterix
1974
1968
null
1980
10.2
In
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
Datensätze mit wher
den meisten Fällen brauchen wir ga
dern nur einen Teil. Wir können hier –
10.2 Datensätze mit where auswählen
In den meisten Fällen brauchen wir gar nicht alle Datensätze einer Tabelle, sondern nur einen Teil. Wir können hier – wie bei der Relationenalgebra – mit Selektionen (siehe etwa Abschnitt 4.5) arbeiten, für die es in der select-Anweisung
die where-Komponente gibt:
Prädikate
Listing 10.9: Alle Alben, die 1968 erschienen sind
select *
select
*
from alben
where jahr=1968
from
alben
In Tabelle
10.7 jahr=1968
sehen wir, dass das Ergebnis aus allen Datensätzen besteht, die
where
dem Prädikat jahr=1968 genügen.
Tabelle 10.7: Ergebnis der Abfrage aus Listing 10.9
reihe
titel
band
preis
jahr
Asterix
Asterix
Asterix der Gallier
Asterix und Kleopatra
1
2
2.80
2.80
1968
1968
Anders als bei der Relationenalgebra müssen wir beim praktisch orientierten SQL
berücksichtigen, dass das Prädikat auch den Wert null annehmen kann. Ein Datensatz gehört genau dann zum Ergebnis einer select-Anweisung, wenn das
Prädikat den Wert true hat. Es folgt auch, dass wir nicht alle Datensätze erhalten,
wenn wir die where-Komponente in Listing 10.9 durch where jahr != 1968
ersetzen. Hier werden nur die Datensätze gefunden, für die jahr = 1968 den
Wert false hat. Die Datensätze, für die das Erscheinungsjahr unbekannt, also
null ist, gehören nicht dazu. Alle Datensätze erhalten wir mit der folgenden Anweisung:
Mit Hilfe von Prädikaten in der where-Komponente
können Sie die Ergebnismenge einschränken.
Nur Datensätze, für die das Prädikat den Wert true hat,
gehören zur Ergebnismenge
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
null nicht vergessen
Eine ungewöhnliche Möglichkeit, alle Datensätze
auszuwählen. Reicht ein einfacheres Prädikat?
select *
from alben
where jahr=1968 or
jahr!=1968 or
jahr is null
Prädikate können mit logischen Operatoren verknüpft
werden.
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
Listing 10.10: Eine ungewöhnliche Möglichkeit, alle Alben zu finden
select *
from alben
where jahr=1968 or
jahr!=1968 or
jahr is null
Natürlichsprachlichkeit
An diesem Beispiel sehen wir, dass
wir mehrere Prädikate mit logischen Operatoren wie not, and und or verknüpfen können;
Prädikate,
die null beinhalten,
nicht mitkönnen
dem Operator
= formulieren
dürfen.natürlicher
not
preis
is null
wir
alternativ
Wenn wir alle Datensätze kennen wollen, zu denen ein Preis bekannt ist, nehmen
schreiben:
wir dazu die folgende Abfrage:
Listing 10.11: null in Prädikaten
select *
from alben
preis is not
null
Daswhere
Prädikat ist natürlichsprachlicher
als seine
– syntaktisch ebenfalls korrekte –
select *
from alben
where preis is not null
Alternative not preis is null. Die Ergebnisse finden wir in Tabelle 10.8.
Tabelle 10.8: Ergebnis der Abfrage aus Listing 10.11
reihe
titel
band
preis
jahr
Gespenster Geschichten
Asterix
Asterix
Asterix
Asterix
Asterix
Franka
Franka
Gespenster Geschichten
Asterix der Gallier
Asterix und Kleopatra
Asterix als Legionär
Die Trabantenstadt
Der große Graben
Das Kriminalmuseum
Das Meisterwerk
1
1
2
10
17
25
1
2
1.20
2.80
2.80
3.00
3.80
5.00
8.80
8.80
1974
1968
1968
null
1974
1980
1985
1986
Neben
können
wir –
auch
ausschließlich
Attributnamen zur FormulieProf.Literalen
Dr. Stefan
Böcker
SQL:
Einfache select-Anweisungen
rung von Prädikaten nutzen:
Prädikate
Spaltennamen sind ebenso wie Literale in Prädikaten
möglich.
select *
178 from alben
10 Einfache select-Anweisungen
where titel=reihe
In Tabelle 10.9 sehen wir, dass unser Datenbestand nur einen Datensatz enthält,
in dem die beiden Attribute titel und reihe den gleichen Wert haben.
Tabelle 10.9: Ergebnis der Abfrage aus Listing 10.12
reihe
titel
band
preis
jahr
Gespenster Geschichten
Gespenster Geschichten
1
1.20
1974
Ein weiteres Beispiel für den Einsatz von logischen Operatoren sehen wir hier:
Listing 10.13: Alle Asterixalben, die weniger als 6.0 kosten.
select *
fromDr.
alben
Prof.
Stefan Böcker – SQL: Einfache select-Anweisungen
where reihe='Asterix' and preis<6.00
10.3 Einige nützliche Operatoren
179
Der between-Operator
So können wir anstatt
Listing 10.14: Alle Alben, die zwischen 5.0 und 6.0
select *
select
*
from alben
where alben
preis >= 5.00 and preis <= 10.00
from
etwas
einfacher
mit between
arbeiten:and preis <= 10.00
where
preis
>= 5.00
Listing 10.15: between ermöglicht natürlichsprachliche Prädikate
select *
select
*
from alben
where preis
between 5.00 and 10.00
from
alben
Daswhere
Ergebnis entnehmen
wir Tabelle 10.11.
Die mit
between
formulierte Anweipreis between
5.00
and
10.00
sung ist zwar nicht kürzer, aber etwas klarer.
Tabelle 10.11: Ergebnis der Abfrage aus Listing 10.15
reihe
titel
band
preis
jahr
Asterix
Franka
Franka
Der große Graben
Das Kriminalmuseum
Das Meisterwerk
25
1
2
5.00
8.80
8.80
1980
1985
1986
Mit
dem
in verbessern
wir die Lesbarkeit
unserer Anweisungen dageProf.
Dr.Operator
Stefan Böcker
– SQL: Einfache
select-Anweisungen
gen deutlich. So können wir
Der in-Operator
select *
from alben
where reihe = 'Asterix' or
reihe = 'Tim und Struppi'
Ist äquivalent zu
select *
from alben
where reihe in ('Asterix', 'Tim und Struppi')
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
eingesetzt, in Kapitel 14 lernen wir Verwendungsmöglichkeiten für verschachtelte
Abfragen kennen.
Sehr nützlich ist auch der Mustervergleich von Texten in Prädikaten. Wenn wir
uns daran erinnern, dass einer der Titel aus der Asterixreihe etwas mit einer Stadt
zu tun hatte, können wir ihn leicht finden (siehe Listing 10.18).
Mustervergleich
Listing 10.18: Alle Alben mit stadt im Titel
select *
from
alben*
select
where titel like '%stadt%'
from alben
Daswhere
Zeichen titel
% repräsentiert
dem Muster '%stadt%' eine beliebige Zeichenlikein'%stadt%'
kette. Dies entspricht dem Ergebnis aus Tabelle 10.13.
Tabelle 10.13: Ergebnis der Abfrage aus Listing 10.18
reihe
titel
band
preis
jahr
Asterix
Die Trabantenstadt
17
3.80
1974
Jeder Titel, der also den Text stadt enthält, gehört somit zum Ergebnis der AbVorsicht:
den
like-Operator
vergessen.
Für
frage.
Gelegentlich
verwendet
man zwarnicht
die Syntax
für die Musterbeschreibung,
vergisst
aber den like-Operator.
Wenndas
das RDBMS
Prädikat titel='%stadt%'
lautitel='%stadt%‘
findet
nur Datensätze,
tet, in
findet
das RDBMS
auchgleich
nur Datensätze,
in denen
der Titel
gleich dem Text
denen
der Titel
dem Text
%stadt%
ist.
%stadt% ist. Mit unseren Beispieldaten ergibt sich so ein leeres Ergebnis. Der
Vergleich mit dem Muster findet nur in Verbindung mit dem Schlüsselwort like
Dr. Stefan
– SQL:
Einfache
select-Anweisungen
statt.Prof.
Neben
demBöcker
%-Zeichen
gibt
es noch
_ als Platzhalter für ein einzelnes, beliebiges Zeichen. Wollen wir beispielsweise alle Titel finden, deren zweiter Buchstabe
Mustervergleich
_ ist Platzhalter für ein einzelnes, beliebiges Zeichen.
Wollen wir beispielsweise alle Titel finden, deren zweiter
Buchstabe ein s ist, verwenden wir das Prädikat
titel like '_s%'
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
Listing 10.19: Ergebnisse sortieren
select distinct reihe
from alben
order by reihe
Sortieren
Das Ergebnis (siehe Tabelle 10.14) enthält die Namen der Albumreihen in alphabetisch aufsteigender Reihenfolge. Dubletten sind durch das Schlüsselwort
select ausgemustert
distinct worden.
reihe Wie die Buchstaben angeordnet sind, ist ebendistinct
sofrom
wie diealben
Einordnung von null vom verwendeten RDBMS abhängig. Der SQLStandard macht hierzu keine Aussage. Oftmals ist die Reihenfolge konfigurierbar.
order
by reihe
Das
alles entspricht
natürlich ganz und gar nicht dem relationalen Modell: Hier
sind weder Dubletten noch eine Reihenfolge vorgesehen.
Tabelle 10.14: Ergebnis der Abfrage aus Listing 10.19
reihe
Asterix
Franka
Gespenster Geschichten
Tim und Struppi
Selbstverständlich können wir die Daten auch in absteigenden Reihenfolge sortieren. Wir benutzen dazu das Schlüsselwort desc:
Listing
10.20:Böcker
Sortierung
absteigender
Reihenfolge
Prof.
Dr. Stefan
– SQL:inEinfache
select-Anweisungen
select distinct reihe
Franka
Gespenster Geschichten
Tim und Struppi
Sortieren
Selbstverständlich können wir die Daten auch in absteigenden Reihenfolge sortieren. Wir benutzen dazu das Schlüsselwort desc:
Listing 10.20: Sortierung in absteigender Reihenfolge
select
distinct
select distinct
reihereihe
from alben
from
alben
order by reihe desc
order by reihe desc
In Tabelle 10.15 beobachten wir die umgekehrte Sortierung.
Tabelle 10.15: Ergebnis der Abfrage aus Listing 10.20
reihe
Tim und Struppi
Gespenster Geschichten
Franka
Asterix
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
from alben
where reihe like 'Asterix%' and jahr = 1968
Alles in einen Topf
In beiden Fällen bestehen die Ergebnisse aus einer Spa
mit Texten. Beide Ergebnisse können jetzt zu einem ein
werden:
Listing 10.22: Die Vereinigung zweier Tabellen
select band, titel
select band, titel
from alben
from alben
where band=1
where band=1
union
union
select jahr, reihe
select jahr, reihe
from alben
where reihe like 'Asterix%' and jahr
from alben
Ergebnis
sehen
in Tabelle 10.17.
where reihe like 'Asterix%'Das
and
jahr
= wir
1968
= 1968
Tabelle 10.17: Ergebnis der Abfrage aus Listing 10.22
Was fällt am Ergebnis auf?
band
titel
1968
1
1
1
1
Asterix
Der geheimnisvolle Stern
Gespenster Geschichten
Asterix der Gallier
Das Kriminalmuseum
Wir beobachten, dass
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
die Spaltentitel für die Vereinigung von der ersten
Alles in einen Topf
Warum wird die folgende Anweisung zurückgewiesen?
select band, titel
from alben
union
select jahr, preis
from alben
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
select jahr, preis
from alben
Unsere Abfrage aus Listing 10.22 wirkt gekünstelt und ist sicher kein praktischer
Anwendungsfall. union kann jedoch sehr hilfreich sein. Wenn wir zwei Comicsammlungen – sagen wir in den Tabellen alben_siggi und alben_barabass
– haben, können wir beide zusammenlegen und die Alben trotzdem noch voneinander unterscheiden.
Raffiniert
Ein etwas lebensnäheres Beispiel.
Listing 10.23: Zwei Comicsammlungen werden zusammengelgt
select 'Siggis Album' besitzer, reihe, titel
from alben_siggi
select
'Siggis Album' besitzer,
union all
from
alben_siggi
select 'Barrabas
Album', reihe, titel
from
union
all
alben_barabass
reihe, titel
select 'Barrabas Album‘ besitzer, reihe, titel
Das Ergebnis sieht dann – je nach Datenbestand der Ausgangstabelle – wie in
Tabelle
10.18 alben_barabass
aus und kann die Grundlage für weitere interessante Auswertungen
From
sein.
Tabelle 10.18: Ergebnis der Abfrage aus Listing 10.23
besitzer
reihe
titel
Siggis Album
Siggis Album
Barrabas Album
Barrabas Album
...
Franka
Franka
Asterix
Asterix
Das Kriminalmuseum
Das Meisterwerk
Asterix der Gallier
Asterix und Kleopatra
band
preis
jahr
Mit dieser Grundausstattung an Werkzeugen zum Formulieren von selectProf. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
Anweisungen können wir schon eine Menge anfangen. Möglicherweise haben die
meisten Abfragen, die an ein RDBMS gerichtet werden, diese Grundform. Seine
Alles klar?
!  Die ausgewählten Spalten einer Tabelle werden in der
select-Komponente definiert.
!  Die select-Komponente kann auch arithmetische
Ausdrücke und Funktionen enthalten.
!  Spaltennamen sollten mit Hilfe eines Alias ihrer Tabellen
qualifiziert werden.
!  Mit distinct lassen sich Dubletten aus dem Ergebnis
entfernen.
!  Das Ergebnis kann mit Hilfe von Prädikaten in der whereKomponente eingeschränkt werden.
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
Alles klar?
!  Prädikate werden mit Hilfe der Booleschen Operationen
and und or verknüpft.
!  Operatoren wie in oder between erhöhen die Lesbarkeit
der Anweisungen.
!  Mit Hilfe des Operators like ist ein Musterabgleich für
Texte möglich.
!  Das Ergebnis einer Abfrage kann mit Hilfe von order by
sortiert werden.
!  Wenn die Ergebnisse von zwei Abfragen eine ähnliche
Struktur haben, können sie mit union und union all
vereinigt werden.
Prof. Dr. Stefan Böcker – SQL: Einfache select-Anweisungen
Funktionen in
SQL-Anweisungen
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Wozu Funktionen?
In SQL dienen sie etwa
!  der Formulierung von Integritätsregeln wie CheckConstraints,
!  der Aufbereitung der Ergebnisse von selectAnweisungen oder
!  der Formulierung von Prädikaten für Selektionen.
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Skalare Funktionen
skalarer
Wert
skalare
Funktion
skalarer
Wert
Funktionen, deren Argumente skalare (atomare) Werte sind,
heißen skalare Funktionen.
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Aggregatfunktionen
skalarer Wert
skalarer Wert
...
Aggregatfunktion
skalarer
Wert
skalarer Wert
!  Mehrwertige Daten sind die Argumente von
Aggregatfunktionen
!  Ihr Ergebnis ist hingegen wieder ein skalarer Wert.
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Eigene Funktionen
Es gibt die beiden folgenden Möglichkeiten um eigene
Funktionen zu definieren:
!  Stored Procedures: Viele Datenbanksysteme stellen
eine eigene Programmiersprache zur Verfügung, die auch
als Stored Procedure Language (SPL) bezeichnet wird.
Mit Hilfe dieser Sprache können eigene Funktionen
definiert werden, die dann das RDBMS übersetzt und
verwaltet.
!  User Defined Functions (UDF): Funktionen, die bereits
in einer Sprache wie C oder Java entwickelt wurden,
können in das RDBMS integriert und als Funktion aus
SQL-Anweisungen heraus aufgerufen werden.
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
11.1 Funktionen zur Textverarbeitung
Funktionen in Projektionen
Eine einfache Funktion zur Textverarbeitung ist upper. In den beiden folgenden
Beispielen für Abfragen, die wieder auf dem Datenbestand aus Tabelle 10.1 operieren, sehen wir exemplarisch, wie wir Funktionen sowohl für Projektionen als
auch für Selektionen nutzen können.
Listing 11.1: Funktionen können für die Projektion genutzt werden
select
distinct upper(reihe)
select distinct upper(reihe)
from alben
from
alben
Das Ergebnis finden wir in der folgenden Tabelle.
Tabelle 11.1: Ergebnis der Abfrage aus Listing 11.1
upper(reihe)
GESPENSTER GESCHICHTEN
ASTERIX
FRANKA
TIM UND STRUPPI
In der nächsten Anweisung haben wir das Prädikat so formuliert, dass unabhängig von der Groß- oder Kleinschreibung alle Asterixalben ermittelt werden.
Listing 11.2: Funktionen können zur Selektion genutzt werden
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
select titel
Funktionen in Prädikaten
190
select titel
from alben
where upper(reihe)='ASTERIX'
11 Funktionen in SQL-Anweisungen
Tabelle 11.2: Ergebnis der Abfrage aus Listing 11.2
titel
Asterix der Gallier
Asterix und Kleopatra
Asterix als Legionär
Die Trabantenstadt
Der große Graben
WirProf.
haben
bereits
am–Anfang
des
Kapitels gesehen, dass die Funktion position
Dr. Stefan
Böcker
Funktionen
in SQL-Anweisungen
einen Wert größer 0 liefert, wenn das zweite Argument der Funktion im ersten
Asterix und Kleopatra
Asterix als Legionär
Die Trabantenstadt
Funktionen
in Prädikaten
Der große Graben
titelam Anfang des Kapitels gesehen, dass die Funktion positio
Wirselect
haben bereits
einen
Wert
größer 0 liefert, wenn das zweite Argument der Funktion im erste
from
alben
Argument
enthalten ist. Das Ergebnis
sehen wir in Listing 11.3.
where 0<position(titel,
'Trabanten')
Tabelle 11.3: Ergebnis der Abfrage aus Listing 11.1
titel
Die Trabantenstadt
Mit Hilfe des Operators like zum Mustervergleich können wir eine äquivalent
aber elegantere Anweisung formulieren.
Wie kann man die Anweisung äquivalent, aber einfacher
select titel
formulieren?
from alben
where reihe like '%Trabanten%'
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
titel
Asterix der Gallier
Asterix und Kleopatra
Es
müssen
nicht
Asterix
als Legionär
Die Trabantenstadt
Der große Graben
immer Funktionen sein
select titel
Wir haben bereits am Anfang des Kapitels gesehen, dass die Funktion positio
from alben
einen Wert größer 0 liefert, wenn das zweite Argument der Funktion im erst
where titel like '%Trabanten%'
Argument enthalten ist. Das Ergebnis sehen wir in Listing 11.3.
Tabelle 11.3: Ergebnis der Abfrage aus Listing 11.1
titel
Die Trabantenstadt
Mit Hilfe des Operators like zum Mustervergleich können wir eine äquivalen
aber elegantere Anweisung formulieren.
select titel
from alben
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
11.2 Funktionen für Zahlen
191
Funktionen für Texte
Listing 11.3: Textverkettung mit dem ||-Operator
select reihe || ' Band ' || band || ': ' || titel
select
from
alben concat(reihe, ' Band ', band,
where
fromreihe='Franka'
alben
': ', titel )
In beiden
Fällen
bekommen wir das folgende Ergebnis:
where
reihe='Franka'
Tabelle 11.4: Ergebnis der Abfrage aus Listing 11.3
(((reihe || ’ Band ’) || band) || ’: ’) || titel
Franka Band 1: Das Kriminalmuseum
Franka Band 2: Das Meisterwerk
Eleganter ist hier der Einsatz des ||-Operators:
11.2 Funktionen für Zahlen
select
reihe
|| gehören
' Band
' || bandbekannte
|| ':
' ||
Zu den
Funktionen
für Zahlen
selbstverständlich
Vertreter
austitel
der Mathematik wie sin, log oder power. Bei der Arbeit mit den Datenbanken
from alben
von natur- oder ingenieurwissenschaftlichen Anwendungen treten diese Funktionen
immer reihe='Franka'
wieder mal auf. In der Praxis haben wir es aber meistens mit bewhere
triebswirtschaftlich orientierten Anwendungen zu tun. Hier finden wir FunktioStefanvon
Böcker
– Funktionen
SQL-Anweisungen
nen Prof.
zum Dr.
Runden
Zahlen
vom Typindecimal:
Zu den Funktionen für Zahlen gehören selbstverständlich bekannte Vertreter aus
der Mathematik wie sin, log oder power. Bei der Arbeit mit den Datenbanken
von natur- oder ingenieurwissenschaftlichen Anwendungen treten diese Funktionen immer wieder mal auf. In der Praxis haben wir es aber meistens mit betriebswirtschaftlich orientierten Anwendungen zu tun. Hier finden wir Funktionen zum Runden von Zahlen vom Typ decimal:
Funktionen für Zahlen
Listing 11.4: Runden von decimal-Zahlen
select titel, round(preis,1), round(preis, 0)
from alben
where
reihe='Asterix'
select
titel, round(preis,1),
round(preis, 0)
Wie from
in Tabelle
11.5 dargestellt, werden die Titel zusammen mit den gerundeten
alben
Preisen angezeigt. Über das zweite Argument von round regeln wir, auf wie viele
where
reihe='Asterix'
Stellen
wir runden
wollen. Positive Zahlen entsprechen den Nachkommastellen;
Zahlen, die kleiner oder gleich null sind, entsprechen den Vorkommastellen.
Tabelle 11.5: Ergebnis der Abfrage aus Listing 11.4
titel
round(preis, 1)
round(preis, 0)
Asterix der Gallier
Asterix und Kleopatra
Asterix als Legionär
Die Trabantenstadt
Der große Graben
2.8
2.8
3.0
3.8
5.0
3.0
3.0
3.0
4.0
5.0
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Wenn einer der Operanden eines arithmetischen Ausdrucks null
ist, dann ist auch das Ergebnis null.
Funktionen für Zahlen
Die Funktion truncate rundet nicht, sondern entfernt einfach einen Teil der Stellen einer Zahl. Die Anweisung
Listing 11.5: Zahlen mit truncate abschneiden
select
titel,preis,truncate(preis,1),truncate(preis, 0)
select
titel, preis, truncate(preis,1), truncate(preis, 0)
from
alben
from alben
where
where reihe='Asterix'
reihe='Asterix'
liefert also das Ergebnis aus Tabelle 11.6.
Tabelle 11.6: Ergebnis der Abfrage aus Listing 11.5
titel
preis
truncate(preis, 1)
truncate(preis, 0)
Asterix der Gallier
Asterix und Kleopatra
Asterix als Legionär
Die Trabantenstadt
Der große Graben
2.80
2.80
3.00
3.80
5.00
2.8
2.8
3.0
3.8
5.0
2.0
2.0
3.0
3.0
5.0
Wenn wir das Ergebnis einer Funktion nur für einige explizite Werte wissen wollen, brauchen wir keine Tabelle und verzichten auf die from-Komponente:
Prof.
Dr. Stefan
Böcker – Funktionen
SQL-Anweisungen -1)
select
truncate(47.11,
0), in
truncate(47.11,
Nur Funktionen
Mit den folgenden Anweisungen ist es möglich
Funktionen etwa für Tests auch ohne Daten aufzurufen.
select truncate(47.11, 0), truncate(47.11, -1)
call truncate(47.11, 0)
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Funktionen für Zeitangaben
create table timedata(
name varchar(20) primary key,
d date default current_date(),
t time default current_time(),
ts timestamp default current_timestamp()
)
!  Mit Hilfe von Funktionen wie current_date() kann
das aktuelle Datum als Standardwert genutzt werden.
!  Zu den Funktionen gibt es auch natürlichsprachliche
Varianten wie current date.
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
d date default current_date(),
t time default current_time(),
ts timestamp default current_timestamp()
)
Standardzeiten nutzen
Die mit default markierten Standardwerte (siehe 5.14) ergeben sich dabei aus
Funktionen, die uns – wie ihre Namen schon sagen – die aktuellen Zeitangaben
liefern. Wir können sie sogar noch suggestiver in der Form current date, also
ohne Unterstrich und Klammern, schreiben.
insert into timedata values('Donald',
Beim Einfügen der Daten können wir explizite Werte angeben, die von den Stan'1934-06-09','12:00:00','1934-06-09
12:00:00.0');
dardwerten
abweichen:
insert
into
timedata(name) values('Baby-Donald');
insert into
timedata
values('Donald','1934-06-09','12:00:00','1934-06-09 12:00:00.0');
insert into timedata(name)
values('Baby-Donald');
select *
from timedata
select *
Beim zweiten insert werden die default Werte eingefügt:
from timedata
Tabelle 11.7: Nützliche Datumsfuntkionen als Standardwerte
name
d
t
ts
Donald
Baby-Donald
1934-06-09
2011-07-09
12:00:00
07:33:07
1934-06-09 12:00:00.0
2011-07-09 07:33:07.104
WirProf.
schauen
weitere
Anweisungen
an,
in denen einige Funktionen beispielhaft
Dr. Stefan
Böcker
– Funktionen in
SQL-Anweisungen
verwendet werden.
Weitere Datumsfunktionen
An welchem Wochentag wurde Donald geboren?
select dayname(d)
from timedata
where name='Donald'
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Hinweis:
Wenn null Argument einer skalaren Funktionen
ist, dann ist ihr Ergebnis ebenfalls null.
Wenn einer der Operanden eines arithmetischen
Ausdrucks null ist, dann ist auch das Ergebnis
null.
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Aggregatfunktionen
select
avg(preis),min(preis),max(preis),sum(preis),count(preis)
from alben
Ihren Namen entsprechend ermitteln die Funktionen die
folgenden Werte:
!  avg: den Durchschnittspreis
!  min: den kleinsten Preis
!  max: den höchsten Preis
!  sum: die Summe über alle Preise
!  count: die Anzahl der Preise in der Tabelle
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Ihren Namen entsprechend ermitteln die Funktionen die folgenden Werte:
avg: den Durchschnittspreis
Aggregatfunktionen
min: den kleinsten Preis
max: den höchsten Preis
sum: die Summe über alle Preise
count: die Anzahl der Preise in der Tabelle
Da jede Aggregatfuntion genau einen Wert liefert, besteht das Ergebnis aus einer
Zeile:
Tabelle 11.9: Ergebnis der Abfrage aus Listing 11.7
avg(preis)
min(preis)
max(preis)
sum(preis)
count(preis)
4.525
1.20
8.80
36.20
8
Ein RDBMS kann sehr viele skalare Funktionen zur Verfügung stellen, die Anzahl
von Aggregatfunktionen ist dagegen überschaubar. Neben den fünf aufgezählten
gibt es gelegentlich noch statistische Aggregatfunktionen etwa zur Berechnung
der Varianz oder der Standardabweichung.
Wenn
wir fällt
einer einfachen
Was
auf? Funktionen null-Werte übergeben, ist das Ergebnis
immer null. Auch bei Vergleichsoperationen wie < oder > oder arithmetischen
Operationen ergibt sich null als Ergebnis. Eine Ausnahme bilden logische Operatoren wie or. Hier gilt, wie wir in Abschnitt 10.2 gesehen haben:
null or true = true
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Wir haben beobachtet, dass Funktionen und arithmetische Operationen null liefern, sobald einer der beteiligten Werte null ist. Da unsere Beispieldaten in der
Hinweis:
Aggregatfunktionen ignorieren null-Werte.
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Listing 11.8: Die zwei Gesichter von count
select avg(preis), sum(preis)/count(*)
from alben
Aggregatfunktionen und null
Von der avg-Funktion werden null-Werte ignoriert. Bei der zweiten Variante
zur Durchschnittsberechnung werden die null-Werte nur im Zähler ignoriert. Die
Funktion
so dass null hier keinerlei Relevanz zu
*) zählt Datensätze,
selectcount(
avg(preis),
sum(preis)/count(*)
kommt. Diese kleinen Unterschiede mögen banal erscheinen, in der Praxis sind
from alben
sie aber eine nicht zu unterschätzende Fehlerquelle.
Tabelle 11.10: Ergebnis der Abfrage aus Listing 11.8
avg(preis)
sum(preis) / count(*)
4.525
3.62
Wenn wir nicht nur null, sondern auch Dubletten bei der Aggregatbildung igno
rieren wollen, hilft uns – wie bei einem einfachen select – das Schlüsselwor
Wie erklären
distinct
weiter:Sie sich die abweichenden Werte?
select count(distinct reihe)
from alben
Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
H2Prof.
findet,
wie erwartet, vier verschiedene Comicreihen.
Alles klar?
!  Parameter von skalaren Funktionen sind atomar.
!  Parameter von Aggregatfunktionen sind mehrwertig.
!  In den meisten RDBMS können eigene Funktionen
definiert werden.
!  Der Katalog von Funktionen eines RDBMS ist
herstellerspezifisch. Viele RDBMS haben ihre eigenen
Funktionen.
!  Skalare Funktionen liefern null als Ergebnis, wenn ein
Parameter null ist.
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Alles klar?
!  Mit Funktionen zur Texverarbeitung können
beispielsweise Texte verkettet oder in Groß- oder
Kleinbuchstaben umgewandelt werden.
!  Funktionen für Zahlen können neben den gängigen
mathematischen Operationen auch Zahlen runden oder
verkürzen.
!  Funktionen für Zeitangaben ermöglichen eine
Kalenderarithemtik. Einzelne Bestandteile einer
Zeitangabe können ermittelt werden.
!  Die gängigen Aggregatfunktionen sind count, sum, min,
max und avg.
!  Aggregatfunktionen ignorieren null-Werte.
Prof. Dr. Stefan Böcker – Funktionen in SQL-Anweisungen
Daten zusammenfassen
Prof. Dr. Stefan Böcker – SQL: Daten zusammenfassen
Eine Überraschung
Wie viele Bände gibt es in jeder Reihe ?
select reihe, count(band)
from alben
Auch wenn die Abfrage ganz plausibel erscheint, meldet
H2 doch einen Fehler:
Feld "REIHE" muss in der GROUP BY Liste
sein
Column "REIHE" must be in the GROUP BY
list;
Prof. Dr. Stefan Böcker – SQL: Daten zusammenfassen
Gruppierungen sind richtig
Wie viele Bände gibt es in jeder Reihe ?
select reihe, count(band)
from alben
group by reihe
Prof. Dr. Stefan Böcker – SQL: Daten zusammenfassen
Hinweis:
Wenn in der Projektion Spalten und
Aggregatfunktionen auftreten, müssen alle Spalten
in die group by-Komponente der Abfrage
aufgenommen werden.
Prof. Dr. Stefan Böcker – SQL: Daten zusammenfassen
Mehre Spalte je Gruppe
12.1 Die group by-Komponente
201
Was ist der Durchschnittspreis aller Bände einer Reihe pro
Listing 12.3: Für jede Reihe den Durchschnittspreis pro Jahr ermitteln
Jahr?
select reihe, jahr, avg(preis)
from alben
group by reihe, jahr
select reihe, jahr, avg(preis)
Das Ergebnis sehen wir in Tabelle 12.2. Interessant ist hier auch, dass die Agfrom alben
gregatfunktion
avg den Wert null ermittelt. Auch wenn count eigentlich null
ignoriert: Für die Tim und Struppi-Alben ist kein einziger Preis bekannt, so dass
group
byWertreihe,
jahr
kein
numerischer
ermittelt werden
kann.
Tabelle 12.2: Ergebnis der Abfrage aus Listing 12.3
reihe
jahr
avg(preis)
Asterix
Asterix
Franka
Franka
Asterix
Gespenster Geschichten
Tim und Struppi
Tim und Struppi
Asterix
1968
null
1986
1985
1974
1974
1972
1973
1980
2.8
3
8.8
8.8
3.8
1.2
null
null
5
Natürlich
können
wir
group– by
auch
ohne zusammenfassen
Aggregatfunktionen verwenden:
Prof. Dr.
Stefan
Böcker
SQL:
Daten
Listing 12.4: Gruppierungen ohne Aggregatfunktion
Gruppierungen ohne Aggregatfunktion
select reihe, jahr
from alben
group by reihe, jahr
202
1
Tabelle 12.3: Ergebnis der Abfrage aus Listing 12.4
Die Wirkung ist die Gleiche
wie bei
select distinct
reihe
jahr
Asterix
Asterix
Franka
Franka
Asterix
Gespenster Geschichten
Tim und Struppi
Tim und Struppi
Asterix
1968
null
1986
1985
1974
1974
1972
1973
1980
Prof. Dr. Stefan Böcker – SQL: Daten zusammenfassen
Eine Spalte, die im group by auftritt, muss also nicht Be
Gruppierungen ohne
Eine Spalte, die im group by au
on sein. So ganz ohne die Namen
auch in Tabelle 12.4 sehen, auf de
Projektionsspalten
geschachtelten Abfragen (siehe Ka
weisen.
Tabelle 12.4: Ergebnis der Abfrage
count(band)
select count(band)
from alben
group by reihe
5
2
1
2
12.2 Die
Diese Vorgehensweise wird im Zusammenhang
mit
verschachtelten Abfragen interessant werden.
Prof. Dr. Stefan Böcker – SQL: Daten zusammenfassen
having-Kom
Wenn wir mit group by arbeiten
wir bekommen. Falls uns beispiels
bisher mindestens drei Alben ersc
Eine weitere Überraschung
Alle Reihen ermitteln, in denen mindestens drei Alben
erschienen sind:
select reihe, count(band)
from alben
where count(band)>=3
group by reihe
Die Anweisung ist syntaktisch falsch und wird nicht vom
RDBMS ausgeführt
Prof. Dr. Stefan Böcker – SQL: Daten zusammenfassen
Die having-Komponente
select reihe, count(band)
from alben
group by reihe
having count(band)>=3
reihe
Asterix
count(band)
5
Prof. Dr. Stefan Böcker – SQL: Daten zusammenfassen
Hinweis:
Prädikate, die Aggregatfunktionen enthalten,
gehören zur having-Komponente und nicht zur
where-Komponente einer Abfrage.
Prof. Dr. Stefan Böcker – SQL: Daten zusammenfassen
Selbstverständlich können auch die Spalten, nach denen wir gruppieren, ohne
Aggregate in der having-Komponente stehen, doch haben sie dann die gleiche
Eine having-Komponente ohne Aggregatfunktion
Funktion wie in der where-Komponente.
Listing 12.8: Eine having-Komponente ohne Aggregatfunktion
select reihe, count(band)
select
reihe, count(band)
from alben
from
groupalben
by reihe
havingby
reihe
not like '% %'
group
reihe
having
reihe
not alle
like
'% %'
Die
Anweisung
ermittelt
Reihen,
die aus nur einem Wort bestehen, zusammen
mit der Anzahl der jeweils vorhandenen Bände:
reihe
count(band)
Asterix
Franka
5
2
Prof. Dr. Stefan Böcker – SQL: Daten zusammenfassen
Alles klar?
!  Immer wenn Spalten und Aggregatfunktionen zusammen
in der Projektion auftauchen, müssen die Spalten in der
group by-Komponente wiederholt werden.
!  Umgekehrt müssen Spalten aus der group byKomponente nicht in der Projektion erfasst werden.
!  Die Prädikate aus der where-Komponente dürfen keine
Aggregatfunktionen enthalten.
!  Für Prädikate mit Aggregatfunktionen ist die havingKomponente vorgesehen.
Prof. Dr. Stefan Böcker – SQL: Daten zusammenfassen
Datensätze verbinden
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Mehr als eine Tabelle
Die Datensätze der Beispieltabelle werden auf die zwei
Tabellen reihen und alben verteilt:
208
13 Datensätze verbinden
Reihen
Alben
Abbildung 13.1: ER-Diagramm für Comicalben und ihre zugehörigen Reihen
Dem ER-Diagramm entnehmen wir auch, dass es Comicreihen geben kann, zu
denen kein einziges Album erfasst ist. Beim exemplarischen Datenbestand haben
wir Prof.
vonDr.
dieser
Gebrauch
gemacht: Zur Reihe „Prinz Eisenherz“ finStefanMöglichkeit
Böcker – SQL: Datensätze
verbinden
den wir in der Tabelle 13.2 kein Album. Ansonsten ist der Datenbestand zu dem
13.1 Joins mit SQL
Alle Asterix-Bände
select id
from reihen
where name='Asterix'
Das Ergebnis hat den Wert 2
Das Ergebnis besteht aus dem Wert 2. Wir ermitte
Tabelle alben, deren Attribut reihe genau diese
Listing 13.1: Die Asterixalben auf einen Blick
select titel
from alben
where reihe=2
Das Ergebnis sehen wir in Tabelle 13.3.
Tabelle 13.3: Ergebnis der Abfrage aus Listing 13.1
select titel
from alben
where reihe=2
titel
Asterix der Gallier
Asterix und Kleopatra
Asterix als Legionär
Die Trabantenstadt
Der große Graben
Die Vorgehensweise gestaltet sich bereits für zwe
anfällig; wenn die Daten über mehr als zwei Tabe
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
schlimmer.
Der Join
Informationen aus zwei Tabellen können wir
auch in einer einzigen Anweisung ermitteln:
select reihen.name, alben.titel
from alben, reihen
where alben.reihe = reihen.id
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Die Übersicht
Mit Hilfe von Aliasen können wir die Anweisung
übersichtlicher gestalten
select r.name, a.titel
from alben a, reihen r
where a.reihe = r.id
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Wozu
die Bedingung?
13.1 Joins mit SQL
id=1
name=Asterix
211
reihe=1
titel=Asterix der Gallier
reihe=1
titel=Asterix und Kleopatra
id=4
name=Franka
reihe=4
titel=Das
Kriminalmuseum
reihe=4
titel=Das Meisterwerk
Abbildung 13.2: Join mit dem Prädikat alben.reihe=reihen.id
Passende
Datensätze werden kombiniert
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Praxis gelegentlich bei Anfängern, die die Join-Bedingung gar nicht oder falsch
Joins ohne Join-Bedingung
select a.titel
from alben a, reihen r
Wie sieht das Ergebnis aus?
Ist dieser Join sinnvoll?
Wie kann man das Ergebnis bezeichnen?
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Hinweis:
Wenn ein Join zu viele Datensätze liefert, ist die
Join-Bedingung in vielen Fällen unvollständig.
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Der where-Teil
select a.titel
from alben a, reihen r
where a.reihe=r.id and r.name='Asterix'
Die Prädikate im where-Teil haben verschiedene
Qualitäten:
!  Der Teil a.reihe=r.id ist die Join-Bedingung.
!  Der Teil r.name='Asterix' ist ein Filter, der sich auf
die Spalten einer einzelnen Tabelle bezieht.
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Eine andere Syntax
Diese verschiedenen Qualtitäten werden bei der folgenden
Syntax differenziert:
select a.titel
from alben a inner join reihen r
on a.reihe=r.id
where r.name='Asterix'
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Hinweis:
Wählen Sie für Joins die Notation, die Ihnen am
meisten zusagt. Es gibt keine bessere oder
schlechtere Variante.
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Outer Joins
Wenn es Inner-Joins gibt, dann auch Outer-Joins:
In den Beispieldaten ist die Reihe ‚Prinz Eisenherz‘ erfasst,
obwohl es dazu keine Alben gibt..
Diese Reihe taucht daher im Ergebnis der folgenden
Abfrage nicht auf.
Select r.name, a.titel
from alben a, reihen r
where a.reihe=r.id
Wie könnte die Alternative aussehen?
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
outer-Join?
In unserem Datenbestand befinden sich die Reihe „Prinz Eisenherz“, zu der wir
in unseren Beispieldaten kein Album erfasst haben. Solche Reihen ohne Album
bleiben beim Inner-Join auf der Strecke und gehören nicht zum Ergebnis (siehe
Tabelle 13.4). Genau dieses Problem löst der Outer-Join:
Outer Joins
select r.name, a.titel
from reihen r left outer join alben a
on r.id = a.reihe
select r.name, a.titel
Der folgenden Tabelle entnehmen wir, dass diesmal alle Reihen zum Ergebnis gefrom reihen r left outer join alben a
hören. Wenn es zu einer Reihe kein Album gibt, werden die Attribute aus der
Projektion,
die zur=Tabelle
alben gehören, einfach durch null ersetzt.
on r.id
a.reihe
Tabelle 13.6: Ergebnis der Abfrage aus Listing 13.3
titel
name
Gespenster Geschichten
Asterix
Asterix
Asterix
Asterix
Asterix
Tim und Struppi
Tim und Struppi
Franka
Franka
Prinz Eisenherz
Gespenster Geschichten
Asterix der Gallier
Asterix und Kleopatra
Asterix als Legionär
Die Trabantenstadt
Der große Graben
Der geheimnisvolle Stern
Tim und der Haifischsee
Das Kriminalmuseum
Das Meisterwerk
null
DemProf.
Diagramm
in Böcker
Abbildung
13.1
entnehmen
wir, dass die Reihen zu den Alben
Dr. Stefan
– SQL:
Datensätze
verbinden
in einer 1-CM-Beziehung stehen. Zu jeder Reihe kann es also Alben geben. Im-
Gibt es Outer-Joins oft?
!  Reihen stehen zu Alben in einer 1-CM-Beziehung.
!  Zu jeder Reihe kann es also Alben geben.
!  Immer dann, wenn wir solchen Beziehungen in unseren
Abfragen Rechnung tragen wollen, brauchen wir den
Outer-Join.
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Hinweis:
Berücksichtigen Sie stets die Möglichkeit des
Outer-Joins. Er tritt häufiger als sie glauben.
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Die Richtung
Gibt es Left-Outer-Joins, dann wohl auch Right-Outer-Joins.
Tatsächlich können wir Left-Outer-Joins auch äquivalent als
Right-Outer-Joins formulieren.
Für das Beispiel heißt das:
select r.name, a.titel
from alben a right outer join reihen r
on a.reihe=r.id
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Muss es immer natürlich sein?
Die Joins, die wir bisher gesehen haben, sind so genannte
natürliche Joins:
In der Join-Bedingung wird der Primärschlüssel der einen
Tabelle gleich einem Fremdschlüssel der anderen Tabelle
gesetzt.
Diese Art Join ist sehr weit verbreitet.
Es gibt aber auch andere Möglichkeiten:
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Die Richtung
Zu jeder Reihe die Jahre ermitteln, in denen es eine
Preiserhöhung gab
select a2.reihe, a2.jahr
from alben a1, alben a2
where a1.reihe=a2.reihe and
a2.band>a1.band and a2.preis>a1.preis
13.5 Joins mit mehr als zwei Tabellen
group by a2.reihe, a2.jahr
Tabelle 13.7: Ergebnis der Abfrage aus Listing 13.4
reihe
jahr
2
2
2
1980
1974
null
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
215
band int not null,
foreign key(reihe, band) references alben,
primary key(autor, reihe, band)
)
Joins über mehr als zwei Tabellen
Zu jedem Album kann es jetzt Autoren geben, und jeder Autor kann, entsprechend dem ER-Diagramm aus Abbildung 13.3, Mitarbeiter an Comicalben sein.
Reihen
Alben
Autoren
AlbenAutoren
Abbildung 13.3: ER-Diagramm für die Beispieltabellen
select au.name, al.titel
Die Beispieldaten finden wir in den Tabellen 13.8 und 13.9:
from alben al, albenautoren aa, autoren au
where al.reihe=aa.reihe and al.band=aa.band and
au.id=aa.autor
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Hinweis:
Verwenden Sie ein ER-Diagramm als
Unterstützung, um Join- Bedingungen für Joins
über mehr als zwei Tabellen zu formulieren.
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Alles klar?
!  Mit einem Join werden die Datensätze von Tabellen
miteinander verknüpft. Die beteiligten Tabellen werden in
der from-Komponente aufgelistet.
!  An einem Join sind mindestens zwei Tabellen beteiligt,
jede Tabelle kann aber auch mit sich selbst verbunden
werden.
!  Sinnvolle Verknüpfungen sind nur möglich, wenn eine
geeignete Join-Bedingung als Prädikat formuliert wird.
!  Inner-Joins verbinden nur Datensätze, die der JoinBedingung genügen.
!  Im Ergebnis eines Outer-Joins sind auch Datensätze
enthalten, zu denen es kein passendes Gegenstück gibt.
Prof. Dr. Stefan Böcker – SQL: Datensätze verbinden
Geschachtelte Abfragen
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Komplexere Abfragen
Wir wollen das älteste Album in der Sammlung ermitteln:
Zuerst
select min(jahr)
from alben
und dann alle Alben aus dem Jahr 1968 suchen:
select titel
from alben
where jahr = 1968
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Geschachtelte Abfragen
Das gleiche Ergebnis liefert
select titel
from alben
where jahr = (
select min(jahr)
from alben)
Diese Anweisung besteht aus
!  einer inneren select-Anweisung, die das Minimum
ermittelt, und
!  einer äußeren select-Anweisung, die alle Alben findet,
deren Erscheinungsjahr gleich diesem Minimum sind.
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Ein äquivalenter Join
Mit viel Geduld findet man auch eine Lösung, die einen
Join nutzt.
select a1.titel
from alben a1, alben a2
where a1.jahr>=a2.jahr
group by a1.titel
having count(distinct a2.jahr)=1
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
So geht‘s nicht
Alle Alben suchen, die im gleichen Jahr wie ein AsterixAlbum erschienen sind
select titel
from alben a
where jahr = (select jahr
from alben
where reihe='Asterix')
Warum weist das RDBMS die Anweisung als fehlerhaft
zurück?
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Mengenorientierte Operatoren
Wenn das Ergebnis der Unterabfrage aus mehreren
Datensätzen besteht, muss ein mengenorientierter
Operator wie in verwendet werden.
select titel
from alben a
where jahr in (select jahr
from alben
where reihe='Asterix')
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Der all-Operator
...
jahr >all (
select jahr
from alben
where reihe like '%Tim%')
Der Ausdruck ist genau dann wahr, wenn der Wert von
jahr größer als jeder der Werte aus dem Ergebnis der
Unterabfrage ist.
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Der any-Operator
...
jahr >any(
select jahr
from alben
where reihe like '%Tim%')
Der Ausdruck ist genau dann wahr, wenn der Wert von
jahr größer ist als irgendein Jahr, in dem ein Album
erschienen ist, dessen Reihe den Text ‚Tim‘ enthält.
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
all und any
Die beiden Operatoren all und any werden stets in
Verbindung mit einem der Vergleichsoperatoren <,> und =
verwendet.
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Die Abfrage soll uns als Ergebnis alle Alben liefern, die nach dem neuesten
Asterix-Album erschienen sind. Auf den ersten Blick mag man glauben, dass das
Gleiche wie bei der folgenden Abfrage herauskommt.
Eine einfache Abfrage
Listing 14.7: Alben, die jünger als alle Asterix-Alben sind
select titel
from alben
select
titel
where jahr > (select max(jahr)
from
from alben
alben
where jahr
reihe='Asterix')
where
> (
max(jahr)
Wenn select
wir das Ergebnis
dieser Abfrage in Tabelle 14.3 mit dem Datenbestand unserer Beispieltabelle
10.1 abgleichen, sehen wir, dass die Abfrage korrekte Daten
from alben
geliefert hat. Führen wir dagegen die scheinbar gleichwertige Abfrage aus Liswhere
reihe='Asterix')
ting 14.6
aus, erhalten
wir eine leere Ergebnistabelle. Die beiden Abfragen sind
daher nicht gleichwertig; die Syntax der Abfrage aus Listing 14.6 ist zwar korrekt,
ihre Semantik muss aber falsch sein.
Alben, die jünger als alle Asterix-Alben sind
Tabelle 14.3: Ergebnis der Abfrage aus Listing 14.7
titel
Das Kriminalmuseum
Das Meisterwerk
An Prof.
Phänomenen
wie diesem
kann man lange
knobeln; die Ursache ist auch hier
Dr. Stefan Böcker
– SQL: Geschachtelte
Abfragen
der Wert null. In den Beispieldaten sind vier der Asterixalben in den Jahren 1968,
Ganz ähnlich
select titel
from alben
where jahr > all (
select jahr
from alben
where reihe='Asterix')
Was macht diese Abfrage?
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Die Analyse
Die innere Abfrage
select jahr
from alben
where reihe='Asterix‘
Liefert die Ergebnisse
1968, 1974, 1980 und null
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Ganz ähnlich
Das Prädikat
jahr >all (1968, 1974, 1980, null)
ist äquivalent zu
jahr>1968 and jahr>1974 and jahr>1980 and jahr>null
Der Wahrheitswert ist null.
Die Abfrage liefert also keinen Datensatz.
Die beiden geschachtelten Abfragen sind also nicht
äquivalent.
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Hinweis:
Bedenken Sie immer, dass null in Ihrer Tabelle
auftreten kann!
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Der exists-Operator
Was ist die Bedeutung der folgenden natürlichsprachlichen
Anweisung?
select reihe, titel, jahr
from alben a1
where not exists (
select *
from alben a2
where a1.jahr=a2.jahr and a1.reihe=a2.reihe)
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Die Bedeutung
Alle Alben ermitteln, aus deren Reihe im Erscheinungsjahr
kein weiterer Band erschienen ist
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Join oder verschachtelte Abfrage?
Zur Lösung eines Problems sind praktisch immer Joins und
Unterabfragen möglich.
select a.titel
from alben a, reihen r
where a.reihe=r.id and r.name='Asterix'
select a.titel
from alben a
where reihe = (select id
from reihen r
where r.id=a.reihe and r.name='Asterix')
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Hinweis:
Bei vielen Abfragen können wir geschachtelte
Abfragen und Joins alternativ einsetzen. Keine der
beiden Strategien ist grundsätzlich besser.
Berücksichtigen Sie beide Varianten, und wählen
Sie die klarere aus.
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Korrelierte Abfragen
Vergleichen Sie den Aufbau der beiden folgenden
Anweisungen
select titel
from alben
where jahr = (
select min(jahr)
from alben)
select a.titel
from alben a
where reihe = (select id
from reihen r
where r.id=a.reihe and r.name='Asterix')
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Korrelierte Abfragen
select titel
from alben
where jahr = (
select min(jahr)
from alben)
Das Ergebnis der inneren Abfrage ist 1968.
Dieses Ergebnis muss für die gesamte Abfrage nur ein
einziges Mal ermittelt werden.
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Korrelierte Abfragen
select a.titel
from alben a
where reihe = (select id
from reihen r
where r.id=a.reihe and r.name='Asterix')
Für jeden Datensatz der äußeren Abfrage muss die innere
Abfrage erneut ausgeführt werden.
Die innere Abfrage hängt von der Äußeren ab.
Diese Art Abfragen werden als korreliert bezeichnet.
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Korrelierte Abfragen
Im vorliegenden Fall kann die Korrelation aufgehoben
werden:
select titel
from alben a
where reihe in (
select id
from reihen r
and r.name='Asterix'
)
Hier ist alternativ (und besser) auch ein Join möglich.
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Alles klar?
!  Geschachtelte Abfragen enthalten eine Unterabfrage in
der where- Bedingung.
!  Wenn die Unterabfrage nur einen atomaren Wert
zurückliefert, kann sie in Prädikaten genutzt werden, die
mit den Vergleichsoperatoren <, > oder = definiert wurden.
!  Liefert die Unterabfrage mehrere Datensätze, so müssen
die Operatoren in, any, all oder exists verwendet
werden.
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen
Alles klar?
!  Um ein atomares Ergebnis zu garantieren, werden in der
Unterabfrage oft Aggregate eingesetzt.
!  Eine Unterabfrage, die Spalten aus der übergeordneten
Abfrage enthält, heißt „korreliert“. Die Verarbeitung einer
korrelierten Abfrage ist aufwändig.
!  Geschachtelte Abfragen können oft durch Joins ersetzt
werden, die sich ihrerseits durch geschachtelte Abfragen
ersetzen lassen.
Prof. Dr. Stefan Böcker – SQL: Geschachtelte Abfragen