Die SQL-Select-Anweisung

Die SQL-Select-Anweisung
Holger Jakobs – [email protected], [email protected]
2011-05-25
Inhaltsverzeichnis
1 Einleitung
1
2 Auswertungsreihenfolge
5
3 Abfragen auf eine Tabelle
3.1 einfache Abfrage . . . .
3.2 Abfrage mit Projektion .
3.3 Abfrage mit Selektion . .
3.4 Selektion und Projektion
3.5 Aggregat-Funktionen . .
3.6 Ähnlichkeitssuche . . . .
5
5
5
5
5
6
7
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
4 Abfragen mit mehreren Tabellen
7
5 Verknüpfung von Relationen mit sich selbst
9
6 Unterabfragen
6.1 Subquery mit IN und = . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.2 Subquery mit ALL, ANY und SOME . . . . . . . . . . . . . . . . . . . . . . .
10
10
11
7 Vereinigungsmengenbildung
11
8 Natürlicher Verbund mit ON- oder USING-Klausel
12
9 Korreliertes Subquery
14
10 Bedingte Ausdrücke
15
1 Einleitung
Die Structured Query Language SQL ist in erster Linie eine Abfragesprache – wie der Name schon sagt (query = Abfrage). Darüber hinaus dient sie auch der Datenveränderung,
1
1 EINLEITUNG
SELECT
[
ALL
DISTINCT
] {
*
attribute-list
functions
}
FROM
Festlegen der
Quell-Tabellen
[ alias ] [ , table-name [ alias ] ] ...
{ table-name
}
table-name [ [ NATURAL ] [ LEFT | RIGHT ] JOIN table-name ] ...
Auswahl von
passenden Tupeln
[ WHERE condition ]
[ GROUP BY column-name [ , column-name ] ...
[ ORDER BY
Auswahl von
passenden Gruppen
[ HAVING condition ]
column-spec [ ASC | DESC ] [ , ... ]
]
]
Sortierung des
Ergebnisses
Abbildung 1: Ausschnitt aus der SQL-Select-Syntax
also dem Einfügen, Ändern und Löschen von Daten (DML = data manipulation language) sowie Erzeugung von Datenbanken, d. h. der Beschreibung der Relationen (Tabellen)
mit ihren Attributen (Spalten) und ihren Beziehungen untereinander (DDL = data definition language). Hier soll zunächst die Abfragefunktion, die mit dem SELECT-Kommando
ausgeführt wird, erläutert werden. Dieses Kommando ist einerseits das komplexeste, andererseits aber auch das am häufigsten benutzte. Einen Auszug aus der Syntax finden Sie in
Abbildung 1.
SQL ist Groß- und Kleinschreibung egal – wie den meisten Sprachen aus derselben Zeit.
Aus Gründen der Übersichtlichkeit werden die SQL-Schlüsselwörter in diesem Text groß
geschrieben, so dass man sie von den Namen der Tabellen und Spalten leicht unterscheiden
kann. Innerhalb von Zeichenketten werden Groß- und Kleinschreibung natürlich unterschieden.
Das SELECT-Kommando wird sowohl beim interaktiven Arbeiten mit SQL, als auch beim
Programmieren mit SQL (embedded SQL, JDBC) verwendet. Mit dem SELECT-Kommando
können alle drei Relationen-Operationen (Selektion, Projektion und Verbund) ausgeführt
werden. SQL-Kommandos können sich über mehrere Zeilen erstrecken (das Zeilenende hat
keine Bedeutung), sie werden meist mit einem Semikolon abgeschlossen.
Durch die Auswahl der Spalten, die man statt des
Operator Bedeutung
Sterns
hinter dem Schlüsselwort SELECT angibt, erzeugt
=
ist gleich
man eine Projektion. Statt Spaltennamen der verwen>
größer als
deten Tabellen können auch Funktionen und Operatio>=
größer oder gleich
nen verwendet werden, z. B. CURRENT_TIME für die ak<
kleiner als
<=
<>
kleiner oder gleich
ungleich
2
1 EINLEITUNG
tuelle Zeit, sogenannte Aggregatfunktionen (siehe Abschnitt 3.5 auf Seite 6) oder mathematische Ausdrücke
z. B. anzahl + 3.
Die WHERE-Klausel dient zur Einschränkung des Suchergebnisses auf Tupel (Zeilen),
die eine bestimmte Bedingung erfüllen, also eine Selektion. Oft werden dabei Attributwerte mit festen Werten oder Ergebnissen von Funktionen wie CURRENT_TIME verglichen. Die üblichen Operatoren sind vorhanden, siehe Tabelle. Auch Mengenvergleiche sind möglich über den Operator IN (mathematisch ∈ für ist Element von“), z. B.
”
WHERE name IN ('Fritz', 'Anna', 'Uta'). Die Menge kann dabei wie hier direkt hingeschrieben werden oder auch Ergebnis einer weiteren SELECT-Abfrage sein, was als Unterabfrage oder Subquery bezeichnet wird (siehe auch Abschnitt 6 auf Seite 10).
Wie man in obigem Beispiel sieht, sind Zeichenketten in einfache Hochkommas zu
schreiben. Auch Datums- und Zeitwerte werden als Zeichenketten an die Datenbank geschickt und kommen als solche auch von dort zurück, so dass man die Abfrage aller Einträge
mit einem bestimmten Datum so schreibt: WHERE datum='2007-04-02'
Außer mit festen Werten wird auch oft mit dem NULL-Wert verglichen, d. h. es wird
geprüft, ob ein bestimmtes Attribut einen oder keinen Wert hat. Dies geschieht mit der
Klausel attributname IS NULL bzw. attributname IS NOT NULL. Dabei muss darauf
geachtet werden, dass ein Vergleich mittels des Gleichheitszeichens hier immer falsche
Ergebnisse liefert – völlig unabhängig vom tatsächlichen Wert des Attributs, d. h. die
Verwendung des Schlüsselworts IS ist bei Vergleichen mit NULL vorgeschrieben.
Es sollen zwei Relationen vorliegen: Student (MatrNr, Name, Fach, Semester) und
Hört (MatrNr, VorlesungNr)
1.
2.
3.
4.
Relationenalgebra
Student [Fach='Physik']
Student [Name, Fach]
Student × Hört
Student ⊗ Hört
SQL-SELECT-Statement
SELECT * FROM student WHERE fach='Physik';
SELECT name, fach FROM student;
SELECT * FROM student, hört;
SELECT * FROM student s, hört h
WHERE s.matrnr=h.matrnr;
SELECT * FROM student JOIN hört USING (matrnr);
SELECT * FROM student s JOIN hört h
ON s.matrnr=h.matrnr;
SELECT * FROM student NATURAL JOIN hört;
1. ist eine Selektion, bei der alle Spalten (*) der Relation ausgewählt werden, aber nur
bestimmte Tupel (WHERE fach='Physik').
2. ist eine Projektion, bei der alle Tupel der Relation ausgewählt werden (keine WHEREKlausel), aber nur bestimmte Attribute (Name, Fach).
3. ist ein einfacher Verbund, bei dem alle Studenten-Tupel mit allen Hört-Tupeln verknüpft werden. Die Ergebnistabelle ist sehr groß hat wenig Aussagekraft (praxisirrelevant).
3
1 EINLEITUNG
4. Meist wird daher der natürliche Verbund verwendet, bei dem die Gleichheit von je
einem Attribut gefordert wird. In der Schreibweise der Relationenalgebra wird nicht
deutlich, wie die Verknüpfung genau aussieht, d. h. es wird i. a. über das gleichnamige
Attribut (bzw. alle gleichnamigen Attribute) verknüpft. Bei SQL gibt es mehrere
Möglichkeiten, den natürlichen Verbund darzustellen. Entweder wird eine Bedingung
zum einfachen Join angegeben (S.MatrNr=H.MatrNr), was die klassische Schreibweise
ist, oder es wird statt des Kommas das Schlüsselwort JOIN verwendet, welches einen
der Zusätze USING oder ON benötigt. Hinter USING steht eine Liste aller Attribute,
die inhaltlich gleich sein müssen, hinter ON eine Bedingung. Weiterhin gibt es die
Möglichkeit, NATURAL JOIN zu schreiben, wodurch eine Gleichheitsbedingung für alle
Attribute erzeugt wird, die in beiden Tabellen vorkommen. Diese letzte Variante ist
mittlerweile die gängiste geworden.
Sehr oft werden Selektion, Projektion und Verbund kombiniert verwendet. Es ist bei SQL
nicht vorgesehen, die Ergebnisrelation temporär zu speichern und anschließend mit dieser
weiterzuarbeiten. Man muss immer – ausgehend von den in der Datenbank vorliegenden
Tabellen oder externen Sichten – alle Operationen auf einmal durchführen. Tatsächlich ist
dies auch der bessere Ansatz, weil man bei gespeicherten Ergebnissen erstens viel Speicherplatz benötigen und außerdem mit historischen Daten arbeiten würde. Trotzdem können
manche Datenbanken über den Standard hinaus temporäre Tabellen anlegen, die beim
Abmelden automatisch gelöscht werden. Für häufig benötigte Abfragen kann man zudem
Sichten auf die Daten speichern, die aber als Abfragevorschrift und nicht als Daten abgelegt werden – sogenannte Views. Außerdem kann überall dort, wo die Syntax eine Tabelle
erfordert (typischerweise hinter FROM) auch eine beliebig komplexe Unterabfrage (siehe Abschnitt 6 auf Seite 10 eingesetzt werden, der dann allerdings ein Aliasname gegeben werden
muss.
Eine Tabelle mit den Matrikelnummern und Vorlesungsnummern aller Physikstudenten
in Vorlesungsnummernreihenfolge kann man mit folgendem SQL-Kommando bekommen:
SELECT matrnr, vorlesungnr
FROM student NATURAL JOIN hört
WHERE fach='Physik'
ORDER BY vorlesungnr;
oder
SELECT matrnr, vorlesungnr
FROM student JOIN hört
USING (matrnr) WHERE fach='Physik'
ORDER BY vorlesungnr;
4
3 ABFRAGEN AUF EINE TABELLE
2 Auswertungsreihenfolge
Die einzelnen Klauseln eines SQL-Kommandos werden immer in einer bestimmten Reihenfolge ausgewertet, die nicht mit der Reihenfolge im Kommando übereinstimmt:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
definiert die Ausgangstabellen
selektiert die Zeilen, die der Bedingung genügen
gruppiert Zeilen auf der Basis gleicher Werte in Spalten
selektiert Gruppen, die der Bedingung genügen
projiziert Spalten
sortiert Zeilen auf der Basis von Spaltenwerten.
3 Abfragen auf eine Tabelle
3.1 einfache Abfrage
Eine ganz einfache Abfrage zeigt den gesamten Inhalt einer Tabelle:
SELECT * FROM spieler;
Es werden also keine expliziten Spaltennamen angegeben, sondern statt dessen ein Stern.
Der Tabellenname wird hinter FROM genannt.
3.2 Abfrage mit Projektion
SELECT spielnr, spielname, vorname FROM spieler;
Um nicht alle Spalten zu bekommen, gibt man die gewünschten Spaltennamen hinter
SELECT an. Diese Operation nennt man Projektion.
3.3 Abfrage mit Selektion
SELECT * FROM spieler WHERE spielnr > 10;
Um nicht alle Zeilen zu bekommen, gibt man eine WHERE-Klausel mit einer Bedingung an,
die die gewünschten Tupel auswählt. Diese Operation nennt man Selektion.
Die Bedingung kann ein Vergleich sein zwischen einem Spaltenwert und einem Literal
(wie hier im Beispiel mit dem integer-Literal 10) oder zwischen zwei Spaltenwerten. Darüber hinaus sind weitere Bedingungen möglich – einzige Voraussetzung ist, dass hierbei ein
boolescher Wert dargestellt wird.
3.4 Selektion und Projektion
Selektion und Projektion werden sehr häufig miteinander kombiniert:
SELECT spielnr, spielname, vorname FROM spieler WHERE spielnr > 10;
5
3.5 Aggregat-Funktionen
Name
SUM(Attribut)
COUNT (*)
COUNT (Attribut)
COUNT (DISTINCT Attribut)
AVG (Ausdruck)
MIN (Ausdruck)
MAX (Ausdruck)
3 ABFRAGEN AUF EINE TABELLE
Bedeutung
Summe aller Werte
Anzahl der Tupel in der Gruppe
Anzahl der Tupel mit Wert ungleich NULL in
der Gruppe
Anzahl der verschiedenen Werte des Attributs in der Gruppe
arithmetisches Mittel aller Werte
kleinster Wert
größter Wert
Tabelle 1: Tabelle der Aggregatfunktionen
3.5 Aggregat-Funktionen
Aggregat-Funktionen fassen Werte zusammen. Werden sie ohne GROUP BY-Klausel verwendet, so wird nur ein einziges Tupel ausgegeben. In Verbindung mit einer GROUP BY-Klausel
wird für jede Gruppe ein Tupel ausgegeben. In Tabelle 1 sind die Aggregatfunktionen
aufgeführt.
Die Funktionen COUNT, MIN und MAX sind auf alle Arten von Attributen anwendbar,
SUM und AVG nur auf numerische Ausdrücke. Bei COUNT kann man entweder ein beliebiges
Attribut oder einen Stern (*) als Parameter angeben – das Ergebnis ist gleich, sofern alle
Tupel einen Wert in dem genannten Attribut haben. Haben einige Tupel dagegen keinen
Wert (also einen sogenannten NULL-Wert – nicht zu verwechseln mit dem Zahlenwert 0),
so werden sie nicht mitgezählt.
Möchte man nur die Anzahl der verschiedenen Werte haben, so kann man das Wort
DISTINCT mit im Parameter angeben. Beispiel: SELECT COUNT (DISTINCT abt_nr) FROM
mitarbeiter; gibt aus, wievielen verschiedenen Abteilungen die Mitarbeiter zugeordnet
sind. Ohne DISTINCT würde die Anzahl der Mitarbeiter (= Anzahl der Tupel) ausgegeben.
Lediglich wenn es Mitarbeiter gibt, bei denen keine Abteilungsnummer eingetragen ist,
würden diese nicht mitgezählt.
Aggregat-Funktionen können auch in HAVING-Klauseln verwendet werden. Beispiel, das
die Namen der Orte ausgibt, in denen genau zwei Spieler wohnen:
SELECT ort FROM spieler GROUP BY ort HAVING COUNT (*) = 2;
Ein weiteres Beispiel für den Einsatz einer Aggregatfunktion mit Bezug auf die Kundenund Rechnungstabellen (siehe Abbildung 2 auf Seite 8) wäre folgendes:
SELECT KName, RNr, RDatum, SUM(Anzahl * APreis) AS RBetrag
FROM kunden NATURAL JOIN rechnungen
NATURAL JOIN rzeilen NATURAL JOIN artikel;
Zeige Kundennamen, die Rechnungsnummern, Rechnungsdatum und Rechnungsbetrag.
6
4 ABFRAGEN MIT MEHREREN TABELLEN
3.6 Ähnlichkeitssuche
Die Rechenoperation SUM (Anzahl * APreis) wird ausgeführt und die Spalte wird mit
dem Namen RBetrag angezeigt. Zu jeder Rechnungszeile wird also der zugehörige Artikelpreis aus der Artikeltabelle geholt. Alle vier Tabellen werden miteinander über einen
natürlichen Verbund verknüpft. Überall dort, wo in der Tabellenabbildung eine Verbindungslinie ist, kann man auch das NATURAL JOIN zwischen die Tabellennamen schreiben.
Man kann hier deutlich sehen, dass es wenig sinnvoll wäre, die Rechnungsbeträge bei den
Rechnungen zu hinterlegen, weil sie sich aus den einzelnen Rechnungspositionen (Zeilen)
leicht errechnen lassen1 .
3.6 Ähnlichkeitssuche
SQL bietet die Möglichkeit, nach Zeichenketten auch mit Jokerzeichen (Wildcards) zu
suchen. Als Ersatzsymbol für eine beliebige Zeichenkette von 0 oder mehr Zeichen Länge
wird das Zeichen '%' verwendet. Man muss allerdings bei Vergleich statt des Zeichens = das
Wort LIKE verwenden. Beispiel: SELECT * FROM spieler WHERE ort LIKE '%e%'; gibt
alle Angaben zu Spielern aus, die in Orten wohnen, deren Name ein 'e' enthält. Möchte
man innerhalb eines LIKE-Vergleichs nach dem Zeichen '%' selbst suchen, muss man es
quoten. Das geschieht folgendermaßen: ... LIKE '%!%%' escape '!'; sucht nach einer
Zeichenkette, die irgendwo das Zeichen '%' enthält. Das Zeichen '_' steht für genau ein
beliebiges Zeichen. ... LIKE '_U_' sucht also nach einer Zeichenkette mit genau drei
Zeichen, von denen das mittlere ein 'U' ist.
Im Standard gibt es über das recht schwache LIKE hinaus auch noch die Klausel SIMILAR
TO, die etwas in Richtung reguläre Ausdrücke hin weiter entwickelt wurde, ohne deren bereits nach POSIX genormte Syntax zu übernehmen. Bei SIMILAR TO können alternative
Zeichenketten durch einen senkrechten Strich getrennt werden, die Wiederholungsfaktoren
Stern und Pluszeichen funktionieren wie bei regulären Ausdrücken, ebenso kann man mit
runden Klammern gruppieren und in eckigen Klammern Zeichenmengen darstellen. Leider gibt es die Klausel SIMILAR TO nicht bei allen Datenbanksystemen; dafür gibt es in
manchen darüber hinaus noch wahlweise die regulären Ausdrücke nach POSIX.
4 Abfragen mit mehreren Tabellen
Um Daten aus mehreren Tabellen in einer Abfrage darzustellen, verwendet man eine Verbundoperation. Um den Verbund (Join) näher zu erläutern, hier einige Beispiele. Wir gehen
davon aus, dass es folgende Tabellen gibt:
Artikel
Kunden
(ANR, ABezeich, APreis)
(KNR, KName, KOrt)
1) In der betrieblichen Praxis ist das ein wenig anders, weil sich Preise nach Rechnungsstellung ändern
können, die alten Rechnungen aber weiterhin auf den damaligen Preis lauten. Daher werden bei Rechnungsstellung die Preise meist in die Rechnung kopiert. Ein Rechnungsbetrag als Summe aus allen
Positionen wird aber dennoch nicht gespeichert, weil das redundant wäre.
7
4 ABFRAGEN MIT MEHREREN TABELLEN
Kunden
1
Artikel
KNR
KName
KOrt
ANR
ABezeich
APreis
Rechnungen
N
RNR
KNR
RDatum
LDatum
1
Rechnungs−
zeile
N RNR
N
ANR
Anzahl
1
Abbildung 2: grafische Tabellendarstellung Kunden & Rechnungen
Rechnungen (RNR, KNR, RDatum, LDatum)
RZeilen
(RNR, ANR, Anzahl)
Wie in der Abbildung 2 zu sehen ist, sind Informationen über Artikel (Artikelnummer,
Artikelbezeichnung, Artikelpreis), über Kunden (Kundennummer, Kundenname, Kundenort), Rechnungen der Kunden (Rechnungsnummer, Kundennummer, Rechnungsdatum,
Lieferdatum) und Rechnungszeilen (Rechungsnummer, Artikelnummer, Anzahl) vorhanden. Hierzu nun einige Standardabfragen mit Erläuterung.
ˆ Da das Verknüpfungsattribut zwischen den beiden Tabellen in beiden Tabellen gleich
heißt und es keine weiteren gleichnamigen Attribute gibt, schreibt man am einfachsten
SELECT KNR, KName, RDatum
FROM Kunden NATURAL JOIN Rechnungen
Die Spalte KNR existiert im Ergebnis nur einmal, weil die beiden KNR-Spalten der
Ausgangstabellen automatisch zusammengefasst werden.
Eine Variante wäre
SELECT K.KNR, KName, RDatum
FROM Kunden K, Rechnungen R
WHERE K.KNR = R.KNR;
Zeige die Kundennummer (die aus der Kundentabelle, aber die aus der Rechnungstabelle ist wegen der WHERE-Bedingung ohnehin identisch), den Kundennamen, das
Rechnungsdatum von den Kunden mit den Rechnungen, wobei die Kundennummer
aus beiden Tabellen gleich sein muss. Andernfalls würden ja auch Werte aus Rechnungen angezeigt, die gar nicht zu dem Kunden gehören. Die Kundentabelle bekommt
8
5 VERKNÜPFUNG VON RELATIONEN MIT SICH SELBST
SELECT R1.*, R2.* FROM R R1, R R2;
Aliasnamen
alle Spalten aus R2
alle Spalten aus R1
Ausgangsrelation
Ergebnisrelation
R A B
1 7
2 3
R x R R1.A R1.B R2.A R2.B
1
1
2
2
7
7
3
3
1
2
1
2
7
3
7
3
Abbildung 3: Verbund-Operation einer Relation mit sich selbst
den Aliasnamen K, die Rechnungstabelle den Namen R, damit man bei der WHEREund der SELECT-Klausel nicht den ganzen, langen Namen angeben muss.
ˆ SELECT RDatum, LDatum, ABezeich, Anzahl
FROM Rechnungen NATURAL JOIN RZeilen NATURAL JOIN Artikel
Auch hier werden die Tabellen wieder über alle gleichnamigen Spalten verbunden, so
dass man einfach mehrere NATURAL JOIN-Verbindungen hintereinander durchführen
kann.
Und wieder die Variante
SELECT RDatum, LDatum, ABezeich, Anzahl
FROM Rechnungen, RZeilen, Artikel
WHERE R.RNR = Z.RNR and Z.ANR = A.ANR;
Zeige das Rechnungdatum, das Lieferdatum und die Artikelbezeichnung von allen
Rechnungen mit ihren Zeilen. Die Bezeichnung des Artikels muss aus der Artikeltabelle geholt werden – daher wird ein Verbund über drei Tabellen nötig.
5 Verknüpfung von Relationen mit sich selbst
Bei einer Verbund-Operation kann auch eine Relation mit sich selbst verknüpft werden.
Dann müssen allerdings Aliasnamen für die Relationen gewählt werden, um die Spalten
eindeutig bezeichnen zu können.
Obiges Beispiel ist natürlich nicht sonderlich interessant. Ein praktischer Anwendungsfall
der Verknüpfung einer Relation mit sich selbst wäre folgender:
Es liegt eine Tabelle mit Büchern vor: Bücher (Titel, Autor, Preis). Es soll nun ermittelt
werden, welche Bücher billiger sind als das Buch SQL“ vom Autor Hoffmann“:
”
”
9
6 UNTERABFRAGEN
SELECT b1.* FROM buecher b1, buecher b2
WHERE b2.titel='SQL' AND b2.autor='Hoffmann'
AND b1.preis < b2.preis
ORDER BY b1.preis;
Der hier vorliegende Verbund ist ein θ-Verbund (theta). Die Relation Bücher wird zweimal verwendet: mit den Aliasnamen b1 und b2. Es entsteht also eine neue Relation als
Kreuzprodukt der Relation Bücher mit sich selbst. Durch die erste Zeile der WHERE-Klausel werden alle Tupel ausgewählt, die das Buch SQL“ von Hoffmann“ in den b2-Spalten
”
”
enthalten. Außerdem muss der Preis in der b1-Spalte kleiner sein als der Preis in der b2Spalte. Jetzt sind in den b1-Spalten alle Bücher übrig, die billiger sind als das SQL-Buch
von Hoffmann. Es wird nach dem b1-Preis sortiert. Es werden alle b1-Spalten angezeigt.
6 Unterabfragen
Oft möchte man in einer WHERE-Klausel einen Attributwert nicht mit einem festen Wert
oder einer festen Menge vergleichen, sondern mit dem Ergebnis einer anderen DatenbankAbfrage. Diese untergeordnete Datenbank-Abfrage nennt man dann Unterabfrage oder Subquery. Es gibt mehrere Varianten, wie man sie einsetzen kann, denn sie ist nicht nur in
Vergleichen erlaubt, sondern überall dort, wo eine Tabelle vorkommen kann.
6.1 Subquery mit IN und =
Grundsätzlich ist das Ergebnis einer Abfrage eine Menge, so dass man auch Mengenoperatoren verwenden muss, beispielsweise den ist Element von“-Operator (∈) namens IN:
”
SELECT * FROM artikel WHERE anr IN (SELECT anr FROM auftraege);
Es wird bei jedem einzelnen Tupel der Hauptabfrage geprüft, ob der Wert des Attributs
anr im Ergebnis der Unterabfrage vorkommt. Die meisten Datenbanksysteme lassen nur
Unterabfragen mit 1 Spalte zu.
Natürlich kann man auch die Artikel herausfinden, die in keinem Auftrag vorkommen.
Dazu wird die Operation ist kein Element von“ (∈)
/ verwendet, also heißt es jetzt NOT IN:
”
SELECT * FROM artikel WHERE anr NOT IN (SELECT anr FROM auftraege);
Sollte das Ergebnis der Unterabfrage nur ein einziges Tupel sein, so kann man den Vergleich auch mit dem Operator = machen. Hier wird dann nicht streng zwischen einem
Element und einer Menge mit einem Element unterschieden; mit IN würde es natürlich
auch funktionieren.
SELECT * FROM sportler WHERE zeit = (SELECT MIN(zeit) FROM sportler);
Hinter dem IN-Operator kann man eine Menge auch explizit durch Aufzählung angeben,
d. h. ohne Unterabfrage:
SELECT spielnr FROM spieler WHERE ort IN ('Krefeld', 'Meerbusch');
10
7 VEREINIGUNGSMENGENBILDUNG
6.2 Subquery mit ALL, ANY und SOME
6.2 Subquery mit ALL, ANY und SOME
Bei Subqueries mit = muss ein einziger Wert herauskommen, bei Subqueries mit IN wird
nur auf Existenz des Wertes in der Tabelle geprüft (Mengenoperation ist Element von“, ∈).
”
Mit ALL und ANY bzw. SOME sind dagegen auch die anderen Vergleichsoperationen möglich,
d. h. man kann fragen, ob ein Wert größer ist als mindestens einer (ANY oder SOME) oder
größer als alle (ALL) Werte, die in der Unterabfrage ermittelt wurden.
Beispiel 1: Wer aus Abteilung A verdient mehr als alle aus Abteilung B?
SELECT persnr FROM personal
WHERE abt='A' AND gehalt > all
(SELECT gehalt FROM personal WHERE abt='B');
Beispiel 2: Welcher Manager verdient weniger als irgendein Nicht-Manager?
SELECT persnr FROM personal
WHERE job='Manager' AND gehalt < ANY
(SELECT gehalt FROM personal WHERE job <> 'Manager');
7 Vereinigungsmengenbildung
Bei einem natürlichen Verbund von Tabellen werden diejenigen Tupel unberücksichtigt gelassen, bei denen das join-Attribut NULL-Werte enthält bzw. der Wert in einer der Tabellen
nicht vorkommt.
Beispiel: Aus der Vereinsdatenbank sollen alle Spieler mit der Anzahl ihrer Strafen ermittelt werden. Das klassische SELECT-Kommando (ohne NATURAL JOIN) sieht so aus und
erzeugt:
spielnr spielname expr
SELECT s.spielnr, spielname, COUNT(*)
6 Peters
1
FROM spieler s, strafen st
8 Neuhaus
1
WHERE s.spielnr = st.spielnr
27 Kohl
2
GROUP BY s.spielnr, spielname;
44 Becker
3
104 Maurer
1
Wie man sieht, kommen alle Spieler, die noch keine Strafe erhalten haben, in der Tabelle
gar nicht vor. Das liegt daran, dass nur die Tupel ausgegeben werden, bei denen die beiden
Spielernummern gleich sind. Kommt eine Spielernummer in einer Tabelle nicht vor oder
hat sie einen NULL-Wert, wird das Tupel nicht ausgegeben.
Also muss man es nachträglich wieder dazuholen, was man durch ein zweites SELECTKommando macht. Die Ergebnistabelle der beiden SELECT-Kommandos werden dann anschließend zu einer Tabelle zusammengefügt. Da dies eine Mengenoperation ist, wird die
Operation UNION (Vereinigung) genannt. Für obiges Beispiel sieht das so aus wie in Abbildung 4 auf der nächsten Seite gezeigt.
11
8 NATÜRLICHER VERBUND MIT ON- ODER USING-KLAUSEL
SELECT s.spielnr, spielname, COUNT(*)
FROM spieler s, strafen st WHERE
s.spielnr = st.spielnr
GROUP BY s.spielnr, spielname
UNION
SELECT spielnr, spielname, 0
FROM spieler
WHERE spielnr not in
(SELECT spielnr FROM strafen);
spielnr
2
6
7
8
27
28
39
44
104
...
spielname
Elfers
Peters
Wiegand
Neuhaus
Kohl
Kohl
Bischof
Becker
Maurer
...
expr
0
1
0
1
2
0
0
3
1
... usw.
Abbildung 4: Vereinigungsmengenbildung mit UNION
Diese Art von Verbund wird Outer Join“ gegenüber dem normalen Inner Join“ ge”
”
nannt. Die beiden mit UNION zu vereinigenden Tabellen müssen in ihrem Aufbau, d. h.
in Anzahl und Typen der Spalten, übereinstimmen. Beide SELECTs sind vollständig, nur
eine ORDER BY-Klausel darf lediglich ganz am Ende enthalten sein und gilt dann für das
gesamte SELECT.
Eine bessere Möglichkeit ist der sogenannte NATURAL LEFT JOIN bzw. NATURAL RIGHT
JOIN, der es ermöglicht, direkt alle Tupel der linken bzw. rechten Tabelle in die Ergebnistabelle zu übernehmen, auch wenn es kein korrespondierendes Tupel in der anderen Tabelle
gibt. Das schreibt man dann so:
SELECT spielnr, spielname, COUNT(betrag)
FROM spieler NATURAL LEFT JOIN strafen
GROUP BY spielnr, spielname;
Außer dem Left Outer Join“ und dem Right Outer Join“ gibt es noch den Full Outer
”
”
”
Join“, bei dem außer den Tupeln des Inner Join“ alle Tupel aus beiden Tabellen verwendet
”
werden, denen kein Tupel aus der jeweils anderen Tabelle zugeordnet werden kann. Diese
Art von Outer Join“ ist bei den einigen Systemen nur über UNION erreichbar (obwohl der
”
Standard es vorsieht) und kommt in der Praxis nicht sehr häufig vor.
8 Natürlicher Verbund mit ON- oder USING-Klausel
Soll ein Join nicht über die Gleichheit aller gleichnamigen Attribute stattfinden, so ist statt
der Klausel NATURAL [ LEFT/RIGHT ] JOIN lediglich [ LEFT/RIGHT ] join zu schreiben und dahinter eine ON- oder eine USING-Klausel anzugeben. In einer USING-Klausel
steht eine geklammerte Liste all der gleichnamigen Attribute, auf Grund derer der Verbund stattfinden soll. Beispiel:
12
8 NATÜRLICHER VERBUND MIT ON- ODER USING-KLAUSEL
SELECT s.spielnr, datum, betrag
FROM spieler s LEFT JOIN strafen st
ON
s.spielnr = st.spielnr
AND datum < '2009-01-01';
spielnr
2
6
7
8
27
28
39
44
44
44
57
83
95
100
104
112
datum
betrag
NULL
NULL
2005-12-02 100.00
NULL
NULL
2005-12-02
25.00
NULL
NULL
NULL
NULL
NULL
NULL
2005-12-02
25.00
2006-04-29
75.00
2007-12-02
30.00
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
SELECT s.spielnr, datum, betrag
FROM spieler s LEFT JOIN strafen st
ON
s.spielnr = st.spielnr
WHERE datum < '2009-01-01';
spielnr
8
6
44
44
44
27
datum
betrag
2005-12-02
25.00
2005-12-02 100.00
2007-12-02
30.00
2005-12-02
25.00
2006-04-29
75.00
2008-09-03 100.00
Abbildung 5: Vergleich zweier ähnlicher Verbund-Abfragen
SELECT w.spielnr, teamnr, liga, gewonnen-verloren
FROM wettkaempfe w JOIN teams USING (teamnr);
Hier werden die Tabellen wettkaempfe und teams über das Attribut teamnr verbunden;
ohne die USING-Klausel (statt dessen dann mit NATURAL JOIN) würde die Werte-Gleichheit
sowohl beim Attribut teamnr als auch bei spielnr notwendig, um ein Tupel der Ergebnistabelle zu generieren. Bei Verwendung von USING wird – genauso wie bei NATURAL JOIN –
für jedes gleichnamige Attribut in den beiden Tabellen ein einziges Attribut in der Ergebnistabelle erzeugt. Daher muss der Zugriff auf dieses Attribut unqualifiziert erfolgen (ohne
Angabe des Tabellen- oder Aliasnamens).
In einer ON-Klausel steht eine Bedingung wie in einer WHERE-Klausel. Die Bedingung kann
auch aus mehreren Teilen bestehen, die mit den üblichen Operatoren AND, OR, NOT verknüpft
werden. Nur Tupel, die der Gesamtbedingung genügen, gelangen in die Ergebnistabelle. Bei
Outer Joins“ werden die Tupel für den Inner Join“ durch die ON-Klausel limitiert; alle
”
”
13
9 KORRELIERTES SUBQUERY
1. mit normalem Subquery
SELECT spielnr, spielname
FROM spieler
WHERE spielnr IN
(SELECT spielnr FROM strafen);
3. mit Natural Join“
”
SELECT DISTINCT spielnr,
spielname
FROM spieler NATURAL JOIN
strafen;
2. mit klassischem“ natürlichen Verbund:
”
SELECT DISTINCT s.spielnr,
spielname
FROM spieler s, strafen st
WHERE s.spielnr=st.spielnr;
4. mit korreliertem Sub-Query:
SELECT spielnr, spielname FROM
spieler s
WHERE exists
(SELECT * FROM strafen st WHERE
s.spielnr=st.spielnr);
Tabelle 2: Vier Arten einer korrelierten Unterabfrage
nicht passenden gelangen ggf. in den Outer Join“-Teil der Ergebnistabelle. Der Unterschied
”
zwischen einer Bedingung in der ON- und der WHERE-Klausel ist, dass die Bedingung in der
ON-Klausel für den Join Gültigkeit haben muss, in der WHERE-Klausel für die Tupel nach
der Durchführung aller Joins der Abfrage.
Hierzu zwei Abfragen zum Vergleich, die völlig unterschiedliche Ergebnisse liefern, siehe
Abbildung 5 auf der vorherigen Seite.
Im ersten SELECT werden alle Strafen aller Spieler aufgelistet, wobei die ON-Klausel alle
diejenigen Spieler/Strafen-Kombinationen ausschließt, wo das Datum der Strafe vor 2009
liegt. Diese Spieler gelten hier als unbestraft, kommen also in den Outer Join“-Part des
”
Left Join“, erscheinen somit im Ergebnis.
”
Im zweiten SELECT werden alle Strafen mit den Spielernummern aufgelistet. Nach Ausführung des Join werden alle Strafen vor 2009 mit der WHERE-Klausel entfernt. Diese WHEREKlausel entfernt aber auch alle Tupel, die beim Datum einen NULL-Wert enthalten. Daher
werden alle Tupel, die aus dem Outer Join“-Part stammen, entfernt.
”
Würde man den Zusatz OR datum IS NULL dazuschreiben, um diese Tupel zu retten,
würden aber dennoch einige Tupel fehlen: Die Spieler, die eine Strafe ab 2009-01-01
bekommen haben, kommen mit diesen Strafen in den Inner Join“-Part, werden aber nach
”
dem Join von der WHERE-Klausel entfernt, so dass sie im Ergebnis fehlen, während sie in er
allerersten Version im Outer Join“-Part als unbestrafte Spieler auftauchen.
”
9 Korreliertes Subquery
Die Anzeige aller Spielernamen, die schon einmal eine Strafe erhalten haben, kann man auf
vier Arten erreichen (siehe Tabelle 2). Die Besonderheit des korrelierten Subquery ist, dass
in der Unterabfrage auf die Tabelle des übergeordneten SELECT zurückgegriffen wird. Dazu
wird ein Aliasname der Tabelle aus der Hauptabfrage in der Unterabfrage verwendet.
Die meisten Abfragen sind ohne korrelierte Unterabfragen durchführbar. Da bei jedem
14
10 BEDINGTE AUSDRÜCKE
Tupel der Hauptabfrage immer die komplette Unterabfrage erneut durchgeführt werden
muss, ist die korrelierte Unterabfrage auch besonders aufwendig und daher langsam. Man
sollte sie also nach Möglichkeit vermeiden.
10 Bedingte Ausdrücke
Um bei Abfragen Werte in verschiedene Klassen einteilen zu können, gibt es CASE. Wenn
wir die Strafen in geringe, mittlere und große Strafen einteilen möchten, schreiben wir eine
Abfrage so:
SELECT spielnr,
CASE WHEN betrag < 50 THEN 'wenig'
WHEN betrag BETWEEN 50 AND 100 THEN 'mittel'
ELSE 'viel'
END
FROM strafen;
Die COALESCE-Funktion kann mehrere Argumente haben und liefert das erste Argument
zurück, dessen Wert nicht NULL ist. Hierdurch kann man in Tabellen vorhandene NULLWerte in Ausgaben durch eine passende Zeichenkette ersetzen. Bei allen Spielern ohne
Titel bleibt die dritte Spalte nicht leer, sondern enthält die Zeichenkette ’ohne Titel’.
SELECT spielnr, spielname, COALESCE (titel, 'ohne Titel')
FROM spieler;
Die NULLIF-Funktion ist die Umkehrung von COALESCE. Hier wird NULL zurückgegeben,
wenn eine Spalte einen bestimmten Wert enthält. Steht also die Zeichenkette ’ohne Titel’
in der Spalte, wird jetzt NULL ausgegeben.
SELECT spielnr, spielname, NULLIF (titel, 'ohne Titel')
FROM spieler;
15