Einführung in SQL: Druckversion: Mehr zu Abfragen
Wegen des Umfangs und der Komplexität der einzelnen Kapitel besteht die Druckversion aus mehreren Teilen.
- Ausführliche SELECT-Struktur liefert umfangreiche Erläuterungen zur SELECT-Syntax.
- Funktionen (2) erläutert eine Reihe weiterer eingebauter Funktionen.
- WHERE-Klausel im Detail erläutert die Selektionsbedingungen.
- Mehrere Tabellen enthält eine Übersicht, wie sie verknüpft werden können:
- Einfache Tabellenverknüpfung beschreibt den „traditionellen“ Weg.
- Arbeiten mit JOIN beschreibt die Grundlagen des „modernen“ Wegs.
- Durch OUTER JOIN können auch NULL-Werte berücksichtigt werden.
- Mehr zu JOIN beschreibt weitere Möglichkeiten von JOIN.
- Nützliche Erweiterungen erläutert verschiedene Klauseln des SELECT-Befehls.
- Berechnete Spalten behandelt Spalten, die nicht aus einer Datenbanktabelle kommen, sondern durch SELECT erzeugt werden.
- Gruppierungen ermöglichen zusammenfassende Informationen.
- Durch Unterabfragen können Informationen in Hauptabfragen und zum Speichern weiterverwendet werden.
- Über Erstellen von Views werden Abfragen zur ständigen Benutzung fest in der Datenbank gespeichert.
Über das Inhaltsverzeichnis des Buches sind die anderen Teile der Druckversion zu erreichen:
| Ausführliche SELECT-Struktur |
Dieses Kapitel erläutert die Syntax des SELECT-Befehls. Anstelle von Beispielen gibt es Verweise auf diejenigen Kapitel, die die betreffenden Klauseln genauer behandeln.
In diesem Kapitel werden vorzugsweise die englischen Begriffe aus der SQL-Dokumentation benutzt.
Bitte beachten Sie: Der SELECT-Befehl bietet so umfangreiche Möglichkeiten, dass auch bei dieser Übersicht nicht alle Einzelheiten vorgestellt werden können.
Allgemeine Syntax
Der SELECT-Befehl wird als <query specification> oder <select expression>, also Abfrage-Ausdruck bezeichnet und setzt sich grundsätzlich aus diesen Bestandteilen zusammen:
SELECT
[ DISTINCT | ALL ]
<select list>
FROM <table reference list>
[ <where clause> ]
[ <group by clause> ]
[ <having clause> ]
[ UNION [ALL] <query specification> ]
[ <order by clause> ]
Diese Bestandteile setzen sich je nach Situation aus weiteren einfachen oder komplexen Bestandteilen zusammen und werden in den folgenden Abschnitten erläutert.
Unbedingt erforderlich sind:
- das Schlüsselwort SELECT
- eine Liste von Spalten
- das Schlüsselwort FROM mit einem oder mehreren Verweisen auf Tabellen
Alle anderen Bestandteile sind optional, können also auch weggelassen werden. Wenn sie benutzt werden, müssen sie in genau der genannten Reihenfolge verwendet werden.
Set Quantifier – Mengenquantifizierer
Als Mengenquantifizierer stehen DISTINCT und ALL zur Verfügung.
Außerdem ist es sehr oft möglich (wenn auch nicht im SQL-Standard vorgeschrieben), das Ergebnis auf eine bestimmte Anzahl von Zeilen zu beschränken, z. B. FIRST 3 oder LIMIT 7.
Erläuterungen dazu finden sich im Kapitel Nützliche Erweiterungen.
Select List – Auswahlliste
Die Liste der Spalten, die ausgewählt werden sollen, wird angegeben durch den Asterisk oder mit einer <column list>.
Asterisk *
Der Asterisk, d. h. das Sternchen *, ist eine Kurzfassung und steht für die Liste aller Spalten (Felder) einer einzelnen Tabelle.
Wenn mehrere Tabellen verknüpft werden, ist der Asterisk zusammen mit dem Namen oder dem Alias einer Tabelle anzugeben.
<Tabellenname>.* /* oder */ <Tabellen-Alias>.*
Erläuterungen dazu finden sich in allen Kapiteln, die sich mit Abfragen befassen.
Column List
Die <Spaltenliste> ist eine Liste von einem oder mehreren Elementen, die in der SQL-Dokumentation als <select sublist> bezeichnet werden:
<select sublist> [ , <select sublist> , <select sublist> , ... ]
Jedes einzelne Element ist eine Spalte einer Tabelle oder eine abgeleitete Spalte <derived column> – siehe den nächsten Abschnitt. Jedem Element kann ein Spalten-Alias zugewiesen, das Wort AS kann dabei auch weggelassen werden:
<element> [ [AS] <column name> ]
Bei Spalten aus Tabellen wird einfach deren Name angegeben. Wenn mehrere Tabellen verknüpft werden und ein Spaltenname nicht eindeutig ist, ist der Spaltenname zusammen mit dem Namen oder dem Alias einer Tabelle anzugeben.
<spaltenname> /* oder */ <Tabellenname>.<spaltenname> /* oder */ <Tabellen-Alias>.<spaltenname>
Hinweis: In manchen DBMS darf der Tabellenname nicht mehr benutzt werden, wenn ein Tabellen-Alias angegeben ist.
Erläuterungen und Beispiele dazu finden sich in allen Kapiteln, die sich mit Abfragen befassen.
Derived Column
Eine abgeleitete Spalte bezeichnet den Wert, der von einem <value expression> zurückgeliefert wird. Das kann ein beliebiger Ausdruck sein, der genau einen Wert als Ergebnis hat: vor allem eine Funktion oder eine passende Unterabfrage.
Erläuterungen dazu finden sich vor allem in den Kapiteln Berechnete Spalten und Unterabfragen.
Table Reference List – Tabellen-Verweise
Als Bestandteil der FROM-Klausel werden die beteiligten Tabellen und Verweise darauf aufgeführt:
FROM <reference list> /* nämlich */ FROM <reference1> [ , <reference2> , <reference3> ... ]
In der <Tabellenliste> stehen eine oder (mit Komma getrennt) mehrere Verweise (Referenzen); diese können direkt aus der Datenbank übernommen oder auf verschiedene Arten abgeleitet werden.
Jede dieser Varianten kann erweitert werden:
<reference> [ [ AS ] <Alias-Name>
[ ( <derived column list> ) ] ]
Mit AS kann ein Alias-Name dem Verweis hinzugefügt werden; das Schlüsselwort AS kann auch entfallen. Diesem Tabellen-Alias kann (je nach DBMS) in Klammern eine Liste von Spaltennamen hinzugefügt werden, die anstelle der eigentlichen Spaltennamen angezeigt werden sollen.
Die folgenden Varianten sind als Verweise möglich.
Tabellen, Views
Primär verwendet man die Tabellen sowie die Views (fest definierte Sichten).
Erläuterungen dazu finden sich in allen Kapiteln, die sich mit Abfragen befassen, sowie in Erstellen von Views.
Derived Table – Abgeleitete Tabellen
Vor allem können Unterabfragen wie eine Tabelle benutzt werden.
Hinweis: Es gibt eine Reihe weiterer Varianten, um andere Tabellen „vorübergehend“ abzuleiten. Wegen der DBMS-Unterschiede würde die Übersicht zu kompliziert; wir verzichten deshalb auf eine vollständige Darstellung.
Erläuterungen dazu finden sich im Kapitel Unterabfragen.
Joined Table – Verknüpfte Tabelle
Wie eine Tabelle kann auch eine Verknüpfung verwendet werden. Dabei handelt es sich um zwei Tabellen, die nach bestimmten Bedingungen verbunden werden.
Für die Verknüpfung zweier (oder mehrerer) Tabellen gibt es zwei Verfahren: Beim (traditionellen) direkten Weg werden die beiden Tabellen einfach nacheinander aufgeführt. Beim (modernen) Weg wird zu einer Tabelle eine weitere mit JOIN genannt, die durch eine Verknüpfungsbedingung über ON verbunden wird.
Erläuterungen dazu finden sich ab dem Kapitel Mehrere Tabellen.
Where Clause – WHERE-Klausel
Mit der WHERE-Klausel wird eine Suchbedingung festgelegt, welche Zeilen der Ergebnistabelle zur Auswahl gehören sollen. Dieser Filter wird zuerst erstellt; erst anschließend werden Bedingungen wie GROUP BY und ORDER BY ausgewertet.
WHERE <search condition>
Die Suchbedingung <search condition> ist eine Konstruktion mit einem eindeutigen Prüfergebnis: Entweder die Zeile gehört zur Auswahl, oder sie gehört nicht zur Auswahl. Es handelt sich also um eine logische Verknüpfung von einer oder mehreren booleschen Variablen.
Erläuterungen zu den folgenden Einzelheiten finden sich vor allem in den Kapiteln WHERE-Klausel im Detail und Unterabfragen.
Eine einzelne Suchbedingung
Eine Suchbedingung hat eine der folgenden Formen, deren Ergebnis immer WAHR oder FALSCH (TRUE bzw. FALSE) lautet:
<wert1> [ NOT ] <operator> <wert2> <wert1> [ NOT ] BETWEEN <wert2> AND <wert3> <wert1> [ NOT ] LIKE <wert2> [ ESCAPE <wert3> ] <wert1> [ NOT ] CONTAINING <wert2> <wert1> [ NOT ] STARTING [ WITH ] <wert2> <wert1> IS [ NOT ] NULL <wert1> [ NOT ] IN <werteliste> EXISTS <select expression> NOT <search condition>
Anstelle eines Wertes kann auch ein Wertausdruck <value expression> stehen, also eine Unterabfrage, die genau einen Wert als Ergebnis liefert.
Anstelle einer Werteliste kann auch ein Auswahl-Ausdruck <select expression> stehen, also eine Unterabfrage, die eine Liste mehrerer Werte als Ergebnis liefert.
Als <operator> sind folgende Vergleiche möglich, und zwar sowohl für Zahlen als auch für Zeichenketten und Datumsangaben:
= < > <= >= <>
Mehrere Suchbedingungen
Mehrere Suchbedingungen können miteinander verbunden werden:
NOT <search condition> <search condition1> AND <search condition2> <search condition1> OR <search condition2>
Bitte beachten Sie: NOT hat die stärkste Verbindung und wird zuerst ausgewertet. Danach hat AND eine stärkere Verbindung und wird als nächstes untersucht. Erst zum Schluss kommen die OR-Verbindungen. Um Unklarheiten zu vermeiden, wird dringend empfohlen, zusammengesetzte Suchbedingungen in Klammern zu setzen, um Prioritäten deutlich zu machen.
Group By Clause – GROUP BY-Klausel
Mit der GROUP BY-Klausel werden alle Zeilen, die in einer oder mehreren Spalten den gleichen Wert enthalten, in jeweils einer Gruppe zusammengefasst. Dies macht in der Regel nur dann Sinn, wenn in der Spaltenliste des SELECT-Befehls eine gruppenweise Auswertung, also eine der Spaltenfunktionen enthalten ist.
Die allgemeine Syntax lautet:
GROUP BY <Spaltenliste>
Die Spaltenliste enthält, durch Komma getrennt, die Namen von einer oder mehreren Spalten. Bei jeder Spalte kann eine eigene Sortierung angegeben werden (wie bei den Datentypen erläutert):
<Spaltenname> -- oder <Spaltenname> COLLATE <Collation-Name>
Erläuterungen dazu finden sich vor allem im Kapitel Gruppierungen.
Having Clause – HAVING-Klausel
Die HAVING-Klausel dient dazu, nicht alle ausgewählten Zeilen in die Ausgabe zu übernehmen, sondern nur diejenigen, die den zusätzlichen Bedingungen entsprechen. Sie wird in der Praxis überwiegend als Ergänzung zur GROUP BY-Klausel verwendet und folgt ggf. direkt danach.
HAVING <bedingungsliste>
Erläuterungen dazu finden sich ebenfalls im Kapitel Gruppierungen.
Union Clause – UNION-Klausel
Mit der UNION-Klausel werden mehrere eigentlich getrennte Abfragen zusammengefasst, um ein einheitliches Ergebnis zu liefern. Dabei sind die einzelnen Bedingungen zu komplex, um sie zusammenzufassen; oder sie können nicht sinnvoll verbunden werden. Es setzt eine weitgehend identische Spaltenliste voraus.
SELECT <spaltenliste1> FROM <tabellenliste1> WHERE <bedingungsliste1> UNION SELECT <spaltenliste2> FROM <tabellenliste2> WHERE <bedingungsliste2>
Erläuterungen dazu finden sich im Kapitel Nützliche Erweiterungen.
Order By Clause – ORDER BY-Klausel
Mit der ORDER BY-Klausel werden die Datensätze der Ergebnismenge in eine bestimmte Reihenfolge gebracht.
Erläuterungen dazu finden sich in allen Kapiteln, die sich mit Abfragen befassen.
Zusammenfassung
In diesem Kapitel erhielten Sie einen umfangreichen Überblick über die Syntax des SELECT-Befehls:
- Die Listen der gewünschten Spalten und der beteiligten Tabellen sind Pflichtangaben, alle anderen Klauseln sind optional.
- Für die Verknüpfung mehrerer Tabellen gibt es einen (traditionellen) direkten Weg und den (modernen) Weg über JOIN.
- Die WHERE-Klausel ermöglicht komplexe Bedingungen darüber, welche Datensätze abgefragt werden sollen.
- Mit Gruppierung, Sortierung und Zusammenfassung gibt es weitere Möglichkeiten für Abfragen.
Übungen
| Übung 1 | Allgemeine Syntax | Zur Lösung |
Bringen Sie die folgenden Bestandteile des SELECT-Befehls in die richtige Reihenfolge (es gibt Begriffe, die an zwei bzw. drei Stellen gehören):
<bedingung> – DISTINCT – FROM – GROUP BY – HAVING – ORDER BY – SELECT – <spalten> – <tabelle> – WHERE
| Übung 2 | Allgemeine Syntax | Zur Lösung |
Welche der genannten Bestandteile eines SELECT-Befehls sind unbedingt erforderlich?
| Übung 3 | Spaltenliste | Zur Lösung |
Welche der folgenden Spaltenlisten aus der Beispieldatenbank sind richtig, welche nicht?
- SELECT * FROM Mitarbeiter;
- SELECT ID, Name FROM Mitarbeiter;
- SELECT ID, Name FROM Mitarbeiter, Abteilung;
- SELECT ID, Name, Kuerzel FROM Mitarbeiter, Abteilung;
- SELECT ab.ID, Name FROM Mitarbeiter, Abteilung ab;
- SELECT ab.ID, Name, Krz Kuerzel FROM Mitarbeiter, Abteilung ab;
- SELECT ab.ID, Name, Kuerzel Krz FROM Mitarbeiter, Abteilung ab;
- SELECT ab.ID, mi.Name, ab.Kuerzel FROM Mitarbeiter mi, Abteilung ab;
| Übung 4 | Spaltenliste | Zur Lösung |
Schreiben Sie für folgende Abfragen die Spalten und Tabellen auf.
- Zeige alle Informationen zu den Mitarbeitern.
- Zeige zu jedem Mitarbeiter Name, Vorname und Nummer der Abteilung.
- Zeige zu jedem Mitarbeiter Name, Vorname und Kuerzel der Abteilung.
- Zeige zu jedem Mitarbeiter ID, Name, Vorname sowie das Kennzeichen des Dienstwagens.
| Übung 5 | Suchbedingungen | Zur Lösung |
Welche der folgenden Suchbedingungen sind richtig, welche nicht? Welche korrekten Bedingungen liefern immer FALSE als Ergebnis?
- WHERE Name NOT = 'Meyer';
- WHERE 1 = 2;
- WHERE NOT Name LIKE 'M%';
- WHERE Geburtsdatum LIKE '1980';
- WHERE ID BETWEEN 20 AND 10;
- WHERE Mobil IS NULL;
- WHERE Name IS NULL;
- WHERE Name STARTING WITH 'L' AND CONTAINING 'a';
- WHERE ID IN (1, 3, 'A');
- WHERE ID IN (1, 3, '5');
| Übung 6 | Suchbedingungen | Zur Lösung |
Formulieren Sie die folgenden Aussagen als Bedingungen der WHERE-Klausel zur Tabelle Mitarbeiter.
- Der Vorname lautet 'Petra'.
- Der Name enthält die Zeichen 'mann'.
- Der Name beginnt mit 'A', es handelt sich um Abteilung 8.
- Es ist keine Mobil-Nummer gespeichert.
| Lösung zu Übung 1 | Allgemeine Syntax | Zur Übung |
Richtig ist dies (mit Vervollständigung zu einem Befehl):
SELECT DISTINCT <spalten> FROM <tabelle> WHERE <bedingung> GROUP BY <spalten> HAVING <bedingung> ORDER BY <spalten>;
| Lösung zu Übung 2 | Allgemeine Syntax | Zur Übung |
SELECT <spalten> FROM <tabelle>
| Lösung zu Übung 3 | Spaltenliste | Zur Übung |
Richtig sind 1, 2, 5, 7, 8.
Falsch sind 3, 4 (ID ist mehrdeutig), 6 (Spaltenname und -Alias in falscher Reihenfolge).
| Lösung zu Übung 4 | Spaltenliste | Zur Übung |
- SELECT * FROM Mitarbeiter
- SELECT Name, Vorname, Abteilung_ID FROM Mitarbeiter
- SELECT Name, Vorname, Kuerzel FROM Mitarbeiter, Abteilung
/* oder mit Tabellen-Alias: */
SELECT mi.Name, mi.Vorname, ab.Kuerzel FROM Mitarbeiter mi, Abteilung ab SELECT Name, Vorname, Dienstwagen.ID, Kennzeichen FROM Mitarbeiter, Dienstwagen
/* auch einheitlich mit Tabellen-Namen oder Tabellen-Alias möglich */
| Lösung zu Übung 5 | Suchbedingungen | Zur Übung |
- Richtig.
- Richtig, immer FALSE: 1 ist immer ungleich 2.
- Richtig, weil die Teilbedingung "Name LIKE 'M%'" verneint wird. Ebenfalls richtig wäre es, das NOT hinter <wert1>, also hinter Name zu schreiben.
- Richtig, weil das Jahr laut ISO 8601 am Anfang steht.
- Richtig, immer FALSE: es gibt keine Zahl „größer/gleich 20“ und gleichzeitig „kleiner/gleich 10“.
- Richtig.
- Richtig, immer FALSE, weil der Name als Pflichtangabe niemals NULL sein kann.
- Falsch, weil Name in der zweiten Bedingung hinter AND fehlt.
- Falsch, weil 'A' keine Zahl ist, aber zu ID bei IN eine Liste von Zahlen gehört.
- Richtig, weil '5' automatisch als Zahl konvertiert wird.
| Lösung zu Übung 6 | Suchbedingungen | Zur Übung |
- WHERE Vorname = 'Petra';
- WHERE Name CONTAINING 'mann';
- WHERE Name STARTING WITH 'A' AND Abteilung_ID = 8;
WHERE Mobil = '' OR Mobil IS NULL;
| Funktionen (2) |
Dieses Kapitel behandelt weitere Skalarfunktionen in Ergänzung zu den grundlegenden Funktionen.
Allgemeine Hinweise
Auch hier gelten die dort aufgeführten Hinweise:
- Jedes DBMS bietet eigene Funktionen sowie Varianten.
- Die Klammern werden in den Beschreibungen der Funktionen oft nicht angegeben.
- Die Beispiele werden durch eine verkürzte Schreibweise dargestellt, wobei der Zusatz "from fiktiv" als optional gekennzeichnet ist und für Firebird/Interbase durch "from rdb$database" bzw. für Oracle durch "from dual" zu ersetzen ist.
SELECT 2 * 3 [from fiktiv];
Funktionen für Zahlen
Auch bei diesen weiteren Funktionen müssen Sie auf den Typ achten.
POWER und SQRT – Potenzen und Wurzeln
Mit POWER wird eine beliebige Potenz oder Wurzel berechnet:
POWER( <basis>, <exponent> )
Sowohl für <basis> als auch für <exponent> sind nicht nur ganze positive Zahlen, sondern alle Zahlen zulässig. Mit Dezimalzahlen als <exponent> werden (genau nach mathematischen Regeln) beliebige Wurzeln berechnet; in diesem Fall sind als <basis> negative Zahlen unzulässig. Beispiele:
SELECT POWER( 5, 3 ) [from fiktiv]; /* Ergebnis: 125,000 */ SELECT POWER( 5, 2.5) [from fiktiv]; /* Ergebnis: 55,902 */ SELECT POWER( 5, 0.5) [from fiktiv]; /* Ergebnis: 2,236 also Wurzel aus 5 */ SELECT POWER( 0.5, -3 ) [from fiktiv]; /* Ergebnis: 8,000 also 3.Potenz zu 2 */ SELECT POWER( 12.35, 1.5) [from fiktiv]; /* Ergebnis: 43,401 */ SELECT POWER(-12.35, 1.5) [from fiktiv]; /* expression evaluation not supported. */ SELECT POWER( 12.35,-1.5) [from fiktiv]; /* Ergebnis: 0,023 */ SELECT POWER(-12.35,-1.5) [from fiktiv]; /* expression evaluation not supported */
Mit SQRT (= Square Root) gibt es für die Quadratwurzel eine kürzere Schreibweise anstelle von POWER(x,0.5):
SELECT SQRT(12.25) [from fiktiv]; /* Ergebnis: 3,500 */
EXP und LOG – Exponentialfunktion und Logarithmen
Mit EXP wird die Exponentialfunktion im engeren Sinne bezeichnet, also mit der Eulerschen Zahl e als Basis.
SELECT EXP(1) [from fiktiv]; /* Ergebnis: 2,71828182845905 */
Mit LOG(<wert>, <basis>) wird umgekehrt ein Logarithmus bestimmt, mit LN der natürliche und mit LOG10 der dekadische Logarithmus.
SELECT LOG(10, EXP(1)) [from fiktiv]; /* Ergebnis: 2,30258509299405 */ SELECT LOG(10, 10) [from fiktiv]; /* Ergebnis: 1,000 */ SELECT LN(10) [from fiktiv]; /* Ergebnis: 2,30258509299405 */
Winkelfunktionen
Die trigonometrischen Funktionen arbeiten mit dem Bogenmaß (nicht mit einer Grad-Angabe).
SIN Sinus COS Cosinus TAN Tangens COT Cotangens ASIN Arcussinus als Umkehrfunktion des Sinus ACOS Arcuscosinus als Umkehrfunktion des Cosinus ATAN Arcustangens als Umkehrfunktion des Tangens
Mit DEGREES wird ein Bogenmaß in Grad umgerechnet, mit RADIANS ein Gradmaß in das Bogenmaß.
PI liefert die entsprechende Zahl und kann auch für die trigonometrischen Funktionen verwendet werden:
SELECT SIN( PI()/6 ) [from fiktiv]; /* sind 30°, also Ergebnis 0,5 */
ABS, RAND, SIGN – verschiedene Funktionen
Mit ABS wird der absolute Betrag der gegebenen Zahl zurückgegeben.
SIGN liefert als Hinweis auf das Vorzeichen der gegebenen Zahl einen der Werte 1, 0, -1 – je nachdem, ob die gegebene Zahl positiv, 0 oder negativ ist.
SELECT SIGN(12.34), SIGN(0), SIGN(-5.67) [from fiktiv]; /* Ergebnis: 1 0 -1 */
RAND liefert eine Zufallszahl im Bereich zwischen 0 und 1 (jeweils einschließlich). Bitte beachten Sie, dass dies keine echten Zufallszahlen sind, sondern Pseudozufallszahlen.
Mit RAND(<vorgabewert>) wird innerhalb einer Sitzung immer dieselbe Zufallszahl erzeugt.
Mit einer Kombination von RAND und FLOOR erhält man eine „zufällige“ Folge ganzer Zahlen:
FLOOR( <startwert> + ( RAND() * ( <zielwert> - <startwert> + 1 )) )
Beispielsweise liefert die mehrfache Wiederholung der folgenden Abfrage diese Zahlen zwischen 7 und 12:
SELECT FLOOR(7 + (RAND() * 6)) [from fiktiv]; /* Ergebnisse: 10 9 9 7 8 7 9 9 10 12 usw. */
Diese Funktion ist geeignet, um Datensätze mit SELECT in beliebiger Reihenfolge oder einen zufällig ausgewählten Datensatz abzurufen:
SELECT * FROM <tabelle> ORDER BY RAND(); SELECT FIRST 1 * FROM <tabelle> ORDER BY RAND();
Funktionen für Zeichenketten
Auch zur Bearbeitung und Prüfung von Zeichenketten (Strings) gibt es weitere Funktionen.
Verknüpfen von Strings
Zu den Standardverfahren || + CONCAT gibt es Ergänzungen.
MySQL bietet mit CONCAT_WS eine nützliche Erweiterung, bei der zwischen den Teiltexten ein Trennzeichen gesetzt wird.
SPACE(n) – für MS-SQL und MySQL – erzeugt einen String, der aus n Leerzeichen besteht.
REPEAT( <text>, <n> ) – für MySQL – und REPLICATE( <text>, <n> ) – für MS-SQL – erzeugen eine neue Zeichenkette, in der der <text> n-mal wiederholt wird.
Mit LPAD wird <text1>, sofern erforderlich, auf die gewünschte <länge> gebracht und dabei von links mit <text2> bzw. Leerzeichen aufgefüllt. Mit RPAD wird von rechts aufgefüllt. MS-SQL kennt diese Funktionen nur für Access.
LPAD ( <text1>, <länge> [ , <text2> ] ) RPAD ( <text1>, <länge> [ , <text2> ] )
Wenn der dadurch erzeugte Text zu lang wird, wird zuerst <text2> und notfalls auch <text1> abgeschnitten. Beispiele:
SELECT LPAD( CAST(12345 AS CHAR(8)), 10, '0') [from fiktiv]; -- Ergebnis: '0012345 '
Nanu, das sind doch nur 7 statt 10 Ziffern? Achso, zuerst wird mit CAST ein 8 Zeichen langer String erzeugt; dann ist nur noch Platz für 2 Nullen. Also muss es mit einer dieser Varianten gehen:
SELECT LPAD( CAST(12345 AS CHAR(5)), 10, '0') [from fiktiv]; -- Ergebnis: '0000012345' SELECT LPAD( 12345, 10, '0' ) [from fiktiv]; -- Ergebnis: '0000012345' SELECT LPAD( 'Hilfe', 10, '-_/') [from fiktiv]; -- Ergebnis: '-_/-_Hilfe' SELECT LPAD( 'Ich brauche Hilfe', 10, '-_/') [from fiktiv]; -- Ergebnis: 'Ich brauch' SELECT RPAD( 'Hilfe', 10, '-_/') [from fiktiv]; -- Ergebnis: 'Hilfe-_/-_' SELECT RPAD( 'Ich brauche Hilfe', 10, '-_/') [from fiktiv]; -- Ergebnis: 'Ich brauch'
LEFT, RIGHT – Teile von Zeichenketten
Als Ergänzung zu SUBSTRING wird mit LEFT( <text>, <anzahl> ) der linke Teil, also der Anfang eines Textes mit der gewünschten Länge <anzahl> ausgegeben. Ebenso erhält man mit RIGHT( <text>, <anzahl> ) den rechten Teil, also das Ende eines Textes.
SELECT LEFT ('Abc Def Ghi', 5) [from fiktiv]; /* Ergebnis: 'Abc D' */
SELECT RIGHT('Abc Def Ghi', 5) [from fiktiv]; /* Ergebnis: 'f Ghi' */
TRIM, LTRIM, RTRIM – Leerzeichen u. a. entfernen
Mit der TRIM-Funktion werden bestimmte Zeichen – meistens Leerzeichen – am Anfang und/oder am Ende eines Textes entfernt:
TRIM( [ [ LEADING | TRAILING | BOTH ] [ <zeichen> ] FROM ] <text> )
Die Parameter werden wie folgt benutzt:
- Es soll <zeichen> entfernt werden. Es kann sich um ein einzelnes Zeichen, aber auch um einen Text handeln.
- Wenn dieser Parameter fehlt, wird nach führenden bzw. abschließenden Leerzeichen gesucht.
- Wenn LEADING angegeben ist, werden nur führende Zeichen geprüft, bei TRAILING nachfolgende und bei BOTH sowohl als auch. Wenn nichts davon angegeben wird, wird BOTH angenommen.
Beispiele:
SELECT TRIM( ' Dies ist ein Text. ' ) [from fiktiv]; /* Ergebnis: 'Dies ist ein Text.' */ SELECT TRIM( LEADING 'a' FROM 'abcde' ) [from fiktiv]; /* Ergebnis: 'bcde' */ SELECT TRIM( TRAILING 'e' FROM 'abcde' ) [from fiktiv]; /* Ergebnis: 'abcd' */ SELECT TRIM( 'Test' FROM 'Test als Test') [from fiktiv]; /* Ergebnis: ' als ' */
LTRIM (= Left-Trim) und RTRIM (= Right-Trim) sind Kurzfassungen, bei denen Leerzeichen am Anfang bzw. am Ende entfernt werden; MS-SQL kennt nur diese beiden Kurzfassungen.
Suchen und Ersetzen
Mit POSITION wird der Anfang eines Textes innerhalb eines anderen gesucht.
POSITION( <text1> IN <text2> ) SQL-Standard POSITION( <text1>, <text2> [, <start>] ) nicht bei MySQL LOCATE ( <text1>, <text2> [, <start>] ) bei MySQL
Bei MS-SQL gibt es diese Funktionen nicht; stattdessen kann (mit abweichender Bedeutung) PATINDEX verwendet werden. Oracle bietet zusätzlich INSTR (= "in string") an.
Die Bedeutung der Parameter dürfte offensichtlich sein:
- <text2> ist der Text, in dem gesucht werden soll.
- <text1> ist ein Teiltext, der in <text2> gesucht wird.
- Sofern <start> angegeben ist, wird erst ab dieser Position innerhalb <text2> gesucht. Wenn <start> fehlt, wird ab Position 1 gesucht.
- Die Funktion gibt die Startposition von <text1> innerhalb von <text2> an. Wenn <text1> nicht gefunden wird, lautet das Ergebnis 0.
Beispiele:
SELECT POSITION( 'ch', 'Ich suche Text' ) [from fiktiv]; /* Ergebnis: 2 */
SELECT POSITION( 'ch', 'Ich suche Text', 3 ) [from fiktiv]; /* Ergebnis: 7 */
SELECT POSITION('sch', 'Ich suche Text' ) [from fiktiv]; /* Ergebnis: 0 */
REPLACE dient zum Ersetzen eines Teiltextes durch einen anderen innerhalb eines Gesamttextes:
REPLACE( <quelltext>, <suche>, <ersetze> )
Die verschiedenen SQL-Dialekte verhalten sich unterschiedlich, ob NULL-Werte oder leere Zeichenketten zulässig sind.
SELECT REPLACE('Ich suche Text', 'ch', 'sch') [from fiktiv]; /* Ergebnis: 'Isch susche Text' */
SELECT REPLACE('Die liebe Seele', 'e', ’’) [from fiktiv]; /* Ergebnis: 'Di lib Sl' */
REVERSE passt zwar nicht zu dem, was man in diesem Zusammenhang erwartet; aber auch diese Funktion ändert einen vorhandenen String, und zwar dadurch, dass die Reihenfolge aller Zeichen umgekehrt wird:
SELECT REVERSE( 'Hilfe' ) [from fiktiv]; /* Ergebnis: 'efliH' */
Funktionen für Datums- und Zeitwerte
Bitte beachten Sie wiederum die Besonderheiten der Datentypen je nach DBMS.
Differenzen bei Datum oder Uhrzeit
Dafür gibt es vorzugsweise die DATEDIFF-Funktion in unterschiedlicher Version:
DATEDIFF ( <part>, <start>, <end> ) /* bei MS-SQL oder Firebird */ DATEDIFF ( <start>, <end> ) /* bei MySQL nur die Anzahl der Tage */
Das Ergebnis ist vom gleichen Typ wie die gesuchte Differenz (also meistens ein ganzzahliger Wert). Als <part> gibt es die gleichen Varianten wie bei den wichtigsten Funktionen:
YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | MILLISECOND
Beim Vergleich von Start- und Enddatum gilt:
- Das Ergebnis ist positiv, wenn der zweite Wert größer ist als der erste.
- Das Ergebnis ist 0, wenn beide Werte gleich sind.
- Das Ergebnis ist negativ, wenn der zweite Wert kleiner ist als der erste.
Bitte beachten Sie: Die DBMS verhalten sich unterschiedlich, ob die Datumsangaben verglichen werden oder der jeweilige Bestandteil. Beispielsweise kann das Ergebnis für beide der folgenden Prüfungen 1 lauten, obwohl die „echte“ Differenz im einen Fall ein Tag, im anderen fast zwei Jahre sind:
DATEDIFF( YEAR, '31.12.2008', '01.01.2009' ) DATEDIFF( YEAR, '01.01.2008', '31.12.2009' )
Bestimme die Anzahl der Tage seit dem letzten gemeldeten Schadensfall.
SELECT DATEDIFF(DAY, MAX(Datum), CURRENT_DATE) FROM Schadensfall; /* Ergebnis: 49 */
Bestimme die Anzahl der Minuten seit Tagesbeginn.
SELECT DATEDIFF(MINUTE, CAST('00:00' AS TIME), CURRENT_TIME) [from fiktiv]; /* Ergebnis: 967 */
Datumsangaben können grundsätzlich auch per Subtraktion verglichen werden, weil „intern“ häufig ein Tag gleich 1 ist. Darauf kann man sich aber nicht immer verlassen; und es ist schwierig, die Bruchteile eines Tages zu berücksichtigen. Beispiel:
SELECT (CURRENT_DATE - MAX(Datum)) FROM Schadensfall; /* Ergebnis: 49 */
Werte für Datum oder Uhrzeit ändern
Sehr häufig muss aus einem vorhandenen Datum oder Uhrzeit ein neuer Wert berechnet werden. Der SQL-Standard sieht dazu die direkte Addition und Subtraktion vor:
<datetime> + <value> <datetime> - <value> <value> + <datetime>
<datetime> steht für den gegebenen Wert, <value> für den Zeitraum, der addiert oder subtrahiert werden soll.
Aus dem aktuellen Zeitwert '19.09.2009 16:10' wird ein neuer Wert bestimmt, der einen halben Tag in der Zukunft liegt:
SELECT CURRENT_TIMESTAMP + 0.5 [from fiktiv]; /* Ergebnis: '20.09.2009 04:10:39' */
MySQL akzeptiert nur ganze Zahlen; deshalb ist explizit die Art des Intervalls anzugeben (siehe Dokumentation).
Da das Umrechnen von Zahlen in Datums- und Zeitwerte und umgekehrt für den Anwender umständlich ist, werden viele zusätzliche Funktionen bereitgestellt. Sehr verbreitet ist DATEADD:
DATEADD( <part>, <value>, <datetime> ) /* Firebird, MS-SQL */ DATE_ADD( <datetime> , INTERVAL <value> <part> ) /* MySQL */
Welche Versicherungsverträge laufen schon mehr als 10 Jahre?
SELECT ID, Vertragsnummer, Abschlussdatum FROM Versicherungsvertrag WHERE DATEADD(YEAR, 10, Abschlussdatum) <= CURRENT_DATE; /* Ergebnis: 18 Datensätze */
Als Ergänzung oder Alternative gibt es weitere Funktionen, beispielsweise DATE_SUB als Subtraktion, ADDDATE oder ADD_MONTHS.
Funktionen für logische und NULL-Werte
Neben den Standardprüfungen vor allem bei der WHERE-Klausel (siehe nächstes Kapitel) und den Operatoren AND, OR, NOT gibt es weitere Prüfungen.
COALESCE – Suche Wert ungleich NULL
Die COALESCE-Funktion sucht in einer Liste von Werten (bzw. Ausdrücken) den ersten, der nicht NULL ist. Wenn alle Werte NULL sind, ist der Rückgabewert (zwangsläufig) NULL.
Nenne zu jedem Mitarbeiter eine Kontaktmöglichkeit: vorzugsweise Mobilnummer, dann Telefonnummer, dann Email-Adresse.
SELECT Name, Vorname, COALESCE(Mobil, Telefon, Email) AS Kontakt FROM Mitarbeiter;
Das Ergebnis überrascht zunächst, denn einige Mitarbeiter hätten danach keine Kontaktmöglichkeit. Bei der Abfrage nach IS NULL zur WHERE-Klausel wird aber erläutert, dass eine leere Zeichenkette ungleich NULL ist; bei diesen Mitarbeitern wird also ein leerer Eintrag, aber nicht "nichts" angezeigt.
- Bitte nehmen Sie diesen Hinweis als Empfehlung, lieber NULL zu speichern als den leeren String ’’.
NULLIF
Die Funktion NULLIF vergleicht zwei Werte und liefert NULL zurück, wenn beide Werte gleich sind; andernfalls liefert der erste Wert das Ergebnis.
Suche alle Versicherungsnehmer, die im Alter von 18 Jahren ihren Führerschein gemacht haben.
select Name, Vorname, EXTRACT(YEAR from Geburtsdatum) + 18 as Jahr
from Versicherungsnehmer
where Geburtsdatum is not null
and NULLIF( EXTRACT(YEAR from Geburtsdatum) + 18, EXTRACT(YEAR from Fuehrerschein) ) is null;
AusgabeNAME VORNAME JAHR
------------ --------- ----
Liebermann Maria 1988
Hinweis: Sowohl COALESCE als auch NULLIF sind Kurzfassungen für spezielle Fallunterscheidungen mit CASE WHEN, in denen zusätzlich IS NULL eingebunden wird – siehe dazu Nützliche Erweiterungen.
Verschiedene Funktionen
Auch wenn die Funktionen in diesem Abschnitt beim SQL-Standard vorgesehen sind, sind sie nicht immer vorhanden. Wir verzichten deshalb wiederum auf nähere Erläuterungen und verweisen auf die jeweilige Dokumentation.
ROW_NUMBER – Zeilen nummerieren
Mit der ROW_NUMBER-Funktion werden die Zeilen im Ergebnis einer Abfrage nach der betreffenden Sortierung durchnummeriert. MS-SQL verwendet die folgende Syntax:
ROW_NUMBER() OVER ( [ <partition_by_clause> ] <order_by_clause> )
CURRENT_USER – der aktuelle Benutzer
Mit CURRENT_USER (in der Regel ohne Klammer) wird der aktuelle Benutzername abgefragt. Dieser kann auch per DEFAULT bei Neuaufnahmen automatisch in einer Spalte einer Tabelle eingetragen werden.
SELECT CURRENT_USER [from fiktiv]; /* Ergebnis: SYSDBA */
Zusammenfassung
In diesem Kapitel lernten Sie weitere eingebaute Funktionen kennen:
- Für Zahlen gibt es viele mathematische Funktionen wie Potenzen, Wurzeln, Exponential- oder Winkelfunktionen.
- Zeichenketten können auf vielfache Weise verknüpft oder zum Erstellen neuer Strings bearbeitet werden.
- Datums- und Zeitwerte können im Detail verglichen und verrechnet werden.
Übungen
| Übung 1 | Funktionen für Zahlen | Zur Lösung |
Geben Sie mit SQL-Funktionen die Formeln für die folgenden Aufgaben an. Es geht nur um die Formeln, nicht um einen passenden SELECT-Befehl. Bei den Aufgaben 1 bis 3 sind jeweils zwei Lösungen möglich.
- Gegeben seien zwei Zahlen a, b. Berechnen Sie a² + 2ab + b².
- Berechnen Sie die Quadratwurzel von 216,09.
- Ein Auftragsverwaltungsprogramm speichert in der Spalte Bestellung die Bestellwerte und in der Spalte Zahlung die Einzahlungen. Bestimmen Sie mit einem einzigen Ausdruck die Prüfung, ob der Kunde Guthaben oder Schulden (Zahlungsverpflichtung) hat.
- Bestimmen Sie den Betrag von Aufgabe 3 (unabhängig davon, ob es sich um Guthaben oder Schulden handelt).
| Übung 2 | Zufallszahlen | Zur Lösung |
Bestimmen Sie mit SQL-Funktionen die folgenden Zufallszahlen.
- eine beliebige Zufallszahl zwischen 0 und 1
- eine beliebige Zufallszahl zwischen 0 und 4
- eine beliebige Zufallszahl zwischen 1 und 5
- eine Zufallszahl als ganze Zahl zwischen 1 und 5
- eine Zufallszahl als ganze Zahl zwischen 1 und 26
- einen zufällig ausgewählten Buchstaben aus der Liste letters der Großbuchstaben 'ABC...XYZ'
| Übung 3 | Zeichenketten bearbeiten | Zur Lösung |
Aus der Tabelle Mitarbeiter sollen die Werte ID und Abteilung_ID zusammengesetzt werden. Dabei soll die ID immer 4-stellig und die Abteilung_ID immer 2-stellig geschrieben werden, bei Bedarf sollen die Teile mit '0' aufgefüllt werden.
| Übung 4 | Zeichenketten bearbeiten | Zur Lösung |
Geben Sie für die Tabelle Mitarbeiter eine der vorhandenen Telefonnummern an – vorrangig die Mobilnummer; berücksichtigen Sie dabei auch, ob überhaupt eine Nummer gespeichert ist.
| Übung 5 | Zeichenketten bearbeiten | Zur Lösung |
Zeigen Sie für die Spalte Kennzeichen der Tabelle Fahrzeug den zugehörigen Kreis an.
| Übung 6 | Zeichenketten bearbeiten | Zur Lösung |
In der Beschreibung für den Schadensfall mit der ID 6 ist das Kennzeichen 'RE-LM 903' durch 'RE-LM 902' zu berichtigen.
| Übung 7 | Datum und Zeit bearbeiten | Zur Lösung |
Die folgenden Teilaufgaben werden benutzt, um im Kapitel Testdaten erzeugen Geburtsdatum, Führerschein-Erwerb oder Abschluss des Versicherungsvertrags zufällig zu bestimmen.
- Bestimmen Sie aus dem Geburtsdatum das Datum des 18. Geburtstags.
- Bestimmen Sie (ausgehend vom 01.01.1950) Datumsangaben bis zum 31.12.1990, bei denen der Monat und das Jahr per Zufallsfunktion bestimmt werden.
- Bestimmen Sie ebenso Datumsangaben, bei denen auch der Tag zufällig festgelegt wird und immer ein gültiges Datum erzeugt wird.
- Prüfen Sie, ob ein neuer Kunde seinen Führerschein bei Vertragsabschluss bereits drei Jahre besitzt. Sie können annehmen, dass sowohl Fuehrerschein als auch Abschlussdatum in derselben Tabelle liegen; Schaltjahre können ignoriert werden.
| Lösung zu Übung 1 | Funktionen für Zahlen | Zur Übung |
- a.
POWER(a, 2) + 2*a*b + POWER(b, 2)
b.POWER(a + b, 2)als einfachste Binomische Formel - a.
SQRT(216.09)
b.POWER(216.09, 0.5) - a.
IF ( SUM(Bestellung) > SUM(Zahlung) )
b.IF( SIGN( SUM(Bestellung) – SUM(Zahlung) ) = 1) ABS( SUM(Bestellung) – SUM(Zahlung) )
| Lösung zu Übung 2 | Zufallszahlen | Zur Übung |
- RAND()
- RAND() * 4
- 1 + RAND() * 4
- FLOOR( 1 + RAND() * 4 )
- FLOOR( 1 + RAND() * 25 )
- SUBSTRING( letters FROM FLOOR( 1 + RAND() * 25 ) FOR 1 )
| Lösung zu Übung 3 | Zeichenketten bearbeiten | Zur Übung |
SELECT LPAD( ID, 4, '0') + LPAD( Abteilung_ID, 2, '0') FROM Mitarbeiter;
| Lösung zu Übung 4 | Zeichenketten bearbeiten | Zur Übung |
SELECT COALESCE( NULLIF(Mobil, ’’), Telefon) AS Tel FROM Mitarbeiter;
Erklärung: Wenn Mobil einen Wert enthält, kommt bei NULLIF dieser Wert heraus; andernfalls wird immer NULL geliefert – entweder als Feldinhalt oder als Ergebnis des Vergleichs mit der leeren Zeichenkette.
| Lösung zu Übung 5 | Zeichenketten bearbeiten | Zur Übung |
SELECT SUBSTRING( Kennzeichen FROM 1 FOR Position('-', Kennzeichen) - 1) FROM Fahrzeug;
| Lösung zu Übung 6 | Zeichenketten bearbeiten | Zur Übung |
UPDATE Schadensfall SET Beschreibung = REPLACE( Beschreibung, 'RE-LM 903', 'RE-LM 902' ) WHERE ID = 6;
| Lösung zu Übung 7 | Datum und Zeit bearbeiten | Zur Übung |
Die Teilaufgaben liefern diese Einzellösungen.
DATEADD( YEAR, 18, Geburtsdatum )DATEADD( MONTH, FLOOR(RAND()*11), DATEADD( YEAR, FLOOR(RAND()*40), '1950-01-01'))
Es handelt sich um eine verschachtelte Funktion: Zuerst wird das Jahr neu bestimmt, es wird maximal ein Wert von 40 addiert. Zu diesem Ergebnis wird der Monat neu bestimmt, es wird maximal 11 addiert. In manchen Fällen sind einzelne Angaben per CAST genauer zu definieren.DATEADD( DAY, FLOOR(RAND()*(41*365+10)), '1950-01-01')
Es handelt sich um 41 Jahre zu je 365 Tagen, dazu 10 Schalttage.SELECT Fuehrerschein, Abschlussdatum FROM (Vertrag/Kunde)
WHERE DATEDIFF( DAY, Fuehrerschein, Abschlussdatum) < 3*365
Hinweis:DATEDIFF(YEAR...)ist nicht unbedingt geeignet, weil ein DBMS nicht die Termine, sondern die Jahreszahlen vergleichen könnte.
Siehe auch
Bei Wikipedia gibt es fachliche Erläuterungen:
- Potenzen
- Exponentialfunktion
- Trigonometrische Funktionen und Bogenmaß
- Betragsfunktion – der absolute Betrag
- Zufallszahl gibt auch Hinweise zu „Pseudozufallszahlen“.
- Binomische Formeln
| WHERE-Klausel im Detail |
In diesem Kapitel werden die Einzelheiten der WHERE-Klausel genauer behandelt. Diese Angaben sind vor allem für den SELECT-Befehl, aber auch für UPDATE und DELETE von Bedeutung.
Die Beispiele beziehen sich auf den Anfangsbestand der Beispieldatenbank; auf die Ausgabe der selektierten Datensätze wird verzichtet. Bitte probieren Sie alle Beispiele aus und nehmen Sie verschiedene Änderungen vor, um die Auswirkungen zu erkennen.
Allgemeine Hinweise
Die WHERE-Klausel ist (neben der Verknüpfung mehrerer Tabellen) der wichtigste Bestandteil des SELECT-Befehls: Je sorgfältiger die Auswahlbedingungen formuliert werden, desto genauer ist das Ergebnis der Abfrage.
Neben den hier erläuterten Varianten bietet jedes DBMS noch andere, z. B. STARTING WITH oder SIMILAR.
Anstelle konstanter Werte können auch passende Ausdrücke angegeben werden, z. B. Funktionen oder Unterabfragen.
Verwenden Sie bei den Beispielen möglichst immer auch die Umkehrung der Auswahl mit bzw. ohne NOT. Wie im Kapitel „Ausführliche SELECT-Struktur“ zur WHERE-Klausel angegeben, steht das NOT ggf. unmittelbar vor dem Parameter-Namen.
Eine einzelne Bedingung
Größenvergleich zweier Werte
Der einfachste Weg ist der direkte Vergleich zweier Werte, nämlich der Inhalt einer Spalte mit einem konstanten Wert. Dies ist möglich mit den folgenden Vergleichsoperatoren, und zwar für alle Datentypen, die verglichen werden können – Zahlen, Zeichenketten, Datumsangaben.
= < > <= >= <>
Beispiele:
Suche einen Datensatz, bei dem der Wert in der Spalte ID gleich ist zu einem vorgegebenen Wert.
select * from Versicherungsnehmer
where ID = 10;
Suche Datensätze, bei denen der Name kleiner als 'B' ist, also mit 'A' anfängt.
select * from Versicherungsnehmer
where Name < 'B';
Suche Führerschein-Neulinge.
select * from Versicherungsnehmer
where Fuehrerschein >= '01.01.2007';
Suche Fahrzeugtypen mit kurzer Bezeichnung.
select * from Fahrzeugtyp
where Char_Length(Bezeichnung) <= 3;
Bei diesen Vergleichen ist NOT zwar ebenfalls möglich; besser verständlich ist aber ein anderer passender Operator.
BETWEEN AND – Werte zwischen zwei Grenzen
Mit der Bedingung BETWEEN <wert1> AND <wert2> wird direkt mit einem Bereich verglichen; die Grenzwerte gehören meistens zum Bereich (abhängig vom DBMS). Auch dies ist möglich für Zahlen, Zeichenketten, Datumsangaben.
Suche Datensätze, bei denen die PLZ außerhalb eines Bereichs 45000...45999 liegt.
select * from Versicherungsnehmer
where PLZ NOT BETWEEN '45000' AND '45999';
LIKE – Ähnlichkeiten (1)
Die LIKE-Bedingung vergleicht Zeichenketten „ungenau“: Der gesuchte Text soll als Wert in einer Spalte enthalten sein; dazu werden „Wildcards“ benutzt: Der Unterstrich '_' steht für ein beliebiges einzelnes Zeichen, das an der betreffenden Stelle vorkommen kann. Das Prozentzeichen '%' steht für eine beliebige Zeichenkette mit 0 oder mehr Zeichen.
Diese Bedingung wird vor allem in zwei Situationen gerne benutzt:
- Der Suchbegriff ist sehr lang; dem Anwender soll es genügen, den Anfang einzugeben.
- Der Suchbegriff ist nicht genau bekannt (z. B. nicht richtig lesbar).
Beispiele:
Der Ortsname beginnt nicht mit 'B'; der Inhalt dahinter ist beliebig.
select * from Versicherungsnehmer
where Ort NOT LIKE 'B%';
Der Ortsname enthält irgendwo 'alt' mit beliebigem Inhalt davor und dahinter.
select * from Versicherungsnehmer
where Ort LIKE '%alt%';
Der Anfangsbuchstabe des Namens ist unklar, aber danach folgen die Buchstaben 'ei' und noch etwas mehr.
select * from Versicherungsnehmer
where Name LIKE '_ei%';
Ein Problem haben wir, wenn eines der Wildcard-Zeichen Teil des Suchbegriffs sein soll. Dann muss dem LIKE-Parameter mitgeteilt werden, dass '%' bzw. '_' als „echtes“ Zeichen zu verstehen ist. Das geschieht dadurch, dass ein spezielles Zeichen davor gesetzt wird und dieses Zeichen als „ESCAPE-Zeichen“ angegeben wird:
Innerhalb der Beschreibung kommt die Zeichenfolge '10%' vor.
select * from Schadensfall
where Beschreibung LIKE '%10\%%' ESCAPE '\';
Das erste und das letzte Prozentzeichen stehen dafür, dass vorher und nachher beliebige Inhalte möglich sind. Das mittlere Prozentzeichen wird mit dem Escape-Zeichen '\' verbunden und ist damit Teil der gesuchten Zeichenfolge. Diese Angabe '\%' ist als ein Zeichen zu verstehen.
Vergleichen Sie das Abfrageergebnis, wenn der ESCAPE-Parameter weggelassen wird oder wenn eines oder mehrere der Sonderzeichen im LIKE-Parameter fehlen.
CONTAINS u. a. – Ähnlichkeiten (2)
Ein Problem des LIKE-Parameters ist die Verwendung der Wildcard-Zeichen '%' und '_', die man gerne vergisst oder (wie im letzten Beispiel) nicht genau genug beachtet. Deshalb gibt es verschiedene Vereinfachungen.
CONTAINS – in Firebird CONTAINING – prüft, ob eine Zeichenkette im Feldinhalt enthalten ist.
select * from Schadensfall
where Beschreibung CONTAINS '10%';
Bitte prüfen Sie in der Beschreibung Ihres DBMS, welche Möglichkeiten für die Suche nach Ähnlichkeiten außerdem angeboten werden.
IS NULL – null-Werte prüfen
Wie schon bei den relationalen Datenbanken besprochen, haben NULL-Werte eine besondere Bedeutung. Mit den folgenden beiden Abfragen werden nicht alle Datensätze gefunden:
select ID, Name, Vorname, Mobil
from Mitarbeiter
where Mobil <> '';
select ID, Name, Vorname, Mobil
from Mitarbeiter
where Mobil = '';
Nanu, es gibt doch 28 Mitarbeiter; wo sind die übrigen geblieben? Für diese Fälle gibt es mit IS NULL eine spezielle Abfrage:
select ID, Name, Vorname, Mobil
from Mitarbeiter
where Mobil is null;
Der Vollständigkeit halber sei darauf hingewiesen, dass die folgende Abfrage tatsächlich die richtige Gegenprobe liefert.
select ID, Name, Vorname, Mobil
from Mitarbeiter
where Mobil is not null;
Die folgende Abfrage liefert eine leere Ergebnismenge zurück, weil NULL eben kein Wert ist.
select ID, Name, Vorname, Mobil
from Mitarbeiter
where Mobil = null;
Es gibt keine einzelne Bedingung, die alle Datensätze ohne explizite Mobil-Angabe auf einmal angibt. Es gibt nur die Möglichkeit, die beiden Bedingungen "IS NULL" und "ist leer" zu verknüpfen:
select ID, Name, Vorname, Mobil
from Mitarbeiter
where ( Mobil is null ) or ( Mobil = '' );
Beachten Sie auch bei "WHERE ... IS [NOT] NULL" die Bedeutung von NULL:
- Bei Zeichenketten ist zu unterscheiden zwischen dem „leeren“ String und dem NULL-Wert.
- Bei Zahlen ist zu unterscheiden zwischen der Zahl '0' (null) und dem NULL-Wert.
- Bei Datumsangaben ist zu unterscheiden zwischen einem vorhandenen Datum und dem NULL-Wert; ein Datum, das der Zahl 0 entspräche, gibt es nicht. (Man könnte allenfalls das kleinste mögliche Datum wie '01.01.0100' benutzen, aber dies ist bereits ein Datum.)
IN – genauer Vergleich mit einer Liste
Der IN-Parameter vergleicht, ob der Inhalt einer Spalte in der angegebenen Liste enthalten ist. Die Liste kann mit beliebigen Datentypen arbeiten.
Hole die Liste aller Fahrzeuge, deren Typen als „VW-Kleinwagen“ registriert sind.
select * from Fahrzeug
where Fahrzeugtyp_ID in (1, 2);
Suche nach einem Unfall Fahrzeuge mit einer von mehreren möglichen Farben.
select * from Fahrzeug
where Farbe in ('ocker', 'gelb');
Vor allem das erste Beispiel wird sehr oft mit einer Unterabfrage versehen; vergleichen Sie dazu auch den folgenden Abschnitt zu EXISTS.
Hole die Liste aller Fahrzeuge vom Typ „Volkswagen“.
select * from Fahrzeug
where Fahrzeugtyp_ID in
( select ID from Fahrzeugtyp
where Hersteller_ID = 1 );
Dabei wird zuerst mit der Unterabfrage eine Liste aller Fahrzeugtypen-IDs für den Hersteller 1 (= Volkswagen) zusammengestellt; diese wird dann für den Vergleich über den IN-Parameter benutzt.
EXISTS – schneller Vergleich mit einer Liste
Im Gegensatz zu den anderen Parametern der WHERE-Klausel arbeitet der EXISTS-Parameter nicht mit fest vorgegebenen Werten, sondern nur mit dem Ergebnis einer Abfrage, also einer Unterabfrage. Das letzte Beispiel zum IN-Parameter kann auch so formuliert werden:
select * from Fahrzeug fz
where EXISTS
( select * from Fahrzeugtyp ft
where ft.Hersteller_ID = 1
and fz.Fahrzeugtyp_ID = ft.ID );
Zu jedem Datensatz aus der Tabelle Fahrzeug wird zu dieser Fahrzeugtyp_ID eine Unterabfrage aus den Fahrzeugtypen erstellt: Wenn es dort einen Datensatz mit passender ID und Hersteller-ID 1 (= Volkswagen) gibt, gehört der Fahrzeug-Datensatz zur Auswahl, andernfalls nicht.
Da Unterabfragen zuerst ausgeführt werden, wird eine EXISTS-Prüfung in aller Regel schneller erledigt als die entsprechende IN-Prüfung: Bei EXISTS handelt es sich um eine Feststellung „ist überhaupt etwas vorhanden“; bei IN dagegen muss ein exakter Vergleich mit allen Werten einer Liste durchgeführt werden. Bei unserer kleinen Beispieldatenbank spielt das natürlich keine Rolle, aber bei einer „echten“ Datenbank mit Millionen von Einträgen schon.
Mehrere Bedingungen verknüpfen
Bei der WHERE-Klausel geht es darum festzustellen, ob ein Datensatz Teil des Abfrageergebnisses ist oder nicht; bei der <search condition> handelt sich also um einen booleschen Ausdruck, d. h. einen Ausdruck, der einen der booleschen Werte WAHR oder FALSCH – TRUE bzw. FALSE – als Ergebnis hat. Nur bei einfachen Abfragen genügt dazu eine einzelne Bedingung; meistens müssen mehrere Bedingungen verknüpft werden (wie beim letzten Beispiel unter IS NULL).
Dazu gibt es die booleschen Operatoren NOT, AND, OR.
NOT als Negation
Dieser Operator kehrt das Ergebnis um: aus TRUE wird FALSE, aus FALSE wird TRUE.
SELECT * FROM Versicherungsnehmer
WHERE NOT (Fuehrerschein >= '01.01.2007');
AND als Konjunktion
Eine Bedingung, die durch eine AND-Verknüpfung gebildet wird, ist genau dann TRUE, wenn beide (bzw. alle) Bestandteile TRUE sind.
SELECT ID, Name, Vorname, PLZ, Ort
FROM Versicherungsnehmer
WHERE PLZ BETWEEN '45000' AND '45999'
AND Name < 'K';
OR als Adjunktion
Eine Bedingung, die durch eine OR-Verknüpfung gebildet wird, ist genau dann TRUE, wenn mindestens ein Bestandteil TRUE ist; dabei ist es gleichgültig, ob die anderen Bestandteile TRUE oder FALSE sind.
SELECT ID, Name, Vorname, PLZ, Ort
FROM Versicherungsnehmer
WHERE PLZ BETWEEN '45000' AND '45999'
OR Name < 'K';
Bitte beachten Sie, dass der normale Sprachgebrauch „alle ... und alle ...“ sagt. Gemeint ist nach logischen Begriffen aber, dass <Bedingung 1> erfüllt sein muss ODER <Bedingung 2> ODER BEIDE.
XOR als Kontravalenz
Eine Bedingung, die durch eine XOR-Verknüpfung gebildet wird, ist genau dann TRUE, wenn ein Bestandteil TRUE ist, aber der andere Bestandteil FALSE ist – „ausschließendes oder“ bzw. „entweder – oder“. Diese Verknüpfung gibt es selten, z. B. bei MySQL; hier wird es der Vollständigkeit halber erwähnt.
SELECT ID, Name, Vorname, PLZ, Ort
FROM Versicherungsnehmer
WHERE PLZ BETWEEN '45000' AND '45999'
XOR Name < 'K';
Bitte beachten Sie, dass hier der normale Sprachgebrauch „oder“ sagt und „entweder – oder“ gemeint ist.
Anstelle von XOR kann immer eine Kombination verwendet werden:
( <Bedingung 1> AND ( NOT <Bedingung 2> ) ) OR ( <Bedingung 2> AND ( NOT <Bedingung 1> ) )
Klammern benutzen oder weglassen?
Bereits im Kapitel „Ausführliche SELECT-Struktur“ wurde die Hierarchie genannt:
- NOT ist die engste Verbindung und wird vorrangig ausgewertet.
- AND ist die nächststärkere Verbindung und wird danach ausgewertet.
- OR ist die schwächste Verbindung und wird zuletzt ausgewertet.
- Was in Klammern steht, wird vor allem anderen ausgewertet.
Bitte setzen Sie im folgenden Beispiel Klammern an anderen Stellen oder streichen Sie Klammern, und vergleichen Sie die Ergebnisse.
SELECT ID, Name, Vorname, PLZ, Ort
FROM Versicherungsnehmer
WHERE not ( PLZ BETWEEN '45000' AND '45999'
AND ( Name LIKE 'B%'
OR Name LIKE 'K%'
OR NOT Name CONTAINING 'ei'
)
)
order by PLZ, Name;
Sie werden ziemlich unterschiedliche Ergebnisse feststellen. Es empfiehlt sich deshalb, an allen sinnvollen Stellen Klammern zu setzen – auch dort, wo sie nicht erforderlich sind – und das, was zusammengehört, durch Einrückungen sinnvoll zu gliedern.
Zusammenfassung
In diesem Kapitel lernten wir neben dem Vergleich von Werten viele Möglichkeiten kennen, mit denen Bedingungen für Abfragen festgelegt werden können:
- Mit BETWEEN AND werden Werte innerhalb eines Bereichs geprüft.
- Mit LIKE und CONTAINS werden Werte gesucht, die mit vorgegebenen Werten teilweise übereinstimmen.
- Mit IS NULL werden null-Werte gesucht.
- Mit IN und EXISTS werden Spaltenwerte mit einer Liste verglichen.
Mit AND, OR, NOT werden Bedingungen zusammengefasst.
Übungen
Bei den folgenden Aufgaben kommt es nur auf die WHERE-Klausel an; Sie dürfen ein SELECT „mit allen Spalten“ benutzen.
| Übung 1 | Auswahl nach Zeichenketten | Zur Lösung |
Suchen Sie alle Versicherungsnehmer, die folgenden Bedingungen entsprechen:
- Der erste Buchstabe des Nachnamens ist nicht bekannt, der zweite ist ein 'r'.
- Der Vorname enthält ein 'a'.
- Die Postleitzahl gehört zum Bereich Essen (PLZ 45...).
| Übung 2 | Auswahl nach Datumsbereich | Zur Lösung |
Suchen Sie alle Versicherungsnehmer, die in den Jahren 1967 bis 1970 ihren 18. Geburtstag hatten.
| Übung 3 | Auswahl nach Ähnlichkeit | Zur Lösung |
Zeigen Sie alle Schadensfälle an, bei denen in der Beschreibung auf eine prozentuale Angabe hingewiesen wird.
| Übung 4 | Auswahl für unbekannte Werte | Zur Lösung |
Zeigen Sie alle Dienstwagen an, die keinem Mitarbeiter persönlich zugeordnet sind.
Hinweis: Die Prüfung „Mitarbeiter ohne Dienstwagen“ ist komplizierter; das dafür erforderliche OUTER JOIN wird erst später behandelt.
| Übung 5 | Bedingungen verknüpfen | Zur Lösung |
Zeigen Sie alle Mitarbeiter der Abteilungen „Vertrieb“ (= 'Vert') und „Ausbildung“ (= 'Ausb') an.
Hinweis: Bestimmen Sie zunächst die IDs der gesuchten Abteilungen und benutzen Sie das Ergebnis für die eigentliche Abfrage.
| Übung 6 | Bedingungen verknüpfen | Zur Lösung |
Gesucht werden die Versicherungsverträge für Haftpflicht (= 'HP') und Teilkasko (= 'TK'), die mindestens seit dem Ende des Jahres 1980 bestehen und aktuell nicht mit dem minimalen Prämiensatz berechnet werden.
Hinweis: Tragen Sie ausnahmsweise nur die notwendigen Klammern ein, nicht alle sinnvollen.
| Lösung zu Übung 1 | Auswahl nach Zeichenketten | Zur Übung |
select * from Versicherungsnehmer
where Name like '_r%' and Vorname like '%a%'
and PLZ STARTING WITH '45' /* oder: */
PLZ like '45%';
| Lösung zu Übung 2 | Auswahl nach Datumsbereich | Zur Übung |
select * from Versicherungsnehmer
where DATEADD(YEAR, 18, Geburtsdatum) BETWEEN '01.01.1967' AND '31.12.1970';
| Lösung zu Übung 3 | Auswahl nach Ähnlichkeit | Zur Übung |
SELECT * from Schadensfall
where Beschreibung like '%\%%' escape '\';
| Lösung zu Übung 4 | Auswahl für unbekannte Werte | Zur Übung |
SELECT * from Dienstwagen
where Mitarbeiter_ID is null;
| Lösung zu Übung 5 | Bedingungen verknüpfen | Zur Übung |
SELECT * from Mitarbeiter
where Abteilung_ID in (
select id from Abteilung
where Kuerzel in ('Vert', 'Ausb') );
| Lösung zu Übung 6 | Bedingungen verknüpfen | Zur Übung |
SELECT * from Versicherungsvertrag
where (Art = 'HP' or Art = 'TK')
and Abschlussdatum <= '31.12.1980'
and (not Praemiensatz = 30) /* oder */
and Praemiensatz > 30;
Siehe auch
Dieses Kapitel verweist auf die folgenden Kapitel:
Bei Wikipedia gibt es weitere fachliche Hinweise:
| Mehrere Tabellen |
Ein besonderes Merkmal von relationalen Datenbanken und damit von SQL ist, dass die Informationen fast immer über mehrere Tabellen verteilt sind und bei Abfragen in der Ergebnismenge zusammengeführt werden müssen. Dieses Kapitel gibt einen Überblick über die Möglichkeiten dazu; Einzelheiten stehen in den folgenden Kapiteln.
Schreibweisen bei mehreren Tabellen
Bitte beachten Sie bei allen Befehlen, die mehrere Tabellen verwenden (das sind zwangsläufig nur SELECT-Befehle):
- Wenn ein Spaltenname in Bezug auf den gesamten SQL-Befehl eindeutig ist, genügt dieser Name.
- Wenn ein Spaltenname mehrfach vorkommt (wie ID), dann muss der Tabellenname vorangesetzt werden; der Spaltenname wird nach einem Punkt angefügt.
SELECT
Personalnummer AS MitNr,
Name, Vorname,
Dienstwagen.ID, Kennzeichen, Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter, Dienstwagen;
- Wegen der Übersichtlichkeit wird die Tabelle meistens auch dann bei jeder Spalte angegeben, wenn es wegen der ersten Regel nicht erforderlich wäre.
SELECT
Mitarbeiter.Personalnummer AS MitNr,
Mitarbeiter.Name, Mitarbeiter.Vorname,
Dienstwagen.ID AS DIW, Dienstwagen.Kennzeichen, Dienstwagen.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter, Dienstwagen;
- Anstelle des Namens einer Tabelle kann überall auch ein Tabellen-Alias benutzt werden; dieser muss einmal hinter ihrem Namen (in der FROM- oder in der JOIN-Klausel) angegeben werden.
SELECT
mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi, Dienstwagen dw;
Alle diese Befehle für „Liste der Mitarbeiter mit Dienstwagen“ sind gleichwertig. Zu empfehlen ist die vollständige Schreibweise mit Alias wie im vorigen Beispiel.
Ein ähnlicher Befehl unter Verwendung der JOIN-Klausel sieht dann so aus:
SELECT
mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
JOIN Dienstwagen dw on mi.ID = dw.Mitarbeiter_ID;
Der Alias ist nur für den betreffenden SQL-Befehl gültig. Ein und dieselbe Tabelle kann mal als 'a', dann als 'mi' oder auch als 'xyz' bezeichnet werden. Wegen des leichteren Verständnisses sind aussagefähige Kürzel sinnvoll; auch deshalb sind sie im Kapitel Tabellenstruktur der Beispieldatenbank angegeben.
Verknüpfung über WHERE – der traditionelle Weg
Beim einfachsten Verfahren, mehrere Tabellen gleichzeitig abzufragen, stehen alle Tabellen in der FROM-Klausel; die WHERE-Klausel enthält neben den Auswahlbedingungen auch Bedingungen zur Verknüpfung der Tabellen.
Einzelheiten werden in Einfache Tabellenverknüpfung behandelt.
JOINs - der moderne Weg
Beim „modernen“ Weg, mehrere Tabellen in einer gemeinsamen Abfrage zu verknüpfen, wird jede Tabelle in einer JOIN-Klausel aufgeführt; der ON-Parameter enthält die Verknüpfungsbedingung. Die WHERE-Klausel enthält nur die Auswahlbedingungen.
Die Einführung dazu wird in Arbeiten mit JOIN besprochen.
OUTER JOIN - auch null-Werte zurückgeben
Bei Abfragen mit einem „einfachen“ JOIN werden nicht alle Datensätze aufgeführt. Zeilen, zu denen es in der einen oder anderen Tabelle keine Verknüpfung gibt, fehlen im Ergebnis. Mit einem OUTER JOIN können auch solche „fehlenden“ Zeilen aufgeführt werden.
Einzelheiten dazu werden in OUTER JOIN behandelt.
Weitere Möglichkeiten von JOIN
Als SELF JOIN wird eine Tabelle mit sich selbst verknüpft.
Oft kommt es vor, dass man die Daten aus einer Tabelle erst bearbeiten möchte, bevor man sie mit einer anderen Tabelle verknüpft. Dazu gibt es die Möglichkeit einer „Inline-View“.
Diese Ergänzungen werden in Mehr zu JOIN besprochen.
Zusammenfassung
In diesem Kapitel erhielten wir Hinweise darauf, wie mehrere Tabellen verknüpft werden können:
- einfach über die FROM-Klausel und passende WHERE-Bedingungen
- übersichtlich über die JOIN-Klausel mit verschiedenen Varianten
Übungen
| Übung 1 | Was ist an diesem SELECT-Befehl falsch? | Zur Lösung |
Zeigen Sie zu bestimmten Versicherungsverträgen die Daten der Fahrzeuge an.
select ID, Abschlussdatum, Art,
vv.Kennzeichen, Farbe
from Versicherungsvertrag vv, Fahrzeug
where vv.Fahrzeug_ID = Fahrzeug.ID
and Kennzeichen LIKE 'BO%';
| Übung 2 | Was ist an diesem SELECT-Befehl falsch? | Zur Lösung |
Zeigen Sie zu einem Versicherungsvertrag die Daten des Versicherungsnehmers und des Sachbearbeiters an.
select ID, Vorname + ' ' + Name AS Kunde, Ort
Name AS Sachbearbeiter, Telefon
from Versicherungsvertrag, Versicherungsnehmer, Mitarbeiter
where ID = 27
and Versicherungsvertrag.Versicherungsnehmer_ID = Versicherungsnehmer.ID
and Versicherungsvertrag.Mitarbeiter_ID = Mitarbeiter.ID;
| Übung 3 | Berichtigen Sie den folgenden SELECT-Befehl. | Zur Lösung |
Zeigen Sie zu jedem Mitarbeiter die Daten seines Dienstwagens (Kennzeichen, Typ, Hersteller) an.
select ID, Name, Vorname,
Kennzeichen, Bezeichnung, Name
from Mitarbeiter mi, Dienstwagen dw,
Fahrzeugtyp ft, Fahrzeughersteller fh
where ID = dw.Mitarbeiter_ID
and ID = dw.Fahrzeugtyp_ID
and ID = ft.Hersteller_ID
order by Name, Vorname;
Lösungen
| Lösung zu Übung 1 | Was ist an diesem SELECT-Befehl falsch? | Zur Übung |
- Die ID muss mit Tabellennamen oder Alias versehen sein, weil sie in beiden Tabellen enthalten ist.
- Die Spalte Kennzeichen gehört zur Tabelle Fahrzeug, also ist der Alias vv falsch.
| Lösung zu Übung 2 | Was ist an diesem SELECT-Befehl falsch? | Zur Übung |
- Die ID muss sowohl in der Spaltenliste als auch in der WHERE-Klausel mit Tabellennamen oder Alias versehen sein, weil sie in allen Tabellen enthalten ist.
- Gleiches gilt für Name und Vorname, weil diese Angaben in mehreren Tabellen enthalten sind.
Wenn (wie in den Anmerkungen zur Beispieldatenbank erwähnt) auch für die Kunden Kontaktdaten gespeichert wären, müsste das auch bei der Spalte Telefon beachtet werden. Für die Spalte Ort gilt das nicht, weil diese nicht zur Tabelle Mitarbeiter gehört, sondern zur Tabelle Abteilung, die hier nicht benutzt wird.
| Lösung zu Übung 3 | Berichtigen Sie den folgenden SELECT-Befehl. | Zur Übung |
select mi.ID, mi.Name, mi.Vorname,
dw.Kennzeichen, ft.Bezeichnung, fh.Name
from Mitarbeiter mi, Dienstwagen dw,
Fahrzeugtyp ft, Fahrzeughersteller fh
where mi.ID = dw.Mitarbeiter_ID
and ft.ID = dw.Fahrzeugtyp_ID
and fh.ID = ft.Hersteller_ID
order by mi.Name, mi.Vorname;
Siehe auch
Selfhtml: JOIN – eine andere Beschreibung und Beispielserie zum JOIN-Befehl.
| Einfache Tabellenverknüpfung |
Dieses Kapitel behandelt den „traditionellen“ Weg, mehrere Tabellen gleichzeitig abzufragen. Dazu werden in der FROM-Klausel alle Tabellen aufgeführt; die WHERE-Klausel enthält neben den Auswahlbedingungen auch Verknüpfungsbedingungen, wie die Tabellen zueinander gehören.
Alle Kombinationen aller Datensätze
Der einfachste Weg, Tabellen zu verknüpfen, ist ein Befehl wie der folgende, in dem verschiedene Spalten aus zwei Tabellen zusammengefasst werden. Aber das Ergebnis sieht reichlich seltsam aus.
select mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID as DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
FROM Mitarbeiter mi, Dienstwagen dw;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN Typ
----- ------------ --------- --- ----------- ---
10001 Müller Kurt 1 DO-WB 421 14
10002 Schneider Daniela 1 DO-WB 421 14
20001 Meyer Walter 1 DO-WB 421 14
20002 Schmitz Michael 1 DO-WB 421 14
30001 Wagner Gaby 1 DO-WB 421 14
30002 Feyerabend Werner 1 DO-WB 421 14
40001 Langmann Matthias 1 DO-WB 421 14
40002 Peters Michael 1 DO-WB 421 14
/* usw. */
10001 Müller Kurt 2 DO-WB 422 14
10002 Schneider Daniela 2 DO-WB 422 14
20001 Meyer Walter 2 DO-WB 422 14
20002 Schmitz Michael 2 DO-WB 422 14
/* usw. */
Tatsächlich erzeugt dieser Befehl das „kartesische Produkt“ der beiden Tabellen: Jeder Datensatz der einen Tabelle wird (mit den gewünschten Spalten) mit jedem Datensatz der anderen Tabelle verbunden. Das sieht also so aus, als wenn alle Dienstwagen zu jedem Mitarbeiter gehören würden, was natürlich Quatsch ist.
Diese Variante ist also in aller Regel sinnlos (wenn auch syntaktisch korrekt). Nützlich ist sie nur dann, wenn auf einfachem Wege große Mengen von Testdaten erzeugt werden sollen, wie es im Kapitel Testdaten erzeugen benutzt wird.
Zwei Tabellen einfach verbinden
Sinnvoll wird die vorstehende Abfrage durch eine kleine Ergänzung. Was will man denn eigentlich wissen?
- Gib mir (einige) Spalten der Tabelle Mitarbeiter zusammen mit (einigen) Spalten der Tabelle Dienstwagen, und zwar bei jedem Mitarbeiter denjenigen Dienstwagen, der zu diesem Mitarbeiter gehört.
Woran erkennt man, zu welchem Mitarbeiter ein Dienstwagen gehört? Nun, in der Tabelle Dienstwagen ist eine Spalte Mitarbeiter_ID enthalten; dieser Wert ist identisch mit der ID eines Eintrags in der Tabelle Mitarbeiter.
Wenn man diese Anfrage und diese Information in „Pseudocode“ übersetzt, dann kommt so etwas heraus:
Hole Spalten der Tabelle Mitarbeiter sowie Spalten der Tabelle Dienstwagen wobei die Mitarbeiter_ID eines Dienstwagens gleich ist der ID eines Mitarbeiters
Das können wir nun in eine vollständige SQL-Abfrage übersetzen; die obige Abfrage muss nur minimal erweitert werden:
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi, Dienstwagen dw
WHERE dw.Mitarbeiter_ID = mi.ID
order by MitNr;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYP
100001 Grosser Horst 10 DO-WB 4210 14
10001 Müller Kurt 1 DO-WB 421 14
110001 Eggert Louis 11 DO-WB 4211 14
120001 Carlsen Zacharias 12 DO-WB 4212 14
20001 Meyer Walter 2 DO-WB 422 14
30001 Wagner Gaby 3 DO-WB 423 14
40001 Langmann Matthias 4 DO-WB 424 14
50001 Pohl Helmut 5 DO-WB 425 14
50002 Braun Christian 14 DO-WB 352 2
50003 Polovic Frantisek 15 DO-WB 353 3
50004 Kalman Aydin 16 DO-WB 354 4
/* usw. */
Wir bekommen also tatsächlich genau diejenigen Mitarbeiter, die über einen (persönlichen) Dienstwagen verfügen.
Hinweis: Wundern Sie sich nicht über die seltsame Reihenfolge. Die Personalnummer wurde als VARCHAR definiert; also kommt das Ergebnis in alphabetischer und nicht in numerischer Reihenfolge.
Mehrere Tabellen verbinden
In der gleichen Weise können auch mehr als zwei Tabellen verknüpft werden. Im Kapitel Gruppierungen steht ein Beispiel ähnlich wie dieses:
Gesucht wird für jeden Fahrzeughersteller (mit Angabe von ID und Name) und jedes Jahr die Summe der Schadenshöhe aus der Tabelle Schadensfall.
SELECT fh.ID AS Hersteller_ID,
fh.Name AS Hersteller_Name,
EXTRACT(YEAR FROM sf.Datum) AS Jahr,
SUM(sf.Schadenshoehe) AS Schadenssumme
FROM Schadensfall sf, Zuordnung_SF_FZ zu,
Fahrzeug fz, Fahrzeugtyp ft, Fahrzeughersteller fh
where sf.ID = zu.Schadensfall_ID
and fz.ID = zu.Fahrzeug_ID
and ft.ID = fz.Fahrzeugtyp_ID
and fh.ID = ft.Hersteller_ID
GROUP BY Hersteller_ID, Hersteller_Name, Jahr
ORDER BY Jahr, Hersteller_ID;
Wichtig ist, dass es immer eine eindeutige Zuordnung zwischen jeweils einer Spalte einer Tabelle und einer Spalte einer anderen Tabelle gibt. Bitte beachten Sie dabei:
- Statt einer einzigen Spalte kann auch eine Gruppe von Spalten verknüpft werden (z. B. Name + Vorname). Dies macht aber alles umständlicher, unübersichtlicher und unsicherer. Deshalb sollte vorzugsweise über eindeutige IDs o. ä. verknüpft werden.
- Wenn es zwischen einzelnen Tabellen keine „gemeinsamen“ Spalten gibt, dann kommt wieder das kartesische Produkt heraus; das Ergebnis ist dann eher sinnlos.
Verknüpfungs- und Abfragebedingungen
Je mehr Kombinationen benötigt werden, desto unübersichtlicher wird diese Konstruktion. Dabei enthält die WHERE-Klausel bisher nur die Verknüpfungen zwischen den Tabellen, aber noch keine Suchbedingungen wie hier:
select ... from ... where ...
and Jahr in [2006, 2007, 2008]
and fhe.Land in ['Schweden', 'Norwegen', 'Finnland']
order by Jahr, Hersteller_ID;
Das führt außerdem dazu, dass die WHERE-Klausel sachlich gewünschte Suchbedingungen und logisch benötigte Verknüpfungsbedingungen vermischt. Wer soll da noch durchblicken? Besser ist das in den nächsten Kapiteln ausführlich behandelte Verfahren mit JOIN.
Zusammenfassung
Dieses Kapitel erläutert, wie mehrere Tabellen einfach durch die FROM-Klausel und passende WHERE-Bedingungen verknüpft werden können:
- In der Spaltenliste sollte immer der jeweilige Tabellenname angegeben werden; es kann auch ein Kürzel als Tabellen-Alias verwendet werden.
- In der FROM-Klausel werden alle Tabellen aufgelistet und in der WHERE-Klausel durch geeignete Bedingungen aufeinander bezogen.
- Durch die Vermischung zwischen Verknüpfungs- und Auswahlbedingungen wird dieses Verfahren schnell unübersichtlich.
Übungen
Bei den folgenden Abfragen beziehen wir uns auf den Bestand der Beispieldatenbank im „Anfangszustand“: die Tabellen Versicherungsvertrag, Fahrzeug, Mitarbeiter mit jeweils etwa 28 Einträgen und Versicherungsnehmer mit etwa 26 Einträgen.
| Übung 1 | Eine einfache Abfrage | Zur Lösung |
Erstellen Sie eine Abfrage zur Tabelle Versicherungsvertrag, die nur die wichtigsten Informationen (einschließlich der IDs auf andere Tabellen) enthält. Wie viele Einträge zeigt die Ergebnismenge an?
| Übung 2 | Das kartesische Produkt | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 1, sodass anstelle der Versicherungsnehmer_ID dessen Name und Vorname angezeigt werden, und verzichten Sie auf eine WHERE-Klausel. Wie viele Einträge zeigt die Ergebnismenge an?
| Übung 3 | Das kartesische Produkt | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 2, sodass anstelle der Fahrzeug_ID das Kennzeichen und anstelle der Mitarbeiter_ID dessen Name und Vorname angezeigt werden, und verzichten Sie auf eine WHERE-Klausel. Wie viele Einträge zeigt die Ergebnismenge an?
| Übung 4 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 2, sodass Name und Vorname des Versicherungsnehmers genau zu einem jeden Vertrag passen. Wie viele Einträge zeigt die Ergebnismenge an?
| Übung 5 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 3, sodass Name und Vorname des Mitarbeiters sowie das Fahrzeug-Kennzeichen genau zu einem jeden Vertrag passen. Wie viele Einträge zeigt die Ergebnismenge an?
| Übung 6 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 5, sodass die ausgewählten Zeilen den folgenden Bedingungen entsprechen:
- Es geht ausschließlich um Eigene Kunden.
- Vollkasko-Verträge sollen immer angezeigt werden, ebenso Fahrzeuge aus dem Kreis Recklinghausen 'RE'.
- Teilkasko-Verträge sollen angezeigt werden, wenn sie nach 1990 abgeschlossen wurden.
- Haftpflicht-Verträge sollen angezeigt werden, wenn sie nach 1985 abgeschlossen wurden.
Wie viele Einträge zeigt die Ergebnismenge an?
| Lösung zu Übung 1 | Eine einfache Abfrage | Zur Übung |
SELECT Vertragsnummer, Abschlussdatum, Art,
Versicherungsnehmer_ID, Fahrzeug_ID, Mitarbeiter_ID
from Versicherungsvertrag
Es werden 28 Zeilen angezeigt.
| Lösung zu Übung 2 | Das kartesische Produkt | Zur Übung |
SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
vn.Name, vn.Vorname,
Fahrzeug_ID,
Mitarbeiter_ID
from Versicherungsvertrag vv, Versicherungsnehmer vn;
Es werden etwa 728 Zeilen angezeigt.
| Lösung zu Übung 3 | Das kartesische Produkt | Zur Übung |
SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
vn.Name, vn.Vorname,
fz.Kennzeichen,
mi.Name, mi.Vorname
from Versicherungsvertrag vv, Versicherungsnehmer vn,
Fahrzeug fz, Mitarbeiter mi;
Es werden etwa 570 752 Zeilen angezeigt.
| Lösung zu Übung 4 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
vn.Name, vn.Vorname,
Fahrzeug_ID,
Mitarbeiter_ID
from Versicherungsvertrag vv, Versicherungsnehmer vn
where vn.ID = vv.Versicherungsnehmer_ID;
Es werden etwa 28 Zeilen angezeigt.
| Lösung zu Übung 5 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
vn.Name, vn.Vorname,
fz.Kennzeichen,
mi.Name, mi.Vorname
from Versicherungsvertrag vv, Versicherungsnehmer vn,
Fahrzeug fz, Mitarbeiter mi
where vn.ID = vv.Versicherungsnehmer_ID
and fz.ID = vv.Fahrzeug_ID
and mi.ID = vv.Mitarbeiter_ID;
Es werden etwa 28 Zeilen angezeigt.
| Lösung zu Übung 6 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
vn.Name, vn.Vorname,
fz.Kennzeichen,
mi.Name, mi.Vorname
from Versicherungsvertrag vv, Versicherungsnehmer vn,
Fahrzeug fz, Mitarbeiter mi
where vn.ID = vv.Versicherungsnehmer_ID
and fz.ID = vv.Fahrzeug_ID
and mi.ID = vv.Mitarbeiter_ID
and vn.Eigener_kunde = 'J'
and ( ( vv.Art = 'HP' and vv.Abschlussdatum > '31.12.1985' )
or ( vv.Art = 'TK' and vv.Abschlussdatum > '31.12.1990' )
OR ( vv.Art = 'VK' )
or ( fz.Kennzeichen STARTING WITH 'RE-' ) );
Es werden etwa 19 Zeilen angezeigt. Die OR-Verknüpfungen könnten teilweise auch mit CASE geschrieben werden.
Siehe auch
Bei Wikipedia stehen weitere Hinweise:
| Arbeiten mit JOIN |
Dieses Kapitel enthält die Einführung in die Variante mit JOIN, mit der mehrere Tabellen verknüpft werden können. Dazu wird jede der in Betracht kommenden Tabellen in einer JOIN-Klausel aufgeführt; der ON-Parameter enthält die Verknüpfungsbedingung. Die WHERE-Klausel enthält „nur“ die Auswahlbedingungen.
Die Syntax von JOIN
Um Tabellen sinnvoll miteinander zu verknüpfen (= verbinden, engl. join), wurde die JOIN-Klausel für den SELECT-Befehl mit folgender Syntax eingeführt.
SELECT <spaltenliste> FROM <haupttabelle> [<join-typ>] JOIN <verknüpfte tabelle> ON <bedingung>
Als <join-typ> stehen zur Verfügung:
- [INNER] JOIN, auch Equi-Join genannt, ist eine Verknüpfung innerhalb zweier Tabellen, d. h. ein Teil des kartesischen Produkts, bei dem ein Wert in beiden Tabellen vorhanden ist. INNER JOIN ist der Inhalt dieses Kapitels.
- OUTER JOIN bezeichnet Verknüpfungen, bei denen auch Datensätze geliefert werden, für die eine Vergleichsbedingung nicht erfüllt ist.
- LEFT JOIN, RIGHT JOIN, FULL JOIN bezeichnen Spezialfälle von OUTER JOIN, je nachdem in welcher Tabelle ein gesuchter Wert fehlt.
OUTER JOIN wird im nächsten Kapitel behandelt.
Einige Sonderfälle und Ergänzungen zu JOIN werden im Kapitel Mehr zu JOIN behandelt.
Als <bedingung> wird normalerweise nur eine Übereinstimmung (also eine Gleichheit) zwischen zwei Tabellen geprüft, auch wenn jede Kombination von Bedingungen erlaubt ist. Genauer: es geht um die Gleichheit von Werten je einer Spalte in zwei Tabellen. (Zwei Beispiele für andere Übereinstimmungen lernen Sie in „Mehr zu JOIN“ kennen.)
Auch mehrere Verknüpfungen sind möglich, entweder direkt hintereinander:
SELECT <spaltenliste> FROM <haupttabelle> [<join-typ>] JOIN <zusatztabelle1> ON <bedingung1> [<join-typ>] JOIN <zusatztabelle2> ON <bedingung2> [<join-typ>] JOIN <zusatztabelle3> ON <bedingung3>
oder durch Klammern gegliedert:
SELECT <spaltenliste>
FROM <haupttabelle>
[<join-typ>] JOIN
( <zusatztabelle1>
[<join-typ>] JOIN
( <zusatztabelle2>
[<join-typ>] JOIN <zusatztabelle3> ON <bedingung3>
) ON <bedingung2>
) ON <bedingung1>
Bitte beachten Sie dabei genau, wo und wie die Klammern und die dazugehörigen ON-Bedingungen gesetzt werden. Beide Varianten können unterschiedliche Ergebnisse liefern – abhängig vom JOIN-Typ und dem Zusammenhang zwischen den Tabellen.
Alle diese Möglichkeiten werden in den nächsten Abschnitten und Kapiteln genauer erläutert.
INNER JOIN von zwei Tabellen
Das Beispiel „alle Mitarbeiter mit den zugehörigen Dienstwagen“ aus dem vorigen Kapitel benötigt nur geringe Änderungen.
select mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
from Mitarbeiter mi
join Dienstwagen dw on dw.Mitarbeiter_ID = mi.ID
ORDER BY MitNr;
Das Ergebnis dieser Abfrage ist identisch mit der Liste im vorigen Kapitel; wir verzichten deshalb auf die erneute Ausgabe.
Die zweite Tabelle wird in die JOIN-Klausel verschoben, die Verknüpfungsbedingung in den ON-Parameter – fertig.
WHERE-Klausel bei JOINs
Eine solche Abfrage kann wie üblich durch eine WHERE-Klausel eingeschränkt werden. Eine Suchbedingung auf die verknüpfte Tabelle Dienstwagen kann wahlweise in der WHERE-Klausel oder in der JOIN-Klausel stehen. In den beiden folgenden Beispielen geht es nur um die Dienstwagen von Mercedes. Die Information, welche Typen zu Mercedes gehören, kommt über eine Unterabfrage, die ebenfalls einen JOIN verwendet und die in Klammern gesetzt ist.
Suche die Dienstwagen vom Typ Mercedes.
Zulässig, aber nicht so schön, weil Verknüpfungsbedingung und Auswahlbedingung vermischt werdenselect mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
from Mitarbeiter mi
join Dienstwagen dw
on mi.ID = dw.Mitarbeiter_ID
and dw.Fahrzeugtyp_ID in ( SELECT ft.ID
from Fahrzeugtyp ft
join Fahrzeughersteller fh
on ft.Hersteller_ID = fh.ID
and fh.Name = 'Mercedes-Benz' );
Besseres Vorgehen, weil die Auswahlbedingungen als solche direkt zu erkennen sindselect mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
from Mitarbeiter mi
join Dienstwagen dw
on mi.ID = dw.Mitarbeiter_ID
where dw.Fahrzeugtyp_ID in ( SELECT ft.ID
from Fahrzeugtyp ft
join Fahrzeughersteller fh
on ft.Hersteller_ID = fh.ID
where fh.Name = 'Mercedes-Benz');
Natürlich sind Einschränkungen auf beide Tabellen möglich:
Gesucht werden Mitarbeiter mit 'M' und Mercedes als Dienstwagen.
select mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
from Mitarbeiter mi
join Dienstwagen dw
on mi.ID = dw.Mitarbeiter_ID
where dw.Fahrzeugtyp_ID in ( SELECT ft.ID
from Fahrzeugtyp ft
join Fahrzeughersteller fh
on ft.Hersteller_ID = fh.ID
where fh.Name = 'Mercedes-Benz')
and mi.Name like 'M%';
AusgabeMITNR NAME VORNAME ID KENNZEICHEN TYP
------ --------- ------- -- ----------- ---
10001 Müller Kurt 1 DO-WB 421 14
20001 Meyer Walter 2 DO-WB 422 14
Bei diesem Beispiel wird sofort deutlich, welche Bedingungen die Verknüpfung und welche Bedingungen die Auswahl bezeichnen. Auf diese Übersichtlichkeit sollten Sie immer achten.
Übrigens gibt es keine allgemeine Regel, was als Haupttabelle und was als verknüpfte Tabelle zu verwenden ist. In den bisherigen Beispielen können die beiden Tabellen ohne weiteres vertauscht werden:
select mi.Personalnummer as MitNr,
mi.Name, mi.Vorname,
dw.ID, dw.Kennzeichen, dw.Fahrzeugtyp_ID as Typ
from Dienstwagen dw
join Mitarbeiter mi
on mi.ID = dw.Mitarbeiter_ID
where dw.Fahrzeugtyp_ID in ( SELECT ft.ID
from Fahrzeugtyp ft
join Fahrzeughersteller fh
on ft.Hersteller_ID = fh.ID
where fh.Name = 'Mercedes-Benz')
and mi.Name like 'M%';
Die Haupttabelle kann nach folgenden Überlegungen gewählt werden:
- Es sollte die Tabelle sein, die die „wichtigste“ bei der Abfrage ist.
- Es sollte diejenige mit den größten Einschränkungen sein; das beschleunigt die Abfrage besonders stark.
INNER JOIN mehrerer Tabellen
Dazu nehmen wir wiederum das komplexe Beispiel aus dem vorigen Kapitel, das bei den Gruppierungen genauer besprochen wird. In diesem Fall spielt die Reihenfolge der JOIN-Klauseln eher keine Rolle, weil es sich sowieso um direkte Übereinstimmungen handelt und nur solche Datensätze benutzt werden, die es zu den betreffenden Werten tatsächlich gibt.
Gesucht wird für jeden Fahrzeughersteller (mit Angabe von ID und Name) und jedes Jahr die Summe der Schadenshöhe aus der Tabelle Schadensfall.
SELECT fh.ID AS Hersteller_ID,
fh.Name AS Hersteller_Name,
EXTRACT(YEAR FROM sf.Datum) AS Jahr,
SUM(sf.Schadenshoehe) AS Schadenssumme
FROM Schadensfall sf
JOIN Zuordnung_SF_FZ zu ON sf.ID = zu.Schadensfall_ID
JOIN Fahrzeug fz ON fz.ID = zu.Fahrzeug_ID
JOIN Fahrzeugtyp ft ON ft.ID = fz.Fahrzeugtyp_ID
JOIN Fahrzeughersteller fh ON fh.ID = ft.Hersteller_ID
GROUP BY Hersteller_ID, Hersteller_Name, Jahr
ORDER BY Jahr, Hersteller_ID;
Übrigens ist es zulässig, den „traditionellen“ Weg mit mehreren Tabellen in der FROM-Klausel und den „modernen“ Weg über JOIN zu mischen. Wenn Sie in einem Ausnahmefall wirklich so vorgehen wollen, sollten Sie erst recht genau auf die Übersichtlichkeit und den Zusammenhang der Bedingungen achten. Der Autor dieses Hinweises kann sich keine passende Situation vorstellen, aber vielleicht ist es auch einmal sinnvoll.
Zusammenfassung
In diesem Kapitel lernten Sie die Verknüpfung von Tabellen über die JOIN-Klausel kennen.
- Mit einem INNER JOIN werden Datensätze abgefragt, bei denen ein Wert in je einer Spalte beider Tabellen vorhanden ist.
- In der ON-Klausel steht diese Verknüpfungsbedingung.
- In der WHERE-Klausel stehen die „normalen“ Auswahlbedingungen.
Genauso können mehrere Tabellen verknüpft werden.
Übungen
| Übung 1 | Definition von JOINs | Zur Lösung |
Welche der folgenden Aussagen sind wahr, welche falsch, welche sinnvoll?
- Der INNER JOIN liefert das kartesische Produkt zwischen den Tabellen.
- LEFT JOIN ist ein Spezialfall von OUTER JOIN.
- Für einen JOIN ist ON Fahrzeug.ID >= Versicherungsvertrag.Fahrzeug_ID eine zulässige Verknüpfungsbedingung.
- Eine Einschränkung auf die mit JOIN verknüpfte Tabelle gehört in die ON-Klausel:
... FROM Zuordnung_SF_FZ zu
JOIN Schadensfall sf
ON sf.ID = zu.Schadensfall_ID AND EXTRACT(YEAR from sf.Datum) = 2008;
| Übung 2 | Definition von JOINs | Zur Lösung |
Erläutern Sie, was am folgenden Befehl falsch oder äußerst ungünstig ist. Es handelt sich um diese Abfrage:
Gesucht sind die Schadensfälle des Jahres 2008. Zu jedem Schadensfall sind die beteiligten Fahrzeuge, der Schadensanteil sowie die Versicherungsdaten des Fahrzeugs (einschließlich Name des Halters) anzugeben.
SELECT Datum, SUBSTRING(Ort from 1 for 30) as Ort, Schadenshoehe,
zu.Schadenshoehe,
fz.Kennzeichen,
Vertragsnummer as Vertrag, Abschlussdatum, Art,
vn.Name as VN-Name, vn.Vorname as VN-Vorname
from Schadensfall sf
join Zuordnung_SF_FZ zu on ID = zu.Schadensfall_ID
join Fahrzeug fz on ID = zu.Fahrzeug_ID
join Versicherungsnehmer vn on ID = vv.Versicherungsnehmer_ID
join Versicherungsvertrag vv on vv.Fahrzeug_ID = zu.Fahrzeug_ID
where EXTRACT(YEAR from Datum) = 2008
order by Schadensfall_ID, Fahrzeug_ID;
Die folgenden Aufgaben entsprechen teilweise Aufgaben aus dem Kapitel „Einfache Tabellenverknüpfung“. Sie sollen jetzt an den passenden Stellen JOINs verwenden, anstatt die Tabellen einfach aufzulisten.
| Übung 3 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Erstellen Sie eine Abfrage zur Tabelle Versicherungsvertrag mit den wichtigsten Informationen (einschließlich der IDs auf andere Tabellen). Beim Versicherungsnehmer sollen dessen Name und Vorname angezeigt werden. Es werden nur Verträge ab 1990 gesucht.
| Übung 4 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Erweitern Sie die Abfrage von Aufgabe 3, sodass Name und Vorname des Mitarbeiters sowie das Fahrzeug-Kennzeichen eines jeden Vertrags angezeigt werden.
| Übung 5 | Sinnvolle Verknüpfung von Tabellen | Zur Lösung |
Ändern Sie die Abfrage von Aufgabe 4 so, dass die ausgewählten Zeilen den folgenden Bedingungen entsprechen:
- Es geht ausschließlich um Eigene Kunden.
- Vollkasko-Verträge sollen immer angezeigt werden, ebenso Fahrzeuge aus dem Kreis Recklinghausen 'RE'.
- Teilkasko-Verträge sollen angezeigt werden, wenn sie nach 1990 abgeschlossen wurden.
- Haftpflicht-Verträge sollen angezeigt werden, wenn sie nach 1985 abgeschlossen wurden.
| Lösung zu Übung 1 | Definition von JOINs | Zur Übung |
- Falsch; es liefert einen Teil des kartesischen Produkts, der durch die ON-Bedingung bestimmt wird.
- Richtig.
- Diese Bedingung ist zulässig, aber nicht sinnvoll. JOIN-ON passt in der Regel nur für Gleichheiten.
- Diese Bedingung ist zulässig. Besser ist es aber, eine Einschränkung der Auswahl in die WHERE-Klausel zu setzen.
| Lösung zu Übung 2 | Definition von JOINs | Zur Übung |
Richtig ist beispielsweise die folgende Version. Als Haupttabelle wurde wegen der WHERE-Klausel die Tabelle Schadensfall gewählt; wegen der Reihenfolge der Verknüpfungen wäre auch Zuordnung_SF_FZ als Haupttabelle geeignet.
SELECT sf.Datum, SUBSTRING(sf.Ort from 1 for 30) as Ort, sf.Schadenshoehe,
zu.Schadenshoehe as Teilschaden,
fz.Kennzeichen,
vv.Vertragsnummer as Vertrag, vv.Abschlussdatum, vv.Art,
vn.Name as VN_Name, vn.Vorname as VN_Vorname
from Schadensfall sf
join Zuordnung_SF_FZ zu on sf.ID = zu.Schadensfall_ID
join Fahrzeug fz on fz.ID = zu.Fahrzeug_ID
join Versicherungsvertrag vv on fz.ID = vv.Fahrzeug_ID
join Versicherungsnehmer vn on vn.ID = vv.Versicherungsnehmer_ID
where EXTRACT(YEAR from sf.Datum) = 2008
order by zu.Schadensfall_ID, zu.Fahrzeug_ID;
Die Variante aus der Aufgabenstellung enthält folgende Problemstellen:
- Zeile 1: Der Tabellen-Alias sf fehlt bei Schadenshoehe und bei Ort. Bei Datum fehlt er auch, aber das ist kein Problem, weil es diese Spalte nur bei dieser Tabelle gibt.
- Zeile 2: Diese Spalte sollte einen Spalten-Alias bekommen wegen der abweichenden Bedeutung zu sf.Schadenshoehe.
- Zeile 4: Es ist schöner, auch hier mit einem Tabellen-Alias zu arbeiten.
- Zeile 5: Der Bindestrich in der Bezeichnung des Spalten-Alias wird nicht bei allen DBMS akzeptiert.
- Zeile 7, 8, 9: Zur Spalte ID ist jeweils die Tabelle anzugeben, ggf. mit dem Alias. Die JOIN-ON-Bedingung bezieht sich nicht automatisch auf diese Spalte und diese Tabelle.
- Zeile 9, 10: In Zeile 9 ist die Tabelle Versicherungsvertrag vv noch nicht bekannt. Wegen der Verknüpfungen ist zuerst Zeile 10 zu verwenden, danach Zeile 9. Die Verknüpfung über vv.Fahrzeug_ID = zu.Fahrzeug_ID ist nicht glücklich (wenn auch korrekt); besser ist der Bezug auf die direkt zugeordnete Tabelle Fahrzeug und deren PrimaryKey, nämlich ID.
- Zeile 11: Es ist klarer, auch hier den Tabellen-Alias sf zu verwenden.
- Zeile 12: Der Tabellen-Alias zu fehlt bei beiden Spalten. Bei Fahrzeug_ID ist er erforderlich (doppelte Verwendung bei vv), bei Schadensfall_ID sinnvoll.
| Lösung zu Übung 3 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT Vertragsnummer, Abschlussdatum, Art,
Name, Vorname,
Fahrzeug_ID,
Mitarbeiter_ID
from Versicherungsvertrag vv
join Versicherungsnehmer vn on vn.ID = vv.Versicherungsnehmer_ID
where vv.Abschlussdatum >= '01.01.1990';
| Lösung zu Übung 4 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT vv.Vertragsnummer as Vertrag, vv.Abschlussdatum, vv.Art,
vn.Name as VN_Name, vn.Vorname as VN_Vorname,
fz.Kennzeichen,
mi.Name as MI_Name, mi.Vorname as MI_Vorname
from Versicherungsvertrag vv
join Versicherungsnehmer vn on vn.ID = vv.Versicherungsnehmer_ID
join Fahrzeug fz on fz.ID = vv.Fahrzeug_ID
join Mitarbeiter mi on mi.ID = vv.Mitarbeiter_ID
where vv.Abschlussdatum >= '01.01.1990';
| Lösung zu Übung 5 | Sinnvolle Verknüpfung von Tabellen | Zur Übung |
SELECT vv.Vertragsnummer as Vertrag, vv.Abschlussdatum, vv.Art,
vn.Name as VN_Name, vn.Vorname as VN_Vorname,
fz.Kennzeichen,
mi.Name as MI_Name, mi.Vorname as MI_Vorname
from Versicherungsvertrag vv
join Versicherungsnehmer vn on vn.ID = vv.Versicherungsnehmer_ID
join Fahrzeug fz on fz.ID = vv.Fahrzeug_ID
join Mitarbeiter mi on mi.ID = vv.Mitarbeiter_ID
where vn.Eigener_kunde = 'J'
and ( ( vv.Art = 'HP' and vv.Abschlussdatum > '31.12.1985' )
or ( vv.Art = 'TK' and vv.Abschlussdatum > '31.12.1990' )
OR ( vv.Art = 'VK' )
or ( fz.Kennzeichen STARTING WITH 'RE-' ) );
Siehe auch
In diesem Kapitel werden Sachverhalte der folgenden Themen angesprochen:
| OUTER JOIN |
Bei den Abfragen im vorigen Kapitel nach „alle Mitarbeiter und ihre Dienstwagen“ werden nicht alle Mitarbeiter aufgeführt, weil in der Datenbank nicht für alle Mitarbeiter ein Dienstwagen registriert ist. Ebenso gibt es einen Dienstwagen, der keinem bestimmten Mitarbeiter zugeordnet ist.
Mit einem OUTER JOIN werden auch Mitarbeiter ohne Dienstwagen oder Dienstwagen ohne Mitarbeiter aufgeführt.
Die Syntax von OUTER JOIN
Die Syntax entspricht derjenigen von JOIN allgemein. Wegen der speziellen Bedeutung sind die Tabellen nicht gleichberechtigt, sondern werden begrifflich unterschieden:
SELECT <spaltenliste> FROM <linke tabelle> [<join-typ>] JOIN <rechte tabelle> ON <bedingung>
Als Spezialfälle des OUTER JOIN gibt es die JOIN-Typen LEFT JOIN, RIGHT JOIN, FULL JOIN.
Anstelle von <haupttabelle> und <zusatztabelle> wird bei OUTER JOIN von <linke tabelle> und <rechte tabelle> gesprochen, weil diese Tabellen unterschiedlich behandelt werden.
Allgemeine Hinweise zu OUTER JOIN
Das Wort OUTER kann entfallen und wird üblicherweise nicht benutzt, weil durch die Begriffe LEFT, RIGHT, FULL bereits ein OUTER JOIN gekennzeichnet wird.
Die Begriffe <linke tabelle> und <rechte tabelle> beziehen sich auf die beiden Tabellen bezüglich der normalen Lesefolge: Wir lesen von links nach rechts, also ist die unter FROM genannte Tabelle die <linke Tabelle> (bisher <Haupttabelle> genannt) und die unter JOIN genannte Tabelle die <rechte Tabelle> (bisher <Zusatztabelle> genannt). Bei Verknüpfungen mit mehreren Tabellen ist ebenfalls die unter JOIN genannte Tabelle die <rechte Tabelle>; die unmittelbar vorhergehende Tabelle ist die <linke Tabelle>.
Auch wenn die folgenden Beispiele so aussehen, als wenn die Datensätze sinnvoll sortiert wären, ist das Zufall; bitte denken Sie daran, dass SQL unsortierte Datenmengen liefert. Eine bestimmte Reihenfolge erhalten Sie erst durch ORDER BY.
Die Anzeige der Ergebnismengen bei den Beispielen ist in der Regel nur ein Auszug des vollständigen Ergebnisses.
LEFT OUTER JOIN
Dieser JOIN liefert alle Datensätze der linken Tabelle, ggf. unter Berücksichtigung der WHERE-Klausel. Aus der rechten Tabelle werden nur diejenigen Datensätze übernommen, die nach der Verknüpfungsbedingung passen.
SELECT <spaltenliste>
FROM <linke Tabelle>
LEFT [OUTER] JOIN <rechte Tabelle> ON <bedingung>;
Für unser Beispiel sieht das dann so aus:
Hole alle Mitarbeiter und (sofern vorhanden) die Angaben zum Dienstwagen.
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
LEFT JOIN Dienstwagen dw ON dw.Mitarbeiter_ID = mi.ID;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYP
-------- ------------ --------- --- ----------- ---
30001 Wagner Gaby 3 DO-WB 423 14
30002 Feyerabend Werner
40001 Langmann Matthias 4 DO-WB 424 14
40002 Peters Michael
50001 Pohl Helmut 5 DO-WB 425 14
50002 Braun Christian 14 DO-WB 352 2
50003 Polovic Frantisek 15 DO-WB 353 3
50004 Kalman Aydin 16 DO-WB 354 4
60001 Aagenau Karolin 6 DO-WB 426 14
60002 Pinkart Petra
Und wenn wir jetzt die beiden Tabellen vertauschen?
Dann erhalten wir alle Dienstwagen und dazu die passenden Mitarbeiter.
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Dienstwagen dw
LEFT JOIN Mitarbeiter mi ON dw.Mitarbeiter_ID = mi.ID;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYP
------- ------------ --------- --- ----------- ---
80001 Schindler Christina 8 DO-WB 428 14
90001 Janssen Bernhard 9 DO-WB 429 14
100001 Grosser Horst 10 DO-WB 4210 14
110001 Eggert Louis 11 DO-WB 4211 14
120001 Carlsen Zacharias 12 DO-WB 4212 14
13 DO-WB 111 16
50002 Braun Christian 14 DO-WB 352 2
50003 Polovic Frantisek 15 DO-WB 353 3
50004 Kalman Aydin 16 DO-WB 354 4
Bitte überlegen Sie selbst, wie sich WHERE-Klauseln auf das Ergebnis einer Abfrage auswirken.
RIGHT OUTER JOIN
Dieser JOIN liefert alle Datensätze der rechten Tabelle, ggf. unter Berücksichtigung der WHERE-Klausel. Aus der linken Tabelle werden nur diejenigen Datensätze übernommen, die nach der Verknüpfungsbedingung passen.
SELECT <spaltenliste>
FROM <linke Tabelle>
RIGHT [OUTER] JOIN <rechte Tabelle> ON <bedingung>;
Für unser Beispiel „Mitarbeiter und Dienstwagen“ sieht das dann so aus:
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
RIGHT JOIN Dienstwagen dw ON dw.Mitarbeiter_ID = mi.ID;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYP
------- ------------ --------- --- ----------- ---
80001 Schindler Christina 8 DO-WB 428 14
90001 Janssen Bernhard 9 DO-WB 429 14
100001 Grosser Horst 10 DO-WB 4210 14
110001 Eggert Louis 11 DO-WB 4211 14
120001 Carlsen Zacharias 12 DO-WB 4212 14
13 DO-WB 111 16
50002 Braun Christian 14 DO-WB 352 2
50003 Polovic Frantisek 15 DO-WB 353 3
50004 Kalman Aydin 16 DO-WB 354 4
Nanu, dieses Ergebnis hatten wir doch gerade? Bei genauerem Überlegen wird klar: Beim LEFT JOIN gibt es alle Datensätze der linken Tabelle mit Informationen der rechten Tabelle; nun haben wir die beiden Tabellen vertauscht. Beim RIGHT JOIN werden alle Datensätze der rechten Tabelle mit Daten der linken Tabelle verknüpft; das entspricht diesem Beispiel.
Ob wir also die beiden Tabellen vertauschen oder LEFT gegen RIGHT, bleibt sich zwangsläufig gleich. Kurz und „knackig“ formuliert kann man sich also merken:
"A LEFT JOIN B" liefert dasselbe Ergebnis wie "B RIGHT JOIN A".
Bitte überlegen Sie, welches Ergebnis die Vertauschung der beiden Tabellen beim RIGHT JOIN liefert und welche Auswirkung WHERE-Klauseln haben.
FULL OUTER JOIN
Dieser JOIN liefert alle Datensätze beider Tabellen, ggf. unter Berücksichtigung der WHERE-Klausel. Wenn Datensätze nach der Verknüpfungsbedingung zusammenpassen, werden sie in einer Zeile angegeben; wo es keinen „Partner“ gibt, wird ein NULL-Wert angezeigt.
SELECT <spaltenliste>
FROM <linke Tabelle>
FULL [OUTER] JOIN <rechte Tabelle> ON <bedingung>;
Für unser Beispiel sieht das dann so aus:
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
FULL JOIN Dienstwagen dw ON dw.Mitarbeiter_ID = mi.ID;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYP
-------- ------------ ---------- --- ----------- ---
100001 Grosser Horst 10 DO-WB 4210 14
110001 Eggert Louis 11 DO-WB 4211 14
120001 Carlsen Zacharias 12 DO-WB 4212 14
13 DO-WB 111 16
50002 Braun Christian 14 DO-WB 352 2
50003 Polovic Frantisek 15 DO-WB 353 3
50004 Kalman Aydin 16 DO-WB 354 4
80002 Aliman Zafer 17 DO-WB 382 2
80003 Langer Norbert 18 DO-WB 383 3
80004 Kolic Ivana 19 DO-WB 384 4
10002 Schneider Daniela
20002 Schmitz Michael
30002 Feyerabend Werner
40002 Peters Michael
Auch hier wollen wir wieder die beiden Tabellen vertauschen:
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Dienstwagen dw
FULL JOIN Mitarbeiter mi ON dw.Mitarbeiter_ID = mi.ID;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYP
-------- ------------ ---------- --- ----------- ---
80001 Schindler Christina 8 DO-WB 428 14
80002 Aliman Zafer 17 DO-WB 382 2
80003 Langer Norbert 18 DO-WB 383 3
80004 Kolic Ivana 19 DO-WB 384 4
90001 Janssen Bernhard 9 DO-WB 429 14
90002 Hinkel Martina
100001 Grosser Horst 10 DO-WB 4210 14
100002 Friedrichsen Angelina
110001 Eggert Louis 11 DO-WB 4211 14
110002 Deiters Gisela
120001 Carlsen Zacharias 12 DO-WB 4212 14
120002 Baber Yvonne
13 DO-WB 111 16
Bei detailliertem Vergleich des vollständigen Ergebnisses ergibt sich: Es ist gleich, nur in anderer Reihenfolge. Das sollte nicht mehr verwundern.
Verknüpfung mehrerer Tabellen
Alle bisherigen Beispiele kranken daran, dass als Typ des Dienstwagens nur die ID angegeben ist. Selbstverständlich möchte man die Typbezeichnung und den Hersteller lesen. Dazu müssen die beiden Tabellen Fahrzeugtyp und Fahrzeughersteller eingebunden werden. Beim INNER JOIN war das kein Problem; probieren wir aus, wie es beim OUTER JOIN aussehen könnte.
Mehrere Tabellen parallel
Erweitern wir dazu die Aufstellung „alle Dienstwagen zusammen mit den zugeordneten Mitarbeitern“ um die Angabe zu den Fahrzeugen.
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS TypID,
ft.Bezeichnung as Typ, ft.Hersteller_ID as FheID
FROM Dienstwagen dw
left JOIN Mitarbeiter mi ON dw.Mitarbeiter_ID = mi.ID
join Fahrzeugtyp ft on dw.Fahrzeugtyp_ID = ft.ID;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYPID TYP FHEID
------ ---------- ---------- --- ----------- ----- --------------- -----
100001 Grosser Horst 10 DO-WB 4210 14 A160 6
110001 Eggert Louis 11 DO-WB 4211 14 A160 6
120001 Carlsen Zacharias 12 DO-WB 4212 14 A160 6
13 DO-WB 111 16 W211 (E-Klasse) 6
50002 Braun Christian 14 DO-WB 352 2 Golf 1
50003 Polovic Frantisek 15 DO-WB 353 3 Passat 1
50004 Kalman Aydin 16 DO-WB 354 4 Kadett 2
Der zweite JOIN wurde nicht genauer bezeichnet, ist also ein INNER JOIN. Das gleiche Ergebnis erhalten wir, wenn wir die Tabelle Fahrzeugtyp ausdrücklich als LEFT JOIN verknüpfen (bitte selbst ausprobieren!). Anders sieht es beim Versuch mit RIGHT JOIN oder FULL JOIN aus:
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS TypID,
ft.Bezeichnung as Typ, ft.Hersteller_ID as FheID
FROM Dienstwagen dw
left JOIN Mitarbeiter mi ON dw.Mitarbeiter_ID = mi.ID
right | full join Fahrzeugtyp ft on dw.Fahrzeugtyp_ID = ft.ID;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYPID TYP FHEID
------ ---------- ---------- --- ----------- ----- --------------- -----
80001 Schindler Christina 8 DO-WB 428 14 A160 6
90001 Janssen Bernhard 9 DO-WB 429 14 A160 6
100001 Grosser Horst 10 DO-WB 4210 14 A160 6
110001 Eggert Louis 11 DO-WB 4211 14 A160 6
120001 Carlsen Zacharias 12 DO-WB 4212 14 A160 6
W204 (C-Klasse) 6
13 DO-WB 111 16 W211 (E-Klasse) 6
Saab 9-3 8
S40 9
C30 9
Versuchen wir eine Erklärung: Die beiden JOINs stehen sozusagen auf der gleichen Ebene; jede JOIN-Klausel wird für sich mit der Tabelle Dienstwagen verknüpft. An der Verknüpfung zwischen Dienstwagen und Mitarbeiter ändert sich nichts. Aber für die Fahrzeugtypen gilt:
- Das erste Beispiel benutzt einen INNER JOIN, nimmt also für jeden vorhandenen Dienstwagen genau „seinen“ Typ.
- Wenn man stattdessen einen LEFT JOIN verwendet, erhält man alle vorhandenen Dienstwagen, zusammen mit den passenden Typen. Das ist faktisch identisch mit dem Ergebnis des INNER JOIN.
- Das zweite Beispiel benutzt einen RIGHT JOIN, das liefert alle registrierten Fahrzeugtypen und (soweit vorhanden) die passenden Dienstwagen.
- Wenn man stattdessen einen FULL JOIN verwendet, erhält man alle Kombinationen von Dienstwagen und Mitarbeitern, zusammen mit allen registrierten Fahrzeugtypen. Das ist faktisch identisch mit dem Ergebnis des RIGHT JOIN.
Sie sehen: Es kommt genau auf die gewünschten und die tatsächlich vorhandenen Verknüpfungen an.
Gliederung durch Klammern
Für Verknüpfungen, die durch Klammern gegliedert werden, nehmen wir ein anderes Beispiel, nämlich „Mitarbeiter RIGHT JOIN Dienstwagen“, denn die Fahrzeugtypen sind eine Ergänzung zu den Dienstwagen, nicht zu den Mitarbeitern (auch wenn den Abteilungsleitern ein Mercedes zugestanden wird, aber das ist ein anderes Thema und hat nichts mit SQL zu tun).
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS TypID,
ft.Bezeichnung as Typ, ft.Hersteller_ID as FheID
FROM Mitarbeiter mi
right JOIN ( Dienstwagen dw
join Fahrzeugtyp ft on ft.ID = dw.Fahrzeugtyp_id )
ON dw.Mitarbeiter_ID = mi.ID;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYPID TYP FHEID
------- ---------- ---------- --- ----------- ----- --------------- -----
80001 Schindler Christina 8 DO-WB 428 14 A160 6
90001 Janssen Bernhard 9 DO-WB 429 14 A160 6
100001 Grosser Horst 10 DO-WB 4210 14 A160 6
110001 Eggert Louis 11 DO-WB 4211 14 A160 6
120001 Carlsen Zacharias 12 DO-WB 4212 14 A160 6
13 DO-WB 111 16 W211 (E-Klasse) 6
50002 Braun Christian 14 DO-WB 352 2 Golf 1
50003 Polovic Frantisek 15 DO-WB 353 3 Passat 1
50004 Kalman Aydin 16 DO-WB 354 4 Kadett 2
Auch hier erhalten wir ein vergleichbares Ergebnis. Prüfen wir zunächst die Abfrage in der Klammer:
- LEFT JOIN und INNER JOIN haben als Grundlage „alle Dienstwagen“, es wird also eine Datenmenge „alle Dienstwagen“ (mit Zusatzinformationen über die Fahrzeugtypen) erstellt.
- RIGHT JOIN und FULL JOIN gehen aus von „alle Fahrzeugtypen“, es wird also eine Datenmenge „alle Fahrzeugtypen“ (mit Zusatzinformationen über die Dienstwagen) erstellt.
Da der Ausdruck innerhalb der Klammern zuerst ausgewertet wird, wird diese Datenmenge anschließend mit den Mitarbeitern verknüpft, soweit es der Verknüpfungsbedingung auf der Basis von dw.Mitarbeiter_ID entspricht.
Mit diesen Erkenntnissen können wir nun auch den Hersteller mit seinem Namen anzeigen; dazu benutzen wir wegen der bisherigen Erkenntnisse das erste Beispiel:
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS TypID,
ft.Bezeichnung as Typ, fh.Name as Hersteller
FROM Dienstwagen dw
left JOIN Mitarbeiter mi ON mi.ID = dw.Mitarbeiter_ID
inner join Fahrzeugtyp ft on ft.ID = dw.Fahrzeugtyp_ID
inner join Fahrzeughersteller fh on fh.ID = ft.Hersteller_ID;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYPID TYP HERSTELLER
-------- ---------- ---------- --- ------------ ----- --------------- -------------
80001 Schindler Christina 8 DO-WB 428 14 A160 Mercedes-Benz
90001 Janssen Bernhard 9 DO-WB 429 14 A160 Mercedes-Benz
100001 Grosser Horst 10 DO-WB 4210 14 A160 Mercedes-Benz
110001 Eggert Louis 11 DO-WB 4211 14 A160 Mercedes-Benz
120001 Carlsen Zacharias 12 DO-WB 4212 14 A160 Mercedes-Benz
13 DO-WB 111 16 W211 (E-Klasse) Mercedes-Benz
50002 Braun Christian 14 DO-WB 352 2 Golf Volkswagen
50003 Polovic Frantisek 15 DO-WB 353 3 Passat Volkswagen
50004 Kalman Aydin 16 DO-WB 354 4 Kadett Opel
80002 Aliman Zafer 17 DO-WB 382 2 Golf Volkswagen
80003 Langer Norbert 18 DO-WB 383 3 Passat Volkswagen
80004 Kolic Ivana 19 DO-WB 384 4 Kadett Opel
Zusammenfassung
In diesem Kapitel lernten Sie die Verwendung von OUTER JOIN kennen:
- Mit dieser Verknüpfung werden auch Datensätze abgefragt und angezeigt, bei denen es in einer der Tabellen keinen zugeordneten Datensatz gibt.
- Mit einem LEFT JOIN erhält man alle Datensätze der linken Tabelle, ergänzt durch passende Angaben aus der rechten Tabelle.
- Mit einem RIGHT JOIN erhält man alle Datensätze der rechten Tabelle, ergänzt durch passende Angaben aus der linken Tabelle.
- Mit einem FULL JOIN erhält man alle Datensätze beider Tabellen, wenn möglich ergänzt durch passende Angaben aus der jeweils anderen Tabelle.
Bei der Verknüpfung mehrerer Tabellen ist genau auf den JOIN-Typ und ggf. auf Klammerung zu achten.
Übungen
| Übung 1 | Allgemeines | Zur Lösung |
Welche der folgenden Aussagen sind wahr, welche sind falsch?
- Um alle Mitarbeiter mit Dienstwagen aufzulisten, benötigt man einen LEFT OUTER JOIN.
- LEFT JOIN ist nur eine Kurzschreibweise für LEFT OUTER JOIN und hat keine zusätzliche inhaltliche Bedeutung.
- Ein LEFT JOIN von zwei Tabellen enthält alle Zeilen, die nach Auswahlbedingung in der linken Tabelle enthalten sind.
- Ein RIGHT JOIN von zwei Tabellen enthält nur noch diejenigen Zeilen, die nach der Verknüpfungsbedingung in der linken Tabelle enthalten sind.
- Wenn wir bei einer LEFT JOIN-Abfrage mit zwei Tabellen die beiden Tabellen vertauschen und stattdessen einen RIGHT JOIN verwenden, erhalten wir dieselben Zeilen in der Ergebnismenge.
- Wir erhalten dabei nicht nur dieselben Zeilen, sondern auch dieselbe Reihenfolge.
| Übung 2 | Allgemeines | Zur Lösung |
Was ist am folgenden SELECT-Befehl falsch und warum? Die Aufgabe dazu lautet:
Gesucht werden Kombinationen von Fahrzeug-Kennzeichen und Fahrzeugtypen, wobei alle Typen aufgeführt werden sollen; es werden nur die ersten 20 Fahrzeuge nach ID benötigt.
select Kennzeichen, Bezeichnung
from Fahrzeug fz
left join Fahrzeugtyp ft on fz.Fahrzeugtyp_ID = ft.ID
where fz.ID <= 20 ;
| Übung 3 | Sinnvollen SELECT-Befehl erstellen | Zur Lösung |
Gesucht werden alle registrierten Versicherungsgesellschaften und (soweit vorhanden) deren Kunden mit Name, Vorname.
| Übung 4 | Sinnvollen SELECT-Befehl erstellen | Zur Lösung |
Gesucht werden die Dienstwagen, deren Fahrzeugtypen sowie die Hersteller. Die Liste der Typen soll vollständig sein.
| Übung 5 | Sinnvollen SELECT-Befehl erstellen | Zur Lösung |
Gesucht werden Kombinationen von Mitarbeitern und ihren Dienstwagen (einschl. Typ). Es geht um die Abteilungen 1 bis 5; auch nicht-persönliche Dienstwagen sollen aufgeführt werden.
| Übung 6 | Sinnvollen SELECT-Befehl erstellen | Zur Lösung |
Gesucht werden alle registrierten Versicherungsgesellschaften sowie alle Kunden mit Name, Vorname, soweit der Nachname mit 'S' beginnt.
| Übung 7 | RIGHT oder LEFT | Zur Lösung |
Vertauschen Sie in der Lösung von Übung 5 die beiden Tabellen Mitarbeiter und Dienstwagen und erläutern Sie:
- Warum werden jetzt mehr Mitarbeiter angezeigt, und zwar auch solche ohne Dienstwagen?
- Warum fehlt jetzt der „nicht-persönliche“ Dienstwagen?
| Übung 8 | SELECT-Befehl mit mehreren Tabellen | Zur Lösung |
Gesucht werden Angaben zu den Mitarbeitern und den Dienstwagen. Beim Mitarbeiter sollen Name und Vorname angegeben werden, bei den Dienstwagen Bezeichnung und Name des Herstellers. Die Liste aller Fahrzeugtypen soll vollständig sein.
| Übung 9 | SELECT-Befehl mit mehreren Tabellen | Zur Lösung |
Ergänzen Sie die Lösung zu Übung 8 insofern, dass nur Mitarbeiter der Abteilungen 1 bis 5 angezeigt werden; die Zeilen ohne Mitarbeiter sollen unverändert ausgegeben werden.
| Lösung zu Übung 1 | Allgemeines | Zur Übung |
Die Aussagen 2, 3, 5 sind richtig, die Aussagen 1, 4, 6 sind falsch.
| Lösung zu Übung 2 | Allgemeines | Zur Übung |
Richtig ist folgender Befehl:
select Kennzeichen, Bezeichnung
from Fahrzeug fz
right join Fahrzeugtyp ft on fz.Fahrzeugtyp_ID = ft.ID
where fz.ID <= 20 or fz.ID is null;
Weil alle Typen aufgeführt werden sollen, wird ein RIGHT JOIN benötigt. Damit auch der Vermerk „es gibt zu einem Typ keine Fahrzeuge“ erscheint, muss die WHERE-Klausel um die IS NULL-Prüfung erweitert werden.
| Lösung zu Übung 3 | Sinnvollen SELECT-Befehl erstellen | Zur Übung |
select Bezeichnung, Name, Vorname
from Versicherungsgesellschaft vg
left join Versicherungsnehmer vn on vg.ID = vn.Versicherungsgesellschaft_ID
order by Bezeichnung, Name, Vorname;
| Lösung zu Übung 4 | Sinnvollen SELECT-Befehl erstellen | Zur Übung |
select Kennzeichen, Bezeichnung, Name
from Dienstwagen dw
right join Fahrzeugtyp ft on ft.ID = dw.Fahrzeugtyp_ID
inner join Fahrzeughersteller fh on fh.ID = ft.Hersteller_ID
order by Name, Bezeichnung, Kennzeichen;
| Lösung zu Übung 5 | Sinnvollen SELECT-Befehl erstellen | Zur Übung |
SELECT mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
RIGHT JOIN Dienstwagen dw ON dw.Mitarbeiter_ID = mi.ID
where mi.Abteilung_ID <= 5 or mi.ID is null;
Die IS NULL-Prüfung wird wegen der „nicht-persönlichen“ Dienstwagen benötigt.
| Lösung zu Übung 6 | Sinnvollen SELECT-Befehl erstellen | Zur Übung |
SELECT Bezeichnung, Name, Vorname
FROM Versicherungsgesellschaft vg
full JOIN Versicherungsnehmer vn ON vg.id = vn.Versicherungsgesellschaft_id
where vn.Name like 'S%' or vn.id is null
ORDER BY Bezeichnung, Name, Vorname;
| Lösung zu Übung 7 | RIGHT oder LEFT | Zur Übung |
- Bei einem RIGHT JOIN werden alle Einträge der rechten Tabelle angezeigt. „Rechts“ stehen jetzt die Mitarbeiter, also werden alle Mitarbeiter der betreffenden Abteilungen angezeigt.
- Bei einem RIGHT JOIN werden die Einträge der linken Tabelle nur dann angezeigt, wenn sie zu einem Eintrag der rechten Tabelle gehören. Der „nicht-persönliche“ Dienstwagen aus der linken Tabelle gehört aber zu keinem der Mitarbeiter.
| Lösung zu Übung 8 | SELECT-Befehl mit mehreren Tabellen | Zur Übung |
SELECT mi.Name, mi.Vorname,
dw.Kennzeichen, ft.Bezeichnung, fh.Name as HST
FROM Dienstwagen dw
left join Mitarbeiter mi on mi.id = dw.Mitarbeiter_id
right JOIN Fahrzeugtyp ft on ft.Id = dw.Fahrzeugtyp_id
inner join Fahrzeughersteller fh on fh.Id = ft.Hersteller_id;
Hinweise: Die Reihenfolge der JOINs ist nicht eindeutig; der LEFT JOIN kann auch später kommen. Wichtig ist, dass die Verbindung Dienstwagen ↔ Fahrzeugtyp ein RIGHT JOIN ist (oder bei Vertauschung der Tabellen ein LEFT JOIN).
| Lösung zu Übung 9 | SELECT-Befehl mit mehreren Tabellen | Zur Übung |
SELECT mi.Name, mi.Vorname,
dw.Kennzeichen, ft.Bezeichnung, fh.Name as HST
FROM Dienstwagen dw
left join Mitarbeiter mi on mi.id = dw.Mitarbeiter_id
right JOIN Fahrzeugtyp ft on ft.Id = dw.Fahrzeugtyp_id
inner join Fahrzeughersteller fh on fh.Id = ft.Hersteller_id
where (mi.Abteilung_id <= 5) or (mi.id is null);
| Mehr zu JOIN |
Die folgenden Ergänzungen zu JOIN sind in besonderen Situationen hilfreich.
Welcher JOIN passt wann?
Diese Frage stellt sich vor allem Anfängern sehr oft. Neben den (theoretischen) Überlegungen der vorigen Kapitel helfen oft besondere Beispiele; vielleicht ist dieses nützlich.
Wir haben zwei Tabellen: Paare (also Eltern) und Kinder. Es gibt kinderlose Paare, Paare mit Kindern und Waisenkinder. Wir wollen die Eltern und Kinder in Abfragen verknüpfen; bei den Symbolen steht der linke Kreis für die Tabelle Paare und der rechte Kreis für die Tabelle Kinder.
| LEFT JOIN | 
|
Alle Paare und (falls es Kinder gibt) auch diese | SELECT * FROM Paare LEFT JOIN Kinder ON Paare.Key = Kinder.Key |
| INNER JOIN | 
|
Nur Paare, die Kinder haben | SELECT * FROM Paare INNER JOIN Kinder ON Paare.Key = Kinder.Key |
| RIGHT JOIN | 
|
Alle Kinder und (falls es Eltern gibt) auch diese | SELECT * FROM Paare RIGHT JOIN Kinder ON Paare.Key = Kinder.Key |
| LEFT JOIN IS NULL |
|
Nur Paare, die keine Kinder haben | SELECT * FROM Paare LEFT JOIN Kinder ON Paare.Key = Kinder.Key WHERE Kinder.Key IS NULL |
| RIGHT JOIN IS NULL |
|
Nur Waisenkinder | SELECT * FROM Paare RIGHT JOIN Kinder ON Paare.Key = Kinder.Key WHERE Paare.Key IS NULL |
| FULL JOIN | 
|
Alle Paare und alle Kinder | SELECT * FROM Paare FULL JOIN Kinder ON Paare.Key = Kinder.Key |
| FULL JOIN IS NULL |
|
Alle kinderlosen Paare und alle Waisenkinder | SELECT * FROM Paare FULL JOIN Kinder ON Paare.Key = Kinder.Key WHERE Kinder.Key IS NULL OR Paare.Key IS NULL |
| Die zweite Variante mit dem INNER JOIN kann man auch so ausdrücken: | |||
| LEFT JOIN IS NOT NULL |
|
Alle Paare und (falls es Kinder gibt) auch diese, wobei es ein Kind geben muss | SELECT * FROM Paare LEFT JOIN Kinder ON Paare.Key = Kinder.Key WHERE Kinder.Key IS NOT NULL |
Tatsächlich sind oft mehrere Wege möglich, wie bereits im letzten Kapitel gesagt wurde: Das Ergebnis für "A LEFT JOIN B" gleicht dem von "B RIGHT JOIN A".
SELF JOIN – Verknüpfung mit sich selbst
Solche Verknüpfungen sind immer dann nötig, wenn Werte einer einzigen Spalte aus verschiedenen Datensätzen verbunden werden. Der JOIN dafür benutzt auf beiden Seiten dieselbe Tabelle <tabelle>; diese beiden „Instanzen“ müssen durch einen Alias unterschieden werden.
SELECT <spaltenliste>
FROM <tabelle> t1
JOIN <tabelle> t2 ON <verknüpfung>
WHERE <auswahlbedingungen>
Hinweis: Es funktioniert nicht, wenn eine der beiden Instanzen mit Alias und die andere ohne Alias benutzt wird. Dann kommt das DBMS erst recht durcheinander. In einem Forum stand einmal ein solches Problem mit einer sehr vertrackten Ausgabe, bei dem diese Ursache erst nach längerer Diskussion klar wurde.
Dies soll zunächst an zwei Beispielen umgesetzt werden.
Beispiel 1
Zeige zu jedem Fahrzeug andere Fahrzeuge aus dem gleichen Kreis.
Wir beschränken uns darauf, dass in einer Zeile jeweils zwei von allen möglichen Kombinationen angezeigt werden, auch wenn viele Angaben wiederholt werden.
select a.Kennzeichen, b.Kennzeichen
from Fahrzeug a
JOIN Fahrzeug b
on SUBSTRING(a.Kennzeichen from 1 for 3)
= SUBSTRING(b.Kennzeichen from 1 for 3)
where a.Kennzeichen < b.Kennzeichen
order by a.Kennzeichen;
AusgabeKENNZEICHEN KENNZEICHEN1
----------- ------------
BO-GH 102 BO-KL 678
BOR-NO 234 BOR-PQ 567
BOR-NO 234 BOR-RS 890
BOR-PQ 567 BOR-RS 890
GE-AB 123 GE-AC 246
GE-AB 123 GE-EG 892
GE-AC 246 GE-EG 892
RE-CD 456 RE-LM 901
RE-CD 456 RE-LM 902
RE-CD 456 RE-LM 903
Gesucht werden Kombinationen eines Fahrzeugs mit jeweils einem anderen Fahrzeug, wobei die Bedingung „gleicher Kreis“ erfüllt sein soll. Wir brauchen also innerhalb eines SELECT-Befehls zwei Zugriffe auf die Tabelle Fahrzeug mit einer passenden Vergleichsbedingung. (Diese haben wir etwas ungenau formuliert, damit sie nicht unübersichtlich wird.)
Dies ist gleichzeitig ein Beispiel dafür, dass beliebige Bedingungen möglich sind. Überlegen Sie bitte auch, warum unter WHERE die „kleiner als“-Bedingung benutzt wird.
Beispiel 2
Zeige zu jedem Fahrzeug mit mehreren Schadensfällen den zeitlichen Abstand von einem Vorfall zum nächsten an.
Wir benötigen für jedes der Fahrzeuge aus der Tabelle Schadensfall zwei Einträge mit dem Datum sowie den Abstand, der einfach als Differenz benutzt wird und somit die Anzahl der Tage anzeigt. Die Fahrzeuge sind freilich erst über die Tabelle Zuordnung_SF_FZ zu finden und müssen zusätzlich verbunden werden. Außerdem sind die Fahrzeuge und die Reihenfolge der Datumsangaben zu kontrollieren.
SELECT fz.ID, fz.Kennzeichen,
sf1.Datum AS Datum1, sf2.Datum AS Datum2, sf2.Datum - sf1.Datum AS Abstand
FROM Zuordnung_SF_FZ zu1
join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID = zu2.Fahrzeug_ID
join Schadensfall sf1 ON zu1.Schadensfall_ID = sf1.ID
JOIN Schadensfall sf2 on zu2.Schadensfall_ID = sf2.Id
JOIN Fahrzeug fz ON zu1.Fahrzeug_ID = fz.ID
where sf1.Datum < sf2.Datum
and sf2.Datum = ( SELECT MIN(sf3.Datum)
FROM Schadensfall sf3
join Zuordnung_SF_FZ zu3 on zu3.Schadensfall_ID = sf3.id
WHERE sf1.Datum < sf3.Datum
and zu3.Fahrzeug_ID = zu1.Fahrzeug_ID )
order by fz.ID, Datum1;
AusgabeID KENNZEICHEN DATUM1 DATUM2 ABSTAND
-- -------------- ---------- ---------- -------
4 GE-AB 123 03.02.2007 05.10.2008 610
6 HER-EF 789 19.12.2007 21.06.2009 550
7 BO-GH 102 11.07.2007 13.03.2009 611
7 BO-GH 102 13.03.2009 01.08.2009 141
Alternativen bieten die folgenden Lösungen:
SELECT fz.ID, fz.Kennzeichen,
sf1.Datum AS Datum1, sf2.Datum AS Datum2, sf2.Datum - sf1.Datum AS Abstand
FROM Zuordnung_SF_FZ zu1
join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID = zu2.Fahrzeug_ID
join Schadensfall sf1 ON zu1.Schadensfall_ID = sf1.ID
JOIN Schadensfall sf2 on zu2.Schadensfall_ID = sf2.Id
and sf1.Datum < sf2.Datum
and sf2.Datum = ( SELECT MIN(sf3.Datum)
FROM Schadensfall sf3
join Zuordnung_SF_FZ zu3
on zu3.Schadensfall_ID = sf3.ID
WHERE sf1.Datum < sf3.Datum
and zu3.Fahrzeug_ID = zu1.Fahrzeug_ID )
JOIN Fahrzeug fz ON zu1.Fahrzeug_ID = fz.ID
order by fz.ID, Datum1;
SELECT fz.ID, fz.Kennzeichen,
sf1.Datum AS Datum1, MIN(sf2.Datum) AS Datum2, MIN(sf2.Datum - sf1.Datum) AS Abstand
FROM Zuordnung_SF_FZ zu1
join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID = zu2.Fahrzeug_ID
join Schadensfall sf1 ON zu1.Schadensfall_ID = sf1.ID
JOIN Schadensfall sf2 on zu2.Schadensfall_ID = sf2.Id
and sf1.Datum < sf2.Datum
JOIN Fahrzeug fz ON zu1.Fahrzeug_ID = fz.ID
group by fz.ID, fz.Kennzeichen, sf1.Datum
order by fz.ID, Datum1;
In dieser Aufgabe stecken mehrere Probleme:
- Die Angaben aus der Spalte Datum der Tabelle Schadensfall müssen zweimal geholt werden.
- Zu jedem Schadensfall wird der Eintrag der Tabelle Zuordnung_SF_FZ benötigt, weil die Schadensfälle für jedes Fahrzeug gesucht werden.
- Das Datum, das zu sf1 gehört, muss immer „kleiner“ sein, also früher liegen als das Datum, das zu sf2 gehört.
- Außerdem benötigen wir „irgendwo“ die Einschränkung, dass zum Vergleich nur der jeweils folgende Schadensfall genommen werden darf, also das MINimum der späteren Einträge:
- Die erste Lösung verwendet dafür eine Unterabfrage für eine Auswahlbedingung.
- Die zweite Lösung arbeitet mit einer Unterabfrage bei der Verknüpfungsbedingung.
- Die dritte Lösung benutzt das MINimum direkt als Aggregatfunktion und verlangt „zum Ausgleich“ eine GROUP BY-Klausel.
Die Lösung benötigt deshalb mehrfach verknüpfte Tabellen:
- Als Grundlage wird die Tabelle der Zuordnungen zwischen Schadensfällen und Fahrzeugen zu1 verwendet.
- Hauptverknüpfung ist der Self-Join zu2 auf dieselbe Tabelle, weil nur solche Einträge verknüpft werden sollen, die sich auf dasselbe Fahrzeug beziehen.
- Zu jedem Schadensfall aus zu1 werden die detaillierten Angaben aus sf1 geholt.
- Zu jedem Schadensfall aus zu2 werden die detaillierten Angaben aus sf2 geholt.
- Ergänzend benötigen wir das Kennzeichen des betreffenden Fahrzeugs, also einen JOIN auf Fahrzeug.
- Vor allem müssen die Vergleichsbedingungen für die Datumsangaben eingebaut werden.
Welche Lösung die Datenbank am wenigsten belastet, kann nicht generell gesagt werden, weil es von zu vielen Umständen abhängt.
Erweiterung durch einen OUTER JOIN
Bei diesen Lösungen stehen nicht alle Schadensfälle im Ergebnis, weil es nur um den zeitlichen Abstand ging. Wenn beispielsweise auch die Schadenshöhe gewünscht wird, müssen wir dafür sorgen, dass von sf1 oder sf2 alle Einträge angezeigt werden; wir brauchen also einen OUTER JOIN wie zum Beispiel (auf der Grundlage der letzten Version) so:
SELECT fz.ID, fz.Kennzeichen,
sf1.Datum AS Datum1, MIN(sf2.Datum) AS Datum2, MIN(sf2.Datum - sf1.Datum) AS Abstand,
sf1.Schadenshoehe
FROM Zuordnung_SF_FZ zu1
left join Zuordnung_SF_FZ zu2 on zu1.Fahrzeug_ID = zu2.Fahrzeug_ID
left join Schadensfall sf1 ON zu1.Schadensfall_ID = sf1.ID
left JOIN Schadensfall sf2 on zu2.Schadensfall_ID = sf2.Id
and sf1.Datum < sf2.Datum
left JOIN Fahrzeug fz ON zu1.Fahrzeug_ID = fz.ID
group by fz.ID, fz.Kennzeichen, sf1.Datum, sf1.Schadenshoehe
order by fz.ID, Datum1;
AusgabeID KENNZEICHEN DATUM1 DATUM2 ABSTAND SCHADENSHOEHE
-- --------------- ---------- ---------- ------- -------------
3 RE-LM 903 27.05.2008 1.438,75
4 GE-AB 123 03.02.2007 05.10.2008 610 1.234,50
4 GE-AB 123 05.10.2008 1.983,00
5 RE-CD 456 11.07.2007 2.066,00
6 HER-EF 789 19.12.2007 21.06.2009 550 3.715,60
6 HER-EF 789 21.06.2009 865,00
7 BO-GH 102 11.07.2007 13.03.2009 611 2.066,00
7 BO-GH 102 13.03.2009 01.08.2009 141 4.092,15
7 BO-GH 102 01.08.2009 2.471,50
Wir nehmen es hin, dass dann alle Schadensfälle aufgeführt werden, auch für die Fahrzeuge, die nur einmal „aufgefallen“ sind. Dies ist eine Folge davon, dass Grundlage aller Verknüpfungen die Tabelle der Zuordnungen sein musste.
Bei allen solchen Situationen müssen Sie genau überlegen, wie die verschiedenen Instanzen miteinander verknüpft werden und wie die übrigen Bedingungen einzubinden sind. Oft führen erst mehrere Versuche zum Ziel. Hilfreich sind auch die Ausführungspläne, die ein DBMS anbieten kann.
Weitere Situationen
Zum Schluss sollen noch ein paar andere Beispiele erwähnt werden, bei denen ein Self-Join hilft.
- Wenn bei den Dienstwagen die privat gefahrenen Strecken abgerechnet werden sollen, können der km-Stand beim Fahrtantritt und beim Fahrtende in derselben Spalte, aber in getrennten Datensätzen gespeichert werden.
- Doppelte Adressen innerhalb einer Adressendatei können aufgespürt werden (siehe Übung 3).
- Wenn in der Tabelle Mitarbeiter zu einem Mitarbeiter der Leiter der Abteilung gesucht wird, benötigen wir wegen des doppelten Zugriffs auf dieselbe Tabelle ebenfalls einen Self-Join.
CROSS JOIN – das kartesische Produkt
Mit dieser speziellen Formulierung kann man deutlich machen, dass man wirklich ein kartesisches Produkt herstellen will und nicht etwa nur die JOIN-Bedingung vergessen hat:
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
CROSS JOIN Dienstwagen dw;
Als Ergebnis wird tatsächlich jede Kombination eines Mitarbeiters mit einem Dienstwagen ausgegeben, also n mal m Sätze – wie beim allerersten Versuch im Kapitel Einfache Tabellenverknüpfung. Man kann die Ergebnismenge auch einschränken durch eine WHERE-Klausel:
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
CROSS JOIN Dienstwagen dw
WHERE mi.Name like 'S%' and CHAR_LENGTH(dw.Kennzeichen) = 10;
Hinweis: Die DBMS verhalten sich bei einem CROSS JOIN unterschiedlich; teilweise ist ein CROSS JOIN mit WHERE-Klausel nichts anderes als ein INNER JOIN.
Der Nutzen des CROSS JOIN wird bei unserer sparsamen Beispieldatenbank nicht klar. Unter Oracle wäre folgendes Verfahren möglich und hilfreich:
Kontrollieren Sie mit der Tabelle Fahrzeugbuchung, welche Fahrzeuge am 2.12.2009 im Fuhrpark zur Verfügung stehen.
SELECT mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS Typ
FROM Mitarbeiter mi
CROSS JOIN Dienstwagen dw
INNER JOIN Fahrzeugbuchung fb
ON dw.Kennzeichen = fb.Kennzeichen
WHERE fb.Datum = to_date('02.12.2009','dd.mm.yyyy')
AND fb.Status = 'noch nicht gebucht';
WITH – Inline-View
Oft kommt es vor, dass man die Daten aus einer Tabelle erst bearbeiten möchte, bevor man sie mit einer anderen Tabelle verknüpft. Beispiel:
SELECT Kuerzel, Bezeichnung, Anzahl_Mitarbeiter
FROM Abteilung
INNER JOIN ( select Abteilung_ID, count(*) AS Anzahl_Mitarbeiter
FROM Mitarbeiter
GROUP BY Abteilung_ID
) MA_Anzahl
ON Abteilung.ID = MA_Anzahl.Abteilung_ID
;
Dabei wird zunächst nach der Tabelle Mitarbeiter die Anzahl der Mitarbeiter für jede Abteilung bestimmt. Das Ergebnis wird wie eine Tabelle MA_Anzahl behandelt und über Abteilung_ID mit der Tabelle Abteilung verknüpft.
Diese Syntax ist ziemlich verschachtelt. Man kann sie auch so schreiben:
WITH MA_Anzahl as
( select Abteilung_ID, count(*) AS Anzahl_Mitarbeiter
FROM Mitarbeiter
GROUP BY Abteilung_ID
)
SELECT Kuerzel, Bezeichnung, Anzahl_Mitarbeiter
FROM Abteilung
INNER JOIN MA_Anzahl
ON Abteilung.ID = MA_Anzahl.Abteilung_ID
;
MA_Anzahl wird benutzt wie eine VIEW, die allerdings nicht permanent angelegt wird, sondern die nur für die Ausführung dieses einen SQL-Befehls gültig ist. Der Unterschied liegt „nur“ darin, dass die Unterabfrage herausgelöst wird und durch WITH als separate temporäre Tabelle eingebunden wird.
Ob man die obere oder die untere Variante besser findet, ist sicher Geschmackssache und hat auch damit zu tun, welche Formulierung man gewöhnt ist. Wenn man sich die SQL-Beispiele im Kapitel Fremdschlüssel-Beziehungen daraufhin ansieht, dann würde eine Formulierung ohne WITH viel länger und komplizierter werden.
Zusammenfassung
In diesem Kapitel lernten Sie einige weitere Möglichkeiten im Zusammenhang mit JOINs kennen.
- Für bestimmte Anforderungen sind Verknüpfungen einer Tabelle mit sich selbst sinnvoll oder notwendig.
- In diesen Fällen sind die Auswahl- und Verknüpfungsbedingungen besonders sorgfältig zu bestimmen.
- Durch WITH können Verknüpfungen über JOINs übersichtlicher werden.
Übungen
| Übung 1 | Fragen zum Verständnis | Zur Lösung |
Welche der folgenden Aussagen sind wahr, welche falsch?
- Eine Tabelle kann mit sich selbst verknüpft werden.
- SELF JOIN ist nur ein inhaltlicher Begriff, aber kein SQL-Schlüsselwort.
- Bei einem SELF JOIN sind nur INNER JOINs erlaubt.
- Eine bestimmte Tabelle darf in einem SELF JOIN nur zweimal verwendet werden.
- Für einen SELF JOIN können Tabellen-Aliase benutzt werden, aber sie sind nicht überall erforderlich.
- Ein CROSS JOIN ist eine Verknüpfung zweier Tabellen ohne Verknüpfungsbedingung.
- Bei einem CROSS JOIN darf sich die WHERE-Klausel nicht auf die (rechte) Tabelle des JOINs beziehen.
- Die Schreibweise mit WITH ist kein Sonderfall eines JOINs, sondern eine übersichtlichere Schreibweise, wenn mehrere Tabellen verknüpft werden.
| Übung 2 | Verknüpfung einer Tabelle mit sich selbst | Zur Lösung |
Suchen Sie zu jedem Mitarbeiter den Namen und Vornamen des Leiters der Abteilung. Die Abteilungsleiter in unserer einfachen Firmenhierarchie haben keinen Vorgesetzten; sie sollen in der Liste deshalb nicht aufgeführt werden.
| Übung 3 | Doppelte Adressen suchen | Zur Lösung |
Suchen Sie Einträge in der Tabelle Versicherungsnehmer, bei denen Name, Vorname, PLZ, Strasse übereinstimmen. Jeweils zwei dieser Adressen sollen mit ihrer ID und den übereinstimmenden Angaben aufgeführt werden.
Hinweis: Benutzen Sie einen JOIN, der sich nicht auf übereinstimmende IDs bezieht.
| Lösung zu Übung 1 | Fragen zum Verständnis | Zur Übung |
Die Aussagen 1, 2, 6, 8 sind wahr, die Aussagen 3, 4, 5, 7 sind falsch.
| Lösung zu Übung 2 | Verknüpfung einer Tabelle mit sich selbst | Zur Übung |
select mi1.Abteilung_ID as Abt, mi1.Name, mi1.Vorname, mi2.Name as LtrName, mi2.Vorname as LtrVorn
from Mitarbeiter mi1
join Abteilung ab on mi1.Abteilung_ID = ab.ID
join Mitarbeiter mi2 on mi2.Abteilung_ID = ab.ID
where mi2.Ist_Leiter = 'J'
and mi1.Ist_Leiter = 'N'
| Lösung zu Übung 3 | Doppelte Adressen suchen | Zur Übung |
select a.Name, a.Vorname, a.PLZ, a.Strasse, a.ID, b.ID
from Versicherungsnehmer a
JOIN Versicherungsnehmer b
on a.Name = b.Name and a.Vorname = b.Vorname
and a.PLZ = b.PLZ and a.Strasse = b.Strasse
where a.ID < b.ID;
Siehe auch
Bei Wikipedia finden Sie weitere Erläuterungen:
- Auswertungsplan, auch „Ausführungsplan“ genannt
| Nützliche Erweiterungen |
In diesem Kapitel werden verschiedene Erweiterungen des SELECT-Befehls genauer behandelt.
Die Beispiele beziehen sich auch hier auf den Anfangsbestand der Beispieldatenbank; auf die Ausgabe der selektierten Datensätze wird wiederum weitgehend verzichtet. Bitte probieren Sie alle Beispiele aus und nehmen Sie verschiedene Änderungen vor, um die Auswirkungen zu erkennen.
DISTINCT – keine doppelten Einträge
Wenn Sie den DISTINCT-Parameter bei einem SELECT-Befehl angeben, erhalten Sie nur eindeutige Ergebnisse:
SELECT distinct Fahrzeug_ID
from Zuordnung_SF_FZ;
AusgabeFahrzeug_ID
3
4
5
6
7
Bitte beachten Sie: Als „eindeutig“ gilt immer die gesamte Zeile, also alle Spalten zusammen. Die folgende Abfrage liefert alle Datensätze; Fahrzeuge mit mehreren Schadensfällen stehen auch mehrfach in der Liste.
Nur theoretisch DISTINCT, praktisch nichtSELECT distinct Fahrzeug_ID, ID
from Zuordnung_SF_FZ;
Als Gegenstück gibt es den ALL-Parameter, der ausdrücklich alle Datensätze abfragt. Da dies der Standardwert ist, wird er äußerst selten benutzt:
SELECT all Fahrzeug_ID
from Zuordnung_SF_FZ
Beschränkung auf eine Anzahl Zeilen
Häufig will man nicht sofort das gesamte Ergebnis sehen, sondern nur einen Teil der Zeilen.
Vor allem im Netzwerk kostet es seine Zeit, eine größere Menge von Datensätzen zu übertragen. Es ist deshalb oft praktisch, zunächst einen Teil des Ergebnisses zu holen und anzuzeigen. Während der Anwender sich mit diesem Teilergebnis beschäftigt, wird „im Hintergrund“ der nächste Abschnitt geholt usw.
Im SQL-Standard gibt es dafür (noch) kein Verfahren. Abweichend vom üblichen Vorgehen in diesem Buch erhalten Sie Lösungen für verschiedene DBMS.
Anstelle konstanter Werte (ohne Klammern) kann in allen folgenden Fällen auch ein SQL-Ausdruck (in Klammern) angegeben werden.
Firebird: FIRST SKIP oder ROWS
Firebird bietet gleich zwei Lösungen an, die erste mit FIRST / SKIP:
SELECT [DISTINCT] [ FIRST <value1> ] [ SKIP <value2> ] <select list> FROM ... /* usw. */
Der FIRST-Parameter gibt an, wie viele Zeilen am Anfang anzuzeigen sind; der SKIP-Parameter legt fest, wie viele Zeilen davor übersprungen werden sollen. Beide Parameter werden einzeln oder zusammen benutzt; sie folgen direkt als erste Klausel nach DISTINCT, noch vor der Spaltenliste. Einige Beispiele:
➤ Nur FIRST zeigt die ersten Zeilen.
select FIRST 10
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
➤ Nur SKIP überspringt die ersten Zeilen.
select SKIP 10
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
➤ FIRST zeigt die ersten 10 Zeilen an, aber wegen SKIP werden vorher 5 Zeilen übersprungen.
select FIRST 10 SKIP 5
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
➤ Mit einem Ausdruck kann dafür gesorgt werden, dass etwa das erste Viertel der Datensätze abgerufen wird:
select FIRST ( (SELECT count(*) from Mitarbeiter) / 4 )
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
Die zweite Firebird-Variante benutzt mit ROWS direkt Zeilennummern:
SELECT ... FROM ... WHERE ... ORDER BY ... ROWS <value1> [ TO <value2> ]
Die ROWS-Parameter legen fest, dass (nur) eine bestimmte Anzahl Zeilen angezeigt werden sollen, die durch die Zeilennummern gekennzeichnet sind.
- Wenn nur ROWS benutzt wird, bezeichnet <value1> die Gesamtzahl der angezeigten Zeilen.
- Wenn ROWS zusammen mit TO benutzt wird, ist <value1> die erste Zeilennummer und <value2> die letzte Zeilennummer.
Einige Beispiele:
➤ Ausgabe der Zeilen 10 bis 20 (also insgesamt 11 Zeilen)
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name
ROWS 10 TO 20;
➤ Der erste Datensatz gemäß Sortierung
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name
ROWS 1;
➤ Der letzte Datensatz gemäß Sortierung
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name desc
ROWS 1;
Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.
Bei einer Interbase-Datenbank sind auch prozentuale Angaben möglich; das ist bei Firebird entfallen.
Microsoft SQL: TOP
Der MS-SQL Server benutzt diese Syntax:
SELECT [DISTINCT] TOP ( <value> ) [PERCENT] [WITH TIES] <select list> FROM ... /* usw. */
Mit den TOP-Parametern wird festgelegt, dass (nur) eine bestimmte Anzahl von Zeilen angezeigt werden sollen, die durch die Zeilennummern gekennzeichnet sind. Diese Parameter folgen direkt als erste Klausel nach DISTINCT, noch vor der Spaltenliste.
- <value> bezeichnet die Gesamtzahl der angezeigten Zeilen. Es wird empfohlen, die Klammern immer zu setzen.
- Wenn TOP zusammen mit PERCENT benutzt wird, handelt es sich dabei um die prozentuale Angabe der Zeilenzahl.
- WITH TIES muss zusammen mit ORDER BY benutzt werden und liefert dann zusätzliche doppelte Zeilen, wenn der letzte Wert nach der Reihenfolge gleich ist den Werten in danach folgenden Zeilen.
Einige Beispiele:
➤ Nur TOP zeigt die ersten Zeilen.
select
TOP 10
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
➤ TOP + PERCENT zeigt z. B. das erste Viertel an.
select
TOP 25 PERCENT
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name;
➤ OFFSET NEXT für einen Teil der Daten (Paging), z.B. Zeile 11-20
select
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;;
➤ Der letzte Datensatz gemäß Sortierung
select
TOP 1
ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name desc;
Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.
MySQL und PostgreSQL: LIMIT
Diese DBMS benutzen den LIMIT-Parameter. Dieser Parameter folgt nach ORDER BY, wobei die Sortierung nicht angegeben werden muss.
SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT <value1> OFFSET <value2>
Dabei wird mit <value1> angegeben, wie viele Zeilen am Anfang angezeigt werden sollen. Mit <value2> kann nach dem Begriff OFFSET außerdem angegeben werden, wie viele Zeilen davor übersprungen werden sollen.
➤ Es werden die ersten 10 Zeilen angezeigt.
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name
LIMIT 10;
➤ Es werden die ersten 10 Zeilen angezeigt, aber vorher werden 5 Zeilen übersprungen.
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name
LIMIT 10 OFFSET 5;
➤ Der letzte Datensatz gemäß Sortierung
select ID, Name, Vorname, Abteilung_ID as Abt
from Mitarbeiter
order BY Name desc
LIMIT 1;
Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.
Eine andere Schreibweise für diesen Parameter verzichtet auf das Wort OFFSET:
SELECT ... FROM ... WHERE ORDER BY ... LIMIT [ <value2>, ] <value1>
Bei dieser Variante wird genauso mit <value1> angegeben, wie viele Zeilen am Anfang angezeigt werden sollen. Mit <value2> kann außerdem angegeben werden, wie viele Zeilen davor übersprungen werden sollen; dieser Wert wird jedoch zuerst angegeben und durch ein Komma von der gewünschten Zeilenzahl getrennt.
Die Bedeutung beider Varianten ist identisch, sodass Beispiele nicht nötig sind. Es ist wohl Geschmackssache, welche Version „eingängiger“ ist.
Oracle: ROWNUM
Bei dem DBMS Oracle gibt es bei jedem SELECT-Ergebnis eine implizite Spalte Rownum. Man kann diese Spalte mit ausgeben lassen. Solange man kein ORDER BY angibt, ist die Reihenfolge der ausgegebenen Sätze nicht festgelegt. Dieselbe Abfrage kann an einem anderen Tag durchaus eine andere Nummerierung der Sätze hervorbringen. Das liegt z. B. daran, dass jemand die Datensätze in der Zwischenzeit reorganisiert hat.
SELECT Name, rownum
FROM Mitarbeiter;
AusgabeNAME ROWNUM
------- ------
Müller 1
Schneider 2
Meyer 3
Schmitz 4 /* usw. */
Wenn man diese Spalte Rownum nicht angibt, dann wird sie auch nicht ausgegeben. Ihr Vorteil ist, dass man sie auch bei WHERE verwenden kann:
SELECT Name
FROM Mitarbeiter
WHERE Rownum <= 2;
AusgabeNAME ROWNUM
------- ------
Müller 1
Schneider 2
Folgende Formulierung funktioniert allerdings nicht, wenn man nur den 10. Satz ausgeben will:
Oracle-VersionSELECT Name
FROM Mitarbeiter;
WHERE Rownum = 10;
Das liegt daran, dass der Zähler Rownum nur die Zeilen zählt, die auch wirklich ausgegeben werden. Wenn die Tabelle 500 Sätze hat, dann wird bei jedem Satz geprüft, ob Rownum bereits den Wert 10 erreicht hat. Das ist jedoch nie der Fall, da der Zähler immer den Wert 0 behält.
Da muss man schon das DBMS zwingen, die Ergebnismenge mit der Rownum zwischenzuspeichern. Dann geht es:
Oracle-VersionSELECT Name
FROM ( SELECT Name, rownum R
FROM Mitarbeiter )
WHERE R = 10;
Welcher Satz dabei ausgegeben wird, ist jedoch dem Zufall überlassen.
Wenn man die Ergebnismenge sortiert ausgeben will und dabei nur die ersten 6 Sätze ausgegeben haben will, dann funktioniert die folgende Formulierung nicht:
Oracle-VersionSELECT Name
FROM Mitarbeiter;
WHERE Rownum <= 6
ORDER BY Name;
Man wird feststellen, dass zwar nur 6 Sätze ausgegeben werden, dass das aber keinesfalls immer die alphabetisch ersten 6 Namen sind. Das liegt daran, dass zunächst WHERE ausgewertet wird und danach erst sortiert wird. Es werden also – wie bei den vorangegangenen Beispielen beschrieben – beliebige 6 Sätze gelesen, und nur diese 6 Sätze werden sortiert.
Für die richtige Lösung muss die Datenbank schon etwas mehr tun. Sie muss zuerst alle vorhandenen Sätze sortieren und dann die ersten 6 Sätze ausgeben:
Oracle-VersionSELECT Name
FROM ( SELECT Name
FROM Mitarbeiter
ORDER BY Name )
WHERE Rownum <= 6;
Sybase: ROWCOUNT
Bei diesem DBMS wird zuerst die Anzahl der gewünschten Zeilen angegeben, danach folgt der SELECT-Befehl.
SET rowcount 10;
SELECT Name, rownum
FROM Mitarbeiter;
Mehrere Abfragen zusammenfassen
UNION – Vereinigung
Mit der UNION-Klausel werden mehrere Abfragen verknüpft und als einheitliche Ergebnismenge geliefert. Dieses Verfahren ist Standard und steht in jedem DBMS zur Verfügung. Es ist vor allem in zwei Situationen sinnvoll:
- Die Daten stammen aus verschiedenen Tabellen mit ähnlicher Struktur und sollen gemeinsam angezeigt werden.
- Die Daten stammen aus derselben Tabelle; die Auswahlbedingungen sind so komplex, dass eine einzelne Abfrage nicht möglich, nicht sinnvoll oder zu unübersichtlich wäre.
Die Syntax einer solchen Verknüpfung sieht aus wie folgt, wobei auch mehr als zwei Abfragen verknüpft werden können:
SELECT <Spaltenliste1>
FROM <Tabellenliste1>
WHERE <Bedingungen1>
UNION [ DISTINCT | ALL ]
SELECT <Spaltenliste2>
FROM <Tabellenliste2>
WHERE <Bedingungen2>
Bei den einzelnen Abfragen können grundsätzlich alle Klauseln benutzt werden. Bitte beachten Sie folgende Bedingungen:
- Alle Einzelabfragen müssen in der Anzahl und Reihenfolge der Ergebnis-Spalten übereinstimmen. Die Datentypen müssen je nach DBMS und Version genau gleich sein oder zumindest so ähnlich, dass sie automatisch konvertiert werden können.
- Die Spaltennamen werden aus der ersten Spaltenliste übernommen, ggf. unter Berücksichtigung von Alias-Namen.
- Grundsätzlich werden doppelte Zeilen aus den Einzelabfragen unterdrückt, d. h. DISTINCT ist Standard und kann weggelassen werden. Wenn doppelte Zeilen angezeigt werden sollen, ist ALL anzugeben.
- Die Benutzung von Klammern sowie ORDER BY für das Gesamtergebnis wird je nach DBMS unterschiedlich geregelt.
Das folgende Beispiel ist eine einfache Zusammenfassung aller Fahrzeuge aus den Tabellen Fahrzeuge und Dienstwagen:
select ID, Kennzeichen, Farbe
from Dienstwagen
union
select ID, Kennzeichen, Farbe
from Fahrzeug;
Im folgenden Beispiel werden als Spalte Var Konstanten eingetragen, die die Herkunft der Daten angeben, und es werden verschiedene Auswahlbedingungen benutzt.
select 'D' as Var, ID, Kennzeichen, Farbe
from Dienstwagen
where Fahrzeugtyp_ID <= 3
union
select 'F', ID, Kennzeichen, Farbe
from Fahrzeug
where Kennzeichen LIKE 'B%';
Wenn die Ergebnismenge sortiert werden soll, werden je nach DBMS unterschiedliche Schreibweisen benötigt.
( select 'D' as Var, ID, Kennzeichen, Farbe
from Dienstwagen
where Fahrzeugtyp_ID <= 3 )
union
( select 'F', ID, Kennzeichen, Farbe
from Fahrzeug
where Fahrzeugtyp_ID <= 3 )
order by Kennzeichen;
select * from
( select 'D' as Var, ID, Kennzeichen, Farbe
from Dienstwagen
where Fahrzeugtyp_ID <= 3
union
select 'F', ID, Kennzeichen, Farbe
from Fahrzeug
where Fahrzeugtyp_ID <= 3 )
order by Kennzeichen;
Mit der folgenden Abfrage werden verschiedene Listen von Mitarbeitern zusammengefasst: zum einen diejenigen, die im September Geburtstag haben, zum anderen die Leiter mit einer Mobil-Rufnummer:
select Personalnummer, Name, Vorname, Geburtsdatum, Ist_Leiter as Leiter, Mobil
from Mitarbeiter
where EXTRACT(month from Geburtsdatum) = 9
union
select Personalnummer, Name, Vorname, Geburtsdatum, Ist_Leiter, Mobil
from Mitarbeiter
where Ist_Leiter = 'J' and Mobil <> '';
Offensichtlich kann diese Abfrage auch ohne UNION erreicht werden, nämlich durch: WHERE <bedingung1> OR ( <bedingung2> AND <bedingung3> ). Sie können sich aber sicherlich noch viel kompliziertere Situationen vorstellen, vor allem wenn die WHERE-Bedingungen auf Informationen in unterschiedlichen weiteren Tabellen zugreifen müssen.
Andere Varianten
Unter den gleichen Voraussetzungen wie UNION gibt es weitere Wege, um Einzelabfragen zusammenzufassen. Diese stehen aber nur vereinzelt zur Verfügung; wir beschränken uns deshalb auf Hinweise.
Mit INTERSECT (MS-SQL) erhält man den Durchschnitt der Teilabfragen.
SELECT <Spaltenliste1> FROM <Tabellenliste1> WHERE <Bedingungen1>
INTERSECT
SELECT <Spaltenliste2> FROM <Tabellenliste2> WHERE <Bedingungen2>
Die Ergebnismenge besteht aus allen Zeilen, die sowohl zum ersten SELECT-Befehl als auch zum zweiten SELECT-Befehl gehören.
Mit EXCEPT (MS-SQL) oder MINUS (Oracle) wird die Differenz der Teilabfragen zurückgegeben.
SELECT <Spaltenliste1> FROM <Tabellenliste1> WHERE <Bedingungen1>
EXCEPT | MINUS
SELECT <Spaltenliste2> FROM <Tabellenliste2> WHERE <Bedingungen2>
Die Ergebnismenge besteht aus allen Zeilen, die zum ersten SELECT-Befehl gehören, aber beim zweiten SELECT-Befehl nicht enthalten sind.
Wie bei UNION können auch solche Verknüpfungen durch sehr komplizierte JOINs und WHERE-Klauseln nachgebildet werden.
CASE WHEN – Fallunterscheidungen
Immer wieder möchte man innerhalb eines SQL-Befehls je nach Situation unterschiedliche Werte erhalten. Das einfachste Beispiel ist die Tabelle Mitarbeiter: Anstelle des Feldinhalts Ist_Leiter mit 'J' oder 'N' kann 'Leiter' oder eine leere Zeichenkette angezeigt werden. Dafür ist der CASE-Ausdruck vorgesehen, den es in zwei Varianten gibt.
Als <expression> können wahlweise konstante Werte oder komplexe Ausdrücke verwendet werden. Der CASE-Ausdruck ist nicht nur für SELECT, sondern auch für Speichern-Befehle geeignet und nicht nur (wie in den meisten Beispielen) als Teil der Spaltenliste, sondern auch in der WHERE-Klausel oder an anderen Stellen, an denen entsprechende Werte benötigt werden.
Der Wert, den der CASE-Ausdruck zurückgibt, erhält einen passenden Datentyp, der sich aus den Result-Werten ergibt.
Simple Case – die einfache Fallunterscheidung
Die einfache Variante hat folgende Syntax:
CASE <expression>
WHEN <expression1> THEN <result1>
[ WHEN <expression2> THEN <result2> ] /* usw. */
[ ELSE <default> ]
END
Bei dieser Version wird der Wert, der sich durch den ersten Ausdruck beim CASE ergibt, nacheinander mit den Ausdrücken nach WHEN verglichen. Sobald eine Gleichheit festgestellt wird, wird der Wert, der sich durch den Result-Ausdruck ergibt, als Wert für den CASE-Ausdruck übernommen. Wenn es keine Übereinstimmung gibt, wird der Wert des Default-Ausdrucks übernommen; wenn ELSE nicht vorhanden ist, wird NULL als Wert genommen. Beispiele:
Ein vorhandener Feldinhalt, nämlich 'J' oder 'N', wird für die Ausgabe durch andere Texte ersetzt:
select Personalnummer as Pers, Name, Vorname, Geburtsdatum as Geb,
CASE Ist_Leiter
WHEN 'J' THEN 'Leiter'
ELSE ''
END as Leiter,
Mobil
from Mitarbeiter;
AusgabePERS NAME VORNAME GEB LEITER MOBIL
--------- ------------ --------- ---------- ------ -----
40001 Langmann Matthias 28.03.1976 Leiter
40002 Peters Michael 15.11.1973
50001 Pohl Helmut 27.10.1980 Leiter (0171) 4123456
50002 Braun Christian 05.09.1966 (0170) 8351647
Dieselbe Lösung ohne ELSE-Zweig ändert nur die Anzeige:
select Personalnummer as Pers, Name, Vorname, Geburtsdatum as Geb,
CASE Ist_Leiter
WHEN 'J' THEN 'Leiter'
END as Leiter,
Mobil
from Mitarbeiter;
AusgabePERS NAME VORNAME GEB LEITER MOBIL
--------- ------------ --------- ---------- ------ -----
40001 Langmann Matthias 28.03.1976 Leiter
40002 Peters Michael 15.11.1973 <null>
50001 Pohl Helmut 27.10.1980 Leiter (0171) 4123456
50002 Braun Christian 05.09.1966 <null> (0170) 8351647
Bei der Art der Versicherungsverträge möchten wir die Varianten im „Klartext“ lesen:
select Vertragsnummer as Vertrag, Abschlussdatum as Datum,
CASE Art
WHEN 'VK' THEN 'Vollkasko'
WHEN 'TK' THEN 'Teilkasko'
WHEN 'HP' THEN 'Haftpflicht'
END
from Versicherungsvertrag;
AusgabeVERTRAG DATUM CASE
------- ---------- -----------
DB-04 25.01.2008 Haftpflicht
RH-01 11.12.1976 Vollkasko
RD-02 29.01.1988 Haftpflicht
RM-03 13.01.1996 Haftpflicht
RD-04 23.11.2006 Haftpflicht
RR-05 29.06.1990 Teilkasko
Searched Case – die komplexe Fallunterscheidung
Die komplexe Variante hat keinen Ausdruck hinter dem CASE; sie arbeitet nach folgender Syntax:
CASE
WHEN <bedingung1> THEN <result1>
[ WHEN <bedingung2> THEN <result2> ] /* usw. */
[ ELSE <default> ]
END
Bei dieser Variante werden nacheinander Bedingungen geprüft. Sobald eine Bedingung als WAHR festgestellt wird, wird der Wert, der sich durch den Result-Ausdruck ergibt, als Wert für den CASE-Ausdruck übernommen. Wenn es keine Übereinstimmung gibt, wird der Wert des Default-Ausdrucks übernommen; wenn ELSE nicht vorhanden ist, wird NULL als Wert genommen. Beispiele:
In einer einzigen Zeile wird angegeben, wieviele Versicherungsverträge innerhalb eines Jahrzehnts abgeschlossen wurden.
select
SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1970 and 1979
THEN 1
ELSE 0
END ) as S_197_,
SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1980 and 1989
THEN 1
ELSE 0
END ) as S_198_,
SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1990 and 1999
THEN 1
ELSE 0
END ) as S_199_,
SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) >= 2000
THEN 1
ELSE 0
END ) as S_200_
from Versicherungsvertrag;
AusgabeS_197_ S_198_ S_199_ S_200_
6 6 6 5
Dazu wird für jedes Jahrzehnt eine Spalte vorgesehen. Jede Spalte enthält einen CASE-Ausdruck mit einer WHEN-Bedingung. Wenn für eine Zeile diese Bedingung TRUE ergibt, wird 1 zur Summe dieses Jahrzehnts addiert, andernfalls 0.
Der CASE-Ausdruck soll innerhalb von ORDER BY eine unterschiedliche
Sortierung, abhängig von der Art der Versicherung, erreichen:
select ID, Art,
Abschlussdatum as Datum, Vertragsnummer as Vertr,
Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
from Versicherungsvertrag
order by Art,
CASE WHEN Art = 'TK' THEN ID
WHEN Art = 'VK' THEN Mitarbeiter_ID
WHEN Art = 'HP' THEN Fahrzeug_ID
END;
AusgabeID ART DATUM VERTR MIT FZG
------------------------------------
14 HP 15.03.1998 KG-03 9 16
18 HP 17.05.2000 HG-03 9 17
19 HP 21.09.2004 HB-04 9 19
10 HP 23.11.2006 RD-04 9 20
6 HP 25.01.2008 DB-04 9 21
15 HP 27.03.1988 KV-04 10 22
11 TK 29.06.1990 RR-05 9 23
12 TK 14.02.1978 KB-01 10 6
21 VK 20.06.1982 XH-02 9 8
23 VK 19.07.2002 XO-04 9 18
7 VK 11.12.1976 RH-01 10 5
22 VK 05.06.1992 XW-03 10 13
Hier wurden alle Abschnitte numerisch sortiert. Auch die Teilsortierung nach Datum funktioniert, muss aber unter Umständen überarbeitet werden:
Firebird-Versionselect ID, Art,
Abschlussdatum as Datum, Vertragsnummer as Vertr,
Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
from Versicherungsvertrag
order by Art,
CASE WHEN Art = 'TK' THEN ID
WHEN Art = 'VK' THEN Mitarbeiter_ID
WHEN Art = 'HP' THEN Abschlussdatum
END;
AusgabeInvalid token.
SQL error code = -104.
Datatypes are not comparable in expression CASE.
Auch wenn Firebird (wie im ersten Beispiel) Namen von Spalten akzeptiert, werden die Datentypen der Spalten vergleichen. Diese sind offensichtlich nicht kompatibel; also bricht Firebird diese Abfrage ab. Aber in der ORDER BY-Klausel können auch die Nummern der Spalten angegeben werden; dann klappt es:
Firebird-Versionselect ID, Art,
Abschlussdatum as Datum, Vertragsnummer as Vertr,
Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
from Versicherungsvertrag
order by Art,
CASE WHEN Art = 'TK' THEN ID
WHEN Art = 'VK' THEN Mitarbeiter_ID
WHEN Art = 'HP' THEN 2
END;
Bitte wundern Sie sich nicht über das identische Ergebnis wie oben bei der Sortierung nach Fahrzeug_ID: Die Fahrzeuge wurden in der gleichen Reihenfolge erfasst wie die Verträge; also stimmen in der ersten Zeit beide Sortierungen überein.
Die Sortierung kann nur einheitlich festgelegt werden, aber nicht mal so, mal so:
Art der Sortierung kann nicht gemischt werden.select ID, Art,
Abschlussdatum as Datum, Vertragsnummer as Vertr,
Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
from Versicherungsvertrag
order by Art,
CASE
WHEN Art = 'TK' THEN ID ASC
WHEN Art = 'VK' THEN Mitarbeiter_ID ASC
WHEN Art = 'HP' THEN 2 DESC
END;
AusgabeInvalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 7, column 39. ASC.
Einheitliche Sortierung DESC sollte möglich seinselect ID, Art,
Abschlussdatum as Datum, Vertragsnummer as Vertr,
Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
from Versicherungsvertrag
order by Art,
CASE
WHEN Art = 'TK' THEN ID
WHEN Art = 'VK' THEN Mitarbeiter_ID
WHEN Art = 'HP' THEN 2
END DESC;
Zumindest unter Firebird gelingt dennoch keine korrekte Sortierung: nur der Abschnitt 'TK' wird absteigend sortiert, die beiden anderen nicht. Sie müssen aber sowieso selbst ausprobieren, was in welchem DBMS möglich ist und was nicht.
CASE-Ausdruck beim Speichern
Wie beim SELECT-Befehl kann ein CASE-Ausdruck auch beim Speichern an verschiedenen Stellen benutzt werden, vor allem bei den Zuweisungen der Werte für INSERT und UPDATE sowie bei der WHERE-Klausel für UPDATE und DELETE. Da sich diese Kapitel vor allem mit Abfragen beschäftigen, beschränken wir uns auf ein Beispiel.
Die Zuständigkeit der Mitarbeiter für die Versicherungsverträge wird neu geregelt:
- Vollkasko-Verträge gehören zum Aufgabenbereich des Abteilungsleiters (Mitarbeiter-ID 9).
- Teilkasko-Verträge gehören zum Aufgabenbereich des Mitarbeiters mit ID 12.
- Die Haftpflicht-Verträge werden in Abhängigkeit von der ID aufgeteilt auf die Mitarbeiter 10 und 11.
update Versicherungsvertrag
set Mitarbeiter_id =
CASE WHEN Art = 'VK' THEN 9
WHEN Art = 'TK' THEN 12
WHEN Art = 'HP' THEN 10 + MOD(ID, 2)
END;
Es wird also eine normale SET-Anweisung geschrieben. Die CASE-Anweisung liefert die benötigten Werte, wobei für den Fall „Haftpflicht“ für gerade IDs der Wert (10+0) und für ungerade IDs der Wert (10+1) gesetzt wird. Eine WHERE-Klausel ist nicht erforderlich, weil alle Verträge neu zugeordnet werden sollen.
Zusammenfassung
In diesem Kapitel lernten Sie einige nützliche Erweiterungen vor allem für Abfragen kennen:
- Auf den DISTINCT-Parameter zur Auswahl wird genauer eingegangen.
- Je nach DBMS wird unterschiedlich geregelt, wenn nur eine gewisse Anzahl von Zeilen gewünscht wird.
- Mit UNION wird das Ergebnis von zwei oder mehr Abfragen in einer gemeinsamen Ergebnistabelle zusammengefasst.
- Mit CASE WHEN sind Fallunterscheidungen für gewisse Werte möglich; dies ist auch beim Speichern von Datensätzen sowohl für die Werte als auch für die WHERE-Klausel hilfreich.
Übungen
| Übung 1 | Richtig oder falsch? | Zur Lösung |
Welche der folgenden Aussagen sind richtig, welche sind falsch?
- Es ist Standard, den Parameter ALL bei einem SELECT-Befehl anzugeben.
- Bei SELECT DISTINCT werden nur solche Zeilen angezeigt, die sich in mindestens einer Spalte unterscheiden.
- Es ist äußerst selten erforderlich, das Ergebnis einer Abfrage nur in Teilen zu holen.
- Für die Anzeige oder Verwendung einer ROW_NUMBER hat der SQL-Standard (2003) ein Verfahren vorgeschrieben.
- Ein solches Verfahren wird von den meisten DBMS verwendet, ist aber in unterschiedlicher Weise verwirklicht.
- Bei UNION, INTERSECT usw. müssen die ausgewählten Spalten von der Anzahl her übereinstimmen.
- Bei UNION, INTERSECT usw. müssen die ausgewählten Spalten vom Datentyp her genau übereinstimmen.
| Übung 2 | DISTINCT und ALL | Zur Lösung |
Bitte überprüfen Sie die folgenden Befehle:
-- Variante 1
select distinct COUNT(*)
from Mitarbeiter
group by Abteilung_ID
-- Variante 2
select all COUNT(*)
from Mitarbeiter
group by Abteilung_ID
Worin unterscheidet sich die Ausgabe? Welche wichtige Information fehlt vor allem bei Variante 2?
| Übung 3 | UNION, INTERSECT usw. | Zur Lösung |
Was ist an den folgenden Befehlen falsch oder fragwürdig? Wie kann man das DBMS dazu bringen, den „fragwürdigen“ Befehl auf jeden Fall auszuführen?
-- Befehl 1
select Name, Vorname from Mitarbeiter
union
select Name, Vorname, Geburtsdatum from Versicherungsnehmer
-- Befehl 2
select Name, Vorname, Abteilung_ID from Mitarbeiter
union
select Name, Vorname, Geburtsdatum from Versicherungsnehmer
| Übung 4 | UNION, INTERSECT usw. | Zur Lösung |
Erstellen Sie eine Abfrage für die Tabellen Versicherungsvertrag, Fahrzeug, Zuordnung_SF-FZ, in der folgende Bedingungen berücksichtigt werden:
- Es sollen Vertragsnummer, Abschlussdatum, Kennzeichen sowie ggf. anteilige Schadenshöhe angezeigt werden.
- Fahrzeuge mit einem Schaden sollen vollständig angezeigt werden.
- Fahrzeuge ohne Schaden sollen nur angezeigt werden, wenn der Vertrag vor 1990 abgeschlossen wurde.
- Das Ergebnis soll nach Schadenshöhe und Kennzeichen sortiert werden.
Benutzen Sie UNION zur Verknüpfung der Verträge mit und ohne Schaden.
| Übung 5 | Fallunterscheidung für Nachschlagewerte | Zur Lösung |
Schreiben Sie einen SELECT-Befehl, bei dem die korrekte Briefanrede für die Mitarbeiter erstellt wird.
Hinweis: Benutzen Sie CONCAT zum Verknüpfen mehrerer Zeichenketten.
| Übung 6 | Fallunterscheidung für Bereiche | Zur Lösung |
Zur Tabelle Fahrzeug soll aus dem Kennzeichen die regionale Herkunft abgeleitet und angezeigt werden. Schreiben Sie eine Abfrage für diese Spalten Kennzeichen und Herkunft.
Hinweis: Benutzen Sie POSITION zur Feststellung des Bindestrichs sowie SUBSTRING.
Zusatzfrage: Wie müsste ein solches Problem sinnvollerweise gelöst werden, falls eine solche Zuordnung häufiger und allgemeiner benötigt wird?
| Übung 7 | Fallunterscheidung für mehrere Varianten | Zur Lösung |
Aus der Tabelle Versicherungsnehmer sollen Name, Vorname und Anschrift angezeigt werden. Außerdem soll jede Adresse eine Markierung bekommen: F = (eigene) Firmenkunden, P = (eigene) Privatkunden, X = eXterne Verträge (d. h. Kunden fremder Versicherungsgesellschaften). Begründen Sie die Reihenfolge der WHEN-ELSE-Bedingungen.
| Übung 8 | Fallunterscheidung beim Speichern | Zur Lösung |
Schreiben Sie einen UPDATE-Befehl, der nach Ablauf eines Versicherungsjahres den Prämiensatz für das nächste Jahr ändert. Berücksichtigen Sie dabei folgende Bedingungen:
- Neue Verträge, für die noch kein Prämiensatz gilt, werden auf 200 [Prozent] gesetzt.
- Verträge mit einem Prämiensatz von mindestens 100 werden um 20 reduziert.
- Verträge mit einem Prämiensatz von weniger als 100 werden um 10 reduziert.
- Der Mindestsatz von 30 darf nicht unterschritten werden.
Ignorieren Sie dabei zunächst, dass dies nur im Fall von Schadensfreiheit gelten darf und innerhalb eines Jahres nur einmal neu berechnet werden darf.
| Übung 9 | Fallunterscheidung beim Speichern | Zur Lösung |
Bei genauerer Untersuchung von Übung 8 sind weitere Bedingungen im UPDATE-Befehl erforderlich.
- In welcher Weise könnte die Schadensfreiheit berücksichtigt werden? Abhängig von der Höhe des Schadens soll sich der Prämiensatz gar nicht, wenig oder mehr erhöhen.
- Wie kann unter Verwendung des Datums Prämienänderung gesichert werden, dass die Neuberechnung nur einmal jährlich stattfinden darf?
Versuchen Sie, dies im selben UPDATE-Befehl zu berücksichtigen. Sie sollen keinen Befehl schreiben, sondern die notwendigen Klauseln erwähnen und erläutern.
Lösungen
| Lösung zu Übung 1 | Richtig oder falsch? | Zur Übung |
Die Aussagen 2, 5, 6 sind richtig. Die Aussagen 1, 3, 4, 7 sind falsch.
| Lösung zu Übung 2 | DISTINCT und ALL | Zur Übung |
Variante 1 nennt jede Anzahl von Mitarbeitern pro Abteilung genau einmal. Bei Variante 2 gibt es diese Anzeige für jede Abteilung einzeln. Dabei fehlt vor allem die Angabe der Abteilung_ID, ohne die die Ausgabe ziemlich sinnlos ist.
| Lösung zu Übung 3 | UNION, INTERSECT usw. | Zur Übung |
Bei Befehl 1 werden unterschiedlich viele Spalten ausgewählt, das ist unzulässig.
Bei Befehl 2 unterscheiden sich die Datentypen der dritten Spalte; es ist unsicher, ob das DBMS die unterschiedlichen Spalten einheitlich als Zeichenkette (zulässig) oder nicht-kompatibel (unzulässig) interpretieren will.
Ein CAST beider Spalten auf VARCHAR macht die Datentypen kompatibel.
| Lösung zu Übung 4 | UNION, INTERSECT usw. | Zur Übung |
Eine mögliche Variante lautet so (Firebird-Version für ORDER BY):
select * from (
select Vertragsnummer, Abschlussdatum, Kennzeichen, Schadenshoehe
from Versicherungsvertrag vv
inner join Fahrzeug fz on fz.ID = vv.Fahrzeug_ID
right join Zuordnung_SF_FZ zu on fz.ID = zu.Fahrzeug_ID
union
select Vertragsnummer, Abschlussdatum, Kennzeichen, 0
from Versicherungsvertrag vv
inner join Fahrzeug fz on fz.ID = vv.Fahrzeug_ID
where Abschlussdatum < '01.01.1990' and not
EXISTS ( select ID from Zuordnung_SF_FZ zu where zu.Fahrzeug_ID = fz.ID )
) order by Schadenshoehe, Kennzeichen;
Der zweite SELECT kann auch so geschrieben werden:
select Vertragsnummer, Abschlussdatum, Kennzeichen, 0
from Versicherungsvertrag vv
inner join Fahrzeug fz on fz.ID = vv.Fahrzeug_ID
left join Zuordnung_SF_FZ zu on fz.ID = zu.Fahrzeug_ID
where Abschlussdatum < '01.01.1990' and zu.ID is null
| Lösung zu Übung 5 | Fallunterscheidung für Nachschlagewerte | Zur Übung |
select CONCAT( 'Sehr geehrte',
CASE Geschlecht
WHEN 'M' THEN 'r Herr '
WHEN 'W' THEN ' Frau '
ELSE '/r Frau/Herr '
END,
Name ) as Anrede
from Mitarbeiter;
Hinweise: CONCAT oder die String-Verknüpfung erzeugen u. U. eine falsche Länge des ersten Teils. Das kann mit TRIM „repariert“ werden; dann ist aber das abschließende Leerzeichen hinzuzufügen. Der ELSE-Zweig ist überflüssig, weil Geschlecht nicht NULL sein kann; er wird nur der Vollständigkeit halber genannt.
| Lösung zu Übung 6 | Fallunterscheidung für Bereiche | Zur Übung |
select Kennzeichen,
CASE SUBSTRING(Kennzeichen from 1 for POSITION('-', Kennzeichen)-1 )
WHEN 'RE' THEN 'Kreis Recklinghausen'
WHEN 'GE' THEN 'Stadt Gelsenkirchen'
WHEN 'E' THEN 'Stadt Essen'
WHEN 'BO' THEN 'Stadt Bochum'
WHEN 'HER' THEN 'Stadt Herne'
ELSE 'unbekannt'
END as Herkunft
from Fahrzeug
order by 2, 1;
Zur Zusatzfrage: Eine Tabelle Region o. ä. würde mit LEFT JOIN über den o. g. SUBSTRING verknüpft. Auch ein INNER JOIN wäre möglich, dann würden aber Fahrzeuge fehlen, deren Kennzeichen in der Tabelle Region fehlen.
| Lösung zu Übung 7 | Fallunterscheidung für mehrere Varianten | Zur Übung |
select Name, Vorname, PLZ, Strasse, Hausnummer,
CASE
WHEN Eigener_Kunde = 'N' THEN 'X'
WHEN Vorname is null or Fuehrerschein is null THEN 'F'
ELSE 'P'
END as Markierung
from Versicherungsnehmer;
Zur Reihenfolge: Bei den Fremdverträgen wird nicht zwischen Firmen- und Privatkunden unterschieden; dies muss also zuerst geprüft werden. Bei Firmenkunden sind weder Vorname noch Führerschein-Datum registriert, das wird als nächstes geprüft (eines der WHEN-Kriterien würde genügen). Alle übrigen Fälle sind Privatkunden.
| Lösung zu Übung 8 | Fallunterscheidung beim Speichern | Zur Übung |
update Versicherungsvertrag
set Praemiensatz = CASE
WHEN Praemiensatz is null THEN 200
WHEN Praemiensatz >= 100 THEN Praemiensatz - 20
WHEN Praemiensatz >= 40 THEN Praemiensatz - 10
ELSE 30
END
where -- passende Bedingungen
| Lösung zu Übung 9 | Fallunterscheidung beim Speichern | Zur Übung |
- Es wird eine geschachtelte CASE-Anweisung verwendet. Zunächst wird unterschieden, ob überhaupt Schadensfälle aufgetreten sind. Im ersten Zweig „kein Schaden“ wird die Lösung von Übung 7 benutzt. Im zweiten Zweig „mit Schaden“ wird eine ähnliche CASE-Anweisung eingefügt, die abhängig von der Schadenshöhe den Prämiensatz erhöht.
- In der WHERE-Klausel wird geprüft, ob die letzte Prämienänderung überschritten ist. Gleichzeitig mit der Neuberechnung wird dieses Datum um ein Jahr weitergesetzt.
Hinweis: Dieses Verfahren wird ausführlich im Kapitel Prozeduren: Automatisches UPDATE behandelt. Bei Interesse können Sie im dortigen Code den abschließenden UPDATE-Befehl vergleichen.
| Berechnete Spalten |
Mit Abfragen kann man nicht nur Spalten auswählen, sondern auch völlig neue Spalten aus anderen Spalten oder mit Funktionen erstellen.
Allgemeine Regel
Eine neue Spalte wird als Teil einer Abfrage wie folgt erstellt:
<Ausdruck> [AS] <Name der neuen Spalte>
Ausdruck ist allgemein der Hinweis auf etwas, das an der betreffenden Stelle verwendet wird: ein konstanter Wert, der Inhalt einer oder mehrerer Spalten, eine Berechnung mit diesen Spalten, das Ergebnis einer Funktion oder das Ergebnis einer Unterabfrage. Der Spalte, die das Ergebnis aufnimmt, wird mit AS ein eigener Name, ein Alias zugewiesen; das AS kann auch entfallen.
In manchen DBMS ist bei berechneten Spalten die Angabe eines Alias Pflicht, und das Vergessen führt zu einem Fehler, manche erzeugen automatisch einen zufälligen Namen, wenn kein Alias eingegeben wird, und andere meckern erst, wenn man versucht, eine physische Tabelle mit einer namenlosen Spalte zu füllen.
Wenn eine Unterabfrage als berechnete Spalte benutzt wird, ist es unbedingt wichtig, dass sie – wie bei einer Funktion, Berechnung oder Verknüpfung – für jede Zeile der Hauptabfrage genau einen Wert ergibt. In dem untenstehenden Beispiel wird dies durch eine passende WHERE-Bedingung erreicht.
Hinweis: Fast alles, was in diesem Abschnitt über Ausdrücke, Funktionen und Konvertierungen gesagt wird, ist auch bei der Manipulation oder Verarbeitung von Daten wichtig. Die Funktionen und Konvertierungen wurden bereits in den Kapiteln Funktionen und Funktionen (2) behandelt.
Ergebnis von Berechnungen
Alle Werte einer Spalte werden für eine Berechnung verwendet, vorzugsweise eine der Grundrechenarten. Das folgende Beispiel errechnet für alle Zeilen der Tabelle Schadensfall aus dem Feld Schadenshoehe den Nettobetrag (ohne MWSt) und fügt alle weiteren Felder an:
SELECT Schadenshoehe / 1.19 AS Netto,
*
FROM Schadensfall;
Dieses Ergebnis kann auch für weitere Berechnungen verwendet werden, wenn auch nicht direkt mit dem Alias:
Der Alias-Name der neuen Spalte Netto kann nicht nochmals verwendet werden.SELECT Schadenshoehe AS Brutto,
Schadenshoehe / 1.19 AS Netto,
Schadenshoehe - Netto AS MWSt
FROM Schadensfall;
Aber die Berechnung kann erneut zugewiesen werden.SELECT Schadenshoehe AS Brutto,
Schadenshoehe / 1.19 AS Netto,
Schadenshoehe - (Schadenshoehe / 1.19) AS MWSt
FROM Schadensfall;
Man kann davon ausgehen, dass das DBMS die Abfrage soweit optimiert, dass die Berechnung tatsächlich nur einmal ausgeführt werden muss.
Zeichenketten verbinden und bearbeiten
Ebenso wie Zahlen für Berechnungen verwendet werden, gibt es die Bearbeitung von Zeichenketten. Das einfachste ist die Verknüpfung (Verkettung) von Strings durch den Operator || bzw. +:
SELECT Name || ', ' || Vorname AS Gesamtname
FROM Mitarbeiter;
AusgabeGesamtname
Müller, Kurt
Schneider, Daniela
In diesem Beispiel wird zwischen die Spalten noch das Komma und ein Leerzeichen als konstanter Text gesetzt.
Bei Textspalten mit fester Feldlänge, die wir in der Beispieldatenbank nicht haben, sollten überzählige Leerzeichen mit der Funktion TRIM abgeschnitten werden:
SELECT TRIM(Name) || ', ' || TRIM(Vorname) AS Gesamtname
FROM Mitarbeiter;
Auf diese Weise kann man auch Teile von Texten übernehmen und den Rest abschneiden:
SELECT Name || ', ' || TRIM( SUBSTRING(Vorname FROM 1 FOR 1) ) || '.' AS Name_kurz
FROM Mitarbeiter;
AusgabeName_kurz
Müller, K.
Schneider, D.
Hinweis: Nach SQL-Standard liefert das Ergebnis von SUBSTRING einen Text von gleicher Länge wie der ursprüngliche Text; die jetzt zwangsläufig folgenden Leerzeichen müssen mit TRIM entfernt werden. Vielleicht ist Ihr DBMS „pfiffiger“ und macht das automatisch.
Ergebnis von Funktionen
In den bisherigen Beispielen werden Feldinhalte direkt modifiziert und ausgegeben. In den beiden Kapiteln über „Funktionen“ werden neue Informationen berechnet und unabhängig von vorhandenen Zeilen und Spalten ausgegeben:
SELECT COUNT(Farbe) AS Anzahl_Farbe
FROM Fahrzeug;
Bei diesem Beispiel mit einer Aggregatfunktion besteht das Ergebnis aus einer einzelnen Zahl in einer einzigen Zeile. Dies muss aber nicht so sein; vor allem mit Gruppierungen gibt es viele Varianten.
Viele andere Funktionen können besondere Abfragen ermöglichen oder stark erleichtern. EXTRACT nach Monat liefert die Liste aller Geburtstage, sortiert nach Monat:
SELECT Name, Vorname,
EXTRACT(MONTH FROM Geburtsdatum) AS Monat,
Geburtsdatum
FROM Mitarbeiter
ORDER BY Monat;
AusgabeNAME VORNAME MONAT GEBURTSDATUM
-------------- --------- ----- ------------
Aagenau Karolin 1 02.01.1950
Langer Norbert 1 13.01.1968
Wagner Gaby 1 18.01.1970
Müller Kurt 1 05.01.1977
Kolic Ivana 2 14.02.1971
Schneider Daniela 2 16.02.1980 // usw.
Unterabfragen
Diese werden ausführlich im Kapitel Unterabfragen besprochen. Hier soll deshalb ein Beispiel genügen, wie es für eine berechnete Spalte geeignet ist.
Gesucht sind alle Abteilungen zusammen mit der Anzahl ihrer Mitarbeiter:
SELECT ab.ID,
ab.Kuerzel,
ab.Ort,
( SELECT COUNT(*)
FROM Mitarbeiter mi
WHERE mi.Abteilung_ID = ab.ID
) AS Mitarbeiterzahl
FROM Abteilung ab;
Von innen nach außen heißt das: In den Klammern gehört zu jeder ab.ID die Anzahl der Einträge in Mitarbeiter mit dieser Abteilung; dies wird mit den anderen Spalten von Abteilung zusammengefasst. Weil es ID in beiden Tabellen gibt, muss die gewünschte Tabelle ausdrücklich erwähnt werden.
Zusammenfassung
In diesem Kapitel bekamen wir verschiedene Erläuterungen dafür, wie aus Berechnungen, Verkettung von Zeichenketten oder als Ergebnis von Skalar- oder Spaltenfunktionen neue Spalten für die Ergebnistabelle entstehen.
Übungen
| Übung 1 | Zusatzspalten durch Berechnung | Zur Lösung |
Zur Tabelle Versicherungsvertrag sollen Versicherungsnummer, Basisprämie und Prämiensatz angegeben sowie die aktuelle Prämie berechnet werden.
| Übung 2 | Zusatzspalten durch Berechnung | Zur Lösung |
Geben Sie (unter Verwendung der Lösung von Übung 1) die Gesamtzahl der Versicherungsverträge sowie den Gesamtbetrag aller aktuellen Prämien an.
| Übung 3 | Zusatzspalten durch String-Verknüpfung | Zur Lösung |
Erstellen Sie zur Tabelle Versicherungsnehmer per Abfrage die Druckanschrift:
- Zeile 1 mit Anrede (22 Zeichen, basierend auf der Spalte Geschlecht mit den Inhalten 'W' bzw. 'M') und der ID am rechten Rand (8 Zeichen rechtsbündig)
- Zeile 2 mit Vorname und Name
- Zeile 3 mit Straße und Hausnummer
- Zeile 4 mit PLZ und Ort
Benutzen Sie CASE, CAST, RPAD und CONCAT; auf TRIM können Sie verzichten.
| Übung 4 | Neue Spalten durch Spaltenfunktion | Zur Lösung |
Bestimmen Sie, wie viele Fahrzeuge in Bochum ('BO') und wie viele in Gelsenkirchen ('GE') angemeldet sind.
Gruppierungen werden erst im nächsten Kapitel behandelt; verwenden Sie stattdessen UNION.
| Lösung zu Übung 1 | Zusatzspalten durch Berechnung | Zur Übung |
select Vertragsnummer,
Basispraemie,
Praemiensatz,
Basispraemie * Praemiensatz / 100 as Aktuell
from Versicherungsvertrag;
| Lösung zu Übung 2 | Zusatzspalten durch Berechnung | Zur Übung |
select COUNT(*) as Gesamtzahl,
SUM(Basispraemie * Praemiensatz / 100) as Praemiensumme
from Versicherungsvertrag;
| Lösung zu Übung 3 | Zusatzspalten durch String-Verknüpfung | Zur Übung |
select CONCAT( CAST( ( CASE Geschlecht
WHEN 'M' THEN 'Herrn'
WHEN 'W' THEN 'Frau'
ELSE ''
END ) AS CHAR(22) ),
RPAD( CAST( ID as VARCHAR(8) ), 8 )
) as Zeile1,
CASE
WHEN Vorname is null THEN Name
ELSE CONCAT(Vorname, ' ', Name)
END as Zeile2,
CONCAT(Strasse, ' ', Hausnummer) as Zeile3,
CONCAT(PLZ, ' ', Ort) as Zeile4
from Versicherungsnehmer vn;
| Lösung zu Übung 4 | Neue Spalten durch Spaltenfunktion | Zur Übung |
select COUNT(*) as Anzahl, 'BO' as Kreis
from Fahrzeug
where Kennzeichen STARTING WITH 'BO-'
union
select COUNT(*), 'GE'
from Fahrzeug
where Kennzeichen STARTING WITH 'GE-';
Für STARTING WITH gibt es Alternativen; wir haben verschiedentlich SUBSTRING mit POSITION o. a. verwendet.
| Gruppierungen |
Abfragen werden sehr häufig gruppiert, weil nicht nur einzelne Informationen, sondern auch Zusammenfassungen gewünscht werden. Durch die GROUP BY-Klausel im SELECT-Befehl werden alle Zeilen, die in einer oder mehreren Spalten den gleichen Wert enthalten, in jeweils einer Gruppe zusammengefasst.
Dies ist in der Regel nur dann sinnvoll, wenn in der Spaltenliste des SELECT-Befehls eine gruppenweise Auswertung, also eine der Spaltenfunktionen enthalten ist.
Syntax von GROUP BY
Die GROUP BY-Klausel hat folgenden allgemeinen Aufbau:
GROUP BY <Spaltenliste>
Die Spaltenliste enthält, durch Komma getrennt, die Namen von einer oder mehreren Spalten. Für jede Spalte kann eine eigene Sortierung angegeben werden:
<Spaltenname> -- oder <Spaltenname> COLLATE <Collation-Name>
Die Spalten in der Spaltenliste können meistens wahlweise mit dem Spaltennamen der Tabelle, mit dem Alias-Namen aus der Select-Liste oder mit Spaltennummer gemäß der Select-Liste (ab 1 gezählt) angegeben werden.
In der Regel enthält die Abfrage eine der Aggregatfunktionen und wird durch ORDER BY nach den gleichen Spalten sortiert.
Gruppierung bei einer Tabelle
Im einfachsten Fall werden Daten nach einer Spalte gruppiert und gezählt.
Im folgenden Beispiel wird die Anzahl der Abteilungen für jeden Ort aufgeführt.
SELECT Ort,
COUNT(*) AS Anzahl
FROM Abteilung
GROUP BY Ort
ORDER BY Ort;
AusgabeORT ANZAHL
--- ------
Bochum 3
Dortmund 4
Essen 4
Herne 1
Die folgende Abfrage listet auf, wie viele Mitarbeiter es in den Abteilungen und Raumnummern gibt:
SELECT Abteilung_ID AS Abt,
Raum,
COUNT(*) AS Anzahl
FROM Mitarbeiter
GROUP BY Abt, Raum
ORDER BY Abt, Raum;
AusgabeABT RAUM ANZAHL
--- ---- ------
1 112 1
1 113 1
2 212 2
3 312 1
3 316 1
4 412 2 // usw.
Am folgenden Beispiel wird die Gruppierung besonders deutlich.
Berechne die mittlere Schadenshöhe für die Schadensfälle mit und ohne Personenschäden.
SELECT Verletzte,
AVG(Schadenshoehe) AS Mittlere_Schadenshoehe
FROM Schadensfall
GROUP BY Verletzte;
AusgabeVERLETZTE MITTLERE_SCHADENSHOEHE
J 3.903,87
N 1.517,45
Die Spalte Verletzte enthält entweder 'J' oder 'N' und ist verpflichtend, kann also keine NULL-Werte enthalten. Deshalb werden durch die GROUP BY-Anweisung eine oder zwei Gruppen gebildet. Für jede Gruppe wird der Mittelwert gesondert berechnet aus den Werten, die in der Gruppe vorkommen. In diesem Fall liefert die Funktion AVG also ein oder zwei Ergebnisse, abhängig davon, welche Werte in der Spalte Verletzte überhaupt vorkommen.
Zeilen, bei denen einer der Werte zur Gruppierung fehlt, oder Zeilen mit NULL-Werten werden als eigene Gruppe gezählt.
Gruppierung über mehrere Tabellen
Eine Gruppierung kann auch Felder aus verschiedenen Tabellen auswerten. Dafür sind zunächst die Voraussetzungen für die Verknüpfung mehrerer Tabellen zu beachten. Bitte beachten Sie das folgende Beispiel.
Gesucht wird für jeden Fahrzeughersteller (mit Angabe von ID und Name) und Jahr die Summe der Schadenshöhe aus der Tabelle Schadensfall.
SELECT fh.ID AS Hersteller_ID,
fh.Name AS Hersteller_Name,
EXTRACT(YEAR FROM sf.Datum) AS Jahr,
SUM(sf.Schadenshoehe) AS Schadenssumme
FROM Schadensfall sf
JOIN Zuordnung_SF_FZ zu ON sf.ID = zu.Schadensfall_ID
JOIN Fahrzeug fz ON fz.ID = zu.Fahrzeug_ID
JOIN Fahrzeugtyp ft ON ft.ID = fz.Fahrzeugtyp_ID
JOIN Fahrzeughersteller fh ON fh.ID = ft.Hersteller_ID
GROUP BY Hersteller_ID, Hersteller_Name, Jahr
ORDER BY Jahr, Hersteller_ID;
AusgabeHERSTELLER_ID HERSTELLER_NAME JAHR SCHADENSSUMME
------------- --------------- ---- -------------
9 Volvo 2007 2.066,00
10 Renault 2007 5.781,60
11 Seat 2007 1.234,50
2 Opel 2008 1.438,75
11 Seat 2008 1.983,00
9 Volvo 2009 4.092,15
10 Renault 2009 865,00
Ausgangspunkt ist die Tabelle Schadensfall, weil aus deren Einträgen die Summe gebildet werden soll. Durch JOIN werden nacheinander die verknüpften Tabellen herangezogen, und zwar jeweils durch die ID auf die Verknüpfung: Schadensfall → Zuordnung → Fahrzeug → Fahrzeugtyp → Hersteller. Dann stehen ID und Name aus der Tabelle Fahrzeughersteller zur Verfügung, die für die Gruppierung gewünscht werden.
Zur Gruppierung genügt eigentlich die Verwendung von Hersteller_ID. Aber man möchte sich natürlich den Herstellernamen anzeigen lassen. Allerdings gibt es einen Fehler, wenn man den Namen nur in der SELECT-Liste benutzt und in der GROUP BY-Liste streicht:
FehlerSELECT fh.ID AS Hersteller_ID,
fh.Name AS Hersteller_Name,
EXTRACT(YEAR FROM sf.Datum) AS Jahr,
SUM(sf.Schadenshoehe) AS Schadenssumme
FROM Schadensfall sf
join ... (wie oben)
group by Hersteller_ID, Jahr /* oder Variante 2: */
group by Hersteller_ID, EXTRACT(YEAR FROM sf.Datum)
order by Jahr, Hersteller_ID
AusgabeUngültiger Ausdruck in der Select-Liste (fehlt entweder in einer Aggregatfunktion oder in der GROUP BY-Klausel).
Siehe dazu die Erläuterungen unter Einschränkungen.
Einschränkungen
Wie das letzte Beispiel zeigt, muss die GROUP BY-Klausel gewisse Bedingungen erfüllen. Auch dafür gilt: Jedes DBMS weicht an manchen Punkten vom Standard ab.
- Jeder Spaltenname der SELECT-Auswahl, der nicht zu einer Aggregatfunktion gehört, muss auch in der GROUP BY-Klausel benutzt werden.
Diese Bedingung wird im letzten Beispiel verletzt: Hersteller_Name steht in der SELECT-Liste, aber nicht in der GROUP BY-Klausel. In diesem Fall ist eine Änderung einfach, weil ID und Name des Herstellers gleichwertig sind. Übrigens erlaubt MySQL auch die Auswahl von Feldern, die in der GROUP BY-Klausel nicht genannt sind.
Umgekehrt ist es in der Regel möglich, eine Spalte per GROUP BY zu gruppieren, ohne die Spalte selbst in der SELECT-Liste zu verwenden.
- GROUP BY kann Spalten der Tabellen, abgeleiteter Tabellen oder VIEWs in der FROM-Klausel oder der JOIN-Klausel enthalten.
- Sie kann keiner Spalte entsprechen, die das Ergebnis einer Funktion (genauer: einer numerischen Berechnung, einer Aggregatfunktion oder einer benutzerdefinierten Funktion) sind. Notfalls kann die Funktion in der GROUP-Klausel wiederholt werden (siehe im letzten Beispiel Variante 2).
Dies entspricht der gleichen Einschränkung, die bei den unter „Ergebnis von Berechnungen“ im Kapitel Berechnete Spalten genannt ist.
Mit der Beispieldatenbank sind keine passenden Beispiele möglich; wir müssen uns deshalb auf fiktive Tabellen und Spalten beschränken:
Die folgenden Abfragen sind nicht zulässig.SELECT Spalte1, Spalte2 FROM T GROUP BY Spalte1 + Spalte2;
SELECT Spalte1 + constant + Spalte2 FROM T GROUP BY Spalte1 + Spalte2;
Die folgenden Abfragen sind zulässig.SELECT Spalte1, Spalte2 FROM T GROUP BY Spalte1, Spalte2;
SELECT Spalte1 + Spalte2 FROM T GROUP BY Spalte1, Spalte2;
SELECT Spalte1 + Spalte2 FROM T GROUP BY Spalte1+ Spalte2;
SELECT Spalte1 + Spalte2 + constant FROM T GROUP BY Spalte1, Spalte2;
- GROUP BY kann nicht benutzt werden in einem SELECT-Befehl mit folgenden Bedingungen:
- Der Befehl enthält eine INTO-Klausel (d. h. er wird benutzt, um einzelne Zeilen zu verarbeiten – dies wird in dieser Einführung nicht erläutert).
- Der Befehl enthält eine Unterabfrage oder eine VIEW, die selbst mit einer GROUP BY- oder HAVING-Klausel arbeitet.
- Jeder SELECT-Befehl darf einschließlich aller Unterabfragen höchstens eine GROUP BY-Klausel enthalten.
Zu dieser Bedingung ist den Autoren kein vernünftiges Beispiel eingefallen. Eines, das dafür konstruiert wurde, hat Firebird trotz klarer Verletzung ausgeführt, wahrscheinlich weil es sich wegen der anderen Bedingungen sowieso nur um jeweils eine Zeile handelte und keine Gruppierung erforderlich war.
Erweiterungen
Zwischensummen mit CUBE
Diese Erweiterung steht nur in manchen DBMS zur Verfügung. Es soll deshalb ein kurzer Hinweis genügen.
GROUP BY CUBE (<spaltenliste>)
Mit diesem Befehl werden zusätzlich zu den normalerweise von GROUP BY erstellten Zeilen auch Zwischensummen in das Ergebnis aufgenommen. Für jede mögliche Kombination von Spalten in der <spaltenliste> wird eine eigene GROUP BY-Summenzeile zurückgegeben.
Erläuterungen und Beispiele sind zu finden z. B. unter GROUP BY (Microsoft) und weiteren Links.
Gesamtsummen mit ROLLUP
Auch diese Erweiterung steht nur in manchen DBMS zur Verfügung. Es soll wiederum ein kurzer Hinweis genügen.
GROUP BY <spaltenliste> WITH ROLLUP
Mit diesem Befehl werden zusätzlich zu den von GROUP BY erstellten Zeilen auch Gesamtsummen in das Ergebnis aufgenommen.
Erläuterungen und Beispiele sind zu finden z. B. unter GROUP BY (MySQL, englisch).
Ergebnis mit HAVING einschränken
Diese Erweiterung ist eine selbständige Klausel des SELECT-Befehls und hat eigentlich nichts mit der GROUP BY-Klausel zu tun. Sie ist eine Ergänzung zu den Aggregatfunktionen, wird deshalb überwiegend bei Gruppierungen verwendet und folgt ggf. direkt danach.
GROUP BY <spaltenliste> HAVING <bedingungen>
Dieser Befehl dient dazu, nicht alle Ergebnisse der Auswahl in die Ausgabe zu übernehmen, sondern nur diejenigen, die den zusätzlichen Bedingungen entsprechen.
Im folgenden Beispiel (vergleiche oben unter „Gruppierung bei einer Tabelle“) wird festgestellt, an welchen Orten sich genau eine Abteilung befindet.
SELECT Ort,
COUNT(*) AS Anzahl
FROM Abteilung
GROUP BY Ort
HAVING COUNT(*) = 1
ORDER BY Ort;
AusgabeHerne 1
Bitte beachten Sie, dass der Alias-Name nicht verwendet werden kann, sondern die Aggregatfunktion bei HAVING erneut aufgeführt werden muss.
Hinweis: Die HAVING-Klausel wird als letzter Teil des SELECT-Befehls ausgeführt. Es ist deshalb zu empfehlen, alle Einschränkungen vorher zu regeln, z. B. als Teil von WHERE-Bedingungen. Nur wenn – wie bei Aggregatfunktionen – diese Einschränkung erst am Schluss geprüft werden kann, ist HAVING zu benutzen.
Möglich, aber nicht günstig.SELECT Abteilung_ID, COUNT(*)
FROM MITARBEITER
GROUP BY Abteilung_ID
HAVING Abteilung_ID < 3;
Besseres Verfahren, weil weniger Datensätze geprüft werden müssen.SELECT Abteilung_ID, COUNT(*)
FROM MITARBEITER
WHERE Abteilung_ID < 3
GROUP BY Abteilung_ID;
Zusammenfassung
In diesem Kapitel lernten wir Einzelheiten über die Gruppierung bei Abfragen.
- Dies wird meistens gleichzeitig mit ORDER BY und in Verbindung mit Aggregatfunktionen verwendet.
- Die Gruppierung ist auch über mehrere Tabellen hinweg möglich.
- Einige wichtige Einschränkungen sind zu beachten; vor allem sind die Felder aus der Spaltenliste in der Regel auch unter GROUP BY aufzuführen.
Die HAVING-Klausel kann das Abfrageergebnis einschränken, sollte aber zurückhaltend benutzt werden.
Übungen
| Übung 1 | Definitionen | Zur Lösung |
Welche der folgenden Feststellungen sind wahr, welche sind falsch?
- GROUP BY kann nur zusammen mit (mindestens) einer Spaltenfunktion benutzt werden.
- GROUP BY kann nur auf „echte“ Spalten angewendet werden, nicht auf berechnete Spalten.
- In der GROUP BY-Klausel kann ein Spaltenname ebenso angegeben werden wie ein Spalten-Alias.
- Die WHERE-Klausel kommt vor der GROUP BY-Klausel.
- Folgende Gruppierung nach den ersten zwei Ziffern der PLZ ist zulässig.
select PLZ, COUNT(*)
from Versicherungsnehmer vn
group by SUBSTRING(vn.PLZ from 1 for 2)
order by 1
- HAVING darf nur zusammen mit einer Gruppierung verwendet werden.
| Übung 2 | Gruppierung für 1 Tabelle | Zur Lösung |
Bestimmen Sie die Anzahl der Kunden (Versicherungsnehmer) in jedem Briefzentrum (d. h. die Ziffern 1 und 2 der PLZ).
| Übung 3 | Gruppierung für 1 Tabelle | Zur Lösung |
Bestimmen Sie, wie viele Fahrzeuge in jedem Kreis angemeldet sind.
| Übung 4 | Gruppierung für mehrere Tabellen | Zur Lösung |
Bestimmen Sie, wie viele Fahrzeugtypen pro Hersteller registriert sind, und nennen Sie Namen und Land der Hersteller.
Hinweis: Erstellen Sie zunächst eine Abfrage für Anzahl plus Hersteller-ID und verknüpfen Sie das Ergebnis mit der Tabelle Hersteller.
| Übung 5 | Gruppierung für mehrere Tabellen | Zur Lösung |
Bestimmen Sie – gruppiert nach Jahr des Schadensfalls und Kreis des Fahrzeugs – die Anzahl der Schadensfälle. Es sollen bei den Fahrzeugen nur Schadensfälle mit einem Schuldanteil von mehr als 50 [Prozent] berücksichtigt werden.
| Lösung zu Übung 1 | Definitionen | Zur Übung |
Richtig sind die Aussagen 3, 4. Falsch sind die Aussagen 1, 2, 5, 6.
| Lösung zu Übung 2 | Gruppierung für 1 Tabelle | Zur Übung |
select SUBSTRING(vn.PLZ from 1 for 2), COUNT(*)
from Versicherungsnehmer vn
group by 1
order by 1
Hinweis: Hierbei handelt es sich um die korrekte Version zur Frage 5 aus Übung 1.
| Lösung zu Übung 3 | Gruppierung für 1 Tabelle | Zur Übung |
select SUBSTRING(Kennzeichen from 1 for POSITION('-', Kennzeichen)-1 ) as Kreis,
COUNT(*) as Anzahl
from Fahrzeug fz
group by 1
order by 1;
Hinweis: Hierbei handelt es sich um die vollständige Version der letzten Übung zu „berechneten Spalten“.
| Lösung zu Übung 4 | Gruppierung für mehrere Tabellen | Zur Übung |
select Name, Land, Anzahl
from (
select ft.Hersteller_ID as ID, Count(ft.Hersteller_ID) as Anzahl
from Fahrzeugtyp ft
group by ft.Hersteller_ID
) temp
join Fahrzeughersteller fh
on fh.ID = temp.ID
order by Name;
| Lösung zu Übung 5 | Gruppierung für mehrere Tabellen | Zur Übung |
select EXTRACT(YEAR from sf.Datum) as Jahr,
SUBSTRING(Kennzeichen from 1 for POSITION('-', Kennzeichen)-1 ) as Kreis,
COUNT(*)
from Zuordnung_SF_FZ zu
right join Fahrzeug fz on fz.ID = zu.Fahrzeug_ID
inner join Schadensfall sf on sf.ID = zu.Schadensfall_ID
where zu.Schuldanteil > 50
group by 1, 2
order by 1, 2;
Erläuterungen: Die Tabelle der Zuordnungen ist kleiner als die diejenige der Fahrzeuge, und darauf bezieht sich die WHERE-Bedingung; deshalb ist sie als Haupttabelle am sinnvollsten. Wegen der Kennzeichen benötigen wir einen JOIN auf die Tabelle Fahrzeug. Wegen des Datums des Schadensfalls für die Gruppierung nach Jahr benötigen wir einen JOIN auf die Tabelle Schadensfall.
Siehe auch
Ergänzende Informationen sind in diesen Kapiteln zu finden:
- Datentypen informiert auch über besondere Sortierungen einer einzelnen Spalte.
- Mehrere Tabellen verknüpfen
| Unterabfragen |
Immer wieder werden zur Durchführung einer Abfrage oder eines anderen Befehls Informationen benötigt, die zuerst durch eine eigene Abfrage geholt werden müssen. Solche „Unterabfragen“ werden in diesem Kapitel behandelt.
- Wenn eine Abfrage als Ergebnis einen einzelnen Wert liefert, kann sie anstelle eines Wertes benutzt werden.
- Wenn eine Abfrage als Ergebnis eine Liste von Werten liefert, kann sie anstelle einer solchen Liste benutzt werden.
- Wenn eine Abfrage eine Ergebnismenge, also etwas in Form einer Tabelle liefert, kann sie anstelle einer Tabelle benutzt werden.
Bitte beachten Sie, dass die Unterabfrage immer in Klammern gesetzt wird. Auch wenn ein DBMS das nicht verlangen sollte, ist es wegen der Übersichtlichkeit dringend zu empfehlen.
Allgemeiner Hinweis: Unterabfragen arbeiten in vielen Fällen langsamer als andere Verfahren. Soweit es irgend möglich ist, versuchen Sie, eine der JOIN-Varianten vorzuziehen.
Ergebnis als einzelner Wert
Ergebnisse einfacher Abfragen
Immer wieder kennt der Anwender den Namen eines Objekts, benötigt aber für Abfragen die ID. Diese holt er sich mit einer Unterabfrage und übergibt das Ergebnis an die eigentliche Abfrage.
Nenne alle Mitarbeiter der Abteilung „Schadensabwicklung“.
- Lösung Teil 1: Hole die ID dieser Abteilung anhand des Namens.
- Lösung Teil 2: Hole die Mitarbeiter dieser Abteilung unter Benutzung der gefundenen ID.
select Personalnummer, Name, Vorname
from Mitarbeiter
where Abteilung_ID =
( select ID from Abteilung
where Kuerzel = 'ScAb' );
AusgabePERSONALNUMMER NAME VORNAME
-------------- --------- -------
80001 Schindler Christina
80002 Aliman Zafer
80003 Langer Norbert
80004 Kolic Ivana
Teil 1 der Lösung ist der SELECT-Befehl innerhalb der Klammern. Das Ergebnis ist eine einzelne ID. Diese kann anstelle einer konkreten Zahl in die WHERE-Klausel der eigentlichen Abfrage übernommen werden. Das Wörtchen „Diese“ hat in diesem Fall sprachlich eine doppelte Bedeutung: zum einen steht es für die Unterabfrage, zum anderen für die ID als Ergebnis.
Hinweis: Dies funktioniert nur deshalb auf einfache Weise, weil die Kurzbezeichnung faktisch eindeutig ist und deshalb genau eine ID geliefert wird. Wenn wir uns darauf nicht verlassen wollen oder können oder wenn das DBMS „empfindlich“ ist und die Eindeutigkeit des Ergebnisses nicht erkennt, können wir daraus bewusst einen einzelnen Wert machen:
select Personalnummer, Name, Vorname
from Mitarbeiter
where Abteilung_ID =
( select MAX(ID) from Abteilung
where Kuerzel = 'ScAb' );
Eine solche Aufgabe kann auch zweimal dieselbe Tabelle benutzen.
Nenne alle anderen Mitarbeiter der Abteilung, deren Leiterin Christina Schindler ist.
- Lösung Teil 1: Hole die Abteilung_ID, die bei Christina Schindler registriert ist.
- Lösung Teil 2: Hole die Mitarbeiter dieser Abteilung unter Benutzung der gefundenen Abteilung_ID.
Zur Sicherheit prüfen wir auch die Eigenschaft Ist_Leiter.
select Personalnummer, Name, Vorname
from Mitarbeiter
where ( Abteilung_ID =
( SELECT Abteilung_ID
from Mitarbeiter
where ( Name = 'Schindler' )
and ( Vorname = 'Christina' )
and ( Ist_Leiter = 'J' )
)
) and ( Ist_Leiter = 'N' );
AusgabePERSONALNUMMER NAME VORNAME
-------------- ------ -------
80002 Aliman Zafer
80003 Langer Norbert
80004 Kolic Ivana
Ergebnisse von Spaltenfunktionen
Häufig werden Ergebnisse von Aggregatfunktionen als Teil der WHERE-Klausel benötigt.
Hole die Schadensfälle mit unterdurchschnittlicher Schadenshöhe.
- Lösung Teil 1: Berechne die durchschnittliche Schadenshöhe aller Schadensfälle.
- Lösung Teil 2: Übernimm das Ergebnis als Vergleichswert in die eigentliche Abfrage.
SELECT ID, Datum, Ort, Schadenshoehe
from Schadensfall
where Schadenshoehe <
( select AVG(Schadenshoehe) from Schadensfall );
AusgabeID DATUM ORT SCHADENSHOEHE
-- ---------- ----------------------------------- -------------
1 03.02.2007 Recklinghausen, Bergknappenstr. 144 1.234,50
2 11.07.2007 Haltern, Hauptstr. 46 2.066,00
4 27.05.2008 Recklinghausen, Südgrabenstr. 23 1.438,75
5 05.10.2008 Dorsten, Oberhausener Str. 18 1.983,00
7 21.06.2009 Recklinghausen, Bergknappenstr. 144 865,00
Bestimme alle Schadensfälle, die von der durchschnittlichen Schadenshöhe eines Jahres maximal 300 € abweichen.
- Lösung Teil 1: Bestimme den Durchschnitt aller Schadensfälle innerhalb eines Jahres.
- Lösung Teil 2: Hole alle Schadensfälle, deren Schadenshöhe im betreffenden Jahr innerhalb des Bereichs „Durchschnitt plus/minus 300“ liegen.
select sf.ID, sf.Datum, sf.Schadenshoehe, EXTRACT(YEAR from sf.Datum) AS Jahr
from Schadensfall sf
where ABS(Schadenshoehe - ( select AVG(sf2.Schadenshoehe)
from Schadensfall sf2
where EXTRACT(YEAR from sf2.Datum) = EXTRACT(YEAR from sf.Datum)
)
) <= 300;
AusgabeID DATUM SCHADENSHOEHE JAHR
-- ---------- ------------- ----
2 11.07.2007 2.066,00 2007
4 27.05.2008 1.438,75 2008
5 05.10.2008 1.983,00 2008
8 01.08.2009 2.471,50 2009
Zuerst muss für jeden einzelnen Schadensfall aus sf das Jahr bestimmt werden. In der Unterabfrage, die in der inneren Klammer steht, wird für alle Schadensfälle des betreffenden Jahres die durchschnittliche Schadenshöhe bestimmt. Dieser Wert wird mit der aktuellen Schadenshöhe verglichen; dazu wird die ABS-Funktion benutzt, also der absolute Betrag der Differenz der beiden Werte.
|
|
Dies ist ein Paradebeispiel dafür, wie Unterabfragen nicht benutzt werden sollen. |
|---|
Für jeden einzelnen Datensatz muss in der WHERE-Bedingung eine neue Unterabfrage gestartet werden – mit eigener WHERE-Klausel und Durchschnittsberechnung. Viel besser ist eine der JOIN-Varianten oder eine der Lösungen im Abschnitt „Ergebnis in Form einer Tabelle“ (siehe unten).
Ergebnis als Liste mehrerer Werte
Das Ergebnis einer Abfrage kann als Filter für die eigentliche Abfrage benutzt werden.
Bestimme alle Fahrzeuge eines bestimmten Herstellers.
- Lösung Teil 1: Hole die ID des gewünschten Herstellers.
- Lösung Teil 2: Hole alle IDs der Tabelle Fahrzeugtyp zu dieser Hersteller-ID.
- Lösung Teil 3: Hole alle Fahrzeuge, die zu dieser Liste von Fahrzeugtypen-IDs passen.
select ID, Kennzeichen, Fahrzeugtyp_ID as TypID
from Fahrzeug
where Fahrzeugtyp_ID in
( select ID
from Fahrzeugtyp
where Hersteller_ID =
( select ID
from Fahrzeughersteller
where Name = 'Volkswagen' ) );
AusgabeID KENNZEICHEN TYPID
22 BOR-PQ 567 3
23 BOR-RS 890 2
Teil 1 der Lösung ist die „innere“ Klammer; dies ist das gleiche Verfahren wie im Abschnitt „Ergebnisse einfacher Abfragen“. Teil 2 der Lösung ist die „äußere“ Klammer; Ergebnis ist eine Liste von IDs der Tabelle Fahrzeugtyp, die als Werte für den Vergleich der WHERE-IN-Klausel verwendet werden.
Wenn im Ergebnis der Fahrzeugtyp als Text angezeigt werden soll, muss die Abfrage erweitert werden, weil die Bezeichnung in der Tabelle Fahrzeugtyp zu finden ist. Dafür kann diese Tabelle ein zweites Mal benutzt werden, wie es im Kapitel Mehrere Tabellen erläutert wird; es ist auch ein Verfahren möglich, wie es unten im Abschnitt „Ergebnis in Form einer Tabelle“ erläutert wird.
Das obige Beispiel mit der durchschnittlichen Schadenshöhe kann auch so gebaut werden:
Gib alle Informationen zu den Schadensfällen des Jahres 2008, die von der durchschnittlichen Schadenshöhe 2008 maximal 300 € abweichen.
- Lösung Teil 1: Bestimme den Durchschnitt aller Schadensfälle innerhalb von 2008.
- Lösung Teil 2: Hole alle IDs von Schadensfällen, deren Schadenshöhe innerhalb des Bereichs „Durchschnitt plus/minus 300“ liegen.
- Lösung Teil 3: Hole alle anderen Informationen zu diesen IDs.
select *
from Schadensfall
where ID in ( SELECT ID
from Schadensfall
where ( ABS(Schadenshoehe
- ( select AVG(sf2.Schadenshoehe)
from Schadensfall sf2
where EXTRACT(YEAR from sf2.Datum) = 2008
)
) <= 300 )
and ( EXTRACT(YEAR from Datum) = 2008 )
);
AusgabeID DATUM ORT BESCHREIBUNG SCHADENSHOEHE VERLETZTE MIT-ID
4 27.05.2008 Recklinghausen, Fremdes parkendes 1.438,75 N 16
Südgrabenstr. 23 Auto gestreift
5 05.10.2008 Dorsten, beim Ausparken hat 1.983,00 N 14
Oberhausener Str. 18 ein fremder Wagen die
Vorfahrt missachtet
Diese Situation wird dadurch einfacher, dass das Jahr 2008 fest vorgegeben ist. Die innerste Klammer bestimmt als Teil 1 der Lösung die durchschnittliche Schadenshöhe dieses Jahres. Die nächste Klammer vergleicht diesen Wert (absolut gesehen) mit der Schadenshöhe eines jeden einzelnen Schadensfalls im Jahr 2008; alle „passenden“ IDs werden in der äußersten Klammer als Teil 2 der Lösung in einer weiteren Unterabfrage zusammengestellt. Diese Liste liefert die Werte für die eigentliche Abfrage.
Ganz offensichtlich ist dieses Beispiel konstruiert: Weil immer dieselbe Tabelle verwendet wird, kann die WHERE-Klausel der Unterabfrage in der äußersten Klammer auch als WHERE-Klausel der Hauptabfrage verwendet werden (die Einrückungen wurden zum besseren Vergleich nicht geändert):
select *
from Schadensfall
where ( ABS(Schadenshoehe
- ( select AVG(sf2.Schadenshoehe)
from Schadensfall sf2
where EXTRACT(YEAR from sf2.Datum) = 2008
)
) <= 300 )
and ( EXTRACT(YEAR from Datum) = 2008 )
;
Ergebnis in Form einer Tabelle
Das Ergebnis einer Abfrage kann in der Hauptabfrage überall dort eingesetzt werden, wo eine Tabelle vorgesehen ist. Die Struktur dieser Situation sieht so aus:
SELECT <spaltenliste>
FROM <haupttabelle>,
( SELECT <spaltenliste>
FROM <zusatztabellen>
<weitere Bestandteile der Unterabfrage>
) <name>
<weitere Bestandteile der Hauptabfrage>
Eine solche Unterabfrage kann grundsätzlich alle SELECT-Bestandteile enthalten. Bitte beachten Sie dabei:
- Nach der schließenden Klammer muss ein Name als Tabellen-Alias angegeben werden, der als Ergebnistabelle in der Hauptabfrage verwendet wird.
- Die Unterabfrage kann eine oder mehrere Tabellen umfassen – wie jede andere Abfrage auch.
- In der Spaltenliste sollte jeweils ein Name als Spalten-Alias vor allem dann vorgesehen werden, wenn mehrere Tabellen verknüpft werden; andernfalls erzeugt SQL selbständig Namen wie ID, ID1 usw., die man nicht ohne Weiteres versteht und zuordnen kann.
- ORDER BY kann nicht sinnvoll genutzt werden, weil das Ergebnis der Unterabfrage als Tabelle behandelt wird und mit der Haupttabelle oder einer anderen Tabelle verknüpft wird, wodurch eine Sortierung sowieso verlorenginge.
Wie gesagt: Eine solche Unterabfrage kann überall stehen, wo eine Tabelle vorgesehen ist. In der vorstehenden Syntax steht sie nur beispielhaft innerhalb der FROM-Klausel.
Überarbeiten wir jetzt, wie oben angekündigt, einige Beispiele. Dabei wird die Unterabfrage, die bisher zur WHERE-Klausel gehörte, als Tabelle in die FROM-Klausel eingebaut.
Bestimme alle Schadensfälle, die von der durchschnittlichen Schadenshöhe eines Jahres maximal 300 € abweichen.
- Lösung Teil 1: Stelle alle Jahre zusammen und bestimme den Durchschnitt aller Schadensfälle innerhalb eines Jahres.
- Lösung Teil 2: Hole alle Schadensfälle, deren Schadenshöhe im jeweiligen Jahr innerhalb des Bereichs „Durchschnitt plus/minus 300“ liegen.
SELECT sf.ID, sf.Datum, sf.Schadenshoehe, temp.Jahr, temp.Durchschnitt
FROM Schadensfall sf,
( SELECT AVG(sf2.Schadenshoehe) AS Durchschnitt,
EXTRACT(YEAR FROM sf2.Datum) as Jahr
FROM Schadensfall sf2
group by EXTRACT(YEAR FROM sf2.Datum)
) temp
WHERE temp.Jahr = EXTRACT(YEAR FROM sf.Datum)
and ABS(Schadenshoehe - temp.Durchschnitt) <= 300;
Zuerst stellen wir durch eine Gruppierung alle Jahreszahlen und die durchschnittlichen Schadenshöhen zusammen (Teil 1 der Lösung). Für Teil 2 der Lösung muss für jeden Schadensfall nur noch Jahr und Schadenshöhe mit dem betreffenden Eintrag in der Ergebnistabelle temp verglichen werden.
Das ist der wesentliche Unterschied und entscheidende Vorteil zur obigen Lösung: Die Durchschnittswerte werden einmalig zusammengestellt und nur noch abgerufen; sie müssen nicht bei jedem Datensatz neu (und ständig wiederholt) berechnet werden.
Bestimme alle Fahrzeuge eines bestimmten Herstellers mit Angabe des Typs.
- Lösung Teil 1: Hole die ID des gewünschten Herstellers.
- Lösung Teil 2: Hole alle IDs und Bezeichnungen der Tabelle Fahrzeugtyp, die zu dieser Hersteller-ID gehören.
- Lösung Teil 3: Hole alle Fahrzeuge, die zu dieser Liste von Fahrzeugtyp-IDs gehören.
SELECT Fahrzeug.ID, Kennzeichen, Typen.ID As TYP, Typen.Bezeichnung
FROM Fahrzeug,
( SELECT ID, Bezeichnung
FROM Fahrzeugtyp
WHERE Hersteller_ID =
( SELECT ID
FROM Fahrzeughersteller
WHERE Name = 'Volkswagen' )
) Typen
WHERE Fahrzeugtyp_ID = Typen.ID;
AusgabeID KENNZEICHEN TYP BEZEICHNUNG
23 BOR-RS 890 2 Golf
22 BOR-PQ 567 3 Passat
Teil 1 der Lösung ist die „innere“ Klammer; dies entspricht dem obigen Verfahren. Teil 2 der Lösung ist die „äußere“ Klammer; Ergebnis ist eine Tabelle von IDs und Bezeichnungen, also ein Teil der Tabelle Fahrzeugtyp, deren Werte für den Vergleich der WHERE-Klausel und außerdem für die Ausgabe verwendet werden.
Hinweis: Mit den Möglichkeiten des nächsten Kapitels Erstellen von Views ergeben sich wesentliche Verbesserungen: Mit einer VIEW lassen sich Unterabfragen, die – wie die Liste von Typen und Herstellern – immer wieder benötigt werden, dauerhaft bereitstellen. Und mit einer Inline-View werden verschachtelte Abfragen deutlich übersichtlicher.
Verwendung bei Befehlen zum Speichern
Bisher hatten wir Abfragen als Teil von anderen Abfragen benutzt; deshalb wird das Ganze auch als „Unterabfrage“ bezeichnet. Man kann das Ergebnis einer Abfrage aber auch zum Speichern verwenden: sowohl für einen einzelnen Wert als auch als vollständigen Datensatz.
Verwendung bei INSERT INTO ... SELECT
Eine Abfrage in einen INSERT-Befehl einzubinden, ist eines der Standardverfahren für INSERT:
INSERT INTO <zieltabelle>
( <spaltenliste> )
SELECT <spaltenliste>
FROM <quelltabelle/n>
[ <weitere Festlegungen> ]
[ WHERE <bedingungen> ]
Der SELECT-Befehl kann dabei beliebig aufgebaut sein: Daten aus einer oder mehreren Tabellen holen, mit oder ohne Einschränkungen, mit oder ohne weitere Festlegungen. Lediglich drei Punkte sind zu beachten:
- Die Spaltenliste in der Zieltabelle muss mit den Spalten in der SELECT-Auflistung genau übereinstimmen; genauer: Die Datentypen müssen zueinander passen, also gleich sein oder automatisch konvertiert werden können.
- Es ist möglich, Daten aus der Zieltabelle zu holen und somit zu verdoppeln. Dann muss die ID automatisch vergeben werden können; und es ist unbedingt mit WHERE zu arbeiten, weil es andernfalls zu einer Endlosschleife kommen kann (siehe das erste nachfolgende Beispiel).
- ORDER BY kann nicht sinnvoll genutzt werden, weil das Ergebnis in die Zieltabelle eingefügt wird, wodurch eine Sortierung sowieso verlorenginge.
Um weitere Testdaten zu erhalten, könnte so verfahren werden:
Kopiere die vorhandenen Schadensfälle.
INSERT into Schadensfall
( Datum, Ort, Beschreibung, Schadenshoehe, Verletzte, Mitarbeiter_ID )
SELECt Datum, Ort, Beschreibung, Schadenshoehe, Verletzte, Mitarbeiter_ID
from Schadensfall
where ID < 10000;
Auf die doppelte Angabe der Spalten kann nicht verzichtet werden, weil ID nicht benutzt werden darf (sie soll automatisch neu vergeben werden) und das DBMS wissen muss, welche Werte wie zugeordnet werden sollen. Auf diese Weise kann man ganz leicht 100 oder 1000 Testdatensätze erzeugen. Für den produktiven Betrieb wird man diese Syntax wohl eher seltener brauchen.
- In einem ersten Versuch fehlte die WHERE-Bedingung; erwartet wurde, dass nur die vor dem Befehl vorhandenen Datensätze bearbeitet würden. Tatsächlich hatte Firebird endlos kopiert, bis mit Strg+Alt+Entf der „Stecker gezogen“ wurde. Danach war die Datenbank von 3 MB auf 740 MB aufgebläht worden und (natürlich) beschädigt, sodass auf diese Tabelle nicht mehr richtig zugegriffen werden konnte. Weitere Versuche mit WHERE-Bedingung arbeiteten wie vorgesehen: nur die vorhandenen Datensätze wurden einmalig kopiert.
Die „neuen“ Daten können auch aus einer anderen Tabelle geholt und mit konstanten Werten gemischt werden, wie es in der Beispieldatenbank geschieht:
Jeder Abteilungsleiter erhält einen persönlichen Dienstwagen.
INSERT INTO Dienstwagen
( Kennzeichen, Farbe, Fahrzeugtyp_ID, Mitarbeiter_ID )
SELECT 'DO-WB 42' || Abteilung_ID,
'elfenbein', 14, ID
FROM Mitarbeiter
WHERE Ist_Leiter = 'J';
Die Spaltenliste der Zieltabelle Dienstwagen enthält alle Spalten mit Ausnahme der ID; diese wird automatisch vergeben. Diesen Spalten werden die Spalten der SELECT-Abfrage zugewiesen:
- Die Mitarbeiter_ID ist das einzige Feld aus der Quelltabelle Mitarbeiter, das unbedingt benötigt wird und übernommen werden muss.
- Die Farbe wird als Konstante eingetragen: Alle Abteilungsleiter bekommen den gleichen Wagentyp.
- Der Fahrzeugtyp wird ebenso als Konstante eingetragen. Auch eine Unterabfrage wie oben als Ergebnis einer einfachen Abfrage wäre möglich.
- Für das Kennzeichen ist eine String-Verknüpfung vorgesehen, bei der zusätzlich die Abteilung_ID übernommen wird. Bitte benutzen Sie die für Ihr DBMS richtige Art der String-Verknüpfung.
Die WHERE-Bedingung beschränkt alles auf Abteilungsleiter.
Verwendung bei INSERT INTO ... VALUES
Die andere Version des INSERT-Befehls nutzt direkt eine Liste von Werten:
INSERT INTO <zieltabelle>
[ ( <spaltenliste> ) ]
VALUES ( <werteliste> )
Ein einzelner Befehl sieht dabei wie folgt aus:
Der Mitarbeiter 2 bekommt einen gelben Dienstwagen Typ 2.
INSERT INTO Dienstwagen
( Kennzeichen, Farbe, Fahrzeugtyp_ID, Mitarbeiter_ID )
VALUES ( 'DO-WB 202', 'gelb', 2, 2 );
Versuchen wir, diesen Befehl variabel zu gestalten, sodass Mitarbeiter_ID und Kennzeichen (wie oben) aus einer Abfrage kommen können. Dazu setzen wir diese beiden Spalten hintereinander und ordnen diesen das „Ergebnis als Liste mehrerer Werte“ zu unter Verwendung der LPAD-Funktion (siehe Funktionen (2)).
FehlerINSERT INTO Dienstwagen
( Kennzeichen, Mitarbeiter_ID, Farbe, Fahrzeugtyp_ID )
VALUES ( ( SELECT 'DO-WB 2' || LPAD(ID, 2, '0'), ID
FROM Mitarbeiter WHERE ID = 2 ),
'gelb', 2 );
AusgabeSQL error code = -804.
Count of read-write columns does not equal count of values.
Aha, der SELECT-Befehl wird nur als ein Wert interpretiert, obwohl er zwei passende Werte liefert. Können wir die Abfrage an beiden Stellen verwenden?
So kann es funktionieren.INSERT INTO Dienstwagen
( Kennzeichen, Mitarbeiter_ID, Farbe, Fahrzeugtyp_ID )
VALUES ( ( SELECT 'DO-WB 2' || LPAD(ID, 2, '0') FROM Mitarbeiter WHERE ID = 2 ),
( SELECT ID FROM Mitarbeiter WHERE ID = 2 ),
'gelb', 2 );
So funktioniert es, aber es ist natürlich nicht schön, wenn eine fast identische Abfrage doppelt auftauchen muss (auch wenn davon auszugehen ist, dass ein DBMS einen vernünftigen Ausführungsplan erstellt). Dann ist die obige Version mit INSERT INTO ... SELECT mit einer Mischung aus Konstanten und Tabellenspalten die bessere Lösung.
Verwendung bei UPDATE
Schauen wir uns die grundsätzliche Struktur eines UPDATE-Befehls an:
UPDATE <tabellenname>
SET <spalte1> = <wert1> [ ,
<spalte2> = <wert2> ]
WHERE <bedingungsliste>;
Daraus ergibt sich, dass Abfragen benutzt werden können, um einen oder mehrere Werte zu speichern oder um Vergleichswerte für die Bedingungsliste zu liefern (ebenso wie in verschiedenen früheren Beispielen).
Die Mitarbeiter am Dienstort „Bochum“ erhalten eine neue Telefonnummer, die neben der Zentrale die Abteilung und die Personalnummer enthält.
- Lösung 1: Die WHERE-Bedingung muss die betreffenden Datensätze (genauer: die IDs) der Tabelle Abteilung prüfen und vergleichen.
- Lösung 2: Der zugeordnete neue Wert muss passend gestaltet werden. (LPAD und SUBSTRING werden nur verwendet, damit mit festen Längen gearbeitet werden kann.)
update Mitarbeiter
set Telefon = '0234/66' || LPAD(Abteilung_ID, 3, '0')
|| SUBSTRING(LPAD(Personalnummer, 6, '0') from 4 for 3)
where Abteilung_ID in ( SELECT ID
from Abteilung
where Ort = 'Bochum' );
Diese Lösung enthält nichts Neues: Die Abfrage wird mit der IN-Abfrage in die WHERE-Klausel eingebunden; die neuen Werte werden aus den vorhandenen (Abteilung und Personalnummer) gebildet.
Die Abteilung „Ausbildung“ soll dorthin umziehen, wo die Abteilung „Personalverwaltung“ sitzt.
- Lösung: Der gewünschte Ort wird aus einer Abfrage geholt.
update Abteilung
set Ort = ( SELECT Ort
from Abteilung
where Kuerzel = 'Pers' )
where Kuerzel = 'Ausb';
Bitte wundern Sie sich nicht über ein solch konstruiertes Beispiel; der neue Ort könnte natürlich im Klartext angegeben werden. Bei der vorgegebenen Datenstruktur ist es manchmal schwer, sinnvolle Beispiele zu entwickeln. Wichtig ist, dass Sie die möglichen Zusammenhänge zwischen einer Abfrage und einem Speichern-Befehl erkennen.
Man kann sich in der Unterabfrage auch auf Spalten beziehen, die aus der Tabelle stammen, die geändert werden soll. Beispiel (nur teilweiser Bezug auf die Beispieldatenbank):
Fehlerupdate Abteilung
set Ort = ( SELECT Ort
from Adressbuch
where Abteilung.PLZ = Adressbuch.PLZ )
;
Diese Abfrage führt zu folgendem Problem: Sie funktioniert nur dann, wenn es im Adressbuch nur exakt einen einzigen Eintrag zu einer bestimmten PLZ gibt. Sobald man zu einer PLZ mehrere Adressen notiert hat, findet die Unterabfrage mehrere Sätze; das ist bei dieser Unterabfrage nicht zulässig. Damit der Update auch in solchen Fällen funktioniert, muss ein DISTINCT eingefügt werden, oder man verwendet die MAX-oder die MIN-Funktion:
Genau ein Datensatz wird geliefert.update Abteilung
set Ort = ( SELECT MAX(Ort)
from Adressbuch
where Abteilung.PLZ = Adressbuch.PLZ )
;
Bei solchen Unterabfragen mit einem Bezug zu dem Satz, der verändert werden soll, kann es vorkommen, dass die Ausführung dieser Anweisung ziemlich lange dauert. Das liegt daran, dass alle Sätze aus der Tabelle Abteilung verändert werden sollen. Für jeden Satz muss die Unterabfrage erneut ausgeführt werden. Wenn eine einzelne Ausführung dieser Unterabfrage eine Sekunde dauert, und wir haben z. B. 1000 Sätze in der Tabelle Abteilung, dann dauert die Ausführung des gesamten Statements 1000 Sekunden, also ca. 16 Minuten.
Man kann auch mehrere Spalten aus Unterabfragen befüllen. Beispiel:
update Abteilung
set Ort = ( SELECT MAX(Ort)
from Telefonbuch
where PLZ = '12345' ),
Leiter = ( SELECT Manager
from Mitarbeiter
where Kuerzel = 'A073' )
where Kuerzel = 'Ausb';
;
Wenn man mehrere Werte aus derselben Unterabfrage übernehmen will, dann könnte man dieselbe Unterabfrage mehrfach angeben. Aber oft kann das Datenbanksystem nicht erkennen, dass es sich immer wieder um dieselbe Unterabfrage handelt, und müsste sie mehrfach ausführen. Einfacher, übersichtlicher und dann auch schneller ist die folgende Variante, die aber nicht jedes DBMS kennt.
In der Tabelle Abteilung werden die Spalten Ort, Leiter, Telefon gemeinsam geändert.
update Abteilung
set ( Ort, Leiter, Telefon )
= ( SELECT Ort, Name, Telefon
from Adressbuch
where Adressbuch.Personalnummer = Abteilung.Chef_Personalnummer
) ;
Hier werden alle Sätze in der Tabelle Abteilung aktualisiert unter Verwendung der Tabelle Adressbuch; die Personalnummer des Abteilungsleiters kann wie oben bestimmt werden.
Verwendung bei DELETE
Schauen wir uns noch die grundsätzliche Struktur eines DELETE-Befehls an:
DELETE FROM <tabellenname> [ WHERE <bedingungsliste> ];
Daraus ergibt sich, dass Abfragen nur für Vergleichswerte der Bedingungsliste sinnvoll sind (ebenso wie in verschiedenen früheren Beispielen).
Die Abteilung „Forschung und Entwicklung“ wird ausgelagert; alle zugeordneten Mitarbeiter werden in der Datenbank gelöscht.
delete from Mitarbeiter
where Abteilung_ID in ( SELECT ID
from Abteilung
where Bezeichnung = 'Forschung und Entwicklung' ) ;
Zusammenfassung
In diesem Kapitel benutzten wir Unterabfragen:
- Sowohl einzelne Werte als auch Listen als Ergebnis einer Abfrage können als Vergleichswerte in der WHERE-Klausel verwendet werden.
- Eine Tabelle als Ergebnis von Abfragen kann wie jede „echte“ Tabelle als Teil der FROM- oder JOIN-Klausel verwendet werden.
Ähnlich können Ergebnisse von Abfragen beim Speichern genutzt werden:
- Ganze Datensätze werden mit INSERT in eine Tabelle eingefügt werden.
- Einzelne Werte kommen in die WHERE-Klausel oder SET-Anweisung.
Übungen
| Übung 1 | Definitionen | Zur Lösung |
Welche der folgenden Feststellungen sind richtig, welche sind falsch?
- Das Ergebnis einer Unterabfrage kann verwendet werden, wenn es ein einzelner Wert oder eine Liste in Form einer Tabelle ist. Andere Ergebnisse sind nicht möglich.
- Ein einzelner Wert als Ergebnis kann durch eine direkte Abfrage oder durch eine Spaltenfunktion erhalten werden.
- Unterabfragen sollten nicht verwendet werden, wenn die WHERE-Bedingung für jede Zeile der Hauptabfrage einen anderen Wert erhält und deshalb die Unterabfrage neu ausgeführt werden muss.
- Mehrere Unterabfragen können verschachtelt werden.
- Für die Arbeitsgeschwindigkeit ist es gleichgültig, ob mehrere Unterabfragen oder JOINs verwendet werden.
- Eine Unterabfrage mit einer Tabelle als Ergebnis kann GROUP BY nicht sinnvoll nutzen.
- Eine Unterabfrage mit einer Tabelle als Ergebnis kann ORDER BY nicht sinnvoll nutzen.
- Bei einer Unterabfrage mit einer Tabelle als Ergebnis ist ein Alias-Name für die Tabelle sinnvoll, aber nicht notwendig.
- Bei einer Unterabfrage mit einer Tabelle als Ergebnis sind Alias-Namen für die Spalten sinnvoll, aber nicht notwendig.
| Übung 2 | Ein einzelner Wert | Zur Lösung |
Welche Verträge (mit einigen Angaben) hat der Mitarbeiter „Braun, Christian“ abgeschlossen? Ignorieren Sie die Möglichkeit, dass es mehrere Mitarbeiter dieses Namens geben könnte.
| Übung 3 | Ein einzelner Wert | Zur Lösung |
Zeigen Sie alle Verträge, die zum Kunden „Heckel Obsthandel GmbH“ gehören. Ignorieren Sie die Möglichkeit, dass der Kunde mehrfach gespeichert sein könnte.
| Übung 4 | Eine Liste von Werten | Zur Lösung |
Ändern Sie die Lösung von Übung 3, sodass auch mehrere Kunden mit diesem Namen als Ergebnis denkbar sind.
| Übung 5 | Eine Liste von Werten | Zur Lösung |
Zeigen Sie alle Fahrzeuge, die im Jahr 2008 an einem Schadensfall beteiligt waren.
| Übung 6 | Eine Liste von Werten | Zur Lösung |
Zeigen Sie alle Fahrzeugtypen (mit ID, Bezeichnung und Name des Herstellers), die im Jahr 2008 an einem Schadensfall beteiligt waren.
| Übung 7 | Eine Tabelle als Ergebnis | Zur Lösung |
Bestimmen Sie alle Fahrzeuge eines bestimmten Herstellers mit Angabe des Typs.
Hinweis: Es handelt sich um das letzte Beispiel aus dem Abschnitt „Ergebnis in Form einer Tabelle“. Benutzen Sie jetzt JOIN.
| Übung 8 | Eine Tabelle als Ergebnis | Zur Lösung |
Zeigen Sie zu jedem Mitarbeiter der Abteilung „Vertrieb“ den ersten Vertrag (mit einigen Angaben) an, den er abgeschlossen hat. Der Mitarbeiter soll mit ID und Name/Vorname angezeigt werden.
| Übung 9 | Speichern mit Unterabfrage | Zur Lösung |
Von der Deutschen Post AG wird eine Tabelle PLZ_Aenderung mit folgenden Inhalten geliefert:
ID PLZalt Ortalt PLZneu Ortneu 1 45658 Recklinghausen 45659 Recklinghausen 2 45721 Hamm-Bossendorf 45721 Haltern OT Hamm 3 45772 Marl 45770 Marl 4 45701 Herten 45699 Herten
Ändern Sie die Tabelle Versicherungsnehmer so, dass bei allen Adressen, bei denen PLZ/Ort mit PLZalt/Ortalt übereinstimmen, diese Angaben durch PLZneu/Ortneu geändert werden.
Hinweise: Beschränken Sie sich auf die Änderung mit der ID=3. (Die vollständige Lösung ist erst mit SQL-Programmierung möglich.) Bei dieser Änderungsdatei handelt es sich nur um fiktive Daten, keine echten Änderungen.
| Lösung zu Übung 1 | Definitionen | Zur Übung |
Richtig sind 2, 3, 4, 7, 9; falsch sind 1, 5, 6, 8.
| Lösung zu Übung 2 | Ein einzelner Wert | Zur Übung |
select ID, Vertragsnummer, Abschlussdatum, Art
from Versicherungsvertrag
where Mitarbeiter_ID
in ( select ID
from Mitarbeiter
where Name = 'Braun'
and Vorname = 'Christian' );
| Lösung zu Übung 3 | Ein einzelner Wert | Zur Übung |
select ID, Vertragsnummer, Abschlussdatum, Art
from Versicherungsvertrag
where Versicherungsnehmer_ID
= ( select ID from Versicherungsnehmer
where Name ='Heckel Obsthandel GmbH' );
| Lösung zu Übung 4 | Eine Liste von Werten | Zur Übung |
select ID, Vertragsnummer, Abschlussdatum, Art
from Versicherungsvertrag
where Versicherungsnehmer_ID
in ( select ID from Versicherungsnehmer
where Name ='Heckel Obsthandel GmbH' );
| Lösung zu Übung 5 | Eine Liste von Werten | Zur Übung |
select ID, Kennzeichen, Fahrzeugtyp_ID as TypID
from Fahrzeug fz
where ID in ( select Fahrzeug_ID
from Zuordnung_sf_fz zu
join Schadensfall sf on sf.ID = zu.Schadensfall_ID
where EXTRACT(YEAR from sf.Datum) = 2008 );
| Lösung zu Übung 6 | Eine Liste von Werten | Zur Übung |
SELECT distinct ft.ID as TypID, ft.Bezeichnung as Typ, fh.Name as Hersteller
FROM Fahrzeugtyp ft
inner join Fahrzeughersteller fh on fh.ID = ft.Hersteller_ID
right join Fahrzeug fz on ft.ID = fz.Fahrzeugtyp_ID
WHERE fz.ID IN ( SELECT Fahrzeug_ID
FROM Zuordnung_sf_fz zu
JOIN Schadensfall sf ON sf.ID = zu.Schadensfall_ID
WHERE EXTRACT(YEAR FROM sf.Datum) = 2008 );
Beachten Sie vor allem, dass die WHERE-Bedingung übernommen werden konnte, aber die Tabellen anders zu verknüpfen sind. Die Bedingung könnte in die ON-Klausel einbezogen werden; da sie aber die Auswahl beschränken soll, ist die WHERE-Klausel vorzuziehen.
| Lösung zu Übung 7 | Eine Tabelle als Ergebnis | Zur Übung |
SELECT fz.ID, fz.Kennzeichen, Typen.ID AS TYP, Typen.Bezeichnung
FROM Fahrzeug fz
join ( SELECT ID, Bezeichnung
FROM Fahrzeugtyp
WHERE Hersteller_ID =
( SELECT ID FROM Fahrzeughersteller
WHERE Name = 'Volkswagen' )
) Typen on fz.Fahrzeugtyp_ID = Typen.ID;
| Lösung zu Übung 8 | Eine Tabelle als Ergebnis | Zur Übung |
SELECT vv.ID as VV, vv.Vertragsnummer, vv.Abschlussdatum, vv.Art,
mi.ID as MI, mi.Name, mi.Vorname
from Versicherungsvertrag vv
right join ( select MIN(vv2.ID) as ID, vv2.Mitarbeiter_ID
from Versicherungsvertrag vv2
group by vv2.Mitarbeiter_id ) Temp
on Temp.ID = vv.ID
right join Mitarbeiter mi on mi.ID = vv.Mitarbeiter_ID
where mi.Abteilung_ID = ( select ID from Abteilung
where Bezeichnung = 'Vertrieb' );
Erläuterungen: Wir benötigen eine einfache Unterabfrage, um die Liste der Mitarbeiter für „Vertrieb“ zu erhalten, und wir benötigen eine Unterabfrage, die uns zur Mitarbeiter-ID die kleinste Vertrags-ID liefert. Wegen der Aufgabenstellung „zu jedem Mitarbeiter“ sowie „mit einigen Angaben“ muss es sich bei beiden Verknüpfungen um einen RIGHT JOIN handeln.
| Lösung zu Übung 9 | Speichern mit Unterabfrage | Zur Übung |
update Versicherungsnehmer
set PLZ, Ort
= ( Select PLZneu, Ortneu
from PLZ_Aenderg
where ID = 3 )
where PLZ = ( Select PLZalt
from PLZ_Aenderg
where ID = 3 )
and Ort = ( Select Ortalt
from PLZ_Aenderg
where ID = 3 );
Vielleicht funktioniert diese Variante bei Ihrem DBMS nicht; dann ist die folgende Version nötig:
update Versicherungsnehmer
set PLZ = ( Select PLZneu
from PLZ_Aenderg
where ID = 3 ),
Ort = ( Select Ortneu
from PLZ_Aenderg
where ID = 3 )
where PLZ = ( Select PLZalt
from PLZ_Aenderg
where ID = 3 )
and Ort = ( Select Ortalt
from PLZ_Aenderg
where ID = 3 );
| Erstellen von Views |
VIEWs sind Abfragen, die in der Datenbank als Objekt fest gespeichert sind. Sie können als virtuelle Tabellen verstanden werden, deren Inhalt und Struktur auf anderen Tabellen oder Views basieren, und können in (fast) jedem SELECT-Befehl anstelle einer „echten“ Tabelle verwendet werden.
Allgemeine Hinweise
Bei einer View wird die Abfrage in der Datenbank gespeichert, aber nicht das Ergebnis. Bei jedem neuen Aufruf der View wird die dahinterliegende Abfrage neu ausgeführt, denn sie soll ja das Ergebnis anhand der aktuellen Daten bestimmen.
Die Abfragen, auf denen Views basieren, können grundsätzlich alle Klauseln wie eine normale Abfrage enthalten. Somit ist es möglich, bestimmte Daten in einer View zu selektieren und zu gruppieren. Hierbei können die Daten aus mehreren Tabellen oder Views selektiert werden.
Je nach DBMS und Situation kann eine einzelne Klausel der View unwirksam sein oder zu unklaren Ergebnissen führen.
- Eine ORDER BY-Klausel der View wird ignoriert, wenn der SELECT-Befehl, der sie benutzt, selbst eine Sortierung verwendet.
- Bei einer Beschränkung durch LIMIT o. ä. weiß das DBMS oft nicht, nach welchen Regeln diese Beschränkung verwirklicht werden soll.
- WHERE-Bedingungen können nur fest eingebaut werden, aber nicht mit variablen Parametern.
Mit Views wird die stark differenzierte Struktur eines Auswahlbefehls vereinfacht. Die View wird mit ihrer komplexen Abfrage einmal angelegt, und die Nutzer können die Daten dieser View immer wieder abfragen.
Weiterhin können Views genutzt werden, um den Zugriff auf bestimmte Daten einzuschränken. Nutzer können Zugriff nur auf bestimmte Views bekommen. Somit lässt sich der Zugriff für einzelne Nutzer auf bestimmte Daten (Spalten und Datensätze) beschränken.
Eine View anlegen und benutzen
Views werden mit dem Befehl CREATE VIEW mit folgender Syntax angelegt.
CREATE VIEW <View-Name>
[ ( <Spaltennamen> ) ]
AS <Select-Ausdruck> ;
Zu dieser Definition gehören folgende Bestandteile:
- CREATE VIEW kennzeichnet den Befehl.
- Unter <View-Name> ist eine Bezeichnung anzugeben, unter der die View in einem SELECT-Befehl angesprochen wird. Dieser Name muss eindeutig sein und darf auch kein Name einer „echten“ Tabelle sein.
- Als <Select-Ausdruck> wird ein (beliebiger) SELECT-Befehl eingetragen.
- Es wird empfohlen, möglichst bei allen Spalten mit einem Alias zu arbeiten.
- Diese können wahlweise vor dem AS in Klammern angegeben werden oder (wie üblich) Teil des <Select-Ausdruck>s sein.
Die View wird dann wie jede Tabelle benutzt, z. B. einfach:
SELECT * FROM <View-Name>
Oder auch als Teil einer komplexen Abfrage:
SELECT <irgendwas>
FROM <Tabelle>
JOIN <View-Name> ON /* usw. */
Eine einfache View
Im einfachsten Fall greifen wir auf eine einfache Verknüpfung zweier Tabellen zu und verbinden dies mit einer festen Suchbedingung.
Erstelle eine View, die eine Liste aller Fahrzeugtypen deutscher Hersteller anzeigt.
CREATE VIEW Deutscher_Fahrzeugtyp
AS SELECT DISTINCT ft.Bezeichnung AS Fahrzeugtyp, fh.Name AS Hersteller
FROM Fahrzeugtyp ft
join Fahrzeughersteller fh on ft.Hersteller_ID = fh.ID
WHERE fh.Land = 'Deutschland';
Die Abfrage basiert auf den beiden Tabellen Fahrzeugtyp und Fahrzeughersteller. Es werden nur die Spalten Bezeichnung und Name abgefragt; durch die WHERE-Klausel wird das Ergebnis auf Fahrzeuge deutscher Hersteller beschränkt. Für die Spalten werden Spalten-Aliase genutzt.
Diese View wird dann wie eine „normale“ Tabellen in Abfragen genutzt.
SELECT * FROM Deutscher_Fahrzeugtyp order by Hersteller;
AusgabeFAHRZEUGTYP HERSTELLER
----------- ----------
A3 Audi
A4 Audi
325 BMW
525 BMW
Z3 BMW
Fiesta Ford
Focus Ford /* usw. */
In diesem Fall kann die ORDER BY-Klausel ebensogut Teil der View sein; das untersuchen wir später noch.
Eine View mit variabler Selektion
Es klappt leider nicht, in eine View eine WHERE-Klausel einen (variablen) Parameter einzubauen, der erst im SELECT mit einem konkreten Wert versehen wird.
Gesucht wird eine Abfrage über die Mitarbeiter einer Abteilung; am Anfang soll der Abteilungsleiter stehen, danach alphabetisch die betreffenden Mitarbeiter. Die Nummer der Abteilung soll nicht fest vorgegeben werden, sondern variabel sein.
Fehlercreate view Mitarbeiter_in_Abteilung
as select Personalnummer, Name, Vorname, Geburtsdatum
from Mitarbeiter
WHERE Abteilung_ID = ?
order by Ist_Leiter, Name, Vorname;
AusgabeCommit nicht möglich
Invalid token.
Auch Alternativen für das Fragezeichen führen nicht zum Ziel. Es bleibt nur ein kleiner Umweg, nämlich die Abteilung_ID in der View zu berücksichtigen und später für WHERE zu nutzen:
So geht es.create view Mitarbeiter_in_Abteilung
( Pers, Name, Vorname, Geburtsdatum, Abt )
as select Personalnummer, Name, Vorname, Geburtsdatum, Abteilung_ID
from Mitarbeiter
order by Ist_Leiter, Name, Vorname;
Damit können alle Angaben einer bestimmten Abteilung geholt werden; die Spalte Abt bleibt zur Verdeutlichung stehen:
select * from Mitarbeiter_in_Abteilung
where Abt = 5;
AusgabePERS NAME VORNAME GEBURTSDATUM ABT
------ ------------- --------- ------------ ---
50001 Pohl Helmut 27.10.1980 5
50002 Braun Christian 05.09.1966 5
50004 Kalman Aydin 17.12.1976 5
50003 Polovic Frantisek 26.11.1961 5
Und siehe da: zuerst kommt der Abteilungsleiter, danach die anderen Mitarbeiter in alphabetischer Reihenfolge.
Hinweis: Eine Alternative zu einer VIEW mit variabler WHERE-Bedingung ist eine „StoredProcedure“, die diese Abfrage enthält und einen Wert als Parameter entgegennimmt; sie wird in einem späteren Kapitel behandelt.
Probleme mit der Sortierung
Ändern wir die obige View deutscher Fahrzeuge dahin, dass die Sortierung nach Hersteller fest eingebaut wird.
- Bitte beachten Sie: Wenn Sie oben die View Deutscher_Fahrzeugtyp fest gespeichert haben, müssen Sie in diesem Abschnitt einen anderen Namen verwenden oder stattdessen etwas wie CREATE OR ALTER (siehe die DBMS-Dokumentation) benutzen.
CREATE VIEW Deutscher_Fahrzeugtyp ( Typ, Firma )
AS SELECT DISTINCT ft.Bezeichnung, fh.Name as Firma
FROM Fahrzeugtyp ft
join Fahrzeughersteller fh on ft.Hersteller_ID = fh.ID
WHERE fh.Land = 'Deutschland'
order by Firma;
Bitte beachten Sie, dass in diesem Fall der Spalten-Alias Firma auch Teil des SELECT-Befehls sein muss, damit er in der ORDER BY-Klausel bekannt ist.
Jetzt wird die Liste wahlweise mit oder ohne Sortierung abgerufen:
SELECT * FROM Deutscher_Fahrzeugtyp; -- automatisch sortiert nach Firma SELECT * FROM Deutscher_Fahrzeugtyp ORDER BY Typ; -- speziell sortiert nach Typ
Views in Verbindung mit JOIN
Die obige Verknüpfung „Fahrzeugtyp plus Hersteller“ benötigen wir in der Praxis ständig, nicht nur in der konkreten Abfrage nach deutschen Herstellern. Bisher – zum Beispiel mit OUTER JOIN – haben wir beide Tabellen separat per JOIN eingebunden, mussten aber immer auf die Art des JOINs aufpassen. Das kann man einmalig durch eine fiktive Tabelle Fahrzeugart, also eine VIEW mit den benötigten Informationen steuern.
Eine solche VIEW erfüllt mehrere Wünsche:
- Die eigentlichen Informationen werden getrennt gespeichert; es ist nicht nötig, bei jedem Fahrzeugtyp den Hersteller und sein Herkunftsland aufzuführen. Wie wir aus der Wirtschaftspolitik des Jahres 2009 wissen, kann sich ein Herkunftsland durchaus ändern; nach den Regeln der Normalisierung ist die separate Tabelle der Hersteller nicht nur sinnvoll, sondern notwendig.
- Bei jeder Abfrage des Fahrzeugtyps erhalten wir sofort auch den Hersteller.
- Jede solche Abfrage wird einfacher, weil eine Tabelle weniger benötigt wird.
- Das DBMS kennt seine VIEWs und hat sie „von Haus aus“ optimiert; also wird jede solche Abfrage auch schneller ausgeführt.
- Diese Aussage gilt nicht unbedingt bei jeder Abfrage und jedem DBMS. Aber nach allen Erkenntnissen über interne Datenbankstrukturen kann man davon ausgehen.
Das obige „einfache Beispiel“ der VIEW müssen wir nur wenig umschreiben:
Bereite eine (fiktive) Tabelle Fahrzeugart vor mit allen relevanten Informationen aus den Tabellen Fahrzeugtyp und Fahrzeughersteller.
CREATE VIEW Fahrzeugart
( ID, Bezeichnung, Hersteller, Land )
AS SELECT ft.ID, ft.Bezeichnung, fh.Name, fh.Land
FROM Fahrzeugtyp ft
join Fahrzeughersteller fh on ft.Hersteller_ID = fh.ID;
Für den Anwender sieht es tatsächlich so aus, als hätten wir eine einfache Tabelle mit allen Angaben:
select * from Fahrzeugart
order by Land, Hersteller, Bezeichnung;
AusgabeID BEZEICHNUNG HERSTELLER LAND
-- ---------------- ------------- -----------
19 C30 Volvo
18 S40 Volvo
12 A3 Audi Deutschland
13 A4 Audi Deutschland
9 325 BMW Deutschland
10 525 BMW Deutschland
11 Z3 BMW Deutschland
Damit kann das letzte der Beispiele zu OUTER JOIN vereinfacht werden.
Hole alle Dienstwagen (ggf. mit den zugehörigen Mitarbeitern) und nenne dazu alle Fahrzeugdaten.
SELECT
mi.Personalnummer AS MitNr,
mi.Name, mi.Vorname,
dw.ID AS DIW, dw.Kennzeichen, dw.Fahrzeugtyp_ID AS TypID,
fa.Bezeichnung AS Typ, fa.Hersteller
FROM Dienstwagen dw
LEFT JOIN Mitarbeiter mi ON mi.ID = dw.Mitarbeiter_ID
INNER JOIN Fahrzeugart fa ON fa.ID = dw.Fahrzeugtyp_ID;
AusgabeMITNR NAME VORNAME DIW KENNZEICHEN TYPID TYP HERSTELLER
------- --------- --------- --- ----------- ----- --------------- -------------
80001 Schindler Christina 8 DO-WB 428 14 A160 Mercedes-Benz
90001 Janssen Bernhard 9 DO-WB 429 14 A160 Mercedes-Benz
100001 Grosser Horst 10 DO-WB 4210 14 A160 Mercedes-Benz
110001 Eggert Louis 11 DO-WB 4211 14 A160 Mercedes-Benz
120001 Carlsen Zacharias 12 DO-WB 4212 14 A160 Mercedes-Benz
13 DO-WB 111 16 W211 (E-Klasse) Mercedes-Benz
50002 Braun Christian 14 DO-WB 352 2 Golf Volkswagen
50003 Polovic Frantisek 15 DO-WB 353 3 Passat Volkswagen
50004 Kalman Aydin 16 DO-WB 354 4 Kadett Opel
80002 Aliman Zafer 17 DO-WB 382 2 Golf Volkswagen
80003 Langer Norbert 18 DO-WB 383 3 Passat Volkswagen
80004 Kolic Ivana 19 DO-WB 384 4 Kadett Opel
Einige kleine Änderungen vereinfachen alles: Die Tabelle Fahrzeugtyp wird durch die View Fahrzeugart ersetzt; der JOIN auf Fahrzeughersteller entfällt ersatzlos. Lediglich zur Klarheit ändern wir Tabellen-Alias und Spaltennamen.
Eine View ändern oder löschen
Die Änderung einer VIEW wird unterschiedlich gehandhabt.
- Üblich ist das „normale“ ALTER VIEW.
- Firebird behandelt eine Änderung mit RECREATE als Löschung und anschließende Neuaufnahme.
Die Löschung einer View erfolgt mit dem üblichen Befehl DROP VIEW.
DROP VIEW Deutscher_Fahrzeugtyp;
Hierbei wird nur die View als Objekt in der Datenbank gelöscht. Die Tabellen und Daten in den Tabellen, auf denen die View basiert, werden davon nicht beeinflusst – sie werden nicht gelöscht.
Zusammenfassung
- Views sind Abfragen, die in der Datenbank als Objekt gespeichert werden.
- Views können die Komplexität für den Anwender reduzieren.
- Views können für eine detaillierte Zugriffskontrolle genutzt werden.
- Views werden in Abfragen wie jede Tabelle benutzt.
- Sie werden mit CREATE VIEW erstellt und mit DROP VIEW gelöscht.
Übungen
Die Formulierung „eine View kontrollieren“ meint: Mit einer geeigneten Abfrage soll überprüft werden, ob die View richtig erstellt worden ist.
| Übung 1 | Definitionen | Zur Lösung |
Welche der folgenden Feststellungen sind richtig, welche sind falsch?
- Eine View ist wie eine „normale“ Abfrage, deren Bestandteile in der Datenbank fest gespeichert werden.
- Das Ergebnis dieser Abfrage wird gleichzeitig gespeichert und steht damit beim nächsten Aufruf der View sofort zur Verfügung.
- Eine ORDER BY-Klausel kann in einer View immer benutzt werden.
- Eine ORDER BY-Klausel ist in einer View nicht erforderlich.
- Wenn diese Klausel in einer View benutzt wird, hat diese Sortierung Vorrang vor einer ORDER BY-Klausel in dem SELECT-Befehl, der die View benutzt.
- Wenn ein SELECT-Befehl komplexe JOINs oder andere Klauseln benutzt und häufiger benutzt wird, ist es sinnvoll, ihn in einer View zu kapseln.
- Wenn ein Anwender nicht alle Daten sehen darf, ist es notwendig, die Zugriffsrechte auf die Spalten zu beschränken; diese Beschränkung kann nicht über eine View gesteuert werden.
- Eine View kann in einem SELECT-Befehl in der FROM-Klausel anstatt einer Tabelle aufgerufen werden.
- Eine View kann nicht in einem JOIN benutzt werden.
| Übung 2 | Eine View benutzen | Zur Lösung |
Skizzieren Sie eine Abfrage, durch die eine beliebige View benutzt werden kann.
| Übung 3 | Eine einfache View erstellen | Zur Lösung |
Bei der Suche nach Dienstwagen sollen mit der View Dienstwagen_Anzeige immer auch angezeigt werden:
- Name und Vorname des Mitarbeiters
- ID und Bezeichnung seiner Abteilung
- der Fahrzeugtyp (nur als ID)
Stellen Sie sicher, dass auch nicht-persönliche Dienstwagen immer angezeigt werden, und kontrollieren Sie das Ergebnis durch eine Abfrage ähnlich diesem Muster:
SELECT * FROM Dienstwagen_Anzeige WHERE ( Abt_ID BETWEEN 5 AND 8 ) or ( Mi_Name is null );
| Übung 4 | Mehrere Tabellen und Views verbinden | Zur Lösung |
Erweitern Sie die vorstehende View so, dass mit Hilfe der View Fahrzeugart auch Bezeichnung, Hersteller und Land angezeigt werden. Kontrollieren Sie das Ergebnis durch die o. g. Abfrage.
Dies ist ein Beispiel dafür, dass eine View bei Abfragen genauso wie eine „echte“ Tabelle benutzt werden kann.
| Übung 5 | Eine VIEW auf mehrere Tabellen | Zur Lösung |
Erstellen Sie eine Sicht Vertrag_Anzeige, bei der zu jedem Vertrag angezeigt werden:
- ID, Vertragsnummer, Abschlussdatum, Art (als Text)
- Name, Vorname des Mitarbeiters
- Name, Vorname des Versicherungsnehmers
- Kennzeichen des Fahrzeugs
| Übung 6 | Eine VIEW auf mehrere Tabellen | Zur Lösung |
Erweitern Sie die vorstehende View so, dass mit Hilfe der View Fahrzeugart auch Bezeichnung, Hersteller und Land angezeigt werden.
| Übung 7 | Eine View abschnittsweise kontrollieren | Zur Lösung |
Erstellen Sie eine Abfrage, sodass für einen Teil der Verträge die vorstehende View kontrolliert wird.
| Übung 8 | Eine weitere VIEW auf mehrere Tabellen | Zur Lösung |
Erstellen Sie eine Sicht Schaden_Anzeige, bei der zu jedem an einem Schadensfall beteiligten Fahrzeug angezeigt werden:
- ID, Datum, Gesamthöhe eines Schadensfalls
- Kennzeichen und Typ des beteiligten Fahrzeugs
- Anteiliger Schaden
- ID des Versicherungsvertrags
| Übung 9 | Eine weitere VIEW auf mehrere Tabellen | Zur Lösung |
Erweitern Sie die vorstehende View so, dass mit Hilfe der View Fahrzeugart auch Bezeichnung, Hersteller und Land sowie Vertragsnummer und ID des Versicherungsnehmers angezeigt werden.
| Übung 10 | Eine View zur Auswertung einer View | Zur Lösung |
Erstellen Sie eine weitere View so, dass die vorstehende View für alle Schadensfälle des aktuellen Jahres benutzt wird.
| Lösung zu Übung 1 | Definitionen | Zur Übung |
Richtig sind die Aussagen 1, 3, 4, 6, 8. Falsch sind die Aussagen 2, 5, 7, 9.
| Lösung zu Übung 2 | Eine View benutzen | Zur Übung |
SELECT * FROM <View-Name>;
| Lösung zu Übung 3 | Eine View erstellen | Zur Übung |
create view Dienstwagen_Anzeige
( Kennzeichen, TypId,
Mi_Name, Mi_Vorname,
Ab_ID, Ab_Name )
as select dw.Kennzeichen, dw.Fahrzeugtyp_ID,
mi.Name, mi.Vorname,
mi.Abteilung_ID,
ab.Bezeichnung
from Dienstwagen dw
left join Mitarbeiter mi
on mi.ID = dw.Mitarbeiter_ID
left join Abteilung ab
on ab.ID = mi.Abteilung_ID;
Erläuterung: LEFT JOIN in beiden Fällen wird benötigt, damit auch NULL-Werte, nämlich die nicht-persönlichen Dienstwagen angezeigt werden.
| Lösung zu Übung 4 | Mehrere Tabellen und Views verbinden | Zur Übung |
alter view Dienstwagen_Anzeige
( Kennzeichen, TypId,
Typ, Fz_Hersteller, Fz_Land,
Mi_Name, Mi_Vorname,
Ab_ID, Ab_Name )
as select dw.Kennzeichen, dw.Fahrzeugtyp_ID,
fa.Bezeichnung, fa.Hersteller, fa.Land,
mi.Name, mi.Vorname,
mi.Abteilung_ID,
ab.Bezeichnung
from Dienstwagen dw
left join Mitarbeiter mi
on mi.ID = dw.Mitarbeiter_ID
left join Abteilung ab
on ab.ID = mi.Abteilung_ID
inner join Fahrzeugart fa
on fa.ID = dw.Fahrzeugtyp_ID;
| Lösung zu Übung 5 | Eine VIEW auf mehrere Tabellen | Zur Übung |
create view Vertrag_Anzeige
( ID, Vertragsnummer, Abschlussdatum, Art,
Mi_Name, Mi_Vorname,
Vn_Name, Vn_Vorname,
Kennzeichen )
as select vv.ID, vv.Vertragsnummer, vv.Abschlussdatum,
CASE vv.Art
WHEN 'TK' THEN 'Teilkasko'
WHEN 'VK' THEN 'Vollkasko'
ELSE 'Haftpflicht'
END,
mi.Name, mi.Vorname,
vn.Name, vn.Vorname,
fz.Kennzeichen
from Versicherungsvertrag vv
join Mitarbeiter mi
on mi.ID = vv.Mitarbeiter_ID
join Versicherungsnehmer vn
on vn.ID = vv.Versicherungsnehmer_ID
join Fahrzeug fz
on fz.ID = vv.Fahrzeug_ID;
Hinweis: Weil die Zusatzangaben Pflicht sind, können wir einheitlich mit INNER JOIN arbeiten.
| Lösung zu Übung 6 | Eine VIEW auf mehrere Tabellen | Zur Übung |
alter view Vertrag_Anzeige
( ID, Vertragsnummer, Abschlussdatum, Art,
Mi_Name, Mi_Vorname,
Vn_Name, Vn_Vorname,
Kennzeichen, Typ, Hersteller, Land )
as select vv.ID, vv.Vertragsnummer, vv.Abschlussdatum,
CASE vv.Art
WHEN 'TK' THEN 'Teilkasko'
WHEN 'VK' THEN 'Vollkasko'
ELSE 'Haftpflicht'
END,
mi.Name, mi.Vorname,
vn.Name, vn.Vorname,
fz.Kennzeichen, fa.Bezeichnung, fa.Hersteller, fa.Land
from Versicherungsvertrag vv
join Mitarbeiter mi
on mi.ID = vv.Mitarbeiter_ID
join Versicherungsnehmer vn
on vn.ID = vv.Versicherungsnehmer_ID
join Fahrzeug fz
on fz.ID = vv.Fahrzeug_ID
join Fahrzeugart fa
on fa.ID = fz.Fahrzeugtyp_ID;
| Lösung zu Übung 7 | Eine View abschnittsweise kontrollieren | Zur Übung |
SELECT * FROM Vertrag_Anzeige
WHERE EXTRACT(YEAR from Abschlussdatum) <= 1990;
| Lösung zu Übung 8 | Eine weitere VIEW auf mehrere Tabellen | Zur Übung |
create view Schaden_Anzeige
( ID, Datum, Gesamtschaden,
Kennzeichen, Typ,
Schadensanteil,
VV_ID )
as select sf.ID, sf.Datum, sf.Schadenshoehe,
fz.Kennzeichen, fz.Fahrzeugtyp_ID,
zu.Schadenshoehe,
vv.ID
from Zuordnung_SF_FZ zu
join Schadensfall sf
on sf.ID = zu.Schadensfall_ID
join Fahrzeug fz
on fz.ID = zu.Fahrzeug_ID
join Versicherungsvertrag vv
on fz.ID = vv.Fahrzeug_ID;
| Lösung zu Übung 9 | Eine weitere VIEW auf mehrere Tabellen | Zur Übung |
alter view Schaden_Anzeige
( ID, Datum, Gesamtschaden,
Kennzeichen, Typ, Hersteller, Land,
Schadensanteil,
VV_ID, Vertragsnummer, VN_ID )
as select sf.ID, sf.Datum, sf.Schadenshoehe,
fz.Kennzeichen, fa.Bezeichnung, fa.Hersteller, fa.Land,
zu.Schadenshoehe,
vv.ID, vv.Vertragsnummer, vv.Versicherungsnehmer_ID
from Zuordnung_SF_FZ zu
join Schadensfall sf
on sf.ID = zu.Schadensfall_ID
join Fahrzeug fz
on fz.ID = zu.Fahrzeug_ID
join Versicherungsvertrag vv
on fz.ID = vv.Fahrzeug_ID
join Fahrzeugart fa
on fa.ID = fz.Fahrzeugtyp_ID;
| Lösung zu Übung 10 | Eine View zur Auswertung einer View | Zur Übung |
create view Schaden_Anzeige_Jahr
as select *
from Schaden_Anzeige
where EXTRACT(YEAR from Datum) = EXTRACT(YEAR from CURRENT_DATE);
Siehe auch
Ergänzende Informationen gibt es in den folgenden Kapiteln:
| Lizenz |
Dieser Text ist sowohl unter der „Creative Commons Attribution/Share-Alike“-Lizenz 3.0 als auch GFDL lizenziert.
Eine deutschsprachige Beschreibung für Autoren und Weiternutzer findet man in den Nutzungsbedingungen der Wikimedia Foundation.